Top 3 Ways To Secure MySQL

This post is day 4 of the A Week Of Staying Safe series.

MySQL security is really surprisingly easy. There are basically 3 things you should focus on: preventing MySQL injections, running MySQL as a non-root user, and using a different user for each database.

Preventing MySQL Injections

MySQL injections happen when a malicious user inserts SQL code into a form element or some other variable that does not get verified or secured by your scripting language (PHP, Perl, etc).

The best way to prevent this in PHP is to not use magic_quotes_runtime (get a new host if they have that turned on!) and to strip any slashes if magic_quotes_gpc is on. Magic_quotes_gpc will “secure” all POST/GET variables, but that protection isn’t very good, MySQL’s is much better. When you script is first run you should do the following:

//remove slashes added by magic_quote_gpc
function stripslashes_nested($str) {
	if(is_array($str)) {
		return array_map('stripslashes_nested', $str);
	} 
	else {
		return stripslashes($str);
	}
}

if(get_magic_quotes_gpc()) { //removed slashes from all GET/POST/SESSION vars if magic_quote gpc is on
	$_GET = stripslashes_nested($_GET);
	$_POST = stripslashes_nested($_POST);
		
	if(isset($_SESSION)) { //session may not always be set
		$_SESSION = stripslashes_nested($_SESSION);
	}
}

That code will remove PHP’s horrible “magic-quotes” protection. You just have to remember to run mysql_real_escape_string() before inserting ANY data into a MySQL database. Mysql_real_escape_string() is much more secure than magic-quotes.

Run MySQL As A Non-Root User

This is really only applicable if you manage your own server or have a dedicated one:

MySQL should never be run as a root user. If some exploit were to occur your entire system could be taken down if it is run as root.

To have MySQL use a non-root user follow these directions on MySQL’s site.

Different User For Each Database

Probably one of the best ways to protect your MySQL data (besides preventing injection) is to make a new user for each database. You should never, under any circumstance, use a user to connect to your server that has all privileges for every database. That is a disaster waiting to happen.

If your host uses a control panel that is pretty easy, all you have to do is make a new database, a new user, and attach the user to that database.

If they don’t use one, I would highly recommend downloading the MySQL Administrator, enabling remote access to your server from your IP, and managing everything that way. That software suite is really nice and can save you lots of time.

A Week Of Staying Safe Articles:
Day 1 - The Ultimate Guide To Detecting E-Mail Scams
Day 2 - Huh? Who Sent That? - How To Find Out What Server Really Sent That E-Mail And How To Deal With Spam
Day 3 - Having A Good Host Can Sure Save Your Sanity
Day 4 - Top 3 Ways To Secure MySQL
Day 5 - Avoiding Make Fast Money Affiliate Scams
Day 6 - 17 Ways To Avoid Spyware Forever
Day 7 - Password Rotation

Please subscribe, or else I will cry. Do you really want to make a programmer cry?