Security Risks and Precautions

SQL Injections

When a query such as on the below is executed:

SELECT * FROM pupils WHERE forename = 'Joe' AND surname = 'bloggs';

A SQL server runs this query showing all details for a particular student.

It will use an HTML form to allow the user to enter data and then pass that data to a PHP page to execute the query.

HTML Code

<html>

<head>

<title>Pupil Search Details

</title>

</head>

<body>

<h1>Pupil Search Page</h1>

<form action="pupilsearch.php" method="post">

Forename:<br>

<input type="text" name="forename" >

<br>

Surname:<br>

<input type="text" name="surname" >


<br><br>

<input type="submit" value="Submit">

</form>

</body>


Rendered HTML

What is happening in the PHP form?

$pupilforename = $_POST["forename"];

$pupilsurname = $_POST["surname"];

$querystring = "SELECT * FROM pupils WHERE forename = '".$pupilforename."' AND surname = '".$pupilsurname."'";

The values from the HTML form is used to construct the SQL query by simply concatenating the values into the query string. It is a simple SELECT query to show all pupils with the forename and surname that is entered.

Example Input and Output

The query string is shown just for demo purposes, you will notice that there is only one single result.

How to perform a basic injection

If we add the following piece of data in the name field, then the second part of the statement (the LIKE condition) will always evaluate to TRUE. As this is the case this will then cause the SQL query to display all of the results.

Bloggs' OR surname LIKE '%

Batch SQL Injections

It is usually possible to run multiple SQL statements by separating them with a ; 

We can use this by inputting the value Bloggs'; DROP TABLE pupils; 

This will execute the following SQL statement:

SELECT * FROM pupils WHERE forename = 'Joe' AND surname = 'Bloggs' ; DROP TABLE pupils;

This would work in a mySQL database natively but this type of query would need to be executed in PHP using the multi_query function/method in order for both queries to be executed. This type of query would be perfectly fine to exemplify an SQL injection in an exam.

How to prevent SQL Injections

We can use pre-prepared statements to combat this. Rather than constructing these SQL statements being built by concatenation - certain parameters are in the SQL query are bound to the form parameters.

First of all we will modify our query string to:

$querystring = "SELECT * FROM pupils WHERE forename = ? AND surname = ?";

The ? are placeholders which we will use to insert values into our SQL statement.

Preparing the statement

We will initialise the statement for execution by using the following code.

$statement = mysqli_stmt_init($connection);

The following line of code will prepare the statement (returning true if it is successful)

if (mysqli_stmt_prepare($statement, $querystring)) {

Binding the Parameters

Now we will bind the parameters ( from our form) into the SQL statement.

mysqli_stmt_bind_param($statement, "ss", $pupilforename, $pupilsurname);

The “ss” specifies that the parameters are string values but there can be other types of data as shown below:

Executing the Statement

Now we execute the statement

if(mysqli_stmt_execute($statement)) {

}

The if statement returns true if the statement executes successfully. Then as with a usual query we can then display the results as appropriate.

Other methods to prevent SQL attacks