How to Protect Your Website Against SQL Injection Attacks

By Ben Dickson
We teamed up with SiteGround
To bring you the latest from the web and tried-and-true hosting, recommended for designers and developers. SitePoint Readers Get Up To 65% OFF Now

preventing 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:

A log in form ready to be filled

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:

Injection code in a log in field

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.”

We teamed up with SiteGround
To bring you the latest from the web and tried-and-true hosting, recommended for designers and developers. SitePoint Readers Get Up To 65% OFF Now
  • No, no, no. Use prepared statements / command parameters, or use static queries. Fencing values with single quotes is what the reviled magic quotes setting did in PHP.

    Using escape string methods is too error prone. The most comprehensive way to stop SQL injection is to cast the data to the proper type, and use prepared statements.

    Also, password hashing should be done with a stretching algorithm: Bcrypt and PBDF2 are good examples. Raw SHA2 is NOT a good idea.

    • Ben Dickson

      All point agreed. For simplicity’s sake and educational purposes, SQLi prevention is tackled in a step-by-step manner here. Prepared statements is definitely the better way to go (as mentioned in the article), but
      Also, going into the details of hashing would be outside the scope of the article, that’s why we have sufficed to simple examples.

      • Dan Mikkelsen

        I think it would be a good idea just to mention the importance of using a salt with the password hash. If people with only little coding experience read this article, it would just help them some more if they afterwards look into how they should use a salt for their password hashes before saving them in the database.

    • Dan Mikkelsen

      I must say that I have yet to find one single example that demonstrantes why just simple escaping of the input string is supposedly so error prone.
      There are such an enormous amout of websites running with this single solution, without ever being hacked, so the danger of this command is greatly exaggerated. Only if there is some larger difference in the charset settings, could there be some complications with maybe the escaping character used being the wrong one.

      Something that instead should be mentioned is the importance of checking the user input for content like html tags or javascript, which could be executed on a page load if the data is output as text.

  • Oroszlány Zsolt

    Do not forget guys it can be “OR 2=2; –” or “OR a=a; –” or any other combination!

  • Oroszlány Zsolt

    I agree, if it is integer then it will be converted as integer ofc checking -/+ max/min length values too depending on x86/x64 system and column type etc. Storing string value is kinda tricky I think there will be always people with better knowledge than me who can “brake things”. Btw. as always I am using prepared statement. What do you think, specifiing input type and prepared statement is enough?

    • MagusZoldik

      To make it short, prepared statements can’t be break (if you never concatenate it with user input, of course).

      The query is evaluated without the bound variables, which don’t need to be escaped (never passed directly in query). But, according to the MySQL docs, a hint must be provided to the server for the type of bound
      variable, to create an appropriate conversion.

      • Oroszlány Zsolt

        Thank you @maguszoldik:disqus, I tend to be a little paranoid about these thinks. Your answer about prepared statement reassured me.

  • NCode Technologies

    Attacks on website is very common now a days. You blog is surely help us to maintain our website security and make safe them. I never thought about this in the technical way you describe via coding. Thanks for the article.

  • Webdesign Pomerode

    Use some framework, it’s better.
    =D
    Filter_sanitize is good =D

  • Benoit Adam

    And nothing about PDO?
    PDO protects the injection of a equality, that’s the key of the injection.

    • Andres Vaquero

      PDO is essentially ‘prepared statements’, which were mentioned. They can be done in MySQLi or PDO, but PDO is better!

  • html encode any input, regex test to verify contents of any input (server side) and unless it should be allowed simply replace any non-numeric or alphabetical characters with blanks then trim this will prevent any sql from ever reaching any command server side in a executable format, though stored procedures and layering are obvious additions to ensure security of the data layer.

  • The Informer

    PDO is good for stopping this. I use the fat free framework and it’s PDO wrapper which makes PDO much much easier to use. native PDO is quite a schlep.