SQL Injection Protection in PHP With PDO

SQL Injection Protection in PHP With PDO

Database abstraction layers like PHP's Portable Data Objects (PDO) are not a new concept, but a lot of developers don't seem to realise the security benefit they're getting for free by using them - inherent protection against SQL injection.
 

SQL injection is the buffer overflow of the web application world - it's been around forever, and every web application developer should know how to write secure code that's not vulnerable to it. For those not in the know, SQL injection is a technique whereby a malicious attacker can exploit inadequate data validation to inject arbitrary SQL code into your application's queries and have it executed as though it is a legitimate query. I won't go too deeply into SQL injection in this article, but here's a simple example:
 

The front page of your application has a login form, which is submitted to a PHP script to validate the user's credentials and allow or deny access to the application. The login form submits two variables by POST as follows:
username=fred&password=Fr3dRul3z
 

The POSTed data is then used to build an SQL query to validate the credentials, like this:
$sql = "SELECT * FROM users WHERE username = '".$_REQUEST['username']."' AND password = '".$_REQUEST['password']."'";
This would result in the SQL query:
SELECT * FROM users WHERE username = 'fred' AND password = 'Fr3dRul3z'
Assuming a row exists in the database with these credentials, the user would be allowed to log in. An attacker could easily circumvent this authentication scheme by escaping out of the username field into the SQL query by entering nothing into the password field and this into the username field:
' OR 1==1 --
The resulting SQL query string would look like this:
SELECT * FROM users WHERE username = 'fred' OR 1==1 -- ' AND password = ''
Which, as I'm sure you can see, would select all users from the database as the condition 1==1 will always be true. The rest of the query is discarded with the comment operator '--'. The way to avoid this kind of attack is to sanitise the data submitted to the form by escaping everything that could be used to escape the confines of the quotes around the fields (e.g. mysql_real_escape_string() if you're using MySQL). However, in a land far away somebody was inventing database abstraction layers...
The primary objective of database abstraction layers like PDO is clean abstraction in your code away from the database platform - so, theoretically, you could switch database platforms from, say, 

 

MySQL to PostgreSQL or Oracle with minimal changes to the code. In practice this depends heavily on how much your code relies on platform-specific features like triggers and stored procedures, but if you're not relying on them at all and you're just doing simple INSERT/UPDATE/DELETE operations it's a free ride. Sounds moderately useful, but nothing exciting, right? Right. Another neat feature invented a long time ago is prepared statements, and most database abstraction layers (including PDO) implement this as a way to perform the same query multiple times with different data sets (e.g. inserting a whole bunch of new rows). Now, when building statements with PDO, instead of building the SQL string manually as demonstrated earlier, we build the statement with placeholders like this:
$sql = "INSERT INTO fruits (name, price) VALUES (?, ?)";
and then execute the query with a data set passed to the abstraction layer as follows:
$sth = $dbh->prepare($sql);
$sth->execute(array($fruit, $price));
 

When the data is handed to PDO like this, it then either passes the data on to the database driver directly, or builds the query internally in a safe manner with any potentially malicious data encoded or escaped. As you can see, this is an easy way around the problem of SQL injection.
 

However, prepared statements with PDO aren't all puppies and rainbows. Using prepared statements can introduce a number of interesting caveats of which developers should be aware. For example, in the MySQL client API prepared statements can not execute certain types of queries[1] and they do not use the query cache[1][2] which may have an impact on your application's performance.
 

The inherent security in using prepared statements sounds great, but developers should not let PDO and other abstraction layers/prepared statement implementations lull them into a false sense of security. Untrusted data should always be validated and sanitised, PDO is just another line of defense. It doesn't cover the territory of a multitude of other input validation vulnerabilities like cross site scripting, but it does do a good job of protecting applications against SQL injection. The best strategy is only allowing known good data by whitelisting characters and matching input data against regular expression patterns, then using prepared statements to catch anything SQL injection-wise that the input validation misses, all in conjunction with a web application firewall like ModSecurity.
 

PDO has been built in to PHP since version 5.1.0, which was released in Nov 2005. Unless you've got a good reason for not using it in your PHP apps, you should be - it is a portable replacement for the old mysql_* functions and other platform-specific functions with the added benefit of protection against SQL injection.

 

[1] http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-problems.html
[2] http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html
Loukas Kalenderidis is a Security Consultant with Sense of Security. Sense of Security is a leading provider of Application Penetration Testing services in Australia, and is the trusted provider to many of Australia's leading companies.

Article Source: http://EzineArticles.com/?expert=Loukas_Kalenderidis