SQL Injection. If You’re decently security-minded, You’ve probably heard of it. But what is it exactly? How does it work and, perhaps most importantly, what we can do to prevent it?

What we do know is that injection is the #1 flaw targeting web applications according to OWASP Top 10. But why exactly is it so prevalent? I’ll try to touch on that in this blog post.

SQL Injection? Never heard of it..

For those of you who haven’t heard of SQL Injection (SQLi) before, here’s a simple explanation:

When You register to a website providing it your email address, your username, and, in most cases, your password, it is stored in a database.

SQL stands for Structured Query Language and is used to communicate with the database which holds that information. The “i” at the end of it stands for injection.

SQL Injection is such an attack where malicious code is placed into an SQL statement. The primary purpose of SQL injection is to extract data from a database.

SQL injection can be divided into several types:

1. Classic SQL injection
2. Error-Based SQL injection
3. Union-Based SQL injection
4. Blind SQL injection, which can also be divided into two types:

• Time-Based Blind SQL injection
• Boolean-Based Blind SQL injection

I am only going to cover classic SQL injection in this article, other types will be covered in the upcoming articles, perhaps.

What is it?

Classic SQL injection is the most common type of SQL injection. It is also the easiest type of SQL injection to exploit.

Consider this:

There’s a website which sells supercars & has a search functionality. You go ahead and search for one.

Fair enough, we get 8 results.

But what if we search for a supercar with an apostrophe (‘) at the end?

We got an error. Not good.

Here’s what happened:

• When we search, the website runs a query against a database. In this case it is
  

  SELECT * FROM cars WHERE car LIKE ‘%Ferrari%’;

• The only thing a user has control of is the input between two wildcard characters (%). When the user searches for a supercar, the input between two wildcards changes according to his search query, for example if we search for a Lamborghini Aventador the query becomes this:

SELECT * FROM cars WHERE car LIKE ‘%Aventador%’;

But when we add an apostrophe at the end of the string, query becomes:

SELECT * FROM cars WHERE car LIKE ‘%Aventador’%’;

What happened now is that the first wildcard symbol was inside a string while the other was not.

Because the query became malformed, it did not run successfully, and thus, we got an error.

The error tells us that something is up. Lets try changing our input up a bit, now we will search for this:

ferrari’ UNION (SELECT 1,2,3 FROM dual);– %

• The UNION operator in our query is used to combine one or more SELECT statements.
• “dual” in MySQL is a placeholder table name to select from.

The fact that we can output the results of queries to the screen is really, really bad because that means we’re only a few steps away from extracting all of the data from a database..

Remediation

SQL injection can be mitigated by either using prepared statements or utilizing input validation.

Instead of mixing the code and data, which is the root cause of SQL injection, prepared statements allows the database to distinguish between them. When using prepared statements, the query and the data are sent to the server separately which eliminates the problem.

• 1. Preparation – An SQL statement template is sent to the database, where the data is substituted by a parameter:

$db->prepare(“SELECT * FROM cars WHERE model = ?”);

• 2. After the preparation phase, the data should be sent with the second request which is separated from the query itself, so it cannot do any harm:

$db->execute($data);

Input validation

Another way SQL injection can be mitigated is by using input validation. There are two main types for input validation:

1. Black-list input validation
2. White-list input validation

Black-list input validation

Black-list input validation compares the input against known bad values.

For example if the input would be “Ferrari'” and the ‘ would be included in a black-list, the request would be blocked.

Using black-list input validation has its downsides though. Here’s why:

1. In order to make black-list input validation work, the developer must maintain and constantly update it because otherwise, it would be trivial for an attacker to avoid getting caught by such filters.
2. Such filters often prevent legitimate input.

White-list input-validation

White-list input validation has a list of allowed values – all other values are blocked. It enables developers to define a very strong validation pattern for validating input.

Goals of input validation

Input validation is performed to ensure only properly formed data is executed. It can significantly reduce the impact of SQL injection if implemented properly.

Proper input validation should enforce correct syntax of fields and the correctness of their values.

Wrap-up

Injection flaws such as SQL injection occur when untrusted data is sent to a server as part of a command or query. Although such flaws are pretty easy to get rid of, the consequences of them being exploited can be severe yet the web is still plagued with them. Injection flaws can be mitigated using a couple of methods – the most widely used one is prepared statements.