For those of you watching but still not getting his point: He is saying that QUOTE() improperly escapes a string where a \ and a ' appear together in the unquoted text. the sequence \' should look like \\\' when QUOTE()-ed. It should escape \ as \\ and ' as \'. \\+\' = \\\' . QUOTE() seems to be leaving out the 3rd \.
http://dev.mysql.com/doc/mysql/en/string-syntax.html Thomas, I think you should file a bug report on this. That way they FIX the QUOTE() function. No middle-ground needed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 03/17/2005 09:14:29 AM: > Hi everyone, > > I'm following up on my own question. There appears to be a bug in the way > how QUOTE() interacts with the mysql client. > > Using --raw mode can apparently solve the problem reported in my original > post: > > shell> mysql --raw -N -B -e "SELECT QUOTE(sometext) FROM foo;" test > 'Pitt\'s Place' > > That seems about right, but it doesn't solve the whole problem. Consider > this: > > shell> mysql -e "INSERT INTO foo VALUES('Joe\'s place\tthat\'s nice');" test > > (note: there is a <TAB> between 'place' and 'that') > > Let's retrieve this from the db, with and without --raw: > > shell> mysql --raw -N -B -e "SELECT QUOTE(sometext) FROM foo;" test > 'Joe\'s place that\'s nice' > > shell> mysql -N -B -e "SELECT QUOTE(sometext) FROM foo;" test > 'Joe\\'s place\tthat\\'s nice' > > None of those results can be re-inserted as is. The only possibility to > fix this would probably be to add a new option (how about --medium-raw ?) > to the mysql client. All else would break existing scripts. > > Thomas Spahni > > > On Tue, 15 Mar 2005, Thomas Spahni wrote: > > > Dear list, > > > > I don't understand what happens here: > > > > shell> mysql -N -e "SHOW VARIABLES LIKE 'version';" test > > +---------+------------+ > > | version | 4.0.14-log | > > +---------+------------+ > > > > shell> mysql -e "CREATE TABLE foo (sometext VARCHAR(255));" test > > shell> mysql -e "INSERT INTO foo VALUES('Pitt\\'s Place');" test > > shell> mysql -N -e "SELECT QUOTE(sometext) FROM foo;" test > > +-----------------+ > > | 'Pitt\'s Place' | > > +-----------------+ > > > > So far so good; exactly what I would expect. The string is nicely escaped > > with ONE backslash. But now, look at this: > > > > shell> mysql -N -B -e "SELECT QUOTE(sometext) FROM foo;" test > > 'Pitt\\'s Place' > > > > Double backslash in batch mode. Same result if I pipe the query into > > mysql. Why? This can't be fed into any INSERT query. Bug or feature? > > > > Any comments from the list are very welcome. > > > > Thomas Spahni > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
