Building SQL firewall: insights from developers

Slide 1

Slide 1 text

Slide 2

Slide 2 text

Artem Storozhuk Security software engineer at Cossack Labs [email protected]

Slide 3

Slide 3 text

This is what OWASP says about SQL injections:

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

Databases and SQL injections

Slide 6

Slide 6 text

1 always equals 1! Databases and SQL injections

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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.

Slide 19

Slide 19 text

Some known WAF’s limitations 1) False negatives – some specific SQL injections can’t be detected From: https://pdfs.semanticscholar.org/9f03/89ad1feea03e43e0626cd02eb5d2483ac6b4.pdf

Slide 20

Slide 20 text

Some known WAF’s limitations 1) False negatives – some specific SQL injections can’t be detected From: https://pdfs.semanticscholar.org/9f03/89ad1feea03e43e0626cd02eb5d2483ac6b4.pdf

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

Database proxy as additional line of defence Database Application Database proxy

Slide 24

Slide 24 text

Database proxy as additional line of defence Database Application Database proxy

Slide 25

Slide 25 text

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/

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

Slide 30

Slide 30 text

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)

Slide 31

Slide 31 text

Acra database firewall – AcraCensor Blacklist policy example Whitelist policy example

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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.

Slide 37

Slide 37 text

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.

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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.

Slide 42

Slide 42 text

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;

Slide 43

Slide 43 text

Thank you! Any questions?

Slide 44

Slide 44 text

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%

Slide 45

Slide 45 text

No content

Slide 46

Slide 46 text

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%

Slide 47

Slide 47 text

No content

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

Performance evaluation (results) 1) A htop results while profiling firewall with logging: 2) A htop results while profiling firewall with logging (with GOMAXPROCS = 1):