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.
Sorry, I had to give it a try!
alert(‘OWNED!’);
Can any one give me a sample code to prevent SQl Injection for login system both URL attack and form data.
Thanks.
The best way I have seen to prevent sql injections in php for account security is to actually salt or hash the usernames and passwords or at least run mysql_real_escape_string.
An example of how this helps…
Normal query to the database: login.php?uname=admin&pass=password
$query=(“SELECT * FROM `users` where `uname`=’” . $_REQUEST['uname'] . “‘ and `pass`=’” . $_REQUEST['pass'] . “‘”); query – SELECT * FROM `users` where `uname`=’admin’ and `pass`=’password’ // If logging in, this would return “false” (bad login)
With this, a modified string would would render the line: login.php?uname=admin&pass=password ‘ or uid=1
$query=(“SELECT * FROM `users` where `uname`=’” . $_REQUEST['uname'] . “‘ and `pass`=’” . $_REQUEST['pass'] . “‘”); query – SELECT * FROM `users` where `uname`=’admin’ and `pass`=’password’ or `uid`=’1′ // If logging in, this would return “true” (successful login)
Using a hash or md5 for the password or username helps like this: login.php?uname=admin&pass=password ‘ or uid=1
$query=(“SELECT * FROM `users` where `uname`=’” . md5($_REQUEST['uname']) . “‘ and `pass`=’” . md5($_REQUEST['pass']) . “‘”); SELECT * FROM `users` where `uname`=’21232f297a57a5a743894a0e4a801fc3′ and `pass`=’e169647db54e33916b03095b12cfb86e’ // If logging in, this would return “false” (bad login)
Furthermore, simply using mysql_real_escape_string helps the most… login.php?uname=admin&pass=password ‘ or uid=1
$query=(“SELECT * FROM `users` where `uname`=’” . mysql_real_escape_string($_REQUEST['uname']) . “‘ and `pass`=’” . mysql_real_escape_string($_REQUEST['pass']) . “‘”); SELECT * FROM `users` where `uname`=’admin’ and `pass`=’password or uid=1′ // If logging in, this would return “false” (bad login)
Hope that helps. We have more resources on my company’s website http://www.goldsborowebdevelopment.com
if you really want to prevent from mysql injection use my code:
use $count as == to ’1′ why? because in sql injection if the statement is true it will query more than 1 it means all data will be fetched. so the count is more than 1 if you want a secure and has specific account or exact 1 account is $count as 1 I prefer.
Does mysql_real_escape_string() also work for numeric inputs?? For example:
$input = $_GET["ID"]; $input = mysql_real_escape_string($input); $query = “SELECT * FROM customers WHERE userID =”.$input;
Say the attribute userID is defined as numeric in database, if the input is: 123 OR 1=1, will the function prevent it?? If not, what is the best way to prevent it?
If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.
So you need to use stripslashes() before, then mysql_real_escape_string() only apply if magic_quotes-gpc is enabled.. so: // we need a connection first, important so newbies learn this. if($mysql_con){ $var_data= stripslashes($_POST['some_data']); $var_data= mysql_real_escape_string($var_data); } and something that everyone has left off, in order to use mysql_real_escape_string() you need to have a mysql connection instance..
Is it necessary to do this also for $_SESSION and $_REQUEST, or it’s fine if I leave it only with security for $_GET and $_POST?
This is all a very nice tutorial on how to use mysql(i)_real_escape_string but the latter function does not in any way protect against all SQL injection attacks. mysqli_real_escape_string can be defeated as simply as using something along the lines of:
\’; DESC users; –
Prepared statements are the way to go. I’m very surprised this has not been mentioned throughout this entire article.
Where exactly do you put this string? on the Index page? $SQL = “SELECT * FROM users where username = “mysql_real_escape_string($POST['user']);
Very nice and helpfull but as well as the above I have taken it a step further.
In my db connection script I also have the following php script to check the headers/querystring for any malicious changes.
$page= $_SERVER["SERVER_NAME"].$_SERVER["REQUEST_URI"]; if ((strpos($page,”select *”) >=1) || (strpos($page,”1=1″) >=1) || (strpos($page,”2=2″) >=1) || (strpos($page,”@@version”) >=1) || (strpos($page,”schema_name”) >=1) || (strpos($page,”union+all+select”) >=1) || (strpos($page,”username”) >=1) || (strpos($page,”password”) >=1) || (strpos($page,”table “) >=1) || (strpos($page,”=1) || (strpos($page,”javascript:void”) >=1) || (strpos($page,”3=3″) >=1) || (strpos($page,”4=4″) >=1) || (strpos($page,”0=0″) >=1) || (strpos($page,”5=5″) >=1) || (strpos($page,”6=6″) >=1) || (strpos($page,”7=7″) >=1) || (strpos($page,”8=8″) >=1) || (strpos($page,”9=9″) >=1) || ( strpos($page,”select “)>=1 && strpos($page,”from “)>=1 ) || ( (isset($_GET["info_hash"])) || (isset($_GET["port"])) | (isset($_GET["module"])) || (isset($_GET["peer_id"])) || (isset($_GET["download"])) || (isset($_GET["compact"])) ) || ( strpos($page,”insert “)>=1 && strpos($page,”into “)>=1 )){ mysql_query( *** sql script to save some information to a database ** for later processing ***) or die(“ERROR:”.mysql_error()); header( ‘Location: **mainpage**’) ; exit(); }
but I have also have a another script that records all page access to a database and then I can trawl through these later to look for other access attempts
for example
select * from database.table where pagename like ‘%admin%’;
and from this I can ascertain regular offenders and store the ip address just for later reference but please note, I am aware that ip address’s can change so this is just an excerise for me
daddycee
kindly tell me if my code is secure or not , i think its not properly filterd.. who will make it secure here is the code
<?php
//echo "”
//$sql=”SELECT * FROM news”; //$result=mysql_query($sql); //while($row = mysql_fetch_assoc($result)) $result = mysql_query(“SELECT * FROM newsjhelum where category = ‘child’ ORDER BY id desc Limit 0, 15″);
while($row = mysql_fetch_array($result)) { $id = $row['id']; //$heading = $row['heading']; //$heading = $row['heading']; //echo “<img src='news/"$row['heading']"”; //mysql_free_result($result); //echo ““; echo ““; //echo ““; echo ““;
echo “”; //echo “; “;
echo ““; echo “<img src=images/news_sep_line.jpg align=top”;
echo “”;
} ?>
ubtng is right “mysqli_real_escape_string” does not defend against SQL Injection attacks and prepared statements and input validation are the way to go. Correct me if I’m wrong, but you can’t batch statements in MySQL? So \’; DESC users; – wouldn’t work…
You can however inject into numeric values where there is no single quote to break out of so if the site contains the following code: “SELECT * FROM Products WHERE ID = “.mysql_real_escape_string($_GET["id"]);
Doing this would bypass mysql_real_escape_string: vulnpage.php?id=1 UNION SELECT * FROM Users;
anywhere you have an SQL query that grabs typed user input and sends it to the database.
you can put it on the variable, on the right hand side, when you assign it the value of the user input.
or if you don’t use variables you can put the post/get/request function inside of it.
Thank you so much for the point that you must be connected to mysql for the mysql_real_escape_string() to work!! I had this set to my variables at the beginning of the code and couldn’t get it to work until I read your post.
mysql_connect(“localhost”, “username”, “password”); $var = mysql_real_escape_string(stripslashes($_POST['var']));
then I could use $var all nice and clean
THANKS AGAIN
Are php sql statements only at risk from injection when you’re using user-input data as part of the sql string?
For example, if you have statements like:
$con = mysql_connect(“localhost”,”myname”,”password123″); mysql_select_db(“mydb”, $con); $result = mysql_query(“SELECT * FROM mytable”); while($row = mysql_fetch_array($result)) {…etc etc etc
all hard coded into the php file and not using user input, is it at risk?
No, Chris, hardcoded statements do not suffer from Sql injection attacks. An Sql injection attack is exactly that, an attack upon a web site from an outside source.
I sincerely hope that coders are paraphrasing their code and not actually using code such as “Select * from users where user = mysql_real_escape_string($_POST[user]… etc. etc.
At the VERY minimum coders need to be using a database abstraction layer that includes any necessary data conversions and safeguards.
“SELECT * FROM Products WHERE ID = “.mysql_real_escape_string($_GET["id"]); Doing this would bypass mysql_real_escape_string: vulnpage.php?id=1 UNION SELECT * FROM Users;
“SELECT * FROM Products WHERE ID = “.mysql_real_escape_string($_GET["id"]);
That is because that statement is just poor coding.
“SELECT * FROM `Products` WHERE `ID` = “.mysql_real_escape_string($_GET["id"].“’”);
That is a MUCH better way to write that statement that subverts SQL injections with the use of mysql_real_escape_string().
Amazing article. I was googling for a quick and easy way to prevent sql injection and i’m very glad to find this site. Information is very technical and fun to read at the same time. Thanks so much for sharing this
Hi !
Instead of use foreach, you can use array_map or array_walk :
function escape($var) { return mysql_real_escape_string($var); }
$post = array_map(“escape”, $_POST);
See u !
for simple sql, how to prevent sql injection in php? for example select * from table1.
Thanks
where should i put this $SQL = “SELECT * FROM users where username = “.mysql_real_escape_string($POST['user']);???
Callum
Home
RSS Feed
