SQL injection as one of the biggest vulnerabilities for your data

What is SQL Injection

Almost all modern existing applications store data in classic relational databases. Unfortunately, if you recklessly write code, then the data may be irretrievably lost. In addition to everything, even on a resource we are accustomed to trust, there may be absolutely wrong and even dangerous, from a security point of view, tips.

Do not trust completely everything that is written on the forums. Often answers get to the top just because some users just liked it. It is also worth paying attention to the fact that information may be outdated, and in matters of security you should always pay attention to the latest trends.

Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data. This can be used to alter query logic to bypass security checks, or to insert additional statements that modify the back-end database, possibly including execution of system commands.

SQL injection has become a common issue with database-driven web sites. The flaw is easily detected, and easily exploited, and as such, any site or software package with even a minimal user base is likely to be subject to an attempted attack of this kind. This flaw depends on the fact that SQL makes no real distinction between the control and data planes.

What consequences may be

What can a user do if he implements his database query? Here are some examples:

Exposing sensitive data

Since SQL databases generally hold sensitive data, loss of confidentiality is a frequent problem with SQL injection vulnerabilities. The simplest and most popular example is the injection of following harmless piece, which opens us all the doors:

' or '1' = '1

If this value is used in the query, we get the first row in the database.

$id = $request->get('id'); // Unvalidated user input

$query = "SELECT * FROM users WHERE id = '$id'";
// SELECT * FROM users WHERE id = '' or '1' = '1'
// SELECT * FROM users WHERE id = 1 or is_admin=1 order by id limit 1

Thus, an attacker can obtain data that he does not have access to, and this is clearly not what we would like to provide to him so easily.

Data mutation or deletion

Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

But it is quite easy to enable by connecting with CLIENT_MULTI_STATEMENTS flag on. Also some modern frameworks do it in the background for performance purposes. But in other way, this allows an attacker to inject their own queries.

name'; DELETE FROM items; --

If the user passes this string as an argument and multi-line statements allowed in the database, then everything is very bad, since the statements is converted to 3 parts:

SELECT * FROM items WHERE label = 'name';
DELETE FROM items;
--' ORDER BY id LIMIT 10;

1. original query that was interrupted by injection
2. any milicious injection statement
3. comment to prevent database errors, as original query was interrupted

Remote database control

It's a shame if this happens to data that is valuable to you, isn't it? However, if you have backups, you will be able to restore your data, although this process will be very painful. 😥

But what if an attacker takes complete control of our database? Is it possible?

Unfortunately yes 😞 if the connection has enough rights for this.

'; exec master..xp_cmdshell 'dir' --

The query will take the following form:

SELECT item, price FROM products WHERE category = ''; exec master..xp_cmdshell 'dir' --' ORDER BY price

Now, this query can be broken down into:

• a first SQL query: SELECT item, price FROM products WHERE category = '';
• a second SQL query, which executes the dir command in the shell: exec master..xp_cmdshell 'dir'
• an SQL comment: --' ORDER BY price

As can be seen, the malicious input changes the semantics of the query into a query, a shell command execution and a comment.

How to prevent SQL Injection

💡 Validate user input.

One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem. Do not trust client, never! Always check what has been given.

if (!\filter_var($email, \FILTER_VALIDATE_EMAIL)) {
    throw new ValidationException($email);
}

The example above is for demonstration purposes. Use proven solutions.

💡 Whitelist, not blacklist

Whitelisting can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, blacklisting is riddled with loopholes that make it ineffective at preventing SQL injection attacks.

// Bad
$blacklist = ['"', '\'', ';'];

foreach ($blacklist as $item) {
    if (\strpos($input, $item) !== false) {
        throw new ValidationException($input);
    }
}

// Better
$whitelist = '/^[\d]*[a-z_][a-z\d_]*$/gi';

if (!\preg_match($whitelist, $input)) {
    throw new ValidationException($input);
}

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

💡 Stored procedures

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they do not protect against many others:

procedure get_item (itm_cv IN OUT ItmCurTyp, usr in varchar2, itm in varchar2)
is open itm_cv for
' SELECT * FROM items WHERE ' || 'owner = '|| usr || ' AND itemname = ' || itm || ';
end get_item;

Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.

They require the developer to just build SQL statements with parameters which are automatically parameterized unless the developer does something largely out of the norm. Note: 'Implemented safely' means the stored procedure does not include any unsafe dynamic SQL generation. Developers do not usually generate dynamic SQL inside stored procedures. However, it can be done, but should be avoided. If it can't be avoided, the stored procedure must use input validation or proper escaping.

⚔️ Enforcing Least Privilege

To minimize the potential damage of a successful SQL injection attack, you should minimize the privileges assigned to every database account in your environment. Do not assign DBA or admin type access rights to your application accounts. We understand that this is easy, and everything just 'works' when you do it this way, but it is very dangerous.

Start from the ground up to determine what access rights your application accounts require, rather than trying to figure out what access rights you need to take away. Make sure that accounts that only need read access are only granted read access to the tables they need access to.

✅ Use prepare statements (with Parameterized Queries)

Yes, and another 100 times yes! This is the safest and most modern way to avoid an injection. The use of prepared statements with variable binding (aka parameterized queries) is how all developers should first be taught how to write database queries. They are simple to write, and easier to understand than dynamic queries. Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.

Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. In the safe example below, if an attacker were to enter the user_id of tom' or '1'='1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string tom' or '1'='1.

Process SQL queries using prepared statements, parameterized queries, or stored procedures. These features should accept parameters or variables and support strong typing. Do not dynamically construct and execute query strings within these features using "exec" or similar functionality, since this may re-introduce the possibility of SQL injection.

Language specific recommendations:

• Java EE – use PreparedStatement() with bind variables
• .NET – use parameterized queries like SqlCommand() or OleDbCommand() with bind variables
• PHP – use PDO with strongly typed parameterized queries (using bindParam())
• Hibernate - use createQuery() with bind variables (called named parameters in Hibernate)
• SQLite - use sqlite3_prepare() to create a statement object

⚠️ Avoid outdated packages or technologies

As mentioned earlier, solutions that were good before, can be outdated and not safe nowadays. Security is a discipline that requires relevance, so you always need to be up to date.

In addition, vulnerabilities may be found in third-party packages that you use, and you must always keep your ear sharp and keep the dependencies up-to-date.

⚠️ Avoid custom solution, prefer well-developed ORMs

Another preferred option is to use a safe API, which avoids the use of the interpreter entirely or provides a parameterized interface, or migrate to use Object Relational Mapping Tools (ORMs).

Modern libraries have modern secure solutions. If you keep them updated, then it is highly likely that all the best solutions, including prepared statements, are already implemented and used correctly.