Seven Deadly SQL Sins Part I

This is the first in a series of four excerpts from my live SQL presentation of the same title that I have given to technical groups in my area. It is inspired by an excellent outline by Arjen Lentz entitled The Deadly Sins of MySQL.

I've taken the outline and added a few more examples from my own professional experience. I expanded each item into a discussion so that you can know exactly what the sins are, why they are bad and how to avoid them.

When I give application code examples, they are given in PHP, but every application language has a counterpart. A few of of these sins are specific to MySQL, but many of them are applicable to SQL in general, be it PostGRES, MS Access, MSSQL, or Oracle.

Here we have some code to get a new username from the user and update record # 10 to the new username.

$user=$_REQUEST['user']; //get user input from form

$query = "UPDATE user SET name='${user}' WHERE id=10";

What if the user inputs: "Bad User',id='1"? Now the query that actually gets to the server is: UPDATE user SET name='Bad User',id='1' WHERE id=10 The intent here is to get admin access. 1 might very well be the admin id! (at least that's what they're guessing.) The answer is to defang user data by escaping it. In mySQL you would insert the following line after setting $user:

$user=mysql_real_escape_string($user);

Side note: In legacy code you are likely to see addslashes used for this purpose. The more up-to-date way to do it is the real escape function, because addslashes does not work for all character sets.

Now, what goes into the database is the following: UPDATE user SET name='Bad User\',id=\'1' WHERE id=10. It will set the name to what the bad user input, and will not reset some arbitrary user's id to 1.

UPDATE t SET c1=5 AND c2=20 WHERE id=1

Of course in our lucid moments we realize we should have written

UPDATE t SET c1=5 , c2=20 WHERE id=1 

but amazingly the first query does not throw an error! Remember AND is a logical operator that is legal in expressions. So we suspect you end up setting c1= (5 AND c2=20) which in most cases would be zero, and completely valid SQL, though probably not what you intended.

$conn=mysql_connect(.....);

$query="SELECT ...";   //whatever query

$res = mysql_query($query);

echo $res;

This code does not check to see whether the connection succeeded, nor whether the query returned anything. I do not advocate hand coding an error check in situ after every database operation. It doubles the size of your code and makes it hard to pick out the business logic from among all the error handling cruft. For this situation I would set up an errorhandler or a try/catch block to automatically trap these errors. Best error handling practice is not the subject of this article. But the gist is, if you code like this and don't have something set up to handle it you will get bitten. Let's assume the connection worked but perhaps the query was invalid. If you start printing out results you're likely to get something puzzling like this:

Resource id #2

The SQL sins I speak of today are very basic. They are like failing to look left and right before you cross a street -- beginner errors. However legacy application code is rife with them, especially sin #1. If you recognize yourself in any of these sins, repent and be absolved. I absolve thee in the name of the server, the client, and the holey data.... AMEN. Go forward therefore, and just don't do these things any more! More advanced guilt trips for you in Seven Deadly SQL Sins Part II.

About the author...

Colleen Dick has been a database techie, php developer, security analyst, and social networker for a long time. She has single-handedly developed a dynamic joke site, a IT certification practice exam repository, social network for interactive video gamers, a community calendar, and an online ticket sales ecommerce site. When she comes off her extended break from programming she might be looking into ning and dojo. Thank you for reading; please feel free to click the "Share" icon above and social bookmark and/or comment if you have a question or something to add.