[ad_1]
Database abstraction layers like PHP’s Portable Data Objects (PDO) 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 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 isn’t vulnerable to it. For the uninitiated, SQL injection is a technique whereby an attacker can exploit inadequate data validation to inject arbitrary SQL code into application queries and have it execute as if it were a legitimate query. I won’t go into too much detail on 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 sends two variables via POST as follows:
username=fred&password=Fr3dRul3z
The POSTed data is then used to build a SQL query to validate 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 with these credentials exists in the database, the user would be allowed access. An attacker could easily bypass this authentication scheme by leaving the username field in the SQL query without entering anything in the password field and this in the username field 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 condition 1==1 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 circumvent the boundaries of quotes around fields (e.g. mysql_real_escape_string() if you’re using MySQL). However, in a distant land someone was inventing database abstraction layers…
The main goal of database abstraction layers like PDO is clean abstraction in code away from the database platform, so in theory you could switch database platforms from, say, MySQL to PostgreSQL or Oracle with minimal code changes . In practice this really 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. Seems moderately useful, but nothing exciting, right? Right. Another nice feature that was invented a long time ago is prepared statements, and most database abstraction layers (including PDO) implement this as a way to run the same query multiple times with different datasets (e.g. inserting an integer group of newlines). Now when we build 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 run the query with a dataset passed to the abstraction layer as follows:
$sth = $dbh->prepare($sql);
$sth->execute(array($fruit, $price));
When data is delivered to PDO this way, it either passes the data 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 get around the SQL injection problem.
PDO-prepped statements aren’t all puppies and rainbows, however. 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 they don’t use the query cache[1][2] which could impact application performance.
The intrinsic safety of using prepared statements seems great, but developers shouldn’t let PDOs and other abstraction layers/prepared statement implementations lull them into a false sense of security. Untrusted data should always be validated and sanitized, DOP 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 a good job of protecting applications from SQL injection. The best strategy is to allow only known good data by whitelisting the characters and comparing the input data against the regular expression patterns, then using prepared statements to detect anything SQL injection missing input validation, all in combination with a web application firewall such as ModSecurity.
PDO has been integrated 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 apps, you should be: it’s a portable replacement for the older mysql_* functions and other platform-specific features with the added benefit of SQL injection protection.