Escaping inputs for mysql queries

billyd

I've only just discovered about mysql_real_escape_string and am now looking in horror at all the unprotected queries I have.

Is it a satisfactory and safe solution simply to stick

function mysql_real_escape_array($t){
return array_map("mysql_real_escape_string",$t);
}

in my function library, and then add a

$POST = mysql_real_escape_array($POST);

$GET = mysql_real_escape_array($GET);

at the top of each page that needs it?

djjjozsi

its only working if you've connected to a database in evry page loads.

if you re-type the POST superglobal's values into a form, the effect becouse of the mysql_real_escape_string is not suggested.

In that way you should make a copy of the $_POST array.

billyd

OK, yep, I'd sussed that.

Cheers!

bradgrafelman

Also note that it's not always correct to simply apply [man]mysql_real_escape_string/man to everything.

For example, if you assume that a given piece of data like $GET['id'] is going to be numeric, and you use it in your query as if it were numeric, e.g.:

$sql = "SELECT * FROM foo WHERE id=$_GET[id]";

(that is, omitting the single quotes), then [man]mysql_real_escape_string/man isn't going to prevent SQL injections. Instead, you should do something like cast $_GET['id'] to integer.

The easiest solution (IMHO) is to move to a more updated library (one that's not deprecated) and use prepared statements.

billyd

Thanks Brad - hopefully I'm covered there because I've always tended to put an intval() check on any numeric inputs anyway.

It's frustrating how difficult it has proved to be, building up a picture of how to make all this work! I'm sure there must be lots of articles out there on how to protect from sql injection attacks, but despite a whole lot of searching I certainly haven't found anything yet - just lots of discussion about bits and pieces around the topic, but no concise overview of the whole topic.

laserlight

billyd wrote:
It's frustrating how difficult it has proved to be, building up a picture of how to make all this work! I'm sure there must be lots of articles out there on how to protect from sql injection attacks, but despite a whole lot of searching I certainly haven't found anything yet - just lots of discussion about bits and pieces around the topic, but no concise overview of the whole topic.

One reason for that could be that the escaping mechanisms may be specific to the database management system and database programming interface, and even to the type of data to be escaped. If you go with the PDO extension, you can use prepared statements and quote, with the latter customised for string escaping for your choice of database for those cases where prepared statements will not do. But these assume that you do not attempt to directly use incoming data for table and column names, etc. Plus if prepared statements will not do and you have numeric data, then you have to fallback on type casting.