SQL Injection

In this tutorial, we'll learn about SQL injections and how to stay safe from them with examples.

SQL Injection is a technique where SQL commands are executed from the form input fields or URL query parameters. This leads to unauthorized access to the database (a type of hacking).

If SQL injection is successful, unauthorized people may read, create, update or even delete records from the database tables. This technique is mainly used by but not limited to hackers, pentesters, QAs and security researchers.


Example 1: SQL Injection Using Multiple Statement

Suppose we have a search form to search products by their ID on our website. The PHP code snippet to search product would look something like,

$prod_id = $_GET["prod_id"];

$sql = "SELECT * FROM Products WHERE product_id = " . $prod_id;

Let's take a look if user submits 20, how SQL is interpreted,

SELECT * FROM Products WHERE product_id = 20

Nothing suspicious, right?

What if user inputs 20; DROP TABLE Products;? Let's take a look at SQL statement again,

SELECT * FROM Products WHERE product_id = 20; DROP TABLE Products;

Now this SQL statement also deletes the Products table from the database based on input data. This was possible becaues most database systems can execute multiple statements at the same time.

How SQL is injected in an app?
Example: SQL Injection

Example 2: SQL Injection Using Always True Condition

Another way to perform SQL injection is by passing a condition that always results in TRUE so that the data is always fetched no matter what.

Let's take a look at another PHP code snippet where we have a login form in our website and we need to fetch users by providing credentials.

$username = $_POST["username"];
$password = $_POST["password"];

$sql = "SELECT * FROM Users WHERE username = \"" . $username . "\" AND password = \"" . $password . "\"";

If user inputs username as root and password as pass, the SQL will interpret,

SELECT * FROM Users WHERE username = "root" AND password = "pass"

This code snippet looks fine when user inputs correct username and password.

What if the user inputs username as invalid_user" OR "1"="1 and password as invalid_pass" OR "1"="1? Let's take a look at how SQL interprets.

SELECT * FROM Users WHERE username = "invalid_user" OR "1"="1" AND password = "invalid_pass" OR "1"="1"

Since, "1"="1" is always true, no matter what the username and password user enters, SQL will fetch all the users from the database.


How to Protect SQL Statements From Injections?

Validate User Input

We should always validate user's input data before actually sending them to the database. Some best practices include, trimming spaces, parsing special characters, limiting the input size, etc.

For example,

$data = $_POST["name"];

$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);

Here, this PHP code snippet validates input data to some extent.


Using ORMs

ORM (Object Relational Mapping) is a tool that basically parses SQL statements into programming language code and vice-versa.

If we use ORM's, we don't have to write raw SQL for the most part. Since ORM's are designed by following good practices and security protocols, they are safe and easy to use.

For example,

SELECT * FROM Users WHERE id = 5;

will look like

select(Users).where(Users.id == 5)

in SQLAlchemy ORM for Python code.


Using Prepared Statements

Another best idea to protect SQL statements from being injected is by using prepared statements.

Prepared statements are basically SQL statements but with placeholders. The passed arguments are just replaced in place of placeholders.

For example,

$sql = "INSERT INTO Users (first_name, last_name, email) VALUES (?, ?, ?)";

mysqli_stmt_bind_param($sql, "sss", $first_name, $last_name, $email);

$first_name = "Harry";
$last_name = "Potter";
$email = "harrypotter@mail.com";

mysqli_stmt_execute($stmt);

Here, the values are only placed in place of ? and the structure of SQL statements are preserved.


SQL Injection is a very common way of hacking any web application. Almost 51% of hacking done each year is done by injecting SQL.

If we use raw SQL statements while building our application, we must test and verify them thoroughly.

Also, if we are building a real world application, it's always a good idea to use frameworks (such as Django, Laravel, ASP.net etc.) instead of writing code from the scratch. It's because these frameworks handle SQL injection as well as many commonly occuring issues by default.

Did you find this article helpful?