Read past the headlines about some of the worst data breaches and you’ll find a SQL injection attack. SQL injections has been used to hack U.S. government systems, major retailers, even the World Trade Organization. More recently, the so-called Panama Papers data leak has been attributed to a SQL injection attack. Since the 1990s, SQL injection attacks remain the go-to hack despite efforts to shore up entry points.
DBAs are in a difficult position when battling these attacks. They must ensure applications have access to data in order to function properly, yet they typically have little or no control over the threat of a SQL injection attack. Preventing them has traditionally been the responsibility of the application developer who weaves protections into the application design.
Fortunately, there is a weapon, made especially for the DBA. EnterpriseDB® (EDB™) arms DBAs with the EDB Postgres™ SQL/Protect, a solution unique to the database industry. SQL/Protect provides a layer of security in addition to the normal database security policies that examines incoming queries for common SQL injection profiles, even ‘learning’ application patterns to avoid being ‘tricked.’
EDB Postgres SQL/Protect gives the control back to the database administrator by blocking suspicious queries and alerting the DBA.
Types of SQL Injection Attacks
Before exploring EDB Postgres SQL/Protect, let’s explore the attack methods. SQL injection attacks occur by inserting a SQL statement into the database through an unprotected path, such as an entry field on a PHP application or web form. The system runs the SQL statement without checking for authorization and provides clues as to the content, structure, or security of the database. At times, the SQL statement instructs the database to perform tasks, like delete, change, or release information.
There are a number of different techniques used to perpetrate SQL injection attacks. Each technique is characterized by a certain signature. For example, EDB Postgres SQL/Protect examines queries for the following signatures:
1. Unauthorized Relations: EDB’s database, EDB Postgres Advanced Server, allows administrators to restrict access to relations (tables, views, etc.), but many administrators do not perform the tedious tasks required to do so. SQL/Protect provides a learn mode that tracks the relations a user accesses. This allows administrators to examine the workload of an application, and for SQL/Protect to learn which relations an application should be allowed to access for a given user or group of users in a role. When SQL/Protect is switched to either passive or active mode, the incoming queries are checked against the list of learned relations.
2. Utility Commands: A common technique used in SQL injection attacks is to run utility commands, which are typically SQL Data Definition Language (DDL) statements. An example would be to create a user-defined function that has the ability to access other system resources. SQL/Protect can block the running of all utility commands, which are not normally needed during standard application processing.
3. SQL Tautology: The most frequent technique used in SQL injection attacks is issuing a tautological WHERE clause condition (that is, using a condition that is always true).
The following is an example:
WHERE password = 'x' OR 'x'='x'
Attackers will usually start identifying security weaknesses using this technique. SQL/Protect can block queries that use a tautological conditional clause.
4. Unbounded DML Statements: A dangerous action taken during SQL injection attacks is the running of unbounded DML (Data Manipulation Language) statements. These are UPDATE and DELETE statements with no WHERE clause. For example, an attacker may update all users’ passwords to a known value or initiate a denial of service attack by deleting all of the data in a key table.
Configuring SQL/Protect
EDB Postgres SQL/Protect is part of the EDB Postgres Platform. The EDB Postgres Platform is an integrated open source-based database management platform; it integrates EDB’s mature, enterprise-ready Postgres database with other leading data management solutions to ensure DBAs have the tools they needs to manage complex architectures. (To learn more, click here.)
EDB Postgres SQL/Protect is installed automatically when installing the database, whether it’s EDB Postgres Advanced Server or PostgreSQL, which EDB customers may choose instead of Advanced Server in customizing the EDB Postgres Platform. The EDB Postgres installer places the .so and .sql files at the following locations by default:
/opt/PostgresPlus/9.5AS/lib/sqlprotect.so
/opt/PostgresPlus/9.5AS/share/contrib/sqlprotect.sql
-- Note that in step 5 below you will need to run the .sql file.
Please note that the steps assume that EDB Postgres Advanced Server is running at port 5555.
1. SQL Protect needs some changes in postgresql.conf
shared_preload_libraries = '.....,$libdir/sqlprotect'
edb_sql_protect.enabled = on
edb_sql_protect.level = active
2. Restart the server
3. Create a test database
./createdb test -p 5555
4. Create a test user
./createuser --port=5555 --createdb ali
5. Create SQL/Protect objects
./psql test -p 5555
drop schema sqlprotect cascade;
\i /opt/PostgresPlus/9.5AS/share/contrib/sqlprotect.sql
6. Create a test table
create table protected (a int, b int, c int, d int);
7. Protect the test user
SELECT sqlprotect.protect_role('ali');
8. Let the protected user access all relations
update sqlprotect.edb_sql_protect set protect_relations = false;
9. Login by user ali and fire some queries
a. Blocked due to SQL Tautology
update protected set b = b + 1234 where c = c;
ERROR: SQLPROTECT: Illegal Query: tautology
b. Blocked due to Unbounded DML
update protected set b = b + 22;
delete from protected;
ERROR: SQLPROTECT: Illegal Query: empty DML
c. Blocked due to Unauthorized Utility Command
create table cant_do(a int, b int);
ERROR: SQLPROTECT: This command type is illegal for this user
10. Check attack attempt statistics
select * from sqlprotect.edb_sql_protect_stats;
username | superusers | relations | commands | tautology | dml
----------+------------+-----------+----------+-----------+-----
ali | 0 | 0 | 1 | 1 | 2
(1 row)
- Check attack attempt queries
select username, date_time, query from sqlprotect.edb_sql_protect_queries;
username | date_time | query
----------+---------------------------+------------------------------------------------
ali | 16-MAY-16 22:45:00 +05:00 | update protected set b = b + 1234 where c = c;
ali | 16-MAY-16 22:45:00 +05:00 | update protected set b = b + 22;
ali | 16-MAY-16 22:46:00 +05:00 | delete from protected;
ali | 16-MAY-16 22:46:00 +05:00 | create table cant_do(a int, b int);
(4 rows)
Armed with the new SQL injection weapon, your database environment will be more secure. With EDB Postgres SQL/Protect, your EDB Postgres database will screen incoming queries for common attack profiles, learning to accept ‘friendly’ queries and reject strangers while you manage this layer of security from a central location. For more information on how EDB Postgres SQL/Protect can support your needs, please contact us at sales@enterprisedb.com.
Abbas Butt is a Senior Architect at EnterpriseDB.