SQL Injection: Security Best Practices & Guidelines How to prevent SQL injection attacks and other injection-based threats targeting web applications and services

SQL Injection: Security Best Practices & Guidelines

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:

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?

SQL Injection: Security Best Practices & Guidelines

The above input value would create the following dynamic query:

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:

SQL Injection: Security Best Practices & Guidelines

The above input value would create the following dynamic query:

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.

The above example is just a basic SQL Injection strategy that tries to exploit a poorly designed HTML form: if you want to see more complex attack patterns, as well as some vulnerable back-end source code examples, be sure to check out The Ultimate SQL Injection Cheat Sheet.

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:

  1. Stop writing dynamic queries.
  2. 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.

WARNING: This technique is less effective (and way less secure) if compared to the previous defense techniques, and – even when implemented properly – could fail to prevent SQL Injection in some edge-case scenarios. For that very reason, it should only be used as a last resort, i.e. when none of the above are available.

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.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.