SQL Injection Protection

[timothyarden]

Hi PHP Freaks,

At the moment I have been researching SQL Injection Protection however most sites just tell me that mysqli::real_escape_string ( string $escapestr ) is not enough and hackers can still insert malicious actions. I don't know how else to secure it. If anyone has a pre-written script for SQL Injection Protection or knows what other functions I need to make use of could you please advise.

 

(Also functions for protecting from HTML injections)

 

Thanks in advance,

Timothy

Edited January 2, 2013 by timothyarden

[Psycho]

You could write a book on this subject - and I believe there are plenty. In fact, the same question(s) and variations on them have been asked numerous times on this forum. There is no way to provide anywhere near a complete response on such a question in a forum post. I suggest you do some searching for good tutorials on the subject. But, I will provide one piece of advice that I believe is absolutely crucial:

 

Always analyze in what context a piece of data is to be used and understand the potential problems that can occur in that process. Then learn how to safeguard against those problems.

 

For example:

 

For data that is used in a query, each "type" of data can require different methods of validating/sanitizing. If a piece of data should be an integer then verify it is an integer or force it to be one. The real_escape_string method on safeguards against malicious string data - using it on a value that should be an integer will prevent sql injection but can still allow the value to cause the query to fail.

[timothyarden]

Thanks for the response Psycho.

At the moment I only want to ensure that malicious string data is not allowed. The other validating or sanitizing to check for values that could cause the query to fail are irrelevant at the moment.

 

So all I need is the mysqli::real_escape_string()?

 

Timothy

[requinix]

The one time I know where mysql_real_escape_string() won't protect you is if you don't use quotes around the value.

$query = "SELECT * FROM table WHERE id = " . mysql_real_escape_string($_GET["id"]);

http://www.example.com/path/to/script.php?id=1+OR+username=0x61646D696E

(that's "admin" as a hex value, which MySQL lets you do in place of strings)

SELECT * FROM table WHERE id = 1 OR username=0x61646D696E

 

[edit] As for XSS, htmlentities() or htmlspecialchars() with ENT_QUOTES (only conditionally required) will guarantee that whatever string you run them will not be interpreted as HTML markup. For better or worse.

Edited January 3, 2013 by requinix

[timothyarden]

Okay, thanks - I'll read the documentation on the other two functions you advised me of.

 

Timothy

[bashy]

Best not to use mysql_* queries any more, using prepared sql statements is injection safe.

More info http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php

 

PHP.net deprecation: https://wiki.php.net/rfc/mysql_deprecation

[timothyarden]

So does that mean that this is not supposed to be used anymore or is it okay and safe to use?

mysqli::query("INSERT INTO `users` (`username`,`password`) VALUES (`".mysqli::real_escape_string($_POST['username'])."`,`".mysqli::real_escape_string($_POST['username'])."`)";

[requinix]

If you're using mysqli you might as well take the safest path and use prepared statements. No escaping required. (In fact trying to escape the data will just mess it up.)

[timothyarden]

Sorry, Im not sure how to. Could you please provide an example (& explain it if possible) - I looked at Bashy's links and was 50/50 for understanding.

[requinix]

Take a look at this.

 

[edit] Ah, that's PDO. Uh... try either of these.

Edited January 3, 2013 by requinix

[wright67uk]

@requinix, your link 'these' led to a page which gave in my opinion a really well explained example of prepared statements.

 

However the comments left at the bottom of the page, say that the example was a bad one that contained mistakes. As somone that has given me plenty of advice before, what did you make of it?

[requinix]

Top to bottom:

* Al's 1:50am is technically correct because it won't protect you if you don't put quotes around the value. But his 2:15am and 2:17am posts are wrong. His "I would be doing crackers a favor" comment screams of newbness.

* Not sure what Jeff Z was getting at.

* Andy Powell suggestion to use MD5 is bad. Don't.

* I didn't look at the class Andy mentioned.

* Tony Arnold, foo, and Paul Dunderdale pointed out important typos.

* matt's comment doesn't apply since there really shouldn't be multiple users with the same username.

* I agree with Zac and Ryan S that using prepared statements just for sanitizing data is wasteful, but it is a perfect solution.

Edited January 5, 2013 by requinix

[wright67uk]

Many thanks, I shall give this a go!