Escaping ' for DB inserts correctly

What it the right way to sterilize your data input before an INSERT stateme

[Hugene]

Hello all

I sterilize all my data input using the PHP method mysql_real_escape_string()

However, I run into a problem. If the string contains the " ' " character, then it gets inserted in my DB with an escape string "\'":

For example, if the user input was:

don't keep the appostrophy

The call to mysql_real_escape_string("don't keep the appostrophy") will produce

don\'t keep the appostrophy

which is then stored in my DB and latter displayed including the \ character in my html output.

How can I get around this problem and still use mysql_real_escape_string() to protect from sql injections?

Thanks a lot

[Matthew1980]

Hi there Hugene,

Have a read through: This link [uk.php.net] and also this link [uk.php.net] to see how to address this issue, and this shouldn't affect the mysql_real_escape_string() function, but make sure that you use other functions to protect your hard work, don't just rely on that one...

Cheers,
MRb

[brotherhood of LAN]

In general, anything that is sent through a POST form should use stripslashes to remove added backslashes... and then mysql_real_escape_string() on data you're going to into a database.

[bhukkel]

its better to turn magicquotes off and use prepared statement for db access

[Hugene]

Thanks to the both of you; I now cracked the nut.

The "magic_quotes_gpc" is ON on my server, which causes addslashes() to be called on the POST input. Then, when I was calling mysql_real_escape_string(), it was escaping my data twice. (basically, my initial explanation above was not really correct, because, if I understand well, when you use mysql_real_escape_string() to escape a string before putting in MySQL, MySQL will actually remove the escape character, so I will get a clean version of "O'Reilly" stored as a string in the DB)

Matthew, what do you mean when you say, don't rely just on that one? Here is the entire code I sue to protect from sql injections:

function helper_sterilizeInputForDb($v)
{
$removed_slashes = array_map("stripslashes",$v);
return array_map("mysql_real_escape_string",$removed_slashes);
}

What more should I do?

[Matthew1980]

Hi there Hugene,

function helper_sterilizeInputForDb()
{
$_POST = array_map("stripslashes",$_POST);
$_POST = array_map("mysql_real_escape_string",$_POST);
return $_POST;
}

As the $_POST array is a global, you don't need to pass it into the function, just reference it, BUT call this function after you have confirmed that the submit button has been clicked legally, and not being done through other means, then all $_POST data will be 'cleaned'

>>what do you mean when you say, don't rely just on that one?

What I mean is, don't just rely on mysql_real_escape_string for protecting your DB, you realistically need to use it in conjunction with other functions, but these can vary depending on the context of application that you are working on, for example: I would use 'trim' in the array_map() function to get rid of any whitespace, don't forget that every char has it's ascii counterpart...

Cheers,
MRb

[topr8]

i use stored procedures with parameters

[Hugene]

Matthew, what you are saying is that, an SQL injection could be done by using the ascii character for the escape strings? (as you can see, sql isn't my forte)

If this is the case, then I would need to do a search for these exact ascii strings.