SQL Injection Prevention

SQL injection is a serious security vulnerability that can compromise your database. This tutorial explains what SQL injection is and how to prevent it.

Understanding SQL Injection

SQL injection is a code injection technique where malicious SQL code is inserted into an application's input fields, allowing attackers to manipulate database queries. This can lead to data breaches, data modification, or even complete database destruction. It's one of the most common web hacking techniques.

How SQL Injection Works

SQL injection typically occurs when user input is directly incorporated into SQL queries without proper sanitization or parameterization. Attackers exploit this by entering malicious SQL code disguised as normal user input. This malicious code is then inadvertently executed by the database, potentially granting unauthorized access or control.

Vulnerable Code Example

This example demonstrates vulnerable code that's susceptible to SQL injection. The user's input ('UserId') is directly concatenated into the SQL query:

Vulnerable Code


txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

Exploiting the Vulnerability (1=1)

A malicious user could enter "105 OR 1=1" as the 'UserId'. The resulting SQL statement becomes:

Malicious SQL


SELECT * FROM Users WHERE UserId = 105 OR 1=1;

Output


(This query will return ALL rows from the Users table because '1=1' is always true.)

This grants the attacker access to all user data.

Exploiting the Vulnerability ("="")

Another attack vector uses empty strings.

Vulnerable Code


uName = getRequestString("username");
uPass = getRequestString("userpassword");
sql = 'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' + uPass + '"';

Entering "" OR ""=""" in the username field results in:

Malicious SQL


SELECT * FROM Users WHERE Name ="" OR ""="" AND Pass ="" OR ""="";

Output


(This returns all rows from the Users table because ""="" is always true.)

Exploiting with Batched Statements

Some databases allow multiple statements separated by semicolons. This is another attack vector.

Malicious Input


SELECT * FROM Users; DROP TABLE Suppliers;

Output


(This first returns data from the Users table, then deletes the Suppliers table!)

Preventing SQL Injection

The best way to prevent SQL injection is to use parameterized queries (also known as prepared statements). Parameters are placeholders for values that are added to the SQL query at execution time in a secure manner.

Using SQL Parameters

Instead of concatenating user input directly into your SQL, use parameters:

Safe Code (C#)


txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL, txtUserId);

Parameters are represented by @ markers. The database engine treats them as literal values, preventing SQL injection.

More Parameterized Query Examples

Here are examples using parameterized queries in different programming languages:

ASP.NET (SELECT)


txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0", txtUserId);
command.ExecuteReader();

ASP.NET (INSERT INTO)


txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0", txtNam);
command.Parameters.AddWithValue("@1", txtAdd);
command.Parameters.AddWithValue("@2", txtCit);
command.ExecuteNonQuery();

PHP (INSERT INTO)


$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City) VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();