SQL injection engine

Please note: the project WIKI documentation has been moved to the ProxySQL website

---

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

ProxySQL 2.0.9 embed libsqlinjection as a mechanism to identify possible SQL injection attack.

Enabling SQL injection detection

To enable SQL injection detection it is required to just 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 injection . 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 already 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 SQL injection engine
• if firewall whitelist is enabled, the queries analyzed by the SQL injection engine are only the queries not whitelisted and that would be executed for users with mode DETECTING. In fact:
  • for users with mode OFF , all queries are considered whitelisted
  • for users with mode PROTECTING, queries not explicitly whitelisted are rejected

Table mysql_firewall_whitelist_sqli_fingerprints

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

Although libsqlinjection is able to detect a lot of SQL injection, unfortunately it generates also 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 SQL injection engine), an this is the best approach to reduce false positive.

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 matching the specified fingerprint.

Currently, the only way to know a fingerprint is 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 pinkys_dbu@192.168.56.104 . Query listed below:
SELECT * FROM users WHERE username='asdsad' AND password='e2a521bc01c1ca09e173bcf65bcc97e9'

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

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

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

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 here.

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