Keeping Data Separate

I have seen discussions in forums where some people have put forward the argument that escaping all of the data fields is sufficient to ensure that the database call is secure and that injection cannot occur. That is true provided that in fact every single data field is escaped. It is even true if all of the data has been properly validated and where only those data fields that are allowed to contain content that might be confused for SQL are escaped.

Where that argument falls down is that you need to be completely certain that all of the fields actually are escaped or are properly validated so that they can't possibly contain anything that could cause problems. The amount of code that you actually need to look at to check that security is being maintained each time that a change is made to the code is relatively large and a good knowledge of both the programming language and SQL is required.

All it takes is for one person to make a small change to the code that results in a data field being allowed to contain a quote character but with the field not being escaped and you have a security hole that will allow SQL injection. Depending on the size of the application the actual code that needs to be checked every time can be spread over several files and be quite substantial.

The smaller the section of code that you need to look at to check security, the easier it is to check and the less likely it is to contain security holes.

For this reason keeping the SQL and the data completely separate by using prepare and bind calls can be considered to be more secure.

Consider the following code fragment:

if ($p = $db->prepare('UPDATE memaddr SET missing=?, mail_centre=?, address=?, suburb=?, state=?, postcode=?, country=?, phone=? WHERE member_number=? AND from_date=?')) {

It is easy to see that this database update is secure simply by examining the content of the prepare statement. That statement contains no variables but it does contain all of the SQL that will be executed by the call - therefore you know that the call is secure simply by examining that SQL. You don't need to know any PHP to check the security of the call because it is self enclosed. The code fragment says nothing about what the variables being passed to the bind statement contain and so looking just at that code we have no way to tell if the nine data fields being passed in the bind statement contain valid data or whether they contain junk. what we can tell is that regardless of whether the fields have been properly validated and whether they contain meaningful data or not, there is no way that any SQL that might be in any of those fields will actually be run by this statement.

With this approach the security of the SQL call can be determined by examining a single text string - that it is a fixed value and doesn't contain any variables clearly demonstrates that it is secure and that that injection into the SQL cannot occur.

The alternative of using a single query call that combines the SQL and data into the one call means that we need to look beyond the single text string to check security. With this particular update we would now have nine data fields jumbled in with the SQL. To gain the equivalent security we would need to add an additional nine function calls to escape each of the data fields in order to ensure that injection cannot occur. The person checking the security of the code now needs to know more than just SQL, they need to know what call that the programming language uses to escape data fields when jumbling them with SQL.

That's assuming that all of the fields are escaped regardless of any prior validation. If the fields have all been properly validated (which is the only way to prevent someone bulk inserting junk into your database) then many of the fields may not be able to contain characters that require the field to be escaped and so in order to speed up the processing for systems that have lots of people using it at the same time those fields that are not allowed to contain characters that need to be escaped will have the escape call removed. For example $missing can only contain 'Y' or 'N' so if it has been validated so that it can only contain one of those two values then escaping the field is unnecessary. The actual application that uses the code being used here as an example would only need the $address, $suburb and $country fields to be escaped as the validation applied to all the other fields means that they can never contain characters that would need to be escaped - of course by using the above code even those three fields do not need to be escaped because they are not jumbled with the SQL.

If you did use a single call with the SQL and data jumbled together and only escaped those three fields so as to avoid the need for unnecessary calls to escape fields that will never need escaping then the amount of code that you now need to check to ensure that injection cannot occur now expands to include not only the three escape calls but also the validation processing for the other six fields. Yes if all of that code is correctly written then you can improve the efficiency of the code by not escaping those six fields and your code will be just as secure as the example above - but in order to prove that it is as secure you have perhaps a hundred or so lines of PHP that need to be checked in addition to the SQL.

The more code you need to check, the more likely that you might miss something or misinterpret something. For this reason many people working on smaller applications that do not have a high volume of traffic will include the code to escape all nine of the fields when building a single string to run the above SQL query with the data jumbled with the SQL. The extra six escape calls are unnecessary and slow processing down slightly but this defence in depth approach reduces the amount of code that they need to check in order to ensure that SQL injection is not possible and so reduces the chance of them missing something. Of course they could reduce the amount of code to check even further without needing to add unnecessary escape calls by using prepare and bind. Doing so not only does away with the need to escape everything in order to avoid needing to include the field validation code in what you are checking but it removes the need to do any escaping at all making the code even more efficient.

In fact where your code is going to be making the same SQL call multiple times but with different values in the data fields, using prepare and bind allows for even greater efficiency as you only need to prepare the SQL once and can simply bind and execute for the second and subsequent calls.

Using prepare and bind is not only much easier to check from a security viewpoint, it is more efficient as well.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow