SQL injection protection in PHP with PDO

SQL injection protection in PHP with PDO

Database abstraction layers like PHP’s Portable Data Objects (PDOs) aren’t a new concept, but many developers don’t seem to realize the security benefit they get for free by using them: inherent protection against injection of SQL.

SQL injection is the buffer overflow of the web application world – it’s been around forever, and all web application developers need to know how to write safe code that isn’t vulnerable to it. For those who don’t know, SQL injection is a technique by which a malicious attacker can exploit inadequate data validation to inject arbitrary SQL code into your application’s queries and execute it as if it were a legitimate query. I won’t go too deep into SQL injection in this article, but here’s a simple example:

Your application’s home page has a login form, which is sent to a PHP script to validate the user’s credentials and allow or deny access to the application. The login form sends two variables per POST as follows:

username = fred and password = Fr3dRul3z

The data sent by POST is then used to create 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 there is a row in the database with these credentials, the user will be able to login. An attacker could easily circumvent this authentication scheme by escaping the username field to the SQL query without entering anything in the password field and this in 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 since the 1==1 condition will always be true. The rest of the query is discarded with the comment operator ‘–‘. The way to avoid this type of attack is to sanitize the data submitted to the form by escaping anything that could be used to escape the bounds of quotes around fields (for example, mysql_real_escape_string() if you’re using MySQL). However, in a faraway land someone was inventing database abstraction layers…

The main goal of database abstraction layers like PDO is clean abstraction in your code away from the database platform, so in theory you could switch database platforms from, eg MySQL to PostgreSQL or Oracle with minimal code changes. In practice, this largely depends on how much your code relies on platform-specific features like triggers and stored procedures, but if you don’t rely on them at all and are just doing simple INSERT/UPDATE/DELETE operations, it’s a free ride. . Sounds moderately useful, but nothing exciting, right? Good. Another cool 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 (for example, inserting a bunch of new rows). Now, when we build statements with PDO, instead of building the SQL string manually as demonstrated above, we build the statement with placeholders like this:

$sql = “INSERT INTO fruits (name, price) VALUES (?, ?)”;

and then run the query with a dataset passed to the abstraction layer as follows:

$sth = $dbh->prepare($sql);

$something->run(array($fruit, $price));

When data is delivered to PDO in this way, it either passes it directly to the database driver or builds the query internally safely with any potentially malicious data encoded or escaped. As you can see, this is an easy way to fix the SQL injection problem.

However, PDO prepared statements aren’t just puppies and rainbows. Using prepared statements can introduce a number of interesting caveats that developers need to be aware of. For example, in the MySQL client API, prepared statements cannot execute certain types of queries.[1] and they don’t use the query cache[1][2] which can have an impact on the performance of your application.

The security inherent 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 sanitized, PDO is just another line of defense. It doesn’t cover the territory of a multitude of other input validation vulnerabilities, such as cross-site scripting, but it does a good job of protecting applications against SQL injection. The best strategy is to only allow known good data by whitelisting characters and matching input data with regex patterns, and then use prepared statements to catch any SQL injections missed by input validation , all in conjunction with a web application firewall like ModSecurity.

PDO has been built into PHP since version 5.1.0, which was released in November 2005. Unless you have a good reason not to use it in your PHP applications, you should: it’s a portable replacement for the old mysql_* functions and other platform-specific features with the added benefit of SQL injection protection.

Leave a Reply

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