Day 22 – Database Security

Jan 11, 2010

SQL Injection – everyone talks about it, but most people fail to protect against it. This, just like most other security concerns with PHP are INCREDIBLY easy to protect against! To explain how to protect against SQL Injection, it’s best if I show you how to perform an attack first. Let us suppose your login page POSTs to a file called login.php where this code resides:

$username = $_POST['username'];

$password = md5($_POST['password']);
// PS. Bonus points on using SOME sort of password hashing.
// Although, something a little less reversable would be ideal.
$sql = "SELECT * FROM users WHERE ".
"username = '{$username}' AND password = '{$password}'";
// Execute Database Query Here
if (count($results) > 0) {
// Logged in!
}

Pretty simple, yes? I pass in username ‘Admin’ with ‘Password1′ as the password and I should authenticate correctly. Now suppose I pass in the following username:

Admin’ OR 1 = 1 —

Here is the resulting SQL statement:

$sql = “SELECT * FROM users WHERE “.

"username = 'Admin' OR 1 = 1 — ' AND password = 'd41d8cd98f00b204e9800998ecf8427e'";

In MySQL (as an example), the — followed by a space signifies the start of a comment (therefore anything after is ignored). This new SQL statement will login as the Admin regardless of the password provided.

It’s obvious that you need to prevent malicious users from providing their own SQL code and executing that. Luckily, PHP provides *_escape_string() for ALL database drivers. Here is a MySQL example:

$username = mysql_escape_string($_POST['username']);

<p>It's also a good idea to instead use Bound Parameters to ensure the right types of data are being placed in your SQL statements.</p>
<h3>Session Security</h3>
<p>Session attacks are also very common.  But these are a little harder to craft (from a malicious user's point of view) as they need to either scrape the cookies of a currently authenticated user or force that user to authenticate with a certain crafted URL.</p>
<p>The first issue is Session Fixation.  This is when a malicious user "rides" a valid user's authentication by forcing them to use a session identifier associated to the malicious user.  This is caused by a strange <em>feature</em> of PHP: allowing a user to set the session identifier via the query string:</p>
<blockquote>
<p>http://www.example.com/index.php?PHPSESSID=1234</p>
</blockquote>
<p>Therefore you as a developer need to regenerate a session identifier every time a user's access level changes:</p>
<div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:887EC618-8FBE-49a5-A908-2339AF2EC720:442123ef-6c88-4aab-a5db-c49c6bae880f" class="wlWriterEditableSmartContent"><pre lang="php">session_start();
// Perform user authentication at this point
// Generate a new session ID before saving
session_regenerate_id();
$_SESSION['is_logged_in'] = true;

The second issue is Session Hijacking. This involves a malicious user stealing the cookies or intercepting packets of a targeted authenticated user. In this case, the malicious user sets his session id to the previously authenticated user. The best way to prevent against this is on each page load, checking some additional credentials (user-agent, ipaddress, etc.) that are saved to the Session during login.

Tomorrow should be Remote Code Injection, Command Injection, and Shared Hosting concerns (almost done, I swear!).