To use this website fully, you first need to accept the use of cookies. By agreeing to the use of cookies you consent to the user of functional cookies. For more information read this page.

The importance of using prepared statements

18 Apr 2016 at 18:04
Prepared statements are something that every SQL developer should know about and yet, I asked several and not one of them knew what they are. This article intends to explore these fantastic tools and explain their benefits.

The other day there, I was astonished when I spoke to a senior web developer who had absolutely no idea of what a 'prepared statement' is. I then realised, that actually, many companies who put a lot of time into developing databases haven't even updated their SQL drivers for PHP, since I know a few who do not.

PHP provides a new MySQLi driver which is the now MySQL driver of choice for a lot of companies. But many of these companies do not know that actually MySQLi also provides a feature called prepared statements.

Preparing a statement is one of the most important steps in the development of an application that access a database. Why you ask, well this article will explain.

What a prepared statement is

A prepared statement is an SQL query that has been given placeholders rather than values. The following shows a standard SQL statement:

SQL
SELECT * FROM Database.Users WHERE username=40 AND password='password';							
						

This statement is fixed. That means that no data is pushed into the query. The following PHP shows what happens when data is not fixed:

SQL
$query = "SELECT * FROM Database.Users WHERE username='".$_GET['test']."' AND password='".$_POST['password']."'";							
						

The above statement actually relies on user input through a GET request. If the user simply inserted the value "testUser" into the GET field and the password "jack" into the POST field, the statement would look like:

SQL
SELECT * FROM Database.Users WHERE username='testUser' AND password='jack';							
						

This is fine, but what if the user put into the password field the value jack' OR password != 'jack

Let's see the whole statement now:

SQL
SELECT * FROM Database.Users WHERE username='testUser' AND password='jack' OR password != 'jack';							
		

Now let's run this.

You can login by not specifying the correct password?

You can login by not specifying the correct password?

The problem comes from mixing data with the query to be executed.

Why bother with prepared statements at all?

There are three main reasons for the use of prepared statements when working with MySQL and PHP. Most of these reasons tie in together but they can be split into:

  • Security
  • Convenience
  • Efficiency

Whilst the latter is actually not always the case, it can be the case.

Security

The use of prepared statements is considered a security measure since it prevents users inserting extra information.

The statement is sent in two parts, one part containing the statement and the second containing the variable values and their types. This adds one extra layer of protection and makes it harder for it to be intercepted.

Statements are composed on the server side once the values have been received.

Prepared statements are also able to prevent inserting keywords into the actual statement as shown above. In general this makes it much easeir for the programmer to develop a secure system.

Convenience

It can be a pain having to compose a statement which contains both ' and " characters in it since one or the other will need escaped. Prepared statements make it so easy to avoid having to do this since they escape characters automatically.

Prepared statements deal with a lot of these problems in general, making it more convenient to just use a prepared statement.

Efficiency

Prepared statements are more efficient because they are compiled once prior to execution. Variables are then placed in to the statement. If the statement is used over and over but the values change, the statement does not need to be recompiled. 

All of this leads to a more efficient program.