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.
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"
"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.
$data = $_POST["name"]; $data = trim($data); $data = stripslashes($data); $data = htmlspecialchars($data);
Here, this PHP code snippet validates input data to some extent.
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.
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.
$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 = "firstname.lastname@example.org"; 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.