This topic describes how to use the SQL/Protect plug-in to protect databases against SQL injection attacks.

Background information

Developers are responsible for protecting databases against SQL injection attacks. They can use SQL/Protect to examine query requests and check for SQL injection. If suspicious query requests are identified, SQL/Protect immediately issues warning messages to database administrators while blocking the execution of the queries.

Types of SQL injection attacks

Attack type What SQL/Protect can do
Unauthorized relations While administrators can restrict access to relations (for example, tables and views), some of them do not perform this tedious task. SQL/Protect provides a learn mode that dynamically tracks the relations that a user accesses. This allows administrators to examine the workload of an application, and for SQL/Protect to learn which relations an application can be allowed to access for a given user or group of users in a role. When SQL/Protect is switched to the passive or active mode, the incoming queries are checked against the list of learned relations.
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 is creating 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.
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 usually start identifying security weaknesses by using this technique. SQL/Protect can block queries that use a tautological conditional clause.
Unbounded DML statements A dangerous action taken during SQL injection attacks is the running of unbounded DML statements. These are UPDATE and DELETE statements with no WHERE clauses. For example, an attacker may update all users' passwords to a known value or initiate a denial of service (DoS) attack by deleting all of the data in a key table.

Protected roles

A protected role is a user or group that the database administrator has chosen to monitor by using SQL/Protect. Each protected role can be customized for the types of SQL injection attacks for which it is to be monitored, thus providing different levels of protection by role.

A role with the superuser privilege cannot be made a protected role. If a protected non-superuser role is subsequently altered to become a superuser, certain behaviors are exhibited whenever an attempt is made by that superuser to issue any command:
  • A warning message is issued by SQL/Protect on every command issued by the protected superuser.
  • When SQL/Protect is in active mode, all commands issued by the protected superuser are prevented from running.

A protected role that has the superuser privilege must either be altered so that it is no longer a superuser, or it must be reverted back to an unprotected role.

Additionally, every command issued by a protected role is recorded incrementally in a statistics view. This view helps to identify the start of a potential SQL injection attack against the role. The statistics are collected by type of SQL injection attack.

Note By default, each database supports up to 64 protected roles and up to 1024 protected tables. The maximum number of roles that can be protected is specified by the max_protected_roles parameter, and the maximum number of tables that can be protected is specified by the max_protected_relations parameter.

Configure SQL/Protect as an administrator to monitor a database

  1. Edit the following parameters to start SQL/Protect:
    edb_sql_protect.enabled = on # (This parameter is set to off by default.)
    edb_sql_protect.level = passive # (SQL/Protect has three modes: learn, active, and passive. The default mode is passive.)
  2. Execute the following statements to create a test database named targetdb and a test user named test:
    CREATE DATABASE targetdb;
    CREATE ROLE test;
    GRANT ALL ON DATABASE targetdb TO test;
    ALTER ROLE test LOGIN;
  3. Log on to the test database targetdb. Then, execute the following statements to create SQL/Protect and add protected roles:
    CREATE EXTENSION sqlprotect;
    SELECT sqlprotect.protect_role('test');
    Execute the following statements to display the list of protected roles:
    SELECT * FROM sqlprotect.list_protected_users;
    SELECT * FROM sqlprotect.edb_sql_protect;
  4. Change the operating mode of SQL/Protect as needed.

    SQL/Protect operates in three modes: learn, active, and passive. The default mode is passive. For more information, see Configure SQL/Protect operating mode.

    • Switch SQL/Protect to the learn mode:
      edb_sql_protect.level = learn; #
      1. Log on to the targetdb database as the test user. Then, create a test table named company, and execute the SELECT and INSERT statements:
        CREATE TABLE company(name VARCHAR(100), employee_num INT);
        SELECT * FROM company;
        INSERT INTO company VALUES('new', 1);
        SELECT * FROM company;
      2. Execute the following statements to display statistics about the test user accessing tables:
        SELECT * FROM sqlprotect.edb_sql_protect_rel;
        SELECT * FROM sqlprotect.list_protected_rels;
    • Switch SQL/Protect to the passive mode:
      edb_sql_protect.level = passive; #
      1. Log on to the targetdb database as the test user.
      2. Execute the following statements to inject SQL statements:
        SELECT * FROM company WHERE 1 = 1;
        DELETE FROM company;
        Note SQL/Protect displays a message suggesting unauthorized SQL statements, but does not block the execution of the SQL statements.
    • Switch SQL/Protect to the active mode:
      edb_sql_protect.level = active; #
      1. Log on to the targetdb database as the test user.
      2. Execute the following statements to inject SQL statements:
        SELECT * FROM company WHERE 1 = 1;
        DELETE FROM company;
        Note SQL/Protect displays a message suggesting unauthorized SQL statements, and blocks the execution of the SQL statements.

Configure protected roles

Protected roles are stored in the edb_sql_protect table. The database administrator can choose the users and user groups to be protected and add them to the table.

  • Invoke the protect_role function to add a user to the table:
    SELECT sqlprotect.protect_role('userA');
  • Execute the following statements to display the protected roles in the table:
    select * from sqlprotect.list_protected_users;
    select * from sqlprotect.edb_sql_protect;
  • Invoke the unprotect_role function to revert a protected role back to unprotected:
    SELECT sqlprotect.unprotect_role('userA');

Configure SQL/Protect operating mode

The edb_sql_protect.level parameter specifies in which mode SQL/Protect operates to monitor a protected role. Three modes are available: learn, passive, and active. The default mode is passive.
Operating mode Description
learn SQL/Protect tracks the relations that a user accesses to learn which relations an application can be allowed to access for a given user or group of users in a role.
passive SQL/Protect monitors every SQL statement that is to be executed. When a protected role attempts to execute an unauthorized SQL statement, SQL/Protect issues a warning message, but does not block the execution of the SQL statement.
active SQL/Protect monitors every SQL statement that is to be executed, and blocks the execution of all unauthorized SQL statements from protected roles by using SQL firewalls when attackers start to perpetrate attacks. Additionally, SQL/Protect tracks the unauthorized SQL statements to help administrators identify database weaknesses ahead of attackers.
For example, if you want to switch SQL/Protect to the active mode, execute the following statement:
 edb_sql_protect.level = active;  #

You can edit certain fields in the edb_sql_protect table to specify what need to be protected for a role.

targetdb=# \d sqlprotect.edb_sql_protect;
        Table "sqlprotect.edb_sql_protect"
      Column       |  Type   | Collation | Nullable | Default 
--------------------+---------+-----------+----------+--------- 
dbid               | oid     |           | not null |  
roleid             | oid     |           | not null |  
protect_relations  | boolean |           |          |  
allow_utility_cmds | boolean |           |          |  
allow_tautology    | boolean |           |          |  
allow_empty_dml    | boolean |           |          | 
Indexes: 
   "edb_sql_protect_pkey" PRIMARY KEY, btree (roleid)
For example, if you have executed the following statement to set the allow_utility_cmds parameter to TRUE for a protected role named 16480, then SQL/Protect blocks the running of the utility commands issued from the protected role 16480:
UPDATE sqlprotect.edb_sql_protect SET allow_utility_cmds = TRUE WHERE roleid = 16480; 

Other related operations

  • To stop SQL/Protect, execute the following statements:
    edb_sql_protect.enabled = off
    edb_sql_protect.level = passive #
  • To display statistics of SQL statements that are blocked by SQL/Protect, execute the following statement:
    SELECT * FROM sqlprotect.edb_sql_protect_stats;
  • To delete statistics of SQL statements that are blocked by SQL/Protect for a specified user, execute the following statement:
    SELECT sqlprotect.drop_stats('username');