SQL/Protect overview v16

SQL/Protect guards against different types of SQL injection attacks.

Types of SQL injection attacks

A number of different techniques are used to perpetrate SQL injection attacks. Each technique is characterized by a certain signature. SQL/Protect examines queries for the following signatures.

Unauthorized relations

While EDB Postgres Advanced Server allows administrators to restrict access to relations (such as tables and views), many administrators don't perform this tedious task. SQL/Protect provides a learn mode that tracks the relations a user accesses.

This mode allows administrators to examine the workload of an application and for SQL/Protect to learn the relations an application is allowed to access for a given user or group of users in a role.

When SQL/Protect is switched to 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 can access other system resources.

SQL/Protect can block running all utility commands that aren't 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 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 running unbounded DML statements. These are UPDATE and DELETE statements with no WHERE clause. For example, an attacker might 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.

Monitoring SQL injection attacks

SQL/Protect can monitor and report on SQL injection attacks.

Protected roles

Monitoring for SQL injection attacks involves analyzing SQL statements originating in database sessions where the current user of the session is a protected role. A protected role is an EDB Postgres Advanced Server user or group that the database administrator chooses to monitor using SQL/Protect. (In EDB Postgres Advanced Server, users and groups are collectively referred to as roles.)

You can customize each protected role for the types of SQL injection attacks it's being monitored for, This approach provides different levels of protection by role and significantly reduces the user-maintenance load for DBAs.

You can't make a role with the superuser privilege a protected role. If a protected non-superuser role later becomes a superuser, certain behaviors occur when that superuser tries to issue any command:

  • SQL/Protect issues a warning message for every command issued by the protected superuser.
  • The statistic in the column superusers of edb_sql_protect_stats is incremented with every command issued by the protected superuser. See Attack attempt statistics for information on the edb_sql_protect_stats view.
  • SQL/Protect in active mode prevents all commands issued by the protected superuser from running.

Either alter a protected role that has the superuser privilege so that it's no longer a superuser, or revert it to an unprotected role.

Attack attempt statistics

SQL/Protect records each use of a command by a protected role that's considered an attack. It collects statistics by type of SQL injection attack, as discussed in Types of SQL injection attacks.

You can access these statistics from the view edb_sql_protect_stats. You can easily monitor this view to identify the start of a potential attack.

The columns in edb_sql_protect_stats monitor the following:

  • username. Name of the protected role.
  • superusers. Number of SQL statements issued when the protected role is a superuser. In effect, any SQL statement issued by a protected superuser increases this statistic. See Protected roles for information about protected superusers.
  • relations. Number of SQL statements issued referencing relations that weren't learned by a protected role. (These relations aren't in a role’s protected relations list.)
  • commands. Number of DDL statements issued by a protected role.
  • tautology. Number of SQL statements issued by a protected role that contained a tautological condition.
  • dml. Number of UPDATE and DELETE statements issued by a protected role that didn't contain a WHERE clause.

These statistics give database administrators the chance to react proactively in preventing theft of valuable data or other malicious actions.

If a role is protected in more than one database, the role’s statistics for attacks in each database are maintained separately and are viewable only when connected to the respective database.

Note

SQL/Protect statistics are maintained in memory while the database server is running. When the database server is shut down, the statistics are saved to a binary file named edb_sqlprotect.stat in the data/global subdirectory of the EDB Postgres Advanced Server home directory.

Attack attempt queries

Each use of a command by a protected role that's considered an attack by SQL/Protect is recorded in the edb_sql_protect_queries view, which contains the following columns:

  • username. Database user name of the attacker used to log into the database server.
  • ip_address. IP address of the machine from which the attack was initiated.
  • port. Port number from which the attack originated.
  • machine_name. Name of the machine from which the attack originated, if known.
  • date_time. Date and time when the database server received the query. The time is stored to the precision of a minute.
  • query. The query string sent by the attacker.

The maximum number of offending queries that are saved in edb_sql_protect_queries is controlled by the edb_sql_protect.max_queries_to_save configuration parameter.

If a role is protected in more than one database, the role’s queries for attacks in each database are maintained separately. They are viewable only when connected to the respective database.