TalkPHP - SQL injections protection

TalkPHP (http://www.talkphp.com/forums.php)

- Tips & Tricks (http://www.talkphp.com/tips-tricks/)

- - SQL injections protection (http://www.talkphp.com/tips-tricks/1620-sql-injections-protection.html)

SQL injections protection

Hey all

I thought i would write a tutorial on SQL injections and just how easily they can effect programmers who don't project their code. This is mainly for people who are new to programming and taking into account the security of the databases you use i find is very important.

What is it?

SQL injections are ways for a hacker to break your code and be able to crack into your databases and get more information then you wanted people to.

How do they do it?

If say you have a user login system and a hacker comes along. He can type the following into the username box or the password box:

PHP Code:


		
			


// user input that uses SQL Injection

$name_bad = $_POST["username"]; // they enter 'OR 1=1



//inturn the SQL query below will look like the following

 

$query_bad = "SELECT * FROM members WHERE username = ''OR 1=1";

The query above will always return true, by using a single quote (') they have ended the string part of our MySQL query.So every single entry in the "members" table would be selected by this statement!

This can cause some big problems if your web host hasn't protected their MySQL on their side to stop this, some of them do. If you find they haven't the good ppl of PHP knew about this problem and proved a nice function called mysql_real_escape_string();

we use that function to prevent the MySQL injection.

[php]
$username = mysql_real_escape_string($_POST["username"]);

What is it?

SQL injections are ways for a hacker to break your code and be able to crack into your databases and get more information then you wanted people to.

How do they do it?

If say you have a user login system and a hacker comes along. He can type the following into the username box or the password box:

PHP Code:


		
			




$username = mysql_real_escape_string($_POST["username"]);

$query_bad = "SELECT * FROM members WHERE username = '$username'";

Now that query will look like the following

PHP Code:


		
			
SELECT * FROM members WHERE username = '\' OR 1\''

The function uses backslashes to escape them evil injects.

Stopping them before they reach MySQl Query

Now i found a way of stopping them from even reaching the MySQL query. You can check if the user has entered a ' and then display an error.

PHP Code:


		
			
$username = $_POST["username"];

$check = explode("'",$username);

  if($check[1])

  {

     echo "You are trying to use MySQL injects!"

  }else{

    //double check incase

    $username = mysql_real_escape_string($_POST["username"]);

    

    //query here

  }

Can't they also screw up your query by using a comment, '--'? And also perhaps by using:

Code:

a';DROP TABLE users; SELECT * FROM data WHERE name LIKE '%

(taken from Wikipedia)

http://en.wikipedia.org/wiki/SQL_injection covers many ways to inject malicious code.

Yeah they can do that also, but as far as i know the mysql_real_escape_string will always escape any input by the user.

Which method is better? the mysql_real_escape_string() or addslashes()? And why? Sorry just curious, because I've seen a few SQL Injection protection measure articles, and some say use addslashes, and some say use the real_escape_string... What's the difference?

When dealing with MySQL queries using mysql_real_escape_string is better as it protects you from more then just ' but also the following:

PHP Code:


		
			


\x00 

\n

\r  

' 

" 

\x1a

Optionally, you could use

PHP Code:


		
			
die("You are trying to inject MySQL");

Good tutorial :)

Yes indeed, which ever way would suit you. Die would be pretty good to stop the rest of the page loading ;-)

Quote:

Originally Posted by d4v1d (Post 5389)

Well, addslashes is PHP's idea of what should be escaped, whilst mysql_real_escape_string is what MySQL knows has to be escaped, and as it's for MySQL I'd be a lot more inclined to go for mysql_real_escape_string any day. Apparently they are removing addslashes from PHP 6.

One site I hacked a while ago was Tutorialized.com. You can often tell which sites are vulnerable just by adding a single quote in random places, such as in the URL:

Clearly vulnerable:
http://www.tutorialized.com/tutorials/Fireworks/1'/1

Ha, they are total idiots then.

You can get another error:
http://www.tutorialized.com/tutorial...9;''/1

Lol. Yep. You can do all sorts with that website. They patched their login after we had a go at it and accessed the administrative area (although we promptly logged out after and informed them), but the rest of the website is still very much vulnerable. Be a million and one ways in which you can hack that site.

Shows though, if someone's determined to hack a website, they will, whereas like Tutorialized stands as living proof that if they don't, they won't.

When I find insecure sites, I go into their admin panel and take a screenshot. I promptly email them showing them of this hole and offer my services to secure it.

Rendair, for your sql cleaning method, mysql_real_escape_string is all thats needed.

Quote:

Originally Posted by gcbdm (Post 5381)

Can't they also screw up your query by using a comment, '--'? And also perhaps by using:

Code:

a';DROP TABLE users; SELECT * FROM data WHERE name LIKE '%

(taken from Wikipedia)

Not with MySQL via PHP. PHP limits one query per call to mysql_query().

I've not used MySQLi much yet, but I believe that allows multiple queries in one call, doesn't it?

Quote:

Originally Posted by Wildhoney (Post 5508)

I've not used MySQLi much yet, but I believe that allows multiple queries in one call, doesn't it?

I'm guessing you're looking for: http://www.php.net/manual/en/functio...ulti-query.php

Quote:

Originally Posted by gcbdm (Post 5511)

I'm guessing you're looking for: http://www.php.net/manual/en/functio...ulti-query.php

That'll be the one. I should really get into MySQLi, especially if it plays nicely with MySQL's stored procedures which I'm confident it will do.

Wildhoney> You're absolutely correct. I'm used to referring to the original MySQL extension.

What about if we use encryption

What about if we use encryption prior to running a sql insert or select statement.

$username=encryptMe(mysql_real_escape_string($_POS T['username']));
$password=encryptMe(mysql_real_escape_string($_POS T['password']));
$sql="Insert into users (username, password) values ('username','password');

Then shouldn't it turn the bad sql "or 1=1" into something unrecognizable by our database. Then we just decrypt something when we need to display it back?

$usernametodisplay=decryptMe($row['username');

Yes, that would work, but escaping it takes less confusion and less processing for the same result.

Quote:

Originally Posted by Village Idiot (Post 5972)

Yes, that would work, but escaping it takes less confusion and less processing for the same result.

Yeah, but then if someone hacks the database wouldn't the data be secure as long as we could protect our key(s). Wouldn't someone if they were able to get in to out database just get gobbledygook that would be unusable without the key and the encryption being used?

I was planning to escape the input and then encrypt it. Whatever I present back to the user will be just that, presentation. As long as it is not vulgar, I could care less. Whenever I send anything back to the database I plan to encrypt it again. I keep the encryption functions all in one .inc.php file and it should basically just have two functions one to encrypt and one to decrypt, passing the string needed to be encrypted or decrpyted.

I understand the additional cpu cycles, but it would make me feel a lot happier if no one if they got a hold of the database could read anything, at least important that is.

The issue of someone hacking your sql database is a different story. Only encrypt data that needs to be encrypted, and encrypt that via 1 way encryption.