Foreword: SQL injection is a very scary phrase. After all, it has single-handedly been responsible for putting down major government websites and thousands of personal home pages- and everything in between. (Something that has been increasingly popular after the “Techie” generation had puberty-riddled children.) Yet believe it or not, guarding against the attack is simple as a couple of lines of code.
There was once a famous doctor that had it completely right: never trust your patients. Now this doctor may have only been a sitcom doctor on the show “House,” but we’ll be taking a page from his book. Of course, in our case the patients will actually be Internet users. Don’t let your guard down! They are conniving, dastardly, plot-making fiends- and you’ll do well to remember it.
First, Let’s define an SQL Injection:
Define SQL Injection
SQL Injection - \S-Q-L-in-'jek-shen\ - Noun The technique of inputting malicious data into an SQL statement, which would therefore make the vulnerability present on the database layer. Surprisingly, it seems everyone who has recently taken up learning a web development language has to try the technique out on their favorite websites. Luckily for said websites, this technique isn't at all hard to protect against.
The technique of inputting malicious data into an SQL statement, which would therefore make the vulnerability present on the database layer. Surprisingly, it seems everyone who has recently taken up learning a web development language has to try the technique out on their favorite websites. Luckily for said websites, this technique isn’t at all hard to protect against.
The vast majority of all SQL injections will take place on an input form. Contrary to popular belief, this isn’t the only place where we will see them- it’s also common to manipulate URLs to inject SQL code. (But we’ll get more into that later.)
The most basic of all SQL injections will look like the following:
The Basic SQL Injection
Variable' or 1=1--
Let’s say we have a login form. By inputting the above code, we can use our SQL injection to gain login even without proper credentials! So how’s it work? Take a look at the “bigger picture” below:
What It Looks Like On The Back-End
SELECT * FROM users WHERE username = 'Variable' or 1=1--'
See how our code is nicely injected into the query? The result of this query will grant us access regardless of the username, since the result of “1=1″ will always be true. In this case, we bypass the whole selection process.
You may have been wondering what the double dashes are for ( — ). These dashes at the end tell the SQL server to ignore the rest of the query. If the exploit isn’t being used on an SQL server, then omitting the double dashes and ending single quote will get the desired results.
Note that while this is the most standard way, it certainly isn’t the only way that malicious users will gain entry. SQL queries will differ greatly from one syntax to another, and thus, so too should the SQL injection. It’s also common to see the following:
More SQL Injection Syntax Fun
') or ('1'='1 "or "1"="1 ' or '1'='1 Or 1=1-- " or 1=1-- ' or 1=1--
Did you know it was possible to attack an SQL server through a URL? Well, it’s possible, and usually much more dangerous to webmasters. When using PHP and SQL, there is commonly a URL such as the following:
By adding a little SQL to the end of the URL, we can do some very mischievous mischief:
You might be confused by the hash. This little guy is just like the double dash we used earlier; it will tell the SQL query to halt after our input. And if you haven’t noticed, we just told the server to drop the entire table of users! This is an example of how powerful and dangerous SQL injections can be- and also shows that constant backups are a necessity.
Enough already! Let’s finally find out how to make sure that little script kiddies aren’t going to ruin the hard work webmasters and web developers set aside for their projects.
The first step in the process is simple: simply restrict input fields to the absolute minimum- usually anywhere from 7-12 characters is fine. Doing so will make long queries unable to be input, since the field is only enough characters for smaller queries. This will actually not prevent an SQL injection, but will make work harder for those trying to make use of one.
Savvy SQL injection users can simply make a new form and remove the limits on the character length, since the length is in plain HTML and viewable (and editable) by anyone.
Another good idea is to validate any data once it is received. If a user had to input an age, make sure the input is an actual number. If it was a date, make sure the date is in proper format. Again, this will not prevent an SQL injection in itself- it just makes work harder for those trying to exploit an SQL server.
Data type validation can be thwarted by modifying the query over a trial-and-error test period. This is still only slowing attackers down- but isn’t it much more satisfying to have them waste their time before finding out one’s own query is impervious to harm? Of course! An eye for an eye!
It’s nice to be able to create a “super user” in one’s own database that can create, drop, and edit tables at will. The security-obsessive webmaster will want to make individual users that can only do one or two tasks at a time. In effect, this means that SQL injections will only be able to do one or two things at a time.
This is just a little prevention fun, it can certainly still cause a certain amount of danger. If a user is made for deleting tables, than an SQL injection can do the same thing- it just won’t be able to do much else. Regardless, deleting a table is a very big privilege to handle. This method is still useful for throwing attackers off track, as well as minimizing risk from areas of a website that aren’t critical to the security of the database.
We all love magic. Heck, magic is downright cool. It serves as the basis for great children books (Harry Potter, anyone?) and even has been used for themes in nerdy card games everyone seems to enjoy (Ah, we’re looking at you, Magic The Gathering!)
One thing that just doesn’t live up to the magic name is magic quotes. PHP developers thought it would be a wonderful idea to make a process that escapes all incoming data in a PHP script. Sounds like it would fix our problem with SQL injections, but alas, there are better ways.
Anyone who has recommended a fix with magic quotes doesn’t know what they are talking about. After all, magic quotes are considered deprecated and removed as of PHP version 6. So why such hostility over a process that is seemingly beneficial to our predicament?
The short answer: magic quotes are horrible for portability issues, performance issues, and they mess with other data that doesn’t need to be escaped.
We came close to finding a real solution there- almost! But we did learn something: don’t use magic quotes, and instead find an alternative that can escape the input data based on what we need: not what we don’t.
We could’ve given you the answer right away, but what fun would that have been? Too often, PHP developers are becoming lazy and not following proper security tactics the way they should. By reaching this point in the lecture, you’ve increased your knowledge on how SQL injections are used, how not to prevent the attacks, and finally: you’ll learn the right way to keep injection attacks at bay.
We’ll accomplish this last feat with a simple function that the developers of PHP made especially for SQL injections. We call this function mysql_real_escape_string() – take a look at it below:
mysql_real_escape_string() In Action!
$name = "John"; $name = mysql_real_escape_string($name); $SQL = "SELECT * FROM users WHERE username = '$name'";
Although for a more practical use, we would have the $name variable pointed to a POST result, as seen below:
$_POST Can Dig In On The Action Too!
$name = mysql_real_escape_string($_POST['user']);
And we can even make things easier by putting it into one line:
All In One Line Now; Here We Go!
$SQL = "SELECT * FROM users where username = "mysql_real_escape_string($POST['user']);
So what’s the output like if malicious users try to get access to our SQL server? Well glad you asked! Their attempts may look something like this:
Cause And Effect With mysql_real_escape_string()
$malcious_input = "' OR 1'"; // The Above Is The Malicious Input. Don't Be Scared! // With The mysql_real_escape_string() usage, the following is obtained: \' OR 1\' // Notice how the slashes escape the quotes! Now users can't enter malicious data
And the best part is, they just wasted their time and effort for nothing. Now how’s that for vindication!
We’ve learned quite a bit today. SQL injections are bad. All Internet users are equally as bad. Protecting against both ensures a happy and stable web application. And above all else, never use magic quotes! Despite their cleverly disguised name, we’ve found no evidence of magic.
Lastly, note that there are libraries and classes that can help aid in the fight against SQL injection. Prepared statements are plausible as well, but as for us, we enjoy sticking to the mysql_real_escape_string() function for less headaches.
Bottom Line: mysql_real_escape_string() – It doesn’t have a magically awesome name, but it’s 24 characters worth of SQL injection-protection goodness.
[...] SQL Injection: How To Prevent Security Flaws In PHP / MySQL [...]
thnx for such good information.
How SQL INJECTION is used for security testing. Please briefly describe me with examples.
Rashmi-
If you want to use SQL injections for security testing you can use two different tools I found for FireFox: 1. addons.mozilla.org/en-US/firefox/addon/6727 2. addons.mozilla.org/en-US/firefox/addon/3899
Good luck!
This simple idea really works !!
$SQL = “SELECT * FROM users where username = “mysql_real_escape_string($POST['user']);
IMHO should be
$SQL = “SELECT * FROM users where username = “.mysql_real_escape_string($POST['user']);
Great Information. Thanks!
Thanks , but could you tell me exactly where I would put the string to prevent an attack Thanks
I found your article very good but you left me wondering about a few things. Why are you pulling all of the information from your SQL databases with select *? I also wanted to know if it is neccesary to put the mysql_real_escape_string everywhere the post command is even if a different function is running such as trim. Also what is the difference between mysql_realescape_string and mysql_escape_string?
‘ or ’1′=’1 how to prevent sql injection when i am putting ‘ or ’1′=’1 into url then data is coming so plz help me.
I use this line of code to do stuff with the current user’s ID number.
$sql1 = “SELECT stuff FROM data WHERE id =’”.mysql_real_escape_string($idnumber).”‘”;
take notice of all the ‘”. usages. also how to update stuff to 1 for that id number.
mysql_query(“UPDATE data SET stuff = ‘”.mysql_real_escape_string(1).”‘ WHERE id = ‘”.mysql_real_escape_string($idnumber).”‘”);
take a good look in the use of ‘”. signs.
the query has a few to many spaces (but didn’t type them in the message..)
also if you want to learn php sql and how to learn the security, don’t buy the basic books, buy the advanced in depth books. I made that error also and have a book written by matt rutledge for creating my own php games and there’s no real information on the subject of “mysql_real_escape_string()” usages. I’m sorry matt, the book is rather helpful on other fields of game devellopment…
Thanks for your article. It really works
Very nice information.
hi all..
I am a student and i am a new leaner. I would like to know how am i going to develop a coding to prevent sql injection attacks from scratch?
Eika, If your page looks like this:
$desc = $_POST['desc'];
You would just do this to prevent the SQL injection:
$desc = $_POST['desc']; $desc = mysql_real_escape_string($desc);
and you’re set.
Thanks for such a nice and understandable information about sql injection. Now i can protect my website from this attack.
Here’s a good method to just clean out all POST variables right off the bat.
Instead of using $_POST['var'], just use $clean_post['var'].
if($_POST){ $post_orig = $_POST; foreach($post_orig as $post_var){ $var = key($post_orig); $clean_post[$var] = stripslashes(stripslashes(mysql_real_escape_string($post_var))); next($post_orig); } } print_r($clean_post);
Going off the last comment, here’s a quick method to cover all your bases in a pinch.
function me($v){return mysql_real_escape_string($v);} if($_POST){foreach($_POST as $posts){$posts=me($posts);}} if($_GET){foreach($_GET as $gets){$gets=me($gets);}}
Put these 3 lines on the same include page as your database class or wherever you connect to a mysql db, you’re all set. Remember three things: (1) This is not the most efficient method cause it has to work those strings every single time all the time. (2) The escaping only works if you are connected to mysql. (3) I only created a separate me() function for brevity and reuse on whatever.
you can do it that way too $username=mysql_real_escape_string($_POST['user']); $SQL = “SELECT * FROM users WHERE username=’$username’ LIMIT 1″); it is safer that way
Great post! I was just wondering, though: how does mysql_real_escape_string protect you against ;DROP TABLE user; #? You can limit to what users can put into text fields, but doesn’t do any good if your script uses ‘get’ and a hacker can just put it into the url. I think what I can do with my script is to get rid of spaces all together when validating user input, since users should not use spaces in my case, anyways ;p
For everyone who’s interested in preventing SQL injection from user input, you might want to check our newly released opensource library ValidForm Builder.
Some of the key features are: - Fully CSS and webstandards based forms (no tables) - Prevent SQL Injection using both clientside and serverside validation - Check http://www.validformbuilder.org/ for more information, tutorials, complete API reference guide and ofcourse the source download!
This is no commercial; it’s a free to use opensource library for creating webforms.
Thanks for the great tutorial. I really appreciated the plain language and simplistic, easy-to-follow examples.
Yeah, I would REALLY want to know how to prevent the SQL Injection from the URL.
to prevent sql injection from url you would do the same thing as for POST,just do it for GET when you’re getting the data from the url. //This stops SQL Injection in POST vars foreach ($_POST as $key => $value) { $_POST[$key] = mysql_real_escape_string($value); }
//This stops SQL Injection in GET vars foreach ($_GET as $key => $value) { $_GET[$key] = mysql_real_escape_string($value); }
Thank you very much for your explanation. It helped me out a lot.
Hey people i found something thats helpful for get values you should also put a post if possible so hackers cant hack those
Very good info to be shared with, I was wondering how to protect my website from sql injection. Now I have learned the real use of mysql_real_escape_string usage. Thank you very much.
Subodh
Home
RSS Feed
