addslashes problem with mysql update

how to handle form output before update

[proper_bo]

I have form output that needs to go into a database.
If the form output contains \' and I use addslashes on it then I am getting an error because for some reason it is only adding a slash to the back slash so:

' becomes \' - correct and will work
\' becomes \\' - incorrect and errors

Am I doing something wrong or missing something here?

It may be just that I am tired.

Thank you

[proper_bo]

both addslashes and mysql_real_escape_string replace "backslash quote" (\') with "backslash backslash quote" (\\')

I thought they would replace it with "backslash backslash backslash quote" (\\\') as this would then mean the backslash and the quote were escaped

anyone?

[seanpecor]

Man, that sounds strange because under no circumstances should addslashes() convert \' into \\'. Assuming the web server in question is the one running your profile URL, then you might want to upgrade from PHP v4.4.1 to v4.4.2 - this issue notwithstanding there were other regressions in 4.4.1 that may give you problems. This issue isn't mentioned in the changelog but you never know.

Is magic_quotes_gpc enabled? Is there another function you're using on that data that may be borking the string? One thing to do is to echo htmlentities($yourvar) after the submit so you make sure any non-display characters are sneaking in.

Sean

[proper_bo]

form is submitted with say bob\'s in it

It takes the output from the form
$_REQUEST['info']

it does mysql_real_escape_string on it and creates the sql.
If I echo it at this point I am getting
"INSERT into information VALUES info='bob\\'s'"

I am glad this is odd. php version 4.3.10

Also if I submit it with a single backslash in it, the backslash disappears.

magic quotes is off.

I will take a close look at my code and make sure I am not doing anything odd and that it is in the right order.

[seanpecor]

What is your sql_mode mysql setting? You can get that with "select @@sql_mode". I ask because mysql_escape_string() is calling the actual mysql api and perhaps there is a setting that is borking the mysql api. Like if 'NO_BACKSLASH_ESCAPE' is enabled or something.

Also try doing addslashes() instead of mysql_escape_string() just to see if you can reproduce the behavior. If you can't at least you know it's a mysql issue and it will help you zero in.

Sean

[proper_bo]

steps:

straight from form:
ex\ampl'etext\'st''ri\\ng - correct

after mysql_real_escape_string:
ex\\ampl\'etext\\\'st\'\'ri\\\\ng - correct

after adding single quotes for db insert:
'ex\\ampl\'etext\\\'st\'\'ri\\\\ng' - correct

in sql query:
'ex\ampl\'etext\\'st\'\'ri\\ng' - WRONG

[proper_bo]

sql_mode is empty for session and global in phpmyadmin.
Is there another way to see it?

I am 100% sure that it is mysql messing it up after testing.
The sql always looks correct until it has been run and then it is wrong and errors.

[omoutop]

From what you say : form is submitted with say bob\'s in it...

Why you submit the form with slashes?

$info - addslashes($_REQUEST['info']); // info=bob's with no \' escaping characters. Try to insert it that way

[proper_bo]

it is an example.
If a user submitted a form with a backslash before a single quote then errors occur.
Also single backslashes disappear.

$info - addslashes($_REQUEST['info']); // info=bob's with no \' escaping characters. Try to insert it that way

Yes it works for the \' because it is already commented but would then fail for ex'amp\'le

In theory I should get the form output, run it through mysql_real_escape_string and then insert it into the database. For some reason this isn't working. The question is why.

[omoutop]

So all you need to do is:
stripslashes() the form fields, // clean any mess from user
addslashes() the form fields,// make text ready to insert to db
insert to db, // hopefull the text eters correctly

[proper_bo]

but striping slashes from a form field will remove any slashes that the user has chosen to submit.
What if the slashes are wanted or needed?

Also php security suggests that mysql_real_escape_string is the only safe way to do it. strip_slashes doesn't cut the mustard.

[seanpecor]

If memory serves, addslashes() and stripslashes() aren't more or less secure than mysql_real_escape_string() when you are using the UTF-8 character set. Only 16 bit character sets (GBF, etc) are susceptible to the sql injection that mysql_real_escape_string() can help protect against.

Sean

[proper_bo]

Just to follow this up.
I have it working correctly now.
The problem stems from me using an execute function where the sql is passed asthe first argument with?'s in it and the second argument is an array of values to replace the?'s
Somewhere during the replace it all goes wrong.

I am going to rewrite the function.

Thanks for all your help.

[seanpecor]

Cool. That's an issue I've previously had with the mysql client. I believe that when it's used as a parameter on the commmand there is some clobbering taking place. If you must use an exec() I'd create two files rather than have two string parameters and use those files as input at the CLI. Safer that way also. Putting form inputs on the CLI *shudder*

Sean

[proper_bo]

I would just like to add to this.

I found that the problem involved preg_replace.

All content going into preg_replace comes back with half the number of backslashes in it.

So as a work around I added the line
$value = str_replace("\\","\\\\",$value);
which basically means replace every backslash with two backslashes.

Everything is now sweet.

If anyone knows a better solution feel free to let me know.

[coopster]

preg_quote [php.net]?

[proper_bo]

Thanks, thats great.
So you run that and then preg_replace?

Excellent. Thanks.