Protect your MySQL Database
How To
ProxySQL: firewall
This site is created as a demo to my Percona Live's Online presentation "Creating OpenSource SQL Firewall". We use ProxySQL whitelist firewall feature to protect MySQL from sql injections (aka "bobby drop tables").
Course of action:
Train:
collect all good SQL queries (digest)
create whitelist
Test: set mode to DETECTING and check error log
Protect: disallow everything except for “whitelist”
(set mode to PROTECTING)
Training
Admin> select username,schemaname, digest, digest_text from stats_mysql_query_digest;
+----------+------------+--------------------+----------------------------------------+
| username | schemaname | digest | digest_text |
+----------+------------+--------------------+----------------------------------------+
| root | books | 0x4F5409F6260C29DB | SELECT * FROM books WHERE published=?; |
| root | books | 0xEA3B9B4F6B08A3C0 | SELECT * FROM books WHERE id=?; |
| root | books | 0x631F24A2FB9B82E0 | SET AUTOCOMMIT = ? |
+----------+------------+--------------------+----------------------------------------+
3 rows in set (0.00 sec)
Detecting
Testing
$ tail /var/lib/proxysql/proxysql.log
2020-05-17 19:11:08 Query_Processor.cpp:1742:process_mysql_query(): [WARNING] Firewall detected unknown query with digest 0x2D63306C4FDC72DF from user root@172.18.0.2
Protection
Admin> update mysql_firewall_whitelist_users set mode = 'PROTECTING' where username = 'root';
Admin> LOAD MYSQL FIREWALL TO RUNTIME;
Now we should expect errors on all unknown queries:
Screen Shots
I have created a simple POC web interface to simplify working with ProxySQL firewall. Switching from OFF to DETECTING to PROTECTING and will load collected query digests to firewall rules.
