Welcome to WebmasterWorld Guest from 54.167.0.111

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Escaping ' for DB inserts correctly

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

     

Hugene

7:07 pm on Sep 22, 2010 (gmt 0)

10+ Year Member



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

7:34 pm on Sep 22, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

7:59 pm on Sep 22, 2010 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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

7:23 pm on Sep 23, 2010 (gmt 0)

5+ Year Member



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

Hugene

7:36 pm on Sep 23, 2010 (gmt 0)

10+ Year Member



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

8:04 pm on Sep 23, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

11:24 pm on Sep 23, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



i use stored procedures with parameters

Hugene

2:45 pm on Sep 27, 2010 (gmt 0)

10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month