Building SQL firewall: insights from developers

How SQL firewalls can help to protect databases from SQL injections: the main difference from WAFs, common usage scenarios, pros, and cons. Developing SQL firewall is a hard task – we will share insights about parsing SQL protocols, matching rules, hidden dangers of logging, best of configuration and usage patterns.

Transcript

1. Building SQL firewall: insights from developers
2. Artem Storozhuk Security software engineer at Cossack Labs dev@cossacklabs.com
3. This is what OWASP says about SQL injections:
5. Databases and SQL injections
6. 1 always equals 1! Databases and SQL injections
7. How to prevent SQL Injections? 1) Follow security practices when writing code
8. How to prevent SQL Injections? 1) Follow security practices when writing code
9. How to prevent SQL Injections? 1) Follow security practices when writing code INPUT VALIDATION
10. How to prevent SQL Injections? 1) Follow security practices when writing code INPUT VALIDATION
11. How to prevent SQL Injections? 1) Follow security practices when writing code INPUT VALIDATION
12. How to prevent SQL Injections? 1) Follow security practices when writing code 2) Evaluate application with static analyzers and vulnerability scanners: - white-box: - Awesome Static Analysis (https://matthias-endler.de/awesome-static-analysis/) [1] - https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet; [2] - https://tableplus.io/blog/2018/08/best-practices-to-prevent-sql-injection-attacks.html [3] - https://security.stackexchange.com/questions/1257/best-practises-for-preventing-sql-injection
13. How to prevent SQL Injections? 1) Follow security practices when writing code 2) Evaluate application with static analyzers and vulnerability scanners: - white-box: - Awesome Static Analysis (https://matthias-endler.de/awesome-static-analysis/) - black-box: - SQLMap (http://sqlmap.org/); - w3af (http://w3af.org/).
14. How to prevent SQL Injections? 1) Follow security practices when writing code 2) Evaluate application with static analyzers and vulnerability scanners 3) Use application level firewalls (WAFs): - ModSecurity (https://modsecurity.org/); - NAXSI (https://github.com/nbs-system/naxsi). Database Application
15. Common Web Application Firewall features 1) Implementing input/output traffic filtering policy.
16. Common Web Application Firewall features 1) Implementing input/output traffic filtering policy. 2) Events logging.
17. Common Web Application Firewall features 1) Implementing input/output traffic filtering policy. 2) Events logging. 3) Supporting SSL/TLS encryption protocol.
18. Common Web Application Firewall features 1) Implementing input/output traffic filtering policy. 2) Events logging. 3) Supporting SSL/TLS encryption protocol. 4) Defending against threats that target WAF itself.
19. Some known WAF’s limitations 1) False negatives – some specific SQL injections can’t be detected From: https://pdfs.semanticscholar.org/9f03/89ad1feea03e43e0626cd02eb5d2483ac6b4.pdf
20. Some known WAF’s limitations 1) False negatives – some specific SQL injections can’t be detected From: https://pdfs.semanticscholar.org/9f03/89ad1feea03e43e0626cd02eb5d2483ac6b4.pdf
21. Some known WAF’s limitations 1) False negatives – some specific SQL injections can’t be detected 2) False positives
22. Some known WAF’s limitations 1) False negatives – some specific SQL injections can’t be detected 2) False positives 3) Can’t protect against insiders Database Application
23. Database proxy as additional line of defence Database Application Database proxy
24. Database proxy as additional line of defence Database Application Database proxy
25. Open source database proxies Firewalls 1) Snort https://www.snort.org/ 2) GreenSQL https://github.com/larskanis/green sql-fw 3) Apache-scalp https://github.com/nanopony/apac he-scalp Load-balancers 1) ProxySQL – with firewall ability in open-source version https://proxysql.com/ 2) HAProxy – with firewall ability in enterprise version http://www.haproxy.org/
26. Database firewall advantage Malicious url with SQL injection: http://localhost/index.php?page=user-info.php&username=qwerty%27+OR+6%3D6+--&password=&user -info-php-submit-button=View+Account+Details
27. Database firewall advantage Malicious url with SQL injection: http://localhost/index.php?page=user-info.php&username=qwerty%27+OR+6%3D6+--&password=&user -info-php-submit-button=View+Account+Details HTML source file fragment with SQL injection:
28. Database firewall advantage Malicious url with SQL injection: http://localhost/index.php?page=user-info.php&username=qwerty%27+OR+6%3D6+--&password=&user -info-php-submit-button=View+Account+Details HTML source file fragment with SQL injection: SQL query with injection: SELECT * FROM accounts WHERE username='qwerty' OR 6=6 -- ' AND password=''
29. Database firewall advantage Malicious url with SQL injection: http://localhost/index.php?page=user-info.php&username=qwerty%27+OR+6%3D6+--&password=&user -info-php-submit-button=View+Account+Details HTML source file fragment with SQL injection: SQL query with injection: SELECT * FROM accounts WHERE username='qwerty' OR 6=6 -- ' AND password='' input for WAF input for Database firewall
30. Acra database firewall – AcraCensor 1) Open-source. Written on Go. 2) Comes as part of Acra suite (encryption + access control + intrusion detection + SQL firewall) https://github.com/cossacklabs/acra 3) Supports MySQL and PostgreSQL databases. 4) Supports logging (query_capture) and data masking. 5) Simple conventional security policy configuring: - whitelist (allow); - blacklist (deny); that can be overridden with exception (query_ignore)
31. Acra database firewall – AcraCensor Blacklist policy example Whitelist policy example
32. Pattern matching SELECT INSERT UNION UPDATE DELETE VALUE LIST_OF_VALUES SUBQUERY WHERE COLUMN rule SELECT %%COLUMN%%, %%COLUMN%% FROM company matching queries SELECT users, cats FROM company SELECT a, b FROM company non-matching queries SELECT users FROM company SELECT users, cats, chameleons FROM company SELECT users, cats, chameleons FROM company SELECT users, cats FROM zoo patterns
33. Flexible configuration: allow – denyall handlers: - handler: allow queries: tables: patterns: - SELECT * FROM accounts WHERE username=%%VALUE%% AND password=%%VALUE%% OR %%VALUE%%=%%VALUE%% - SELECT * FROM accounts WHERE username=%%VALUE%% OR %%VALUE%%=%%VALUE%% -- ' AND password=%%VALUE%% - "%%UNION%%" - handler: denyall
34. How does it work? IN RUNTIME ONCE ON START ON DEVELOPMENT
35. Problems we encountered while development 1. SQL syntax differences in MySQL and PostgreSQL: • RETURNING keyword; • Interpretation of double quoted identifiers: “tableName” | “columnName”; • JSON; • Full Text Search; • Prepared Statements (prepare FROM vs prepare AS); • Common table expressions (set up names for queries); • Typecasting (P: column_name::type_name, M: column_name type_name). http://mwiki.gichd.org/IM/Difference_MySQL_PostGreSQL https://www.postgresql.org/docs/10/features.html – difference between PostgreSQL and ANSI SQL
36. Problems we encountered while development 2. SQLParser (https://github.com/xwb1989/sqlparser) is good, but is not perfect: - supports limited set of queries (no Prepared Statements, Stored Procedures, etc.); - latest commit on June 6, 2018.
37. Performance evaluation Database Acra proxy measures.go Sending in sequence SQL query: `select * from pg_catalog.pg_tables where tablespace='pg_global'` for 60 seconds Profiling Acra proxy with standard tool `go tool pprof` security_config.yaml PC details : Intel Core i7 (12 cores) - 4000 MHz, 16 GB RAM, OS Ubuntu 18.04 LTS x64; Database: PostgreSQL 9.5.
38. Performance evaluation (results) No Acra Test № Handled queries (for 60 seconds) 1 142552 2 143178 3 141310 4 139669 5 143135 6 140792 7 139454 8 136989 9 139875 10 140254 Average: ~2345,35 per second
39. Performance evaluation (results) Acra, no firewall Test № Handled queries (for 60 seconds) 1 81902 2 82699 3 81647 4 81911 5 82302 6 83492 7 83650 8 82769 9 82651 10 82033 Called function CPU consumption profile (in %) acra(*PgProxy) PgDecryptStream 71,74 acra(*PgProxy) PgProxyClientRequests: 14,33 runtime mcall 4,07 runtime mstart 4,91 runtime gcBgMarkWorker 2,51 Average: ~1375,09 per second
40. Performance evaluation (results) Acra firewall heavy security configuration: Test № Handled queries (for 60 seconds) 1 72853 2 73487 3 73174 4 73279 5 72694 6 73097 7 72993 8 73264 9 73158 10 72747 Average: ~ 1217,91 per s Called function CPU consumption profile (in %) acra(*PgProxy) PgDecryptStream 55,74 acra(*PgProxy) PgProxyClientRequests: - acra(*AcraCensor) HandleQuery 27,54: - 14,87 runtime mcall 4,69 runtime mstart 4,95 gcBgMarkWorker 4,04
41. Performance evaluation (results) Type of security configuration Query processing speed Overhead No Acra 2345,35 queries per second ~ No SQL firewall 1375,09 queries per second 41,37% SQL firewall with heavy security configuration 1217,91 queries per second 48,07% (+6,7%) With heavy security configuration Acra’s firewall adds 6,7% overhead.
42. Conclusions 1) High level of security against SQL injections in modern databases can be achieved only with combination of measures: - using security practices while application development, infrastructure and database management; - using additional security tools; - testing with existing vulnerability scanners. 2) Use Database firewall to strengthen WAF abilities to prevent some specific SQL injections;
43. Thank you! Any questions?
44. Performance evaluation (results) Acra firewall security configuration: Test № Handled queries (for 60 seconds) 1 83703 2 84043 3 83970 4 83256 5 84291 6 83387 7 84344 8 84728 9 84525 10 84467 Average: ~ 1401,19 per second Total CPU consumption: < 28%
46. Performance evaluation (results) with GOMAXPROC = 1 Acra firewall security configuration: Test № Handled queries (for 60 seconds) 1 1488 2 1489 3 1489 4 1488 5 1489 6 1489 7 1489 8 1489 9 1489 10 1489 Average: ~24,81 per second Total CPU consumption: < 11%
48. Performance evaluation (results) 1) A htop results while profiling firewall with logging:
49. Performance evaluation (results) 1) A htop results while profiling firewall with logging: 2) A htop results while profiling firewall with logging (with GOMAXPROCS = 1):