SQL injection is an attack technique to execute SQL statements inserted as a part of text fields.

ProxySQL 2.0.9 embeds libsqlinjection as a mechanism to identify possible SQL injection attacks.

Enabling SQL injection detection

To enable SQL injection detection it is only required to enable the variable mysql-automatic_detect_sqli .

SQL injection detection and firewall whitelist

Even if mysql-automatic_detect_sqli is enabled, not all the queries are checked to identify possible SQL injections. All the queries that have been explicitly allowed by firewall whitelist are not processed by the SQL injection engine. That is: whitelisted queries are considered safe, as they have already been explicitly authorized.

In other words, if automatic detection of SQL injection is enabled (mysql-automatic_detect_sqli=1):

  • if firewall whitelist is disabled, all queries are analyzed by the SQL injection engine
  • if firewall whitelist is enabled, the queries analyzed by the SQL injection engine are only the queries that are not whitelisted and that would be executed for users with mode set to DETECTING. In fact:
    • for users with mode set to OFF , all queries are considered whitelisted
    • for users with mode set to PROTECTING, queries not explicitly whitelisted are rejected

Table mysql_firewall_whitelist_sqli_fingerprints

The way libsqlinjection works (in a nutshell) is that it parses the query, it generates a fingerprint of the query, and it determines whether the fingerprint is a known SQL injection attack, or a possible attack.

Although libsqlinjection is able to detect a lot of SQL injections, unfortunately it also generates a lot of false positives.

False positives can be greatly reduced combining SQL injection detection with firewall whitelist (all the queries that are whitelisted are not processed by the SQL injection engine), an this is the best approach to reduce the number of false positives.

A different approach to reduce the number of false positive is to whitelist some fingerprints generated by libsqlinjection. It is possible to whitelist fingerprints in table mysql_firewall_whitelist_sqli_fingerprints :

CREATE TABLE mysql_firewall_whitelist_sqli_fingerprints (
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    fingerprint VARCHAR NOT NULL,
    PRIMARY KEY (fingerprint) )
  • active defines if the entry is active or not
  • fingerprint is the fingerprint generated by libsqlinjection that we want to (temporarily) disable , allowing queries that match the specified fingerprint

Currently, the only way to know a fingerprint is via the error log, where ProxySQL will print the fingerprint and the failed query. For example:

2019-11-28 16:17:23 MySQL_Session.cpp:3323:handler(): [ERROR] SQLinjection detected with fingerprint of 'Eoknk' from client [email protected] . Query listed below:
SELECT * FROM users WHERE username='asdsad' AND password='e2a521bc01c1ca09e173bcf65bcc97e9'

If we recognize this as a valid query, we can whitelist it using the mysql_firewall_whitelist_rules .

On the other hand, we can also temporarily prevent this fingerprint from causing libsqlinjection to block the query:

INSERT INTO mysql_firewall_whitelist_sqli_fingerprints
VALUES
(1, 'Eoknk');

The table mysql_firewall_whitelist_sqli_fingerprints is part of the mysql firewall, therefore it is loaded at runtime using the same command used for firewall whitelist: LOAD MYSQL FIREWALL TO RUNTIME .

For a complete list of commands, refer to here.

The firewall whitelist SQL injection fingerprints table (mysql_firewall_whitelist_sqli_fingerprints) has a runtime table too, following the naming convention of other configuration tables. Its name is runtime_mysql_firewall_whitelist_sqli_fingerprints .