How to Avoid SQL Injection in Your Code

SQL Injection is still one of the most common attack vectors in the wild.
According to a recent report by Imperva’s security research team “Lesson Learned from Analyzing 100 Data Breaches” 13.3% of those 100 data breaches initially occurred by a SQL Injection attack.
In this we'll briefly explain how SQL Injection works, including examples, and how to avoid SQL Injections in your application code.

What is SQL Injection

"SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution" (Wikipedia).
Practically, SQL Injection happens when a malicious user input is concatenated as is into a SQL query executed on the backend database of an application. When the user input is concatenated into the SQL query, it allows an attacker to input SQL code that manipulates the SQL query in a ways that alters the original intention of the query to exfiltrate or destruct data from the backend database.
Since the backend database typically holds the application data, including sensitive customers' data, and the fact that SQL Injection is often relatively easy to exploit, make it a very attractive and common attack vector among adversaries.

SQL Injection Example

let's look at some code to see how a SQL Injection looks like, and how it can be exploited. We will use NodeJS for the sake of this example, but SQL Injection will look fairly similar in most languages, and so is the solution for it.
In the code snippet below, we see an HTTP controller that handles GET requests for User objects.

As seen above, the user input, that is, that birthdate and the ID is being appended as is into the SQL query, which is then executed on the database. This allows the user to input non-standard input (i.e. not a birthdate and an ID number) to manipulate the query. This can lead to data exfiltration or data destruction.

Data Exfiltration

Data exfiltration happens when the user is able to get more data than he should be able to get. In our example, the user should only get data about users he knows their birthdate and ID number. Let's see now how a malicious user can expliot this SQL Injection to get data about all users in the system.
Suppose the user inputs the following:

birthdate = 1/1/1900';--
id = 1234567890

Such an input concatenated into the SQL query will render the following query:

SELECT * FROM users WHERE birthdate = '1/1/1900'; -- ' AND ID = '1234567890' 

The part of the query that comes after the -- gets ignored since this is the standard character for SQL comment. And so the actual query that will run is the following:

SELECT * FROM users WHERE birthdate = '1/1/1900'

This query will return all users that were born after 1/1/1900 which are practically all users.

Data Destruction

Leaking data is one thing an attacker can do. A malicious user can also use SQL Injection to damage the data, or even destroy it completely.
Let's look at some possible inputs that destroys the data.

birthdate = 1/1/1900'; DROP TABLE users; --

This input will yield the following statement:

SELECT * FROM users WHERE birthdate = '1/1/1900'; DROP TABLE users; -- ' AND ID = '1234567890'

Here we have actually 2 queries, and they will both be executed:

SELECT * FROM users WHERE birthdate = '1/1/1900'; 
DROP TABLE users;
-- ' AND ID = '1234567890'

The second query will practically delete the users table completely, which may disrupt the application functionality.

Preventing SQL Injection

Generally speaking, there are many ways to prevent SQL injection, at many different levels. One may use a WAF (Web Application Firewall) to block suspicious payloads at runtime. One can also use SAST to find possible SQL Injections in the source code. But the easiest way to avoid a SQL Injection is prevent it in the code at first place. Since SQL Injection happens when a user input is appended to a SQL query, we can sanitize the user input to make sure it does not contain any SQL code by itself.

Input Sanitation

One way to avoid SQL Injection is to sanitize the input by validating its data type. In the example above, we expected a dat format for the birthdate field. we could have created a simple date format validator to make sure the user input is indeed a date. Such a validation will not accept values as the one shown above, thus preventing the attack.
Though this is an effective measure, it is time consuming as we need to implement a different validation for each data type.

Query Placeholders

Another, more generic way of input sanitation can be achieved using query placeholders. With placeholders, instead of appending the user input directly to the query, we may replace it with question marks (query placeholders) or parameters (named placeholders). We then provide the user input as values for these placeholders, but those values are escaped and validated for the actual require type according to the database schema. The code will look as follows, with only a slight change:

This practically provide a generic way to sanitize and validate user input in SQL queries.

Key Takeaways

SQL Injection is a fairly popular attack vector. Althoug very effective, it is also very simple to discover and prevent SQL Injection. The easiest way to do that is in the code, by always escaping the user input involved with a SQL query. as preventive measures, it is also recommended using a Static Code Analysis tool to make sure we don't miss any SQL Injection in the code, and on top of that, a WAF can make sure no requests containing any suspicious SQL Injection payload is passed to the application at all.