FB

SQL Injection: The Definitive Guide

1 Comment / Last updated: September 27, 2022

SQL injection is a type of vulnerability that software developers aim to avoid.

In this guide, you’ll learn:

  • what SQL injection is
  • examples of SQL injection
  • how to prevent SQL injection

Let’s get into the guide.

 

What is SQL Injection and Why Is It Bad?

SQL Injection is a type of website attack that allows people to run a malicious SQL statement on a database.

An SQL injection attack can result in:

  • Displaying sensitive data from the database
  • Modifying data
  • Executing administrative operations
  • Dropping tables or the whole database

The consequences of an attack are:

  • Loss of confidentiality: databases often hold confidential or sensitive data, which can be displayed or retrieved.
  • Bypass authentication: an attack can allow someone to view the data about someone else, which can have a large impact depending on the software used.
  • Data integrity: data can be modified using this attack, meaning data is no longer truthful and accurate.
  • Outages: changes to the database can cause outages with the application if tables or other objects are dropped or altered.

 

A Simple Example of How SQL Injection Happens

How does an SQL Injection attack happen?

Let’s see an example.

 

Our User Form

This is a simple login page. It has a username and password field, and a Submit button.

Sample UI for SQL injection

When the user enters their username and password, there would be some code that gets the values that were entered and checks the database.

It may look something like this:

query = "SELECT COUNT(*) FROM user_table WHERE username = '"+ txtUsername +"' 
  AND password = encrypt('"+ txtPassword +"');"
result = database.run(query);
if(result > "0") {
  displayUserDashboard();
}

The SQL query string will select a value of 1 if the username and password match a database record.

Here’s what the user could enter:

  • Username: ben
  • Password: abc123

If a user enters a username of “ben” and a password of “abc123”, this is what the SQL query would look like:

SELECT COUNT(*)
FROM user_table
WHERE username = 'ben' AND password = encrypt('abc123');

So far so good! It should only show a Count value greater than 0 if there is a record that has a username of “ben” and matches the password that was entered.

 

SQL Injection

Now, what if the user enters these values?

  • Username: ‘OR 1=1; —
  • Password: abcdefgh

Notice that the username has an OR keyword, a semicolon and two dashes.

What does that do to the SQL query?

The query is then set to this:

SELECT COUNT(*)
FROM user_table
WHERE username = '' OR 1=1; --' AND password = encrypt('abc123');

The two dashes are a comment and cause everything after it to be excluded.

So our query looks like this:

SELECT COUNT(*)
FROM user_table
WHERE username = '' OR 1=1;

The OR 1=1 means that a record needs to have a blank username, or 1=1, which is true for every row.

The logic that follows means that the login is successful and the dashboard is displayed.

The result is that the user is able to login to the system without having an account.

This is just one example of SQL Injection.

 

Further Reading

OWASP (Open Web Application Security Project) official page on the SQL Injection vulnerability

Imperva’s description of SQL Injection

 

What Else Can SQL Injection Do?

Drop Tables or Delete Data – “Bobby Tables”

Here’s another popular example. A comic created by XKCD, often referred to by people when speaking or writing about SQL injection, about a kid called Bobby Tables:

sql injection bobby tables

This comic points out that a string can be used to drop a table from a database. It uses the same concept as above, by ending a query and starting a new query that drops a table.

 

Retrieve Hidden Data

SQL Injection attacks can also be used to retrieve hidden data.

Part of a query can be commented out or excluded.

For example, an events page has a list of events. The page shows events that are published, but there are also some “draft” events that are still being defined.

The SQL query filters out the draft events for the selected category like this:

SELECT id, event_name, event_details
FROM planned_events
WHERE category = ' + txtCategory + ' AND event_status = 'Published';

A string can be provided for the txtCategory:

"Food; --"

This would mean the event_status is ignored, and all events are returned that belong to the Food category.

SELECT id, event_name, event_details
FROM planned_events
WHERE category = 'Food';

 

Get Data From Other Tables

Data can be retrieved from other tables using UNION keywords.

A query like this:

SELECT id, product_name
FROM product
WHERE category = '+ txtCategory +'';

This query could be combined with a UNION string to get data from another table:

SELECT id, product_name
FROM product
WHERE category = 'x'
UNION
SELECT user_id, email_address
FROM users;

This would likely show zero records from the product table, but show all records in the user table. The attacker can make a guess at the column and table names until they get the right one.

 

Inspect the Database

Similar to the union example above, the attacker can inspect different properties in the database to learn more about it.

They can query data dictionary tables to learn things like:

  • Database time, locale
  • Database version
  • Settings on the database (parameters, cache values, etc)

All of this can be used to plan for further attacks.

 

Blind SQL injection

There’s another method called “blind SQL injection”.

It’s different because it doesn’t do anything to the database or show any results. It’s used to gather intelligence.

An attacker can insert a string that would cause the application to show a specific value if a condition is true, or show some kind of error.

The condition would be something that the attacker writes.

For example, they can write some code to use for an SQL Injection attack to display a number 1 if there is a user called “admin”. While they won’t see the actual table data, it’s still bad because they can infer or determine the result another way.

 

Further Reading

PortSwigger’s guide with details on different impacts of SQL Injection

OWASP guide to Blind SQL Injection

Netsparker guide to Blind SQL Injection

 

Preventing SQL Injection with Parameterised Queries

We’ve learned about what SQL injection is and why it’s bad. How can we prevent it from happening?

The most common way is to use a concept called “parameterised queries”.

This is done by using a feature built-in to your programming language that uses parameters to build the SQL query.

It can prevent the SQL Injection attacks mentioned above, as it prevents text values from being added to queries and from bad queries being run.

Here’s what a query looks like before parameterisation (it’s just generic code, not a particular language).

query = "SELECT COUNT(*) FROM user_table
  WHERE username = '"+ txtUsername +"' 
  AND password = encrypt('"+ txtPassword +"');"
result = database.run(query);
if(result > "0") {
  displayUserDashboard();
}

Here’s the code using parameterised queries.

query = "SELECT COUNT(*) FROM user_table
  WHERE username = @0
  AND password = encrypt(@1);"
result = database.run(query, providedUsername, providedPassword);
if(result > "0") {
  displayUserDashboard();
}

The code that builds the query uses parameters, which are the @0 and @1 parts. These are substituted with the providedUsername and providedPassword.

Now, this code is just generic pseudo-code. If you want to get some real examples in a range of languages, check out bobby-tables.com. They have pages with code snippets for adding this into your code.

 

Further Reading

Bobby-Tables.com, a list of languages and source code examples

OWASP SQL Injection cheat sheet

StackOverflow question and useful answers

Hacksplaining article on parameterised queries

 

Preventing Unauthorised Operations with Permissions

While parameterised queries are the major and the best solution in preventing SQL injection, there are other things you can do as well to improve the security of your database.

You can apply permissions in a way that prevents web site users from doing bad things to your database.

When you develop your website or web application, the website needs to use an account to access the database. This account will need permissions (also known as privileges) to access data.

It may be tempting to give them read and write access to the entire database because that’s easier.

However, if you design your privileges to only give the website user access to what they need, then this can improve security and help prevent SQL injection.

For example:

  • You may allow your website to SELECT from only a few tables and none of the data dictionary tables.
  • You may allow the website to INSERT or UPDATE data into a few tables
  • You may prevent all DELETE statements from your website user, and implement a kind of “soft delete” instead (where deletes are marked as inactive.

Those are just a few ideas on how to implement permissions. This could prevent users from running a Delete or Drop statement from the website.

It’s not the entire solution to SQL Injection. You’ll need to implement parameterised queries, but this is a good layer of security to add.

 

Further Reading

Oracle privileges

SQL Server permissions

MySQL privileges

PostgreSQL privileges

 

Using Whitelists to Restrict What Can Be Done

A whitelist is a type of data validation that has a list of all acceptable values to use in a query.

This is another additional defence against SQL Injection attacks. Parameterised queries are the most effective, and using whitelists is an additional option to use in some cases.

A common example is sorting your data. When you sort data using the ORDER BY clause, you specify either ASC or DESC. The ASC and DESC are the only two keywords allowed here, so you should add some code that only accepts those two values, and nothing else.

Another suggestion from OWASP is to accept a boolean for the sort order and map that to either ascending or descending.

This is not the only defence against SQL Injection and should be used alongside parameterised queries.

 

Further Reading

Bill Karwin slides on myths and fallacies

StackOverflow discussion about whitelisting

 

Things to Avoid When Protecting From SQL Injection

So we’ve covered using parameterised queries and a couple of other additional solutions to improve query security overall.

What are some things you should avoid when trying to protect your database from SQL injection?

 

Only escaping

Escaping your code is the process of removing specific characters. This is common with accepting code as an input, so that code snippets can be stored and the special characters like quotes or <> are kept intact.

This can be useful to save data, but it shouldn’t be used solely as a defence against SQL Injection.

 

Creating a blacklist of characters

At some point in our development lives, we may have thought it would be easy enough just to create a list of characters to scan for in our inputs and remove those.

This is a kind of blacklist. We add disallowed characters to a blacklist, which performs a validation of the input data and prevents these characters from being used.

We would block things like double dashes, special characters, and basically anything that isn’t a letter or a number, depending on the field you’re looking at.

This might work in theory, but there are many other inputs that can’t be planned for in advance. Other character sets or languages may be used and can get around this blacklist and cause problems for the database.

So, it can be tempting to add this, but it’s only a small part of the defence and may not be worth it.

 

Writing your own sanitisation function

With the concept of escaping, whitelisting, and blacklisting, it might make sense to add all of this logic and protection into a single reusable sanitisation function.

You would provide your input and this function would strip away all of the bad characters and check other validation rules.

It’s suggested that this is not a good approach. There are built-in functions in most programming languages that perform sanitisation and have been tested and widely used.

Using a built-in sanitisation function along with parameterised queries is an option, but just don’t write your own sanitisation function.

 

Further Reading

An article on PHP and SQL injection

StackOverflow – Arguments against using blacklists for protection

 

Real Cases of SQL Injection Attacks

SQL Injection isn’t just a theory or the topic of a popular XKCD comic. It has occurred in real businesses.

Here are some occurrences to help you understand the impact of it.

You can see that the attacks range from the early days of the public Internet to current times. The vulnerability has been around for a while and sites are still impacted.

For a full list of SQL Injection examples in companies, check out the list on the Wikipedia page.

 

SQL Injection Cheat Sheet

If you’re looking for an SQL Injection Cheat Sheet, here are some you can use:

 

Conclusion

I hope you’ve found this guide to SQL Injection useful.

Do you have any questions? Is there anything you feel is missing? Let me know in the comments below.

1 thought on “SQL Injection: The Definitive Guide”

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.