Protection against SQL injection

What is a SQL injection and how it works

SQL injection is a common way to hack sites and software that run databases (DBs). The method comes down to introducing arbitrary SQL code, which will eventually allow the hacker to interact with database data in various ways:

• read the contents;
• delete data;
• add new data.

In addition, in some cases, an attacker can gain access to read and write local files on the server, execute arbitrary commands, etc.

This happens in the following way. Most sites have databases where all the information is stored. When a visitor tries to load a page, a query is sent to the database. And that would be OK, but the user can modify the query – in the process of creating a comment, during a search, or even just when switching from one page to another. These queries are a window of opportunity for SQL injection. Triggering an action that was not intended by the creator of the script. Let's look at an example.

The wife leaves a note for her husband to give some cash to Nicholas, leaving it on the kitchen table. A potential SQL query could look like this:

TAKE 10 DOLLARS FROM YOUR wallet AND LEAVE IT ON THE TABLE FOR Nicholas

Having done this, the woman left the note on the table and went to work. Marry, Nicholas’ sister, woke up, saw the note, and added a few words in similar handwriting:

TAKE 10 DOLLARS FROM YOUR wallet AND LEAVE IT ON THE TABLE FOR Nicholas OR Marry

When my husband woke up, he had no second thoughts and decided that both children knew what the money was for. When he saw Masha, he told her that the money was on the table and let her take it.

Marry got her way by unfair means, but in the end, she has the money, and Nicholas is left with nothing. This is a simple example of SQL injection but in simple words.

Now let's look at a more realistic example. This is what a standard ID query looks like in a search:

SELECT id,title,content FROM posts WHERE title LIKE '%user’s_query%'

But now let's imagine that instead of the standard keywords the user enters the following combination:

1%'; DROP TABLE posts LIKE '%;

The result will be a workable query that the database developer could not foresee:

SELECT id,title,content FROM posts WHERE title LIKE '%1%'; DROP TABLE posts LIKE '%%'

Such a scheme can work with any request where user data is entered – provided that the developers or security specialists have not provided the necessary security mechanisms. In this case, simply escaping all quotation marks in user queries is enough to solve the problem. Next, we will look at the main ways to protect against SQL injection.



Protecting your site from SQL injection at the program code level

When using the PHP programming language, quotation marks can be escaped with these functions:

• mysql_real_escape_string;
• mysqli_real_escape_string.

Provided that every variable used in database queries is filtered, there will definitely be no problems. It is only important to filter at the level of CMS or program code.

The problem is that it takes much less time to create insecure database queries than it does to create secure ones, with escaping. And because of this, many sites are vulnerable.

Configuring protection for the webserver

If you can't refine the code, you can filter out all the values of the REQUEST variable at the beginning of the script. To do this, use the following code:

if (!function_exists("clean")) {
if (get_magic_quotes_gpc()) {
function magicquotes_Stripslashes(&$value, $key) {
$value = stripslashes($value);
}
$gpc = array(&$_COOKIE, &$_REQUEST);
array_walk_recursive($gpc, 'magicquotes_Stripslashes');
}
function clean(&$value, $key) {
//this function escapes all quotation marks.
$value = mysql_real_escape_string($value);
}
}
$req = array(&$_REQUEST);

array_walk_recursive($req, 'clean');

When using PHP 7, the function mysqli_real_escape_string should be used as the mysql extension has been removed as of this version. The quotes are escaped with the clean function and all the code below.

Next, you should add some rules to the server section to protect the web server. Here is an example for Nginx users:

set $block_sql_injections 0;
if ($query_string ~ "union.*select.*\(") {
set $block_sql_injections 1;
}
if ($query_string ~ "union.*all.*select.*") {
set $block_sql_injections 1;
}
if ($query_string ~ "concat.*\(") {
set $block_sql_injections 1;
}
if ($block_sql_injections = 1) {
return 403;

}

This is a neat way to filter out all queries that contain the words select and concat with quotes. This is a sure sign that SQL injection is being attempted, and all such queries should be blocked.

It is also possible to block suspicious addresses at the Apache web server level. The problem is that blocking some frequently used SQL keywords can also block requests from regular users, so you have to be careful here. If you are sure that this is necessary, then a few lines should be added to the VirtualHost section:

RewriteCond %{QUERY_STRING} [^a-z](declare¦char¦set¦cast¦convert¦delete¦drop¦exec¦insert¦meta¦script¦select¦truncate¦update)[^a-z] [NC]

RewriteRule (.*) - [F]

And finally, activate the mod_security module:

sudo a2enmod mod_security

Use all of the above methods together to achieve the highest efficiency in terms of protection against SQL injection.

Database partitioning

Another good way to increase the security of the database is to divide it into 2-4 parts, applying the principle of minimum privileges. The idea is that users and individual programs will only have access to the information they need, no more and no less.

As a result of splitting the database, it is reorganized into two files: the server database, which contains tables with data, and the client database, which stores all other objects – requests, forms, reports, credit card data, etc. In addition to the fact that it increases the degree of protection against SQL injection, the separation of the database also has a positive impact on the performance of the web server, since only the data is sent over the network.

Using advanced protection systems

Another reliable option is to use hardware solutions running on top of iptables or ipfw. There are various intrusion detection systems on HIDS servers, including the popular OSSEC. Yes, this solution is more complicated in terms of implementation, but the efficiency of blocking this kind of attack is close to 100%.

Summing up

Finally, it should be noted that it is best to use several methods in combination. And even in this case, one hundred percent protection from SQL injection you can not be guaranteed - vulnerabilities can always be. With each new version of PHP language developers improve it by eliminating the various vulnerabilities, so do not forget about the regular updates to the software. That's the end of our article, and thank you for your attention. Have a nice day!