| Database abstraction layers like PHP's Portable Data | | | | your code relies on platform-specific features like |
| Objects (PDO) are not a new concept, but a lot of | | | | triggers and stored procedures, but if you're not relying |
| developers don't seem to realise the security benefit | | | | on them at all and you're just doing simple INSERT |
| they're getting for free by using them - inherent | | | | UPDATE/DELETE operations it's a free ride. Sounds |
| protection against SQL injection. | | | | moderately useful, but nothing exciting, right? Right. |
| SQL injection is the buffer overflow of the web | | | | Another neat feature invented a long time ago is |
| application world - it's been around forever, and every | | | | prepared statements, and most database abstraction |
| web application developer should know how to write | | | | layers (including PDO) implement this as a way to |
| secure code that's not vulnerable to it. For those not in | | | | perform the same query multiple times with different |
| the know, SQL injection is a technique whereby a | | | | data sets (e.g. inserting a whole bunch of new rows). |
| malicious attacker can exploit inadequate data | | | | Now, when building statements with PDO, instead of |
| validation to inject arbitrary SQL code into your | | | | building the SQL string manually as demonstrated |
| application's queries and have it executed as though it | | | | earlier, we build the statement with placeholders like |
| is a legitimate query. I won't go too deeply into SQL | | | | this: |
| injection in this article, but here's a simple example: | | | | $sql = "INSERT INTO fruits (name, price) VALUES (?, |
| The front page of your application has a login form, | | | | ?)";and then execute the query with a data set passed |
| which is submitted to a PHP script to validate the | | | | to the abstraction layer as follows: |
| user's credentials and allow or deny access to the | | | | $sth = $dbh->prepare($sql); |
| application. The login form submits two variables by | | | | $sth->execute(array($fruit, $price)); |
| POST as | | | | When the data is handed to PDO like this, it then either |
| follows:username=fred&password=Fr3dRul3z | | | | passes the data on to the database driver directly, or |
| The POSTed data is then used to build an SQL query | | | | builds the query internally in a safe manner with any |
| to validate the credentials, like this: | | | | potentially malicious data encoded or escaped. As you |
| $sql = "SELECT * FROM users WHERE username = | | | | can see, this is an easy way around the problem of |
| '".$_REQUEST['username']."' AND password = | | | | SQL injection. |
| '".$_REQUEST['password']."'"; | | | | However, prepared statements with PDO aren't all |
| This would result in the SQL query: | | | | puppies and rainbows. Using prepared statements can |
| SELECT * FROM users WHERE username = 'fred' | | | | introduce a number of interesting caveats of which |
| AND password = 'Fr3dRul3z' | | | | developers should be aware. For example, in the |
| Assuming a row exists in the database with these | | | | MySQL client API prepared statements can not |
| credentials, the user would be allowed to log in. An | | | | execute certain types of queries[1] and they do not |
| attacker could easily circumvent this authentication | | | | use the query cache[1][2] which may have an impact |
| scheme by escaping out of the username field into the | | | | on your application's performance. |
| SQL query by entering nothing into the password field | | | | The inherent security in using prepared statements |
| and this into the username field: | | | | sounds great, but developers should not let PDO and |
| ' OR 1==1 -- | | | | other abstraction layers/prepared statement |
| The resulting SQL query string would look like this: | | | | implementations lull them into a false sense of security. |
| SELECT * FROM users WHERE username = 'fred' | | | | Untrusted data should always be validated and |
| OR 1==1 -- ' AND password = '' | | | | sanitised, PDO is just another line of defense. It doesn't |
| Which, as I'm sure you can see, would select all users | | | | cover the territory of a multitude of other input |
| from the database as the condition 1==1 will always be | | | | validation vulnerabilities like cross site scripting, but it |
| true. The rest of the query is discarded with the | | | | does do a good job of protecting applications against |
| comment operator '--'. The way to avoid this kind of | | | | SQL injection. The best strategy is only allowing known |
| attack is to sanitise the data submitted to the form by | | | | good data by whitelisting characters and matching |
| escaping everything that could be used to escape the | | | | input data against regular expression patterns, then |
| confines of the quotes around the fields (e.g. | | | | using prepared statements to catch anything SQL |
| mysql_real_escape_string() if you're using MySQL). | | | | injection-wise that the input validation misses, all in |
| However, in a land far away somebody was inventing | | | | conjunction with a web application firewall like |
| database abstraction layers... | | | | ModSecurity. |
| The primary objective of database abstraction layers | | | | PDO has been built in to PHP since version 5.1.0, which |
| like PDO is clean abstraction in your code away from | | | | was released in Nov 2005. Unless you've got a good |
| the database platform - so, theoretically, you could | | | | reason for not using it in your PHP apps, you should be |
| switch database platforms from, say, MySQL to | | | | - it is a portable replacement for the old mysql_* |
| PostgreSQL or Oracle with minimal changes to the | | | | functions and other platform-specific functions with the |
| code. In practice this depends heavily on how much | | | | added benefit of protection against SQL injection. |