Sql Injection, How To Do It, And How To Prevent It NOT a hacking tutorial, learn how to secure PHP scripts

[shadowx 0]

Sql Injection, its one of those terms that is banged around the internet, and not everyone knows what it is. Basically it is what it says it is, its a way of injection SQL code into a script (in this case a PHP script) that connects to, and queries a databae, specifically an SQL based database.

 

So how do you inject code into code? Think about this logically and with some code examples, the following code takes the users input in POST variables:

 

$user = $_POST['username'];$pass = $_POST['password'];$query = mysql_query("SELECT * FROM users WHERE username='$user' AND password='$pass'");$rows = mysql_num_rows($query);if($rows == 1){	echo "logged in";} ELSE {	echo "Wrong user/pass";}

NOTE: This code may or may not work, im not testing any code i use here, its mainly for illustrative purposes.

 

So the basics of it are: we get the username/password entered by the user, check the database for a row with those details. If there is ONE row it means the user and pass are correct and we log them in. Otherwise chuck em out!

 

If i entered username: admin password: password the query looks like this:

 

$query = mysql_query("SELECT * FROM users WHERE username='admin' AND password='password'");

But what happens if i enter this: username: ' OR 1='1' password: ' OR 1='1'

 

our query looks like this:

 

 

$query = mysql_query("SELECT * FROM users WHERE username='' OR 1='1' AND password='' OR 1='1'");

In other words: Select everything from the table where the username matches '' (nothing) OR where the number 1 is equal to the number 1.

 

Now of course 1='1' is TRUE because 1 does equal 1 and so the script logs you in even though you never entered a real username or password. As far as the code is concerned the query returned TRUE and so it logs you in.

 

THAT IS BAD! Not only can i log in without having a valid username and password, what happens if i enter my username: ' DROP TABLE 'tablename' --? (-- is the comment character in SQL so everything AFTER the -- is ignored by SQL we now have:

$query = mysql_query("SELECT * FROM users WHERE username='' DROP TABLE 'tablename' -- AND password='' ");

so our SQL does the following: select everything in the table where username = NOTHING then delete the entire table called tablename then ignore everything after -- ignored ignored ignored ignored......

 

So ive just delete EVERY user your site ever had! Bad times!

 

So how do you protect your sites against these attacks? Pretty complicated i suppose? So many things that can go wrong it must be a complex solution! WRONG! ONe function solves ALL these problems!

 

mysql_real_escape_string(STRING, LINK TO DATABASE);//for example:mysql_real_escape_string($username, $link);

That little function will prevent all those bad things happening to you. Remember however that you MUST connect to the database BEFORE you use this function. The $link variable there is the resource ID for the connection to the database, this is needed because mysql_real_escape_string() will format the STRING in accordance with the database format. So this function should work on ANY database compatible with mysql (hopefully).

 

So here is our fixed and secure code:

 

$user = $_POST['username'];$pass = $_POST['password'];$pass = mysql_real_escape_string($pass, $link);$query = mysql_query("SELECT * FROM users WHERE username='$user' AND password='$pass'");$rows = mysql_num_rows($query);if($rows == 1){	echo "logged in";} ELSE {	echo "Wrong user/pass";}

I hope that helped some people This vulnerability is NOT limited to login systems. Any system with a mysql_query using user submitted variable data is a possible target. In one study out of 1000 sites 11.8% were vulnerable against this attack. Not a huge percentage, but how many websites are out there? now work out 10% of that number... Big number huh?!