Code has been added to clipboard!

How to Prevent SQL Injection

Reading time 4 min
Published Aug 9, 2017
Updated Oct 11, 2019

TL;DR – An SQL injection is a type of hacking attack that lets an intruder modify databases from outside.

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:

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

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

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

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

Example
SELECT * FROM users; DROP TABLE customers

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

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

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

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

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

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

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

Example
$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.
Learn SQL
Introduction
Syntax
Data Types
Server Data Types
Commands
Commands List
Wildcards
Constraints
Aggregate Functions
Date Functions
Date Format
Injection
SQL Server Hosting
Views
Auto-incrementation
SQL Operators
AS
AND & OR
IN
BETWEEN
WHERE
GROUP BY
HAVING
ORDER BY
LIKE
NOT
NOT EQUAL
UNION
NULL
NOT NULL
DEFAULT
UNIQUE
FOREIGN KEY
PRIMARY KEY
CHECK
Indexes
ALTER TABLE
CREATE DATABASE
CREATE TABLE
DELETE
DROP
INSERT INTO SELECT
INSERT INTO
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
UPDATE
FULL OUTER JOIN
INNER JOIN
JOIN
LEFT JOIN
RIGHT JOIN
AVG()
COUNT
FIRST
LAST
MAX
MIN()
SUM()
LEN
UCASE
MID
NOW
ROUND
FORMAT
LOWER
CONVERT
ISNULL