Re: [PHP] Mixing sprintf and mysql_real_escape_string
On Monday 07 August 2006 19:39, Peter Lauri wrote: [snip]My guess: magic_quotes_gpc is enabled where you're running the script. Therefore slashes are already present in the data from the form post.[/snip] Should I turn it off? Adding slashes and mysql_real_escape_string is not exactly the same thing, correct? /Peter As standard procedure, you should disable magic quotes and register globals on any PHP install, just on principle. Both are very old ideas intended to make life easier and more secure for people who didn't know what they were doing but ended up causing more trouble than they were worth. Both are now to be avoided. And no, addslashes() and mysql_real_escape_string() are not the same thing. addslashes() just dumbly escapes quotes with backslashes. mysql_real_escape_string() does real string escaping according to MySQL's locale settings and various other rules, including escaping quotes as appropriate. -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mixing sprintf and mysql_real_escape_string
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:[EMAIL PROTECTED] Sent: Tuesday, August 08, 2006 5:54 AM To: Peter Lauri Cc: php-general@lists.php.net Subject: Re: [PHP] 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
Re: [PHP] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mixing sprintf and mysql_real_escape_string
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:[EMAIL PROTECTED] Sent: Tuesday, August 08, 2006 5:54 AM To: Peter Lauri Cc: php-general@lists.php.net Subject: Re: [PHP] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mixing sprintf and mysql_real_escape_string
2006/8/7, Peter Lauri [EMAIL PROTECTED]: 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? My guess: magic_quotes_gpc is enabled where you're running the script. Therefore slashes are already present in the data from the form post. -Original Message- From: Richard Lynch [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 08, 2006 5:54 AM To: Peter Lauri Cc: php-general@lists.php.net Subject: Re: [PHP] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mixing sprintf and mysql_real_escape_string
[snip]My guess: magic_quotes_gpc is enabled where you're running the script. Therefore slashes are already present in the data from the form post.[/snip] Should I turn it off? Adding slashes and mysql_real_escape_string is not exactly the same thing, correct? /Peter -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php