ProxySQL does not propagate SQL mode correctly and confuse mysql_real_escape_string()

[pali]

ProxySQL does not propagate current SQL mode via MySQL network protocol to the C MySQL library client. SQL mode is sent in Server Status field of MySQL packet.

SQL mode is used by more C MySQL library functions, like mysql_real_escape_string() or mysql_real_escape_string_quote(). See long description at:

https://dev.mysql.com/doc/refman/8.0/en/mysql-real-escape-string.html https://dev.mysql.com/doc/refman/8.0/en/mysql-real-escape-string-quote.html https://mariadb.com/kb/en/library/mysql_real_escape_string/

When SQL mode specified in MySQL packet sent by ProxySQL does not match real and correct SQL mode on MySQL or MariaDB server, then above functions starts escaping arguments incorrectly. So it may open security hole for SQL injections as mysql_real_escape_string() and mysql_real_escape_string_quote() stop working and incorrectly escape untrusted parameters for SQL code.

This problem was detected by test suite of Perl's DBI database driver DBD::MariaDB.

When DBD::MariaDB test 45bind_no_backslash_escapes.t is run directly against MariaDB or MySQL server it passes under more then 100 different configurations on Travis.

And when is run against ProxySQL 1.4.12 on CentOS 7.5 (with MariaDB 5.5.60 behind) it always fails that C MySQL library incorrectly escapes supplied string argument because ProxySQL incorrectly propagates SQL mode.

Error output from that test:

#   Failed test at t/45bind_no_backslash_escapes.t line 33.
#          got: ''string\\string\"string\'string''
#     expected: ''string\string"string''string''

In attachment test-sql-mode.c.gz is alternative and simplified program in C which uses C MySQL client which verifies that received Server Status packet matches to what is C MySQL client expecting. When run directly against MariaDB server, verification passed. When run with ProxySQL in the middle, it fails.

Compile it as:

$ gcc test-sql-mode.c -o test-sql-mode -W -Wall -O2 `mysql_config --libs --cflags`

And run:

./test-sql-mode 127.0.0.1 6033 user pass

As this problem affects results of mysql_real_escape_string() and mysql_real_escape_string_quote() functions used for untrusted input, it may lead to SQL injections and therefore is security related.

[pali]

Hi! Can you look at this issue?

Or should I look at possibility if there is really a SQL injection in ProxySQL?

[pali]

Now I have finally working SQL injection. Do you want me to submit it privately?

[renecannao]

Yes please! Thanks

[pali]

Ok, where should I report it? Do you use any PGP key for encryption?

[renecannao]

PGP public key attached. email address is obvious from filename :-) rene.cannao@gmail.com---public-key.gpg.gz

Thanks

[pali]

I sent encrypted email with details and POC application.

[renecannao]

Fixed in 2.0.4

[ignatenkobrain]

Any chance to backport this to 1.4.x?

[renecannao]

@ignatenkobrain: We are trying to deprecate 1.4.x , and only maintain it for customers