PHP Freaks

3. SQL injections

One of the most common problems with security in web applications is SQL injection. To begin with I will present this comic for you:

The comic clearly illustrates the problems with SQL injection. If you do not get it, do not worry, you will in just a moment.

SQL injections work by injecting SQL into the queries you have already written in your script. Often you will pass some sort of variable data to your queries; this data might be influenced by user input. In the above comment we might imagine that the school had a query that looks something like this:

The above snippet works. As long as users input data that conforms to an expected format. Now, the mother in the comic did not provide expected data, rather she injected an entire additional query into the existing query. Let's take a look at how the query looks when we enter the string given by the mother:

(Note: PHP does not support stacking queries with all DBMSs. MySQL in particular)

As you probably know, a semi-colon ends a query and most times it is actually required, but PHP just adds it automatically if you omit it. Therefore, by closing the string and finishing the query by entering the closing parenthesis and a semi-colon we will be able to add an additional query that drops the student table. The two hyphens at the end make whatever comes after it a comment, so whatever remaining characters that might have been in the original query will simply be ignored.

It should not take too much brain power to figure out why this is a bad thing. Malicious users will basically be able to execute any kind of queries they would like to. This can be done for various purposes. It could be retrieving confidential information or destroying your data just to name a few.

3.1. Protecting your script from SQL injections

Fortunately, protecting yourself from SQL injections is rather easy. It is just a matter of calling a single function which make data safe for use in a query. How you should do this depends on which PHP extension you are using. Many people use the regular mysql extension, so let us start with that one. That particular extension has a function called mysql_real_escape_string(). Let us take a look at how that one works with a simple example that illustrates its usage:

As you see, doing it is incredibly easy yet many people fail to do this and only find out when it is too late. Other extensions support something called prepared statements. An example of a such extension is PDO (PHP Data Objects). Let us take a look at how that works:

If you have many fields you need to use in your query then it might be a little difficult remembering the order of all these different question marks which act as place holders for the data. An alternate syntax is using named parameters. In our case it would look like this:

Obviously, in our case this would not have any benefits, but as I said, if you have many parameters then you might find that more useful. There can be other reasons why using prepared statements would be useful, but I will leave that to research for yourself.

The mysqli (MySQL improved) extension has support for prepared statements as well, so if you are using that then check out its documentation to see the syntax.

The golden rule regarding this is that nothing is to be trusted and all data should be escaped.

Additionally, I mentioned earlier that users should not get information from error messages. Not only is it irrelevant, but it may also be information that may aid people with malicious purposes. You may sometimes be told that you should add or die(mysql_error()) to the end of your query calls to functions like mysql_query(). However, you should not do that. By doing that you are no longer using PHP's error and exception handling functionality and you remove the opportunity to control whether errors should be displayed or not. In my opinion the best solution would be to use PHP's exceptions. If you do not want to do that then at least do something like or trigger_error('Query failed: '. mysql_error()). By doing that you are utilizing PHP's built-in functionality and you will be able to use the methods discussed under Error Reporting. Moreover, ending script execution with die() is simply bad practice. You will not be able to give the user a proper error page and you will not be able to do any cleaning up for the rest of the script.