How to Prevent SQL Injection

What Is an SQL Injection?

An SQL injection is a type of a website attack that allows a hacker to mess with SQL queries. To understand what damage can such an attack do, you need to remember SQL is used in working with databases. This means an intruder can gain access to the data you keep.

If you're lucky, they might just insert or alter something in your database. However, most SQL injections result in destroying or stealing sensitive information. The results can be especially devastating for huge businesses – e.g., Starbucks had a ton of financial and payroll information stolen in 2019.

In a most extreme case, a hacker can even take over the control of your database entirely. Therefore, it's crucial to understand how to prevent SQL injections.

Basic SQL Injection Examples

To get a better idea on what is an SQL injection, we will see a few basic examples. In the code snippet below, we assign a string value to a variable called txt_user_id. We will fetch this variable via user input:

txt_user_id = getRequestString("user_id");
txt_sql = "SELECT *  FROM users WHERE user_id = " + txt_user_id;

1=1

Let's assume the initial goal of this code was to create an SQL statement which selects a user with a certain ID. There has to be a way to distinguish a valid input from a smart input such as this:

user_id:

SELECT * FROM users WHERE id = 105 or 1=1

This statement is valid. All rows from users will be returned, because WHERE 1=1 is true no matter what.

A table of confidential information, such as usernames and passwords, can easily become a target of such insertion.

""="" is Always True

The form you see below is commonly used to verify a user's login to a website:

User Name:

Password:

u_name = getRequestString("user_name");
u_pass = getRequestString("user_pass");  

sql = "SELECT * FROM users WHERE username ='" + u_name + "' AND pass ='" + u_pass + "'"

A hacker could get access to an unsecured database by simply typing "" or ""="" into the input form. The result would be valid and return all the rows from the users table, since WHERE ""="" is always true:

SELECT * FROM users WHERE username ="" or ""="" AND password ="" or ""=""

Batched SQL Statements

Usually, databases support joined SQL queries (written in one line and separated by semicolons). The query below will return all the rows from the users table, and then delete the table called customers:

SELECT * FROM users; DROP TABLE customers

Imagine we had the following server code and the following input:

txt_user_id = getRequestString("user_id");
txt_sql = "SELECT *  FROM users WHERE user_id = " + txt_user_id;

User id:

The code at the server would then create a valid SQL statement like this:

SELECT * FROM users WHERE id = 105; DROP TABLE customers

Preventing SQL Injection Using Parameters

Some web development practices use a dictionary of banned words (blacklists) as an SQL injection prevention. That is poor practice in most cases. Most of the words in the blacklist (e.g., delete, select or drop) could be used in common language.

The only proven way to protect a website from SQL injection attacks is to use SQL protection parameters. These are custom values, added to SQL query at the time of execution. In an SQL statement, the SQL protection parameters are defined by an @ marker:

txt_user_id = getRequestString("user_id");
txt_sql = "SELECT *  FROM users WHERE user_id = @0";
db.Execute(txt_sql,txt_user_id);

When an SQL query is processed, each parameter is checked to ensure safety. The markers are always treated like input text and not a part of an SQL query:

txt_nam = getRequestString("company_name");
txt_psc = getRequestString("passcode");
txt_add = getRequestString("address");  
txt_sql = "INSERT INTO customers (company_name,passcode,address) Values(@0,@1,@2)";
db.Execute(txt_sql,txt_nam,txt_psc,txt_add);

Simple Examples

The examples below show how to set up SQL injection protection parameters in ASP.NET and PHP.

In the first example, you can see an application of the SELECT statement in ASP.NET:

txt_user_id = getRequestString("user_id");
sql = "SELECT * FROM customers WHERE customer_id = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txt_user_id); 
command.ExecuteReader();

Now here is an example of using the INSERT INTO statement in ASP.NET:

txt_nam = getRequestString("company_name");
txt_psc = getRequestString("passcode");
txt_add = getRequestString("address"); 
txt_sql = "INSERT INTO customers (company_name,passcode,address) Values(@0,@1,@2)";
command = new SqlCommand(txt_sql); 
command.Parameters.AddWithValue("@0",txt_nam);
command.Parameters.AddWithValue("@1",txt_psc); 
command.Parameters.AddWithValue("@2",txt_add);
command.ExecuteNonQuery();

In this last example, we will be using an INSERT INTO statement in PHP:

$stmt = $dbh->prepare("INSERT INTO Customers (CompanyName,Passcode,Address) VALUES (:nam, :psc, :add)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':psc', $txtPsc);
$stmt->bindParam(':add', $txtAdd);
$stmt->execute();

How to Prevent SQL Injections: Useful Tips

• The most basic SQL injections can be prevented by filtering input. However, it will not stop more complex attacks.
• To prevent maximum SQL injection damage, it is recommended to separate your sensitive data and store it in multiple databases.
• Customize your error messages so they don't give away any information about the structure of your database to a potential intruder.