SQL injection is the injection of unwanted text into a SQL command that makes that SQL command perform an action it wasn’t intended to perform.

At its simplest, SQL injection works against a piece of SQL that allows values to be added to it by concatenation (sticking bits of strings together). A vulnerable piece of SQL would be:

Notice that the variables usersEmail and usersPassword will end up surrounded by single quotes.

This SQL could be used to log someone in, only returning the ID of a user who has matched BOTH the username and password.

Given the variables:
   usersEmail = “admin@test.com”
   usersPassword = “password123”

Our SQL concatenation would become:

Which would return the ID of all users named admin@test.com, only if their password was password123. Hopefully we’d only get one user here.
Given the chance, someone malicious would come along and enter:
   usersEmail = “admin@test.com”
   usersPassword = “’ OR ’1’=’1”

Our SQL concatenation would become:

This SQL would return the ID of all users where the email is “admin@test.com” and the password is blank (unlikely to be any users) OR where 1 = 1. Now 1 is always equal to 1 (hopefully), so this would return the ID of all users in the table! Not the desired effect of this SQL command.

The Results of SQL Injection Attacks

Attacks generally result in a negative effect on one or a combination of Confidentiality / Integrity / Availability. What can be the result of SQL injection?

• Tricking the underlying code e.g. log you in as admin without knowing the password (Confidentiality)
• Extracting valuable information from the database – credit card numbers, passwords etc (Confidentiality)
• Damaging the integrity of data on the database – change a price so you can buy something cheaper, alter an address so one person’s order is delivered somewhere else (Integrity)
• Affecting the service by damaging the database e.g. Dropping tables (Availability)
• Compromising the database server – some databases allow scripts to be run (Confidentiality / Integrity / Availability)

A Selection of Available Defences

Defences fall into the broad categories of Preventative / Detective / Corrective

• Parameterised queries - telling the database which parts of the query are data (preventative)
• Stored procedures (preventative)
• Input validation e.g. using regular expressions to check input (preventative)
• Escaping input (preventative)
• Using a WAF, IDS, IPS (preventative / detective)
• Encrypt data that could be valuable, like bank details and passwords (preventative)
• Disable custom errors – some languages (like ASP.NET) default to showing custom errors (complete error messages, including stack traces). This can show information like connection strings, so it’s best to switch custom errors off (preventative)
• Least privilege – if a query only needs to read from the database then run it as a user who only has read access (preventative)
• Check results – if you only expect one result and you get multiple then this may at least warrant a warning in the logs. Are you looking at the logs? (detective)
• Error reporting – some attempts at injecting SQL may cause errors – are you logging them? Are you SURE you’re looking at the logs? (detective)
• Use a NoSQL database? They may have their own issues, but it’s worth a thought (preventative)
• Testing – automated tests to attempt SQL injection (preventative / a test of the detective systems in use)
• Backups which can be restored in the event of compromise (corrective)

It’s common to use a number of the above options as part of an overlapping defence (aka Defence in Depth), so if one of the defences fails (e.g. someone accidentally disables input validation) then you should still be safe* from SQL injection

*safety is never absolute in security

For further information there's an excellent course on SQL injection by Troy Hunt, which can be found over at Pluralsight