What is the biggest threat to a tool that prevents unauthorised database access? Requests from the application side that trigger data leakage. Namely, SQL injections and other application attacks that allow attackers to craft custom SQL queries. How can we prevent that?
The standard industry response is obvious — input sanitization, web application firewalls (WAFs), and prepared statements are typically used for addressing these concerns. We’re shipping a product which aims to intervene into application logic as little as possible but as it turns out, input sanitization is rarely done well, WAFs are not always efficient, and prepared statements are a question of app developer’s choice.
Note! If you’re familiar with the way SQL injections and WAFs work, you can proceed straight to the Problems with current mitigation techniques section.
SQL Injections 101
SQL injection (SQLi) is a way to leak data from database-driven applications. It is based on injecting arbitrary code into an SQL query, the execution of which can be carried out without the app administrator’s knowledge. These attacks are possible due to the lack (or incorrect implementation) of input data validation.
Let’s consider a basic SQLi example. Imagine a simple application that receives
password values on input and sends them to the database using following SQL query:
string query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
Let’s suppose, the user entered
Kevin as the
example' OR 'a' = 'a as password. As a result, the following request will be sent to the database:
SELECT * FROM users WHERE username = 'Kevin' AND password = 'example' OR 'a' = 'a'
OR 'a' = 'a' will always be
true so the database will always return all the lines from the
This means, any application that uses SQL, regardless of its exact implementation, framework, programming language, popularity, etc. is potentially vulnerable. The problem gets especially acute since the exploitation of the discovered vulnerabilities is not hard, and this is actively used (and abused) by malicious attackers all over the Internet. At the moment of writing this article, there are over 7000 known cases of various SQLi. Which is odd, because, in fact, mitigating SQL injections is not rocket science.
Mitigating SQL injections inside the application
According to the OWASP SQL Injection Prevention Cheat Sheet, for successful prevention of the currently known SQLi, there are 4 approaches to be adopted during the development of apps that interact with databases in-code and in database configuration:
- Escaping All User-Supplied Input.
Mitigating SQL injections with WAFs
Web application firewall is a tool to use when you can’t quite rely on the quality of all your backend code (and in the world of dependency creep, you typically can’t — 80% of a typical web app code is a tangle of haphazardly interconnected dependencies). WAF can help stop most suspiciously-looking requests dead in their tracks by looking for signatures (sequences of characters) that seem to be doing the wrong thing. It’s worth mentioning that besides WAFs, there are other known effective SQLi-preventing approaches based on machine learning and pattern matching.
One of the most famous and readily available tools for protecting web apps against SQLi is ModSecurity (ModSecurity GitHub). It is a complex WAF, which protects from a wide range of attacks, including SQL injections (when configured using OWASP CRS, ModSecurity employs around 16,000 specific security rules). Using ModSecurity, it is also possible to log HTTP traffic, monitor network activity, and access events from web applications.
Despite a large number of means of protection (both highly scientific and simple technical ones), the SQLi attacks keep taking place. This happens because any time dynamic requests (requests containing the data entered by users) towards the database are created, there is always a potential risk of an injection, but it’s impossible to make do without those requests.
Problems with current mitigation techniques
In-code mitigation: challenges
The problem with common in-code/configuration approaches to protection from the SQLi (prepared statements, input sanitization, whitelisting/typed processing of user inputs, proper escaping) is the same as the threat they’re mitigating: it all comes down to human mistakes. They all rely on developers’ good judgement, diligence, sanity, and sometimes even on the levels of caffeine as related to the lateness of the hour when some solution was being developed. And developers are usually too overloaded with things falling apart and unrealistic project deadlines as they are, to also be obsessing over small details.
Limitations of WAFs
The problem with WAFs is that they try to detect known attacks or patterns of attacks. And they do the filtering on the network traffic level and verify signatures on the border of an application: before it gets into the application.
Which is fine, but leaves a gazillion of ways to bypass WAFs. For instance, how about faking where the request is coming from by sending an
X-Forwarded-For header? Or let’s say that there was a rule that validated a field, but it was case-sensitive. You could send a custom request using mixed-case and bypass the rule, allowing the request to get through to the protected application, perhaps breaking it in the process.
And, in the end, isn’t the whole architecture of “we’ll render the query on the backend but we protect the system on front-end” a bit peculiar? It would be naive to expect WAF to deal with it. Why aren’t we dealing with queries as we should — which is “as close to the database as possible”?
Limitations of blocking
Many security events cannot be blindly blocked to avoid disrupting the normal application behaviour, especially during the active development phase. You can’t rely on preventing typical attacks when the attack surface is changing several times a day with each new deploy. Along with all the tools mentioned above, exhaustive monitoring and log analysis is required to make sure that the system is sound security-wise.
Insiders are a problem, too
No matter the initial penetration vector, data leakage prevention should not exclusively focus on external adversaries. It is known that 25% of all data leaks (and impressive 60% of cyber attacks) — that’s 1 in 4! — involve some variety of an inside job. Against this, most of the approaches are no better than a tinfoil hat.
Preventing SQL injections using SQL firewalls
If the perimeter of your application, along with the backend for web app, cannot be trusted to prevent SQL injections fully, what can you do? Introduce a layer of defense right in front of the database.
Imagine a firewall that works as a database proxy, checking every SQL query that passes through it. This firewall would have a number of advantages compared to WAF:
Such firewall works directly with the pure SQL queries over the chosen database protocol.
The rules are not matched as strings, but are parsed into a tree of SQL nodes and are matched up to the set allow/deny lists by their logical structure, not symbolic interpretation.
Considerable granularity — you can specify blocking off a certain table/row. Imagine a flexible and effective system for rules’ configuration with an option to deny/allow the access to certain tables, rows, to filter certain queries or query templates (i.e. DROP table %tablename%).
You can teach the firewall the “normal workflow” of an app during the beta-testing stage, set the rules, and release it into the production environment.
Such firewall is a great point for sending masked logs and events to SIEM/IDS systems in addition to the WAF and database monitoring.
You can set a specific combination of rules to redirect some requests to the honeypot database instead of the real production database.
Curious to try Acra SQL firewall?
One of the main advantages of our Acra encryption suite is its cryptographic design that provides a high level of security even in case of data leakage from database or compromisation of the user app: all the encryption keys are stored on Acra’s server component. When we’ve built Acra, we quickly realized that having encryption/decryption proxy in front of a database is a perfect place to integrate SQL firewall… only to find an absence of readily-available open-source components.
The only good one that currently exists on the market comes from Oracle (Oracle Firewall, so you’re bound to Oracle if you want to be using it), others are either nonexistent or are also parts of larger packages (i.e. ProxySQL or Hexatier). Since Acra has to parse SQL to provide some of its features anyway, we took a break from the cloud of the pure hardcore crypto and rolled up our sleeves to build our own SQL filtering engine — AcraCensor SQL firewall for Acra encryption suite.
In comparison to traditional web application firewalls, the main advantage of Acra’s SQL firewall AcraCensor lies in the additional SQL query filtration on database proxy, which has access to full SQL statements the way database is supposed to process them and is the only gateway to perform requests which return plaintext. Consequently, the database proxy has significantly more chances of preventing specific injections than a WAF.
This is a slide from a talk by Cossack Labs' security software engineer Artem Storozhuk on building SQL firewalls, which illustrates the difference in parsing requests between SQL firewalls and WAFs.
Also, common WAF security configuration is usually based on some well-known rulesets (i.e. OWASP CRS for ModSecurity) that don’t consider specific database structures in different web applications. For instance, here is a fragment of the OWASP CRS for SQL injections:
SecRule REQUEST_COOKIES|!REQUEST_COOKIES:/__utm/|REQUEST_COOKIES_NAMES|ARGS_NAMES|ARGS|XML:/* "@rx (?i:(?:[\"'`](?:;*?\s*?waitfor\s+(?:delay|time)\s+[\"'`]|;.*?:\s*?goto)|alter\s*?\w+.*?cha(?:racte)?r\s+set\s+\w+))" \
As you can see, it’s based on a rather complex regular expression, which is not very understandable and maintainable (although it is aimed at detecting many typical SQLi patterns in one shot). Now, let’s look at Acra’s database proxy firewall security configuration:
ignore_parse_error: true handlers: - handler: deny queries: - SELECT * FROM users tables: - cars patterns: - SELECT user_address FROM users %%WHERE%% - "%%INSERT%%" - handler: allowall
Such configuration is based on the denylist (blacklist) approach. As you can see, we decided to block 1 actual query
SELECT * FROM users, each query that contains table
cars, and, finally, each
INSERT query and queries that start with
SELECT user_address FROM users WHERE .... All the other queries are allowed in our security configuration.
Having said and illustrated the pros and cons of WAFs and SQL firewalls, it’s worth mentioning that we still don’t expect AcraCensor, some other SQL firewall, or, in fact, any other single tool alone to be a “silver bullet” of security.
Security-wide project should implement the echelonized defense (defense in depth) principles:
implement good input sanitization and proper escaping in web application code;
use prepared statements and stored procedures for database requests;
use WAF as first line of active defense to protect from most common CVEs;
use SQL firewall to block any other suspicious requests;
use SIEMs for monitoring events and analyzing anomalies.
Such a project should also take care of the data itself: encrypt, control and log access for no leak to take place even if all lines of defense fail.
If you want to implement proper protection from SQLi for your web application, but don’t know where to start, try using OWASP-based demo of AcraCensor. The open source version of Acra is free to use, it has extensive documentation and useful one-line Docker-based demos for a quick start.