Turn off Magic Quotes.
They are doing an extra (bogus) escape before you ever get a chance at
the data.
On Mon, August 7, 2006 1:00 pm, Peter Lauri wrote:
> I should maybe add that the data actually comes from a form:
>
> mysql_query(sprintf("INSERT INTO table (value1, value2) VALUES (1,
> '%s')", mysql_real_escape_string($_POST['formvalue'])));
>
> And when I have ' in the field, it will insert \' into the database in
> pure
> form. If I do this it will add just ' (with the
> $_POST['formvalue']="'";
>
> mysql_query(sprintf("INSERT INTO table (value1, value2) VALUES (1,
> '%s')", $_POST['formvalue']));
>
> Something that we are missing out here?
>
>
> -----Original Message-----
> From: Richard Lynch [mailto:ceo@xxxxxxxxx]
> Sent: Tuesday, August 08, 2006 5:54 AM
> To: Peter Lauri
> Cc: php-general@xxxxxxxxxxxxx
> Subject: Re: Mixing sprintf and mysql_real_escape_string
>
> On Mon, August 7, 2006 12:35 pm, Peter Lauri wrote:
>> I get strange output if I combine sprintf and
>> mysql_real_escape_string. If I
>> do this the resulting into the database is \' not ' as I want.
>>
>> mysql_query(sprintf("INSERT INTO table (value1, value2) VALUES (1,
>> '%s')",
>> mysql_real_escape_string(" ' ")));
>>
>> Should this be like this? Do the sprintf already add slashes or
>> something?
>
> mysql_real_escape_string(" ' ") will yield: \'
>
> This is because the ' is a "special" character to the MySQL parser --
> It indicates the beginning and end of character-based data.
>
> So if you want ' to *BE* part of your data, it needs to be escaped
> with \ in front of ' and that tells MySQL, "Yo, this apostrophe is
> data, not a delimiter".
>
> sprintf should simply output:
> INSERT INTO table (value1, value2) VALUES(1, ' \' ')
> because is just slams the output into the %s part.
>
> mysql_query() sends that whole thing off to MySQL.
>
> When MySQL "reads" the SQL statement, and tries to figure out what to
> do, it "sees" that line.
>
> Because of the \' in there, it knows that the middle ' is not the end
> of the string, but is part of the data.
>
> So what MySQL actually stores for value2 is just:
> '
>
> MySQL does *NOT* store \' for that data -- The \ part of \' gets
> "eaten" by MySQL parser as it works through the SQL statement, and it
> just turns into plain old ' to get stored on the hard drive.
>
> If you think it did store that, then either you didn't tell us the
> correct thing for what you did, or your test for what MySQL stored is
> flawed.
>
> The usual suspect, in PHP, for this problem, is that the data is
> coming from GET/POST (or COOKIES) and you have Magic Quotes turned
> "ON" and the data is already getting escaped by
> http://php.net/addslashes, and then you escape it *AGAIN* with
> mysql_real_escape_string.
>
> mysql_real_escape_string is better than addslashes (and/or Magic
> Quotes) so turn off Magic Quotes and keep the mysql_real_escape_string
> bit.
>
> --
> Like Music?
> http://l-i-e.com/artists.htm
>
>
>
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
