Table of Contents
Few weeks ago we wrote a post enumerating the most common Input Validation vulnerabilities in software development and how to fix them.
In this article we will deal with one of the most important topics covered within that post: SQL Injection, a threat so common and widespread that it consistently occupies the first place among the OWASP Top 10 Web Application Security Risks.
Introduction
SQL Injection is a threat that affects most Web Applications that rely upon a back-end layer that uses a relational database: the threat is mostly caused when software developers create dynamic (or code-generated) database queries that include user supplied input before running them against the database.
SQL Injection example
For example, let's take the following dynamic query:
|
1 |
SELECT * FROM myblog.posts WHERE Title LIKE '%{SEARCH_QUERY}%'; |
The above query will retrieve all the entries in myblog.posts table containing the keywords set in place of the {SEARCH_QUERY} placeholder.
Ideally, such placeholder is meant to be replaced by some user-defined query through a standard search form, right?
The above input value would create the following dynamic query:
|
1 |
SELECT * FROM myblog.posts WHERE Title LIKE '%blockchain%'; |
Which will work as intended (by the developer), thus posing no security issues.
However, a typical SQL Injection attack can occur if such input value is submitted in the following way:
The above input value would create the following dynamic query:
|
1 2 |
SELECT * FROM myblog.posts WHERE Title LIKE '%blockchain%'; SELECT * FROM myblog.users WHERE ID LIKE '%%'; |
Which will pose a huge security issue, possibly exposing the full myblog.users table to the attacker.
And that's just a read-only attack example: just imagine what could happen if a UPDATE, INSERT or DELETE statement would be used instead.
How to prevent
According to the Open Web Application Security Project (OWASP) SQL injection prevention guidelines, avoiding SQL injection flaws is simple. Developers need to either:
- Stop writing dynamic queries.
- Prevent user supplied input with malicious SQL from affecting the logic of any dynamic query.
Option #1 can be a major problem for most dynamic websites, such as blogs, CMS, and the likes; Option #2 is definitely more versatile, as long as the countermeasures taken to avoid "malicious SQL instructions" are implemented correctly throughout the whole application's architecture layers (Data Provider, Business Logic and User Interface).
Here's a brief list of the most used and standardized techniques for preventing SQL Injection vulnerabilities by implementing Option #2, i.e. to prevent malicious SQL from affecting dynamic queries:
- Prepared Statements with Parameterized Queries
- Stored Procedures
- Allow-list Input Validation
- Escaping
- Enforcing Least Privilege
The above techniques are architecture agnostic, meaning that they can be used with any kind of programming language and with any type of database; this includes most non-SQL databases, which are prone to different attack patterns - such as XPath and XQuery injection threats in case on a XML database).
Prepared Statements
A prepared statement is a built-in feature available on most database engines (including SQL Server, MySQL, MariaDB, and the likes) that can be used to execute the same (or similar) SQL statements repeatedly with high efficiency. Such statements are basically SQL Templates, which makes them ideal to host dynamic queries: the variable part of the query (including the user-defined input, which is what we want to check) is passed to these statements using a variable binding approach (one single variable per input). Needless to say, each variable passed this way is checked and validated against SQL Injections by the underlying SQL engine, which will make it safe by escaping the "not allowed" characters or by rejecting the whole input as invalid.
Such approach, also known as parameterized queries, is rather simple to write and definitely easier to understand than the standard, unsafe dynamic queries that most unexperienced or self-taught developers tend to use. Furthermore, it's way better in terms of overall logic and decoupling: writing parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. Such coding style allows the database to distinguish between code and data, regardless of what user input is supplied.
Parametrized Queries are natively supported by most used server-side language used nowadays, including:
- Java. PreparedStatement() with bind variables.
- ASP.NET. SqlCommand() or OleDbCommand() with bind variables; Entity Framework (without raw SQL approach).
PHP. PDO with strongly typed parameterized queries using bindParam() - SQLite. sqlite3_prepare()
If prepared statements and/or parametrized queries are not available, or you are forced to not use them, it's highly recommended to:
- strongly validate all data using manual techniques;
- escape all user supplied input using the escaping routine made available by the database use.
Stored procedures
Stored procedures are not intrinsecally safe from SQL injection, but - if created using variables - have the same security posture of prepared statements, because their input variables are internally checked in the same way.
The most relevant difference between prepared statements and stored procedures is that the SQL code for a prepared statement come from the application (together with the input variables), while a stored procedure is defined and stored in the database itself, and then called from the application (along with the input variables) using a reference name. However, from a security point of view, these two techniques have the same effectiveness in preventing SQL injection.
Allow-list Input Validation
As a general rule, any time the user-defined input is used to identify one of a "fixed" list of possible values, such input should be either:
- Mapped to a (possibly numeric) ENUM or ID related to that value.
- Checked and validated to ensure that it belongs to that list.
The above techniques, if implemented properly, will prevent such value from containing malicious SQL code.
Escaping
As the name suggest, the technique aims to escape the user input before putting it in the dynamic query: since each DBMS supports one or more character escaping schemes specific to certain kinds of queries, by escaping all user input using that escaping scheme we can prevent the DBMS from confusing that input with SQL code, thus avoiding any possible SQL injection vulnerabilities.
However, as we can easily understand, the escaping implementation is database-specific: for that very reason, it should only rely to the built-in escaping function (or method) provided for that specific DBMS, avoiding "generic" or "hand-made" implementations that could easily leave your web application vulnerable to the SQL Injection threat.
Enforcing Least Privilege
Last but not least, comes the Least Privilege enforcement option: despite not being a specific counter for SQL Injection threats, such security principle can definitely help to minimize the potential damage of a successful SQL injection attack.
On general terms, the principle of least privilege (PoLP), also known as the principle of minimal privilege or the principle of least authority, requires that every module must be able to access only the information and resources that are necessary for its legitimate purpose.
When applying the least privilege principle on a web application with needs to access to a DBMS, we need to ensure that every database account has only the permissions strictly required to fullfill their job. This basically means to enforce the following rules:
- Do not assign DBA or admin type access rights to application accounts.
- Make sure that all accounts are only granted read and/or write access to the tables they need to access.
- When possible, use a different account for read-only connections (without write permissions).
- We understand that this is easy, and everything just 'works' when you do it this way, but it is very dangerous.
Other good practices can be used for DBMS that allows the use of views other than raw tables: for example, if an account only needs access to portions of a table, we could create a view that only shows that portion of the data and assign read (or r/w) access to that view, leaving the underlying table not accessible (and therefore protected).
Conclusion
That's it, at least for now: we hope that this post will help most DBA and System Administrators to secure their web applications against the SQL Injection threat.
