Use PDO to Avoid SQL Injection

Ten years ago, people talked about SQL Injection. Today, people still talked about this. The difference is, SQL Injection used to be very easy. For some old website, you might simply input ' or 1=1 -- in username box and login yourself without password. Nowadays, people at least use some filtering method to escape so called dangerous characters like . However, this is still not enough to protect database from sophisticated attacks. The best way to solve this problem is telling SQL server SQL query and variables separately instead of splicing SQL string (combing things together and pass the entire SQL query to SQL server). If SQL server knows something is variable, it won’t incorrectly treat it as part of structure of SQL query. This is exactly how PDO works to avoid SQL Injection.

I recently upgrade all my PHP code to use PDO to connect MySQL. Here I recommend to use the newest PHP version since there’re some vulnerabilities in previous PHP version which allows injection through PDO (by using the vulnerability of encoding, I’ll discuss later).

Before I go to PDO, let’s first look at some examples on stackoverflow about how injection happens even when filter functions are used. I use mysql_real_escape_string() as a example of filter function. It’s similar if addslashes() is used.

Even if we use single quote characters, we can’t entirely avoid SQL injections.

Even we find a really secure escape function one day, this is still not good enough. Because we change the original string the user inputs.

PDO is a new database connection abstraction library for PHP5, which supports giving SQLserver query and variables separately. By this way, we don’t need to pre-process user inputs at all because we know SQLserver will treat all user inputs as variables. However, for PHP version before 5.3.6, there’s a vulnerability that may leads to SQL injection. The reason is these versions use native prepare (prepare the whole SQL query and then pass to SQL server) by default while ascii is always used as character set. If SQL server doesn’t use ascii, there might be some vulnerabilities attackers can make use of. If you must use these versions, you should let SQLserver do such preparations instead of letting PHP do them. You can set this option by the following PHP script after you create a new PDO object.

For compatibility and portability reason, I recommend you always add this in your PHP code. The following script is my version of MySQL connection library.

Then I can just simply communicate with MySQL database in my code

By the way, it’s also easy to make a transaction with PDO.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">