WebDev Corner: Are You Making This Mistake In Your MySQL Queries?

Posted: July 12th, 2010

Photo by Koke (Flickr)

Photo by Koke (Flickr)

I have a lot of respect for the designers who get their hands dirty and learn a little PHP and MySQL to code those websites themselves. I can sympathize with you that it doesn’t seem different from learning Chinese. Maybe not that hard but you get the picture.

Unfortunately, I see a common mistake made, not only by designers but programmers too, that leaves your database vulnerable to hackers. We’re talking leaving the door wide open with the keys still in the lock.

Let me explain with a piece sample code retrieving make-believe contact info from a database:

$query = "SELECT name, email FROM contacts WHERE id='.$_GET['id'].'";
$result = mysql_query($query);

Now here is where it can get dangerous. Since the “id” is retrieved directly from the $_GET variable in the URL (for example http://somewebsite.com?id=2), that leaves the query vulnerable to an injection attack. Let’s say we type the following in the browser:

http://somewebsite.com?id=’ OR 1=1

We then get the resulting query after the injection attack that, instead of returning the info for a single name, returns the entire list of names in the contacts table. Note that the 1=1 is valid SQL and always returns a true result.

$query = "SELECT name, email FROM contacts WHERE id='' OR 1=1'";

OK, that might not sound like serious damage being done to the database. This one, however, will delete all the rows in the contacts table. Note that the semi-colon will stop the query leading up to the semi-colon and execute everything after. The ”=” is also valid SQL.

$query = "SELECT name, email FROM contacts WHERE id='; DELETE FROM contacts WHERE ''=''";

These are only a couple examples of what can be done once your database has been breached. Access to user logins, adding rows to tables to “deface” a website, or the dreaded access to private customer information are other possibilities. Not exactly something you want happening to you.

This can easily be corrected, though, by getting into the habit of escaping your database queries with the PHP function mysql_real_escape_string. More specifically, this function is to be used for every variable inside your query string and is only to be used in your query string.

For example:

$query = "SELECT name, email FROM contacts
          WHERE name='".mysql_real_escape_string($_GET['name'])."'
          OR email='".mysql_real_escape_string($_GET['email'])."'";

$result = mysql_query($query);

What the function does is escape a string by adding slashes to characters that can alter an SQL query into an injection. So instead of a harmful query being executed, the proper query with the unharming string parameter executes. Not to mention, your database is spared a happy hacker.

Now, some of you may have caught the fact that the $_GET variables were not verified for data integrity nor sanitized to prevent XSS attacks. If I just confused the hell out of you, don’t worry, I’ll be posting a tutorial in the future that will cover these.

Stay tuned!