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: 8% [?]
Entries (RSS)
[...] « Showoff Rankings – Not Just for Wordpress! How-to: Prevent SQL Injection Attacks » May 23 [...]
Nice article. A lot of newbie developers make these mistakes. I was a newbie too
Next article: Spam injection in contact forms!
I think Magic Quotes is off by default in PHP 5, as opposed to on in earlier versions.
It actually causes a lot of problems for programmers who have to determine whether it is on and switch code accordingly… it can also cause problems authenticating logins where special characters have been used in passwords (on an LDAP server for example). It is the view of some that Magic quotes actually did more harm than good, for example, what if your host disables it without telling you, and your code does not handle this……
Spam injection in mail forms is an interesting one, though I think wordpress have got it covered… but if your mail code inserts a subject in the headers taken from an html form, an attacker can inject header info including bcc mail addresses and relay spam through your site…. not nice….
XSS would be another good one to write about….
True… I never thought of spam injection through contact forms.. I checked and I’m safe from that.
You got it Jez, cross site scripting coming up next week!
I suggested mail for injections in general. Mysql injections are pretty well covered in Wordpress too, so this article, imo, is more for people who create their own sites. And for those people, especially the newbies, an article about mail form injection would be a nice one.
[...] Jon Lee writes 2 articles on SQL injections. The first explains the injections and the second provides tips on how to prevent injections. [...]
I don’t understand at all..
But i think my wordpress v 2.2 is anti mysql injection..
CMS make my life easier
Yup, I’d say that most public content management systems are anti mysql injection.. or I’d least I hope so!
;-((
Now i need this tutorial to my new web..
I wanna build web with old php script, and i need this
Hem..
I’m newbie, so i use this or this one?
http://www.phpfreaks.com/quickcode/Anti-SQL-Injection-Login-Filter/203.php
Thanks guys
I am thinking of this method u use to prevent SQL injection, the thing with the user permission. I thought the SQL injection require some input so anything with reading from database should not be any problem even if it is admin account, isn’t it. But of course, the method used will be post so the user cannot manipulate the data from the url. Like: SELECT * from blah WHERE blah = $_POST['sth']; . Can u pls clarify…
SQL injection does require input but this input could be from the database. For example, someone could enter malicious injection code into the “username” field when they register for an account on your site. And if that field isn’t properly check, then when you include their username (from the database) in an SQL statement, it could potentially create an SQL injection situation.
Also, even if data is POST, that is no guarantee that a user cannot manipulate it but it will make it harder than simply editing a URL.
Hope that helps
How about this.
1) Take the login data. Split it into an array delimited by space. Take only the first element string[] ld = txtlogin.text.split(’ ‘)
2) Repeat for password.
Also put logic #1 & #2 in an httpmodule to filter out querystrings between page requests.
This takes care of alot of possible injections like OR ‘x’ = ‘x’, OR true, etc…..
Then throw in your single quote replacements etc……
Also, filter out any SELECT,UPDATE,INSERT,DELETE,DROP,TABLE,EXEC, etc……
this should eliminate alot of potential threats.
one of our sites got broken into by someone using SQL injection.
i think it was one of our competitors actually.
no damage done, but a hell of a lot of hits to the login page.
Not sure. I make my function for mi site of postales gratis:
function apply_filters($tag) //funcion aplica filtros
{
// $tag= filter_var($tag, FILTER_SANITIZE_STRING);
$tag=htmlspecialchars(trim($tag));
$tag= mysql_real_escape_string($tag);
$tag = str_replace(”<script", "<sc#ipt", $tag);
$tag = str_replace("<java", "<j@va", $tag);
$tag = str_replace("”, “script”, $tag);
$tag = str_replace(”mail(”, “m@ail(”, $tag);
$tag = str_replace(”a href”, “a hr@f”, $tag);
return $tag;
}