Here’s a question which tends to make Your JoeDog cringe: “So, what do you do?”

It’s often asked when he has a drink in his hand. And when he has a drink in hand, he doesn’t want to talk about work. Sometimes the inquiring person hears the answer, parses “computers” and wants to know why their laptop is slow. Honestly, Your JoeDog has no idea. Occasionally, he meets another nerd who wants to talk shop.

Recently he met a web nerd, the kind of web nerd who suffers from illusory superiority because he lacks the skill to recognize his ineptitude. These guys often contain a conspiratorial streak. This guy was no exception. The conversation soon shifted to hacking and web security.

Web Nerd puked a word salad of vulnerabilities but his beloved PHP was exonerated. “You can’t inject SQL because the mysql libs don’t allow multiple statements,” he said.

Couple points. 1.) the PHP mysql_ functions are deprecated. Astute JoeDog readers use PDO or MySQLi. 2.) You can still do injection as long as you keep it in a single statement.

Let’s try that after the jump!

We’ll create a database called ‘public’ and add a simple table:

DROP TABLE things;
CREATE TABLE things (
 id int NOT NULL AUTO_INCREMENT,
 name varchar (128),
 PRIMARY KEY (id)
);

INSERT INTO things VALUES (NULL, 'Tennis racquet');
INSERT INTO things VALUES (NULL, 'Ski goggles');
INSERT INTO things VALUES (NULL, 'Football');
INSERT INTO things VALUES (NULL, 'Baseball');
INSERT INTO things VALUES (NULL, 'Hockey Stick');

Next we’ll create a simple PHP page using Web Nerd’s precious deprecated mysql_ functions. According to our pretend usecase, a user is allowed to see the Thing associated with his UID. So if I’m ID number one, I can see “Tennis racquet.”

<?php
 $dbh = mysql_connect('localhost', 'user', 'pass') or die('Failed to connect to DB');
 mysql_select_db('public') or die('Could not select database');

 $uid = $_REQUEST['id'];
 $sql = "SELECT * FROM things WHERE id = $uid";
 $res = mysql_query($sql) or die('Query failed: ' . mysql_error());

 echo "<table>n";
 while ($line = mysql_fetch_array($res, MYSQL_ASSOC)) {
   echo "t<tr>n";
   foreach ($line as $col) {
     echo "tt<td>$col</td>n";
   }
   echo "t</tr>n";
 }
 echo "</table>n";
 mysql_free_result($res);
 mysql_close($dbh);
?>

If we send this request: haha.php?id=1

Our page displays a single line like this:

1   Tennis raquet

Let’s see if we can’t coax out the entire table. What happens if we send a compound conditional? But not just any condition, we’ll use boolean OR in which the second part is ALWAYS true. One is always 1, right? Let’s try that!

haha.php?id=1 OR 1=1

(The escaped version looks like this: haha.php?id=%31%20%4F%52%20%31%3D%31)

Whoa, hey, now we see everyone’s products:

1 Tennis racquet
2 Ski goggles
3 Football
4 Baseball
5 Hockey Stick

As you can imagine, this could be problematic.

So how can you prevent this? The answer is always the same: Cleanse and check your inputs.

 $data = $_REQUEST;
 foreach ($data as $key => $val) {
   $data[$key] = preg_replace('/s+|n|r|s+$/m', '', $val);
 }

Instead of assigning the uncleansed $_REQUST[‘id’] to $uid, we’ll use $data[‘id’] which has been scrubbed of white space. But have we done enough to call ourselves a contentious and give ourselves a big pat on the back? No. We should still check the input before we use it.

if (! is_int(uid)) {
  echo "Nice try, bad guy!";
  exit(1);
}