Share
Embed
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.
Artem Storozhuk
February 02, 2019
Transcript
49 slides
-
How to prevent SQL Injections? 1) Follow security practices when
writing code INPUT VALIDATION -
How to prevent SQL Injections? 1) Follow security practices when
writing code INPUT VALIDATION -
How to prevent SQL Injections? 1) Follow security practices when
writing code INPUT VALIDATION -
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 -
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/). -
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 -
Common Web Application Firewall features 1) Implementing input/output traffic filtering
policy. 2) Events logging. -
Common Web Application Firewall features 1) Implementing input/output traffic filtering
policy. 2) Events logging. 3) Supporting SSL/TLS encryption protocol. -
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. -
Some known WAF’s limitations 1) False negatives – some specific
SQL injections can’t be detected From: https://pdfs.semanticscholar.org/9f03/89ad1feea03e43e0626cd02eb5d2483ac6b4.pdf -
Some known WAF’s limitations 1) False negatives – some specific
SQL injections can’t be detected From: https://pdfs.semanticscholar.org/9f03/89ad1feea03e43e0626cd02eb5d2483ac6b4.pdf -
Some known WAF’s limitations 1) False negatives – some specific
SQL injections can’t be detected 2) False positives -
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 -
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/ -
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='' -
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 -
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) -
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 -
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 -
How does it work? IN RUNTIME ONCE ON START ON
DEVELOPMENT -
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 -
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. -
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. -
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 -
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 -
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 -
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. -
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; -
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% -
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% -
Performance evaluation (results) 1) A htop results while profiling firewall
with logging: 2) A htop results while profiling firewall with logging (with GOMAXPROCS = 1):