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

Background information

Developers are responsible for protecting databases against SQL injection attacks. Database administrators can prevent only a few types of SQL injection attacks. SQL/Protect detects SQL injection attacks based on query requests. If suspicious query requests are identified, SQL/Protect immediately sends alerts to database administrators and prevents the queries from running.

Types of SQL injection attacks

Attack type Description
Unauthorized relations Administrators can restrict access to tables. This operation is tedious. SQL/Protect provides a learn mode that dynamically tracks the relationship of tables accessed by a user. In learn mode, SQL/Protect can automatically learn which tables an application can be allowed to access for a user or group. When SQL/Protect is in passive mode or active mode, the incoming queries are checked based on the list of learned tables.
Utility commands A common technique used in SQL injection attacks is to run utility commands such as typical DDL statements. For example, a user-defined function is created to access the data of other tables. SQL/Protect can prevent some utility commands from being run. In most cases, these commands are not used in applications.
SQL tautology The most frequent technique used in SQL injection attacks is to issue a tautological WHERE clause. A tautological WHERE clause contains a condition that is always true, such as WHERE password = 'x' OR 'x'='x'. In most cases, attackers use this technique to identify security vulnerabilities. SQL/Protect can block queries that contain a tautological conditional clause.
Unbounded DML statements Unbounded DML statements are database update statements in which no conditions are specified. These statements are UPDATE and DELETE statements that have no WHERE clauses. For example, an attacker may update or delete the passwords of users to initiate a denial-of-service (DoS) attack.

Protected roles

Protected roles are users or groups protected by SQL/Protect. Database administrators can use SQL/Protect to specify protected roles. You can use SQL/Protect to customize different levels of injection attack prevention for different protected roles. The types of SQL injection attacks vary based on the levels.

A role that has the superuser privilege cannot be a protected role. A protected non-superuser role can become a protected superuser role. In this case, SQL/Protect performs operations for the protected superuser role in the following scenarios:
  • SQL/Protect generates an alert for each command run by the protected superuser.
  • When SQL/Protect is in active mode, SQL/Protect blocks all commands run by the protected superuser.

When SQL/Protect is running, a protected role that has the superuser privilege is changed to a common role or restored to an unprotected role.

In addition, each command run by a protected role is recorded in a statistics view. This view helps you identify the start of a potential SQL injection attack against the role. The statistics are collected based on the 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. The maximum number of tables that can be protected is specified by the max_protected_relations parameter.

Use the administrator role to configure SQL/Protect for a database

  1. Modify the parameters in the following code block to enable SQL/Protect.
    set polar_sql_protect.enabled = on; #(The default value is off.)
    set polar_sql_protect.level = passive; #(Valid values: learn, active, and passive. The default value is passive.)
  2. 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');
    View the list of protected roles.
    SELECT * FROM sqlprotect.list_protected_users;
    SELECT * FROM sqlprotect.polar_sql_protect;
  4. Change the mode in which SQL/Protect works based on your requirements.

    SQL/Protect works in three modes: learn, active, and passive. The default mode is passive. For more information, see Configure the mode in which SQL/Protect works to monitor a protected role.

    • Change the mode in which SQL/Protect works to learn.
      polar_sql_protect.level = learn; #(Valid values: learn, active, and passive. The default value is passive.)
      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. View the learned information about tables used by the test user.
        SELECT * FROM sqlprotect.polar_sql_protect_rel;
        SELECT * FROM sqlprotect.list_protected_rels;
    • Change the mode in which SQL/Protect works to passive.
      polar_sql_protect.level = passive; #(Valid values: learn, active, and passive. The default value is passive.)
      1. Log on to the targetdb database as the test user.
      2. Inject SQL statements.
        SELECT * FROM company WHERE 1 = 1;
        DELETE FROM company;
        Note SQL/Protect returns a message that indicates unauthorized SQL statements. However, SQL/Protect does not prevent the SQL statements from being executed.
    • Change the mode in which SQL/Protect works to active.
      polar_sql_protect.level = active; #(Valid values: learn, active, and passive. The default value is passive.)
      1. Log on to the targetdb database as the test user.
      2. Inject SQL statements.
        SELECT * FROM company WHERE 1 = 1;
        DELETE FROM company;
        Note SQL/Protect returns a message that indicates unauthorized SQL statements. SQL/Protect also prevents the SQL statements from being executed.

Configure protected roles

Protected roles are stored in the polar_sql_protect table. The database administrator can choose the users and user groups that are protected, and add the users and user groups to the table.

  • Invoke the protect_role function to add a user to the table.
    SELECT sqlprotect.protect_role('userA');
  • Query the information about the tables that SQL/Protect learned for the protected roles.
    select * from sqlprotect.list_protected_users;
    select * from sqlprotect.polar_sql_protect;
  • Invoke the unprotect_role function to remove a protected role.
    SELECT sqlprotect.unprotect_role('userA');

Configure the mode in which SQL/Protect works to monitor a protected role

The polar_sql_protect.level parameter specifies the mode in which SQL/Protect works to monitor a protected role. The following three modes are available: learn, passive, and active. The default mode is passive.
Work mode Description
learn SQL/Protect tracks the tables that a user accesses and records the tables. This allows you to record the behavior of protected roles.
passive If a protected role attempts to execute an unauthorized SQL statement, SQL/Protect sends an alert but does not prevent the SQL statement from being executed.
active SQL/Protect prevents all unauthorized SQL statements from being executed by protected roles. To prevent the SQL statements from being executed, SQL firewalls take effect when attackers perform penetration tests. SQL/Protect also tracks and queries the SQL statements. This way, administrators can identify database vulnerabilities earlier than attackers.
For example, if you want to change the mode in which SQL/Protect works to active, execute the following statement:
 polar_sql_protect.level = active;  #Set the mode in which SQL/Protect works to active.

To modify some fields in the polar_sql_protect table to specify what need to be protected for a role, execute the following statement:

targetdb=# \d sqlprotect.polar_sql_protect;
        Table "sqlprotect.polar_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: 
   "polar_sql_protect_pkey" PRIMARY KEY, btree (roleid)
For example, if you execute the following statement to set the allow_utility_cmds parameter to TRUE for a protected role named 16480, SQL/Protect blocks the utility commands run by the protected role 16480.
UPDATE sqlprotect.polar_sql_protect SET allow_utility_cmds = TRUE WHERE roleid = 16480; 

Other operations

  • To stop SQL/Protect, execute the following statement:
    polar_sql_protect.enabled = off #(The default value of this parameter is off.)
    polar_sql_protect.level = passive #(Valid values: learn, active, and passive. The default value is passive.)
  • To view statistics about the SQL statements blocked by SQL/Protect, execute the following statement:
    SELECT * FROM sqlprotect.polar_sql_protect_stats;
  • To delete statistics about the SQL statements blocked by SQL/Protect blocks for a specified user, execute the following statement:
    SELECT sqlprotect.drop_stats('username');