Seven Deadly SQL Sins Part IV

continued from part three

This is the fourth in a series of four excerpts from the MySQL presentation I have given live on a number of occasions. It was inspired Arjen Lentz' outline entitled The Deadly Sins of MySQL. Most of the deadly sins are not particular to MySQL. They apply pretty much the same over any SQL Relational Database. Parts one and two dealt with some pretty elementary errors, and part three was about misunderstandings you can have in the SQL language. Today I will focus on backup and database security.

 UPDATE users SET 

 name = 'Homer Simpson';

DOH! Well, I hope you have a backup! If you do this kind of thing on a regular basis, MySQL 5 has the --safe-updates option. An alias for the safe-updates is i-am-a-dummy. I kid you not, it's in the manual! ( Does this qualify as an easter egg?) In cased you missed it, what the above query does is sets EVERY user name in the database to "Homer Simpson" because there is no limiting constraint, i.e. no WHERE clause. That's not usually what you intended to do.

As a general rule you should not be typing raw queries into a production database directly anyway, and you should not be testing queries through your application program's Application Program Interface on a live database. It is easy enough to make a copy of a database to test things on. But if a program bug lets a query like this slip through "in the wild" by accident, always have your production database backed up, preferrably several layers deep and in several different places.

Note: Database Replication should not be considered a form of backup for your database any more than a Redundant Array of Inexpensive Disks (RAID) set should be considered backup for your hard disk? Why? Because in mirroring systems, a mistake to the master will be mirrored to the slave before you have a chance to say "Oops!" Redundant disks and databases are designed strictly for failover.

GRANT ALL PRIVILEGES ON *.* 

TO newapp IDENTIFIED BY 'abracadabra';

You have just given newapp privileges to all the databases on the system. Think no one would ever do that? Unfortunately, say the guys who support MySQL, it happens all too often. Don't let some shopkeeper on your shared hosting who just installed a shiny new copy of OSCommerce have all privileges on all the other databases including yours and the configuration meta-database! From what I've seen, 99% of users wouldn't know what to do with the privileges or even know that they have them, but when someone curious or bored who just knows enough to be dangerous logs in and types:

SHOW databases 

you could potentially lose all the data on the system!

GRANT ALL PRIVILEGES ONyourdb.* 

TO newapp IDENTIFIED BY 'abracadabra';

That's better. Now she can only trash her own database.

The following code is in production on a "Big Public Website":

$host = 'localhost';$user = 'root';

$pwd = 'kazoom';

...

$conn = mysql_connect($host,$user,$pwd);

What's wrong with that, you say? The code is serverside, it's not like the credentials are going to be sent to the client . OK but the gotcha is: not under under normal circumstances... First of all, you shouldn't be root and it's a pretty pathetic password. OK, let's make a new user (see previous sin) and only give them just exactly the privileges they need and no more, and let's make a real strong password. Voila! all fixed?

$host = 'localhost';

$user = 'limiteduser';

$pwd = '*&^$@#$09tH!$-9*vVerw*';

...

$conn = mysql_connect($host,$user,$pwd);

Most websites do not connect as root and have code something like the above. It's better than letting the webserver connect as root, but still not up to my paranoid standards. You've now got a strong password, and a user whose privileges are much less dangerous. But here's the thing: Your data is critical to your or your client's business and often contains sensitive information that they would not want to share. Db credentials are a direct tap into your raw data. Your db connect credentials are so critical that you should never never have them anywhere near the document root. Most apps do not hard code your conn credentials directly in the application code, but they often have them in a configuration file that lives in a web-accessible directory. They should not appear in a file that is web-accessible! Just in case someone does something like this:

cp myconfig.php myconfig.php.temp

and forgets to turn off indexing, some hacker could conceivably be in the right place at the right time and get them. Example: I took a class at Oregon State University taught by a professor who loves WIKI's. Well one day he was apparently fooling with the code because much to my surprise instead of serving me a WIKI page it served me some source code, complete with conn credentials, which I captured (of course.) Before I informed the professor of the security breach I had a nice romp through the database with full update privileges. I didn't tweak my grade; grades are not my thing. But I couldn't resist the temptation to leave my mark. I wonder if he ever found it. That experience made me take it seriously when they say "DON'T PUT YOUR CONN CREDENTIALS under DOCUMENT ROOT!!!" I have mine off somewhere in my home directory that is outside the webserver's realm of knowledge with only read permission for the webserver user and I include it. My code looks like this:

require_once "/home/me/deep/nested/obscure.php";

$db=mysql_connect($host,$user,$pw);

Even if they get my code, they still can't see what's in obscure.php You might argue that this makes the code less clear and a little harder to install. I agree. It's that way for a good reason. You only have to install once so it's a price I'm willing to pay. Some modern frameworks set the document root at a bootstrapper that lives in a sub directory of the main application, i.e. NONE of the application files are directly accessible to the web server. This also solves this problem.

There you have it. Today's sins are common sense but are often neglected.

Programmers think, "Oh surely the IT guys have backups of this!" or "It's OK, we have a replicated server," or, "Hey this is a well-known project, surely it is secure!"

Don't assume! Check!

About the author...

Colleen Dick has been a database techie, php developer, security analyst, musician 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. Colleen is now finally making the plunge into clientside programming with Dojo, because as in the fashion industry, there is a lot more money in doing faces than in doing backsides. Thank you for reading; If you found this article worthwhile, useful, or cool, please do a stumble, digg, or whatever you do to articles you like. If you didn't find it any of the above, please leave a comment letting me know how I could improve it and earn your LUV.