Thwarting Database Injection

Posted May 08, 2007

My Grand Canyon article has gotten a lot of traffic since being linked from Joel on Software and hitting it big on Reddit and Digg, so I set up Mint to learn more about the people coming to my site. After a couple weeks, I started seeing some hits coming in from a very strange Google query: inurl:"php?id=".

Apparently I've gotten enough page rank to come up as the 47th hit for that search. But why would anyone search for that? It turns out that this is a search people use when they are trying to hack into a website, in this case, my website. They are looking for web pages written in PHP that interact directly with a database. They are hoping that the developer of that page has written something like:

<?php
  ...
  $sql = "SELECT * FROM Table WHERE id=" . $_GET['id'];
  $result = mysql_query($sql, $db_connection);
  ...
?>

If this is the case, they can add arbitrary SQL commands to the query and, depending on how the query is handled, do nasty things like drop tables, edit rows, or even add users to the database.

As I looked through my logs, I found at least 18 people had tried to do just that to my site. Here's one example:

Hostname: 20.red-81-184-25.user.auna.net
Windows, Firefox 1.5.0.11, 1024x768
  http://hicks-wright.net/blog.php?id=5173
  http://hicks-wright.net/blog.php?id=5173%27%20and%20%271%27=%271%27
  http://hicks-wright.net/blog.php?id=5173%20and%201=1
  http://hicks-wright.net/blog.php?id=5173%20and%201=0
  http://hicks-wright.net/blog.php?id=5173%20union%20select%201/*
  http://hicks-wright.net/blog.php?id=5173%20union%20select%201,1/*
  http://hicks-wright.net/blog.php?id=5173%20union%20select%201,1,1/*
  http://hicks-wright.net/blog.php?id=5173%20union%20select%201,1,1,1/*
  http://hicks-wright.net/blog.php?id=5173%20union%20select%201,1,1,1,1/*
  http://hicks-wright.net/blog.php?id=5173%20union%20select%201,1,1,1,1,1/*
  http://hicks-wright.net/blog.php?id=5173%20union%20select%201,1,1,1,1,1,1/*
  http://hicks-wright.net/blog.php?id=5173%20union%20select%201,1,1,1,1,1,1,1/*

It's called a SQL Injection Attack. At the time I put my website together, I hadn't really thought about it. Fortunately, the way my code was written and the way the database was set up, it didn't pose much of a threat. Since then I've taken measures to ensure that all user input is checked and sanitized.

For those of you who have written your own PHP apps, there are two quick things you can do to secure your own site.

1. Use $myString = mysql_escape_string($string) on all string data coming from outside your code. This will ensure that if anyone puts a ' into their string, it will not be interpreted as the end of a MySQL string, which could allow them to inject code.
2. For all numbers, cast the variable to an int: $myNumber = (int)$number. This removes all non-digit characters from the data, truncating at the first non-digit. For example, (int)"123abc" returns 123, (int)"12ab3c" returns 12, and (int)"a123" returns 0.

These two things will go a long way to securing your site, and making your server admin much happier.