How-to: Prevent SQL Injection Attacks
Posted by Jon Lee in How-to, Web Development, tags: hacking, MySQL, PHP, prevention, security, SQL, SQL-Injection, web development
In my previous post, I described what SQL injection attacks are. In this post, I will suggest several ways to prevent them when using PHP/MySQL. I’ve heard of a few different solutions from different people and some of them are very ineffective – you’ll see why.
Setting Maxlength
The first method I’ll discuss is ineffective but is often suggested. That is setting a maxlength on an input field to disallow users from entering long complex SQL injection attacks. Setting the maxlength attribute on an HTML input field is merely a small obstacle that can be easily circumvented. In fact, anything that is client side is not a solution but merely an inconvenience for an intruder. Not to mention the example in the last article comprised of a mere 8 characters!
Limit Permissions
The database user that you use to connect to your database should not be set as the top level administrator. Instead, create a new user that contains only the permissions required by your web site. For example, if the front end of your website only reads data from the database then connect to the database with an account that only has SELECT permissions. This method is indeed useful, but with a proper attack, an intruder can create their own superuser from a simple SELECT statement as well.
Turn on Magic Quotes
Turn on magic quotes in your PHP server settings (that’s the magic_quotes_gpc variable). What this does is automatically escape quotes and other special characters with a backslash; that way SQL won’t recognize the quote as part of the query and treat it just like any other character. This is automatically done for any HTTP request data including POST, GET and COOKIE. Because it only filters HTTP request data, magic quotes stops most but not all SQL injection attacks! Data passed into SQL statements from the database or files is not filtered and thus can be manipulated to become an SQL injection attack depending on how your site uses this data.
However, this is probably the best solution for beginners. It’s “set it and forget it” since all the work is done for you automatically. Unfortunately, if for some reason magic quotes gets turned off (a possibility with managed hosting/shared hosting), your website is suddenly at risk for an SQL injection attack. This is why you should always do some of your own dirty work 
Do your own Input Cleaning
Since you can never be sure that magic quotes will stay on, you should always clean up submitted data on your own. This can be done by checking whether magic quotes is on with the get_magic_quotes_gpc() command. If it returns false, you can escape quotes and special characters manually with the simple addslashes command. It’ll look a little something like this:
$username = $_POST['username'];
$password = $_POST['password'];if (!get_magic_quotes_gpc()) {
$username = addslashes($username);
$password = addslashes($password);
}
Another method is to assume magic quotes is always off and do your own cleaning for everything. Harry Fuecks from SitePoint came up with this little piece of code to strip any slashes added by magic quotes if it is on. This way you have a guarantee that all data you work with is untouched by magic quotes.
if (get_magic_quotes_gpc()) {
$_REQUEST = array_map(‘stripslashes’, $_REQUEST);
$_GET = array_map(‘stripslashes’, $_GET);
$_POST = array_map(‘stripslashes’, $_POST);
$_COOKIE = array_map(‘stripslashes’, $_COOKIE); }
Conclusion
The best method of all is a combination of all the solutions above. If nothing, make sure you understand how magic quotes work instead of simply taking it for granted because one day it will get turned off and you’ll be screwed! Magic quotes is your friend but remember, it doesn’t prevent all SQL injection attacks. So to be really secure, it is best to do your own cleaning, assuming you do it properly that is!
I’m not saying I know everything about this subject, so please, add your thoughts in the comments!
Popularity: 5% [?]
Entries (RSS)