Protecting user data from SQL injection theft

TechCrunch has an article this morning about RockYou's loss of 32 million user accounts and their email and passwords.

The last thing a startup wants to get press for is how they lost all their user's personal information. Unfortunately, it's also nearly impossible to 100% protect this information. Instead, you need to work out a set of reasonable precautions such as:

1) always encrypt sensative information like usernames, email addresses and passwords in your database
2) always use some standard library for sanitizing user-input before it's used in SQL queries
3) run daily vulnerability scans from nessus or something like it.
4) do the normal system security stuff -- don't run unnecessary services that could be exploitable, use good passwords, etc

Encrypting your database content is nice since it also protects backups of the data, the data while it's being replicated, etc. This unfortunately usually means storing the decryption keys somewhere in the filesystem or in the app itself, all of which is readable by Apache. In theory an attacker could get them, but at this point your system is probably so compromised that nothing is going to save you.

Make sure that wherever you store the keys that they are not servable by Apache so store them (and ideally all of your source) outside your htdocs directory. Just to be clear, I'm talking about decryption keys for encrypted data like email addresses. Passwords should always be encrypted by a salted one-way hash that is not reversible. Something simple like DES is perfectly fine for encrypting email and passwords. SHA is a good non-reversible password hash. Ideally your app framework has standard code to do this for you so that you're not reinventing the wheel.

Next, avoid programatically building SQL queries based on user input. That is, don't do stuff like:

query = "select * from foo where username = '" + username_from_user + "'"

The classic SQL injection attack would then be for a user to enter a username like:

'; select * from foo where username not = '

Which ends the query you intended to run and instead runs the user's own query to list every record in the table.

Instead use whatever variable substitution system your db server or client provide. For example, in Python's MySQLdb you can template in user-supplied variables like so:

cursor.execute("select * from foo where username = %s", username_from_user)

MySQLdb then will do any escaping and quoting of the user supplied input to make sure the user can't inject their own SQL into the query.

Additionally, you should always validate user supplied input against some sort of regular expression. For example, in the example above you should make sure to match the variable username_from_user against a regex that makes sure it only consists of alphanumeric characters.

Finally, it doesn't hurt to run nessus against your system every day. I've never had it actually find a real vulnerability, but it's a cheap, free and easy way to make sure you haven't done something really stupid.