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:
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:
$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:
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:
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?
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.
