homepage Welcome to WebmasterWorld Guest from 54.234.147.84
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Escaping ' for DB inserts correctly
What it the right way to sterilize your data input before an INSERT stateme
Hugene




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

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




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

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




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

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




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

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

Hugene




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

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




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

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




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

i use stored procedures with parameters

Hugene




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

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved