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

  4. None
  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%
  45. None
  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%
  47. None
  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):