SQL Attacks Explained

By Alex Probert

Basics of an SQL Query

When you search any information in a textbox that will retrieve any information from a database, it will likely be using a SQL database to store and retrieve the data. So, when you search it will embed the text you typed into a SQL query, which will then search the database to retrieve/modify the data.

So, if you are searching for all details about all the people called join. You would type “John” into the search bar and it would be placed in the blue circle in the query.

SELECT * FROM Customers WHERE FirstName = John;

The query above will then select all information in the Customers table where the FirstnameName is equal to John. So, it could come back like the following, because these rows contain the word “John” (highlighted in blue):

ID	FirstName	LastName	Age	Country
3	John	Smith	34	England
7	John	Bloggs	64	Spain

SQL Injection

SQL injections are a malicious way to retrieve or cause damage to a database. It involves inserting a command into the search that will be placed in the query and will perform a task the owner doesn’t intend for it to do.

A common type of SQL injection and simplest is to insert a statement that will always be true, which is 1 = 1. An example can be seen below.

“SELECT * FROM Customers WHERE FirstName = ‘ ’ Or 1 = 1/*”;

Highlighted is the part that the information entered in the search bar will be placed within the query and will then be used to extract all the information out of the database table “Customer”.

How to mitigate the chances of a SQL Injection

One of the first basic ways to prevent a SQL attack would be to use mysqli_real_escape_string($input), this will remove any characters that may change the nature of the SQL command.

Another way to help prevent a SQL injection is to remove unwanted characters that the user may type in using the preg_match() function in PHP (if using). This can remove any unwanted characters that can be used to cause harm, some of these characters can include “ ” ( ) = ;.

Ensure to hash and salt all sensitive data (e.g. Passwords), if a successful SQL injections were to happen it will show all contents of the database. So, by making the sensitive data (Passwords) unreadable it will lower any damage that can cause harm to the subjects.

Prepared statements are also a good method to use to minimise the risks of a SQL injections, below is an example of a prepared statement.

$stmt = $conn->prepare(“INSERT INTO Customers (firstname, lastname, email, age, country) VALUES (?, ?, ?, ?, ?)”);

$stmt->bind_param(“sssss“, $firstname, $lastname, $Age, $Country);

In the above example, you have two lines of code. The first one being a template that is sent to the database, so the database will store the query but not execute it. Then the application will bind the parameters and values together, which will then execute the query. This prevents SQL Injections by simply sending both the query and the data separately, as the problem with SQL injections is that they mix the query and the data together. If it were to be done together, the data will become part of the body (the query).