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 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.
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
Configure protected roles
Protected roles are stored in the polar_spl_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.polar_spl_protect;
- Invoke the unprotect_role function to revert a protected role back to unprotected:
SELECT sqlprotect.unprotect_role('userA');
Configure SQL/Protect operating mode
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. |
polar_spl_protect.level = active; #
You can edit certain fields in the polar_spl_protect table to specify what need to be protected for a role.
targetdb=# \d sqlprotect.polar_spl_protect;
Table "sqlprotect.polar_spl_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_spl_protect_pkey" PRIMARY KEY, btree (roleid)
UPDATE sqlprotect.polar_spl_protect SET allow_utility_cmds = TRUE WHERE roleid = 16480;
Other related operations
- To stop SQL/Protect, execute the following statements:
polar_spl_protect.enabled = off polar_spl_protect.level = passive #
- To display statistics of SQL statements that are blocked by SQL/Protect, execute the
following statement:
SELECT * FROM sqlprotect.polar_spl_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');