The Washigton Post has an excellent article today on how a USB drive was used to compromise military security. The article is light on technical details but it does act as a good reminder on the importance of physical security, as well as having secure operating systems.

PHP and MySQL are a natural pair. From the very beginning of PHP the language has had direct bindings to the MySQL API and a lot of applications still use those functions (i.e. mysql_connect, mysql_query, etc.) today. PHP has improved a lot and there are certainly better alternatives to using those direct API functions which I'll explore in subsequent articles. But if you have one of these legacy applications that uses the direct APIs you can do a lot to secure it from SQL injection without doing a massive port to one of the newer, better, PHP database APIs.

So, what's the problem with using this direct method? The issue comes in how we construct SQL queries. Here is a classic example:

<?php
$sql = "SELECT * FROM user WHERE ";
$sql .= "user_id='"+$_GET['user']+"' ";
$sql .= "AND password='"+$_GET['password']+"'";
$resp = mysql_query( $sql );
?>

Now leaving aside for the moment that the password field is unencrypted cleartext the primary issue here is that the query is vulnerable to SQL injection. The hacker could easily send a password string that terminated the single quote and added an OR condition that was always satisfied. That would give them instant access to the system because the query would always return the user record regardless of whether the correct password was provided.

The easiest way to secure your application from this type of attack is to use the mysql_real_escape_string method. This method escapes the string that's used in the SQL and thus protects against input that has malicious SQL embedded in it. Shown below is the same code fragment but with the mysql_real_escape_string method used.

<?php
$sql = "SELECT * FROM user WHERE ";
$sql .= "user_id='"+mysql_real_escape_string($_GET['user'])+"' ";
$sql .= "AND password='"+mysql_real_escape_string($_GET['password'])+"'";
$resp = mysql_query( $sql );
?>

Now some of the applications I've seen have had custom versions of mysql_real_escape_string that escaped out quotes. I really don't see the point of doing that. The mysql_real_escape_string does the job the right way. It's secure, it's maintained, and you should use it.

Having said all this, using the MySQL API directly is not really the best option. If you have the time you should use one of the newer database APIs for the following reasons:

• Database Portability - The MySQL functions are bound to MySQL only. Using an abstract database interface allows you to connect to any supported database using the same code.
• Replacement Operators - You place ? characters in your query string wherever you need parameters, then you pass in an array of parameters and they automatically fill those slots. That makes the query code a lot easier to read and maintain.
• SQL Injection Security - If you use replacement operators the database abstraction layer will automatically do the parameter escaping for you, thus saving you the hassle of doing it yourself and keeping you safe from SQL injection.
• Prepared Statements - You can prepare a SQL statement for execution, then use that statement multiple times in an efficient batch mode. That allows the database driver to cache the compiled SQL statement which gives you a real performance boost.

This blog post shows you how to secure the MySQL/PHP application you have today. In the follow-on blog post next week I'll show you how to convert this code to use one of PHPs database abstraction layers to get all of the advantages I enumerated above.