MySQL Bugs: #47409: Escaped single quote works but raises the error 1064

Bug #47409	Escaped single quote works but raises the error 1064
Submitted:	17 Sep 2009 11:12	Modified:	18 Sep 2009 6:22
Reporter:	Danny D Mamaev	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.67	OS:	FreeBSD
Assigned to:		CPU Architecture:	Any
Tags:	backslash, error 1064, single quote

Description:
Hello, the problem is as follows:

I run the following SQL at my webserver

insert into address set
partyId = '-22',
addressTypeId = '23106',
address = 'asddd\'fee'

it inserts a line ok and I can get the inserted id via the insert_id()

but when afterwards I look up for error status:

mysql_errno();
mysql_error();

it returns

1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fee\'', 0, '/index3/number')' at line 1

Why is that happening?

server version 5.0.67, client version is the same.
I use PHP 5.2.8 under apache 2.2.11

thanks,
Danny

How to repeat:
To repeat try to insert a string into varchar or text field escaping the single quote with backslash (btw if I amend the backslash with the single quote it works well and returns 0 as error code)

Thank you for the report.

This is more likely not MySQL bug, but error in you script. But if you still believe this is MySQL bug please provide PHP script demonstrating the problem.

Well it's simple enough,

mysql_query("insert into address set
partyId = '-22',
addressTypeId = '23106',
address = 'asddd\'fee'");

print_r(mysql_errno());
print_r(mysql_error());

thanks, 
Danny

Thank you for the feedback.

I can not repeat described behavior using current development version of MySQL server and script following:

<?php
mysql_connect('127.0.0.1:3350','root','');
mysql_select_db('test');
mysql_query("insert into address set
partyId = '-22',
addressTypeId = '23106',
address = 'asddd\'fee'");

print_r(mysql_errno());
print_r(mysql_error());
?>

Please try with current version 5.0.85 and if problem still exists indicate accurate version of PHP you use. Also, please, turn general query log to on and examine it in case if you sent query with syntax error really.

Ok, this is what I've got from the general query log:

----------------------------------------------------

server# tail server.log
                      7 Query       insert into address set
partyId = '-22',
addressTypeId = '23310',
address = 'asddd\'fee'
                      7 Quit

----------------------------------------------------

As per your questions I repeat once again:
mysql server/client version 5.0.67
PHP 5.2.8

I cannot try 5.0.85 because I need to rebuild half of the server so I must be absolutely sure this helps. That's why I ask you for the ultimate answer how to solve this problem.

FYI I have the same problem on both of my servers and the different one's mysql client version is 5.0.51a. Behaviour is absolutely the same: it inserts a line but throws an error.

thanks, 
Danny

Thank you for the feedback.

Record in general query log is correct. Additionally I can not repeat described behavior with test case provided. So we need exact test from you including SHOW CREATE TABLE and full PHP script.

I recreated the plain code for the error but the `bug` didn't appear so I think it's somewhere at the rest of the code.

Sorry for the disturbance.

thanks, 
Danny

Thank you for the update.

Closed as "Can't repeat" because last comment.