How to Protect Your Website Against SQL Injection Attacks

Thanks to Chris Lienert and Guido Tonnaer for kindly helping to peer review this article.

Of all the attacks that can be staged against websites, SQL injection is among the most dangerous and pervasive kind, and has been used to deal real damage to businesses and organizations in the past year. The scheme has been used to target well-known organizations and firms, including TalkTalk, VTech, Wall Street Journal and the U.S. government.

In a nutshell, SQL injection — also referred to as SQLi — uses vulnerabilities in a website’s input channels to target the database that sits in the backend of the web application, where the most sensitive and valuable information is stored. The scheme can be used by attackers to steal or tamper with data, hamper application functionality, and, in a worst-case scenario, gain administrative access to the database server.

Here’s what you need to know about SQL injection and how to protect your site against it.

How SQL Injection Attacks Work

SQL injection attacks are staged by sending malicious SQL commands to database servers through web requests. Any input channel can be used to send the malicious commands, including <input> elements, query strings, cookies and files.

To see how it works, suppose you have a login form that takes a username and password:

When users enter their credentials and press the “log in” button, the information is posted back to your web server, where it is combined with an SQL command. For instance, in PHP, the code would look something like the following:

$sql_command = "select * from users where username = '" . $_POST['username']; $sql_command .= "' AND password = '" . $_POST['password'] . "'"; 

The command would then be sent to a database server, and the resulting dataset would determine whether the username and password correspond to a valid user account. An average user inputting “john” as username and “123456” as password (never use that password, by the way) would translate to the following command:

SELECT * FROM users WHERE username='john' AND password='123456' 

But what if the user decides to try something else, such as the following:

The resulting command would be the following, which would always return a non-empty dataset:

SELECT * FROM users WHERE username='john' OR 1=1; -- ' AND password='123456' 

The snippet would possibly allow the user to bypass the login screen without having proper credentials.

This is one of the simplest forms of SQL injection. With a little more effort, the same user can insert new user accounts, and delete or modify existing user accounts. In pages that display results, the same scheme can be used to display records and information that would otherwise be restricted to normal visitors, or to change the contents of records.

In more severe cases, where the connection to the database server is made through an administrative account (such as “root” in MySQL or “sa” in MS SQL Server), the attacker can go as far as fully compromising the server’s operating system. On Windows servers, this can manifest itself in the attacker executing extended stored procedures such as xp_cmdshell. In one case, attackers used an SQL injection vulnerability to create user accounts on the compromised server, enable the Remote Desktop feature, setup SMB shared folders and upload malware — aside from practically messing up everything that was stored in the database.

How to Protect Yourself Against SQL Injection Attacks

With user input channels being the main vector for SQL injection attacks, most of the defensive methods involve controlling and vetting user input for attack patterns.

Here are several measures that can ensure user input safety.

Never trust user input

The first rule of thumb about user input is “don’t trust and verify,” which effectively means all forms of user input should be considered malicious unless proved otherwise. This accounts not only for simple input boxes such as text areas and text boxes, but for everything else as well — such as hidden inputs, query string parameters, cookies and file uploads.

Just because the browser’s user interface doesn’t allow the user to manipulate an input, it doesn’t mean that it can’t be tampered with. Simple tools such as Burp Suite enable users to capture HTTP requests and modify anything, including hidden form values, before submitting them to the server. And if you think yourself clever by Base64 encoding your data, it can easily be decoded, modified and re-encoded by malicious users.

Validate input strings on the server side

Validation is the process of making sure the right type of input is provided by users and to neutralize any potential malicious commands that might be embedded in input string. For instance, in PHP, you can use the mysql\_real\_escape\_string() to escape characters that might change the nature of the SQL command.

An altered version of the previously-mentioned login code would be as follows:

$con=mysqli_connect("localhost","user","password","db");
$username = mysqli_real_escape_string($con, $_POST['username']); 
$password = mysqli_real_escape_string($con, $_POST['password']); 
$sql_command = "select * from users where username = '" . $username; $sql_command .= "' AND password = '" . $password . "'"; 

This simple modification would protect your code against the attack that was presented by adding an escape character (\) in front of the single quotes that were intentionally added by the malicious user.

A note on validation: If you’ve added client-side validation functions, well done. But don’t rely on it as a defensive measure against SQL injection attacks. While client-side functions might make it a notch harder to send malicious input to your server, it can easily be circumvented with a few browser tweaks and tools such as the one just mentioned. Therefore, you need to complement it with server-side validation.

Some programming platforms, such as ASP.NET, include built-in features that will automatically evaluate user input for malicious content on page postbacks. But they can be circumvented by hackers with enough nerves and subtlety, so you should nonetheless run user input through your own security check procedures. You can never be too cautious.

Use command parameters

A better alternative to escaping would be to use command parameters. Command parameters are defined by adding placeholder names in SQL commands, which will later be replaced by user input. ASP.NET has a very intuitive and easy-to-use set of APIs for this purpose.

The following code, written in C#, shows how you can use the command parameters to protect your website against SQL injection:

SqlCommand cmd = new SqlCommand ("SELECT * FROM users WHERE username=@username AND password=@password",con); 

SqlParameter username = new SqlParameter(); username.ParameterName = "@username"; username.value = txtUsername.Text; cmd.Parameters.Add(username); 

SqlParameter password = new SqlParameter(); password.ParameterName = "@password"; password.value = txtPassword.Text; cmd.Parameters.Add(password); 

You start by creating a SqlCommand object and using the @parameter_name paradigm in the command string where user input should be inserted.

You then create instances of SqlParameter objects, in which you insert the user input, instead of directly concatenating it with the command string.

Finally, you add the SqlParameter object to the SqlCommand object’s Parameters collection, which will replace the parameters with the provided input. ADO.net takes care of the rest.

In PHP, the equivalent is prepared statements, which is a bit more involved than its ASP.net counterpart. You can explore it here.

Explicitly cast your input

This tip is for languages such as PHP, which are weakly typed, which means you do not usually define data types for variables, and the language automatically takes care of converting different data types between each other.

Explicit casts can act as a shortcut to escaping input where non-string types are involved. So, if you’re expecting the user to input an int for the age parameter, you can ensure the safety of the input with the following code in PHP:

$age = (int)$_POST['age']; 

Take note that this snippet only validates the input’s type, not its range. So you’ll have to run other code to make sure the user doesn’t enter a negative age — or an unrealistic one such as 1300.

Also, another best practice is to avoid using single quotes in SQL commands where non-string input is involved. So instead of using the following code …

$sql_command = "select * from users where age = " . $age; 

… it would be a bit safer to use the following one:

$sql_command = "select * from users where age = '" . $age . "'"; 

How to Root Out SQL Injection Vulnerabilities

As a general practice, you should check the code of every page for places where you combine page contents, commands, strings, etc. with sources that might come from users. Vetting your source code for vulnerabilities and security holes should be an inherent part of your software development process.

You can also use scanning tools such as sqlmap to crawl your site’s pages for potential SQL injection vulnerabilities. In fact, hackers frequently use this tool to find and exploit SQL injection attack vectors on targeted websites, so why not use it to make them more secure?

Your Last Line of Defense

No matter how much you harden your website’s security, you must prepare yourself for the day when SQL injections do take place. After all, as it is widely known in the cybersecurity industry, defenders have to win every fight, but hackers only have to win once.

Here are a few tips that will help you minimize the damage when you do become a SQL injection victim.

Avoid administrative privileges

Using the “root” or “sa” accounts to connect your web application to your database server is one of the worst mistakes you can commit. As I’ve already mentioned, a compromised administrative account can potentially give hackers access to the entire system. Even non-administrative accounts that have access to all databases within a server can be damaging, especially if the database server is being shared among different applications and databases.

Therefore, it’s best to use an account that only has simple read-write permissions to the specific database that sits behind your website, so in case your site becomes hacked through SQL injection, the scope of damage remains within the boundaries of that single database.

A more advanced approach would be to use separate connections for code segments that read from or write to your database, and further cull down permissions and roles for each segment. For instance, list pages — which do not make any modifications to the database but extensively use search parameters — can be coded to use a read-only connection to the database in order to further harden your code against mishaps.

In MySQL, improve security by limiting access to the user account to specific IP address ranges instead of the “%” model, in order to prevent compromised accounts from being accessed from remote locations.

In MS SQL server, I would strongly advise you to use the Windows Authentication model, which would limit hackers’ access to the database and make sure they won’t be able to use other channels to get into your database.

Also, unless you’re planning on using some of the advanced features of SQL Server, it is preferable to set up the windows service to use a limited account instead of the high privilege “Local System” account. This will minimize damage in case the “sa” account becomes compromised.

Encrypt sensitive data

Encrypt sensitive data in your database. This includes passwords, security questions and answers, financial data, health information, and other information that might be useful to malicious actors. This will make sure that even if hackers lay hands on your data, they won’t be able to exploit it immediately, giving you time to discover the breach, plug the hole, and take other reactive measures such as enforcing password resets, which will make sure that the stolen data loses its value before the attacker deciphers it.

If you’re hashing your passwords, use strong algorithms such as SHA-2, which will soon become the industry standard for password protection. MD5 and SHA-1 are obsolete and can be reversed.

For other forms of encryption, take care where you store the keys, and never put all your eggs in one basket. There would be no point in using encryption if the keys are right next to the encrypted data and hackers will easily gain access to them as soon as they compromise the server.

Don’t store sensitive data if you don’t need it

Whenever you store information in your database, consider how damaging it can become if it falls into the wrong hands, and decide whether you really need to store it or not. The Ashley Madison hack, which spilled the dark secrets and most intimate information of some 37 million people on the internet and dealt some serious damage, partly owed its success to the fact that the provider had refrained from wiping sensitive information from its databases.

So the bottom line is, don’t store sensitive information in your database unless you really have to. And even then, delete the information when it’s no longer of use.

Final Thoughts

SQL injection has been around for decades and will likely continue to top the vulnerability charts for years to come. It takes a few easy — but well-calculated — steps to protect yourself and your users against it, and it should be among your top priorities when auditing your source code for security holes.

The key to avoid being the victim of the next huge SQL injection data breach is first, to control and validate user input, and second, to prepare yourself for the “when,” not the “if.”

Frequently Asked Questions (FAQs) on Protecting Your Website Against SQL Injection Attacks