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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
