Building SQL firewall: insights from developers

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.

C4b182066abd8ed2a260fd1f243f25ec?s=128

Artem Storozhuk

February 02, 2019
Tweet

Transcript

  1. 4.
  2. 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
  3. 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/).
  4. 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
  5. 17.

    Common Web Application Firewall features 1) Implementing input/output traffic filtering

    policy. 2) Events logging. 3) Supporting SSL/TLS encryption protocol.
  6. 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.
  7. 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
  8. 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
  9. 21.

    Some known WAF’s limitations 1) False negatives – some specific

    SQL injections can’t be detected 2) False positives
  10. 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
  11. 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/
  12. 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=''
  13. 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
  14. 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)
  15. 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
  16. 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
  17. 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
  18. 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.
  19. 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.
  20. 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
  21. 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
  22. 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
  23. 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.
  24. 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;
  25. 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%
  26. 45.
  27. 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%
  28. 47.
  29. 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):