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
   

   View Slide

2. Artem Storozhuk
   Security software engineer at
   Cossack Labs
   dev@cossacklabs.com
   

   View Slide

3. This is what OWASP says about SQL injections:
   

   View Slide

4. View Slide

5. Databases and SQL injections
   

   View Slide

6. 1 always equals 1!
   Databases and SQL injections
   

   View Slide

7. How to prevent SQL Injections?
   1) Follow security practices when writing code
   

   View Slide

8. How to prevent SQL Injections?
   1) Follow security practices when writing code
   

   View Slide

9. How to prevent SQL Injections?
   1) Follow security practices when writing code
   INPUT VALIDATION
   

   View Slide

10. How to prevent SQL Injections?
    1) Follow security practices when writing code
    INPUT VALIDATION
    

    View Slide

11. How to prevent SQL Injections?
    1) Follow security practices when writing code
    INPUT VALIDATION
    

    View Slide

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
    

    View Slide

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/).
    

    View Slide

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
    

    View Slide

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

    View Slide

16. Common Web Application Firewall features
    1) Implementing input/output traffic filtering policy.
    2) Events logging.
    

    View Slide

17. Common Web Application Firewall features
    1) Implementing input/output traffic filtering policy.
    2) Events logging.
    3) Supporting SSL/TLS encryption protocol.
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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
    

    View Slide

21. Some known WAF’s limitations
    1) False negatives – some specific SQL injections can’t be detected
    2) False positives
    

    View Slide

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
    

    View Slide

23. Database proxy as additional line of defence
    Database
    Application
    Database proxy
    

    View Slide

24. Database proxy as additional line of defence
    Database
    Application
    Database proxy
    

    View Slide

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/
    

    View Slide

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
    

    View Slide

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:
    

    View Slide

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=''
    

    View Slide

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
    

    View Slide

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)
    

    View Slide

31. Acra database firewall – AcraCensor
    Blacklist policy example
    Whitelist policy example
    

    View Slide

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
    

    View Slide

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
    

    View Slide

34. How does it work?
    IN RUNTIME
    ONCE ON
    START
    ON DEVELOPMENT
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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;
    

    View Slide

43. Thank you! Any questions?
    

    View Slide

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%
    

    View Slide

45. View Slide

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%
    

    View Slide

47. View Slide

48. Performance evaluation (results)
    1) A htop results while profiling firewall with logging:
    

    View Slide

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

    View Slide