[RESOLVED] SQL Injection attack prevention advice

stroix

After reading a blog post about SQL Injection attacks, the following example was provided to offer protection against these attacks for data being stored in MySQL. Before implementing this on my web site I wanted to double check with the PHP community to assure myself that this is the most I could do and is the best way to protect the web site and the data against such attacks. I am asking the following 2 questions, but please feel free to comment on anything.

1) Does the following provide enough protection against SQL Injection attacks and malicious HTML code?

2) Is there a better method of prevention that could be used? Please provide a working example.

Thank you for your support.

<?php

if ( get_magic_quotes_gpc() )
  {
  $name = htmlentities(mysql_real_escape_string(stripslashes($_POST["name"]), $dbconnection));
  }
  else
  {
  $name = htmlentities(mysql_real_escape_string($_POST["name"], $dbconnection));
  }

// then :

$sql = "INSERT INTO tbl(name) VALUES ($name)";

?>

laserlight

1) Does the following provide enough protection against SQL Injection attacks and malicious HTML code?

Probably, but the actual SQL statement is not correct and should be:

$sql = "INSERT INTO tbl (name) VALUES ('$name')";

2) Is there a better method of prevention that could be used? Please provide a working example.

Maybe. The problem with the given method is that it is too inflexible. It assumes that the data will be printed in the context of HTML, and thus applies htmlentities() to it for storage. As such, I would suggest:

$name = get_magic_quotes_gpc() ? stripslashes($_POST['name']) : $_POST['name'];
$name = mysql_real_escape_string($name);

then you use htmlentities() when printing the data to a webpage.

Some argue that magic_quotes_gpc is evil to begin with as it corrupts data due to its indiscriminate manhandling of input. As such, they recommend that you simply abort the script if magic_quotes_gpc is set, and force it to be turned off.

Roger_Ramjet

A much better method is to use PDO and Prepared Statements. As the manual says:

The parameters to prepared statements don't need to be quoted; the driver handles it for you. If your application exclusively uses prepared statements, you can be sure that no SQL injection will occur. (However, if you're still building up other parts of the query based on untrusted input, you're still at risk).

rulian

I used to use htmlenttities as well, but the problem was as was explained to me it's character code specific, and you are entering crypted info into your db which would make matching and extraction less precise

NogDog

laserlight wrote:
...Some argue that magic_quotes_gpc is evil to begin with as it corrupts data due to its indiscriminate manhandling of input. As such, they recommend that you simply abort the script if magic_quotes_gpc is set, and force it to be turned off.

And "some" are lucky enough to be working in an situation where they can dictate the environment; but in the real world that is not always feasible (such as when coding an application to work on a shared host or for general distribution in an open-source context), and it certainly doesn't hurt anything, except for a few microseconds of processing time, to check for it and counteract it if needed.

stroix

Thanks guys. =)