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 malware injection. SQL.
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 isn’t vulnerable to it. For those who don’t know, SQL injection is a technique by which a malicious attacker can exploit improper 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 is 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 a row exists in the database with these credentials, the user will be able to login. An attacker could easily bypass 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 in the database since the condition 1==1 will always be true. The rest of the query is discarded with the comment operator ‘–‘. The way to prevent 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, for example, 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 trip. . Sounds moderately useful, but nothing exciting, right? Right. 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 against different sets of data (for example by 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 securely builds the query internally 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 are not just puppies and rainbows. Using prepared statements can introduce a number of interesting caveats that developers should be aware of. For example, in the MySQL client API, prepared statements cannot execute certain types of queries.[1] and do not 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 shouldn’t 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 comparing input data against regular expression patterns, and then using 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 protection against SQL injection.