Bug #16338 QUOTE() function fails when the string contains '

• Submitted: 10 Jan 2006 13:54
• Modified: 11 Jan 2006 9:05
• Reporter: Thomas Spahni
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.25
• OS: Linux (Linux)
• CPU Architecture: Any

[10 Jan 2006 13:54] Thomas Spahni

Description:
QUOTE(str) will not produce a result that can be used as a properly escaped data value when str contains the quote character and when mysql client is used in --batch mode.

Client: mysql  Ver 12.22 Distrib 4.0.25, for pc-linux-gnu (i686)

How to repeat:
mysql -e "CREATE TABLE foo (str varchar(255));" test
mysql -e "INSERT INTO foo VALUES('Joe\'s place');" test
mysql -e "SELECT str FROM foo;" test
+-------------+
| str         |
+-------------+
| Joe's place |
+-------------+

mysql -N -B -e "SELECT QUOTE(str) FROM foo;" test
'Joe\\'s place'

Obviously this can't be used in an INSERT statement to produce the original result.

Suggested fix:
Fix the mysql command line client.

[11 Jan 2006 9:05] Valeriy Kravchuk

Thank you for a problem report. Sorry, but it is not a bug. The value you selected with -B can (and should) be inserted with -B. Look:

[openxs@Fedora 5.0]$ bin/mysql -N -B -e "select quote(str) from foo;" -uroot -P3306 --host=127.0.0.1 test
'Joe\\'s place'
[openxs@Fedora 5.0]$ bin/mysql --version
bin/mysql  Ver 14.12 Distrib 5.0.19, for redhat-linux-gnu (i686) using readline
5.0
[openxs@Fedora 5.0]$ bin/mysql -N -B -e "insert into foo values ('Joe\\'s place');" -uroot -P3306 --host=127.0.0.1 test
[openxs@Fedora 5.0]$ mysql -N -B -e "select str from foo;" -uroot -P3306 --host=127.0.0.1 test
Joe's place
Joe's place

So, you have just to compare "apples to apples"...