Bug #18293 Values in stored procedure written to binlog unescaped
Submitted: 17 Mar 2006 1:57 Modified: 25 Mar 2006 19:39
Reporter: Jeremy Cole (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.19 OS:
Assigned to: Mats Kindahl CPU Architecture:Any

[17 Mar 2006 1:57] Jeremy Cole
Description:
If a row is inserted inside a stored procedure directly using the values passed in to the function, the value string is replaced in the binary log with NAME_VALUE(name, value), but value is not escaped properly, causing queries with ' (single quote) to break the binary log (and replication).

How to repeat:
delimiter //

RESET MASTER //
DROP TABLE IF EXISTS t //
DROP PROCEDURE IF EXISTS addt //
CREATE TABLE t (s CHAR(50)) //
CREATE PROCEDURE addt (IN inValue CHAR(50)) BEGIN INSERT INTO t (s) VALUES ( inValue ); END; //
CALL addt("Foo's Bar") //
SHOW BINLOG EVENTS //

You will see this line in the binary log:

use `test`; INSERT INTO t (s) VALUES (  NAME_CONST('inValue',_latin1'Foo's Bar') )

Note the unescaped quote.

Suggested fix:
Fix in sql/item.cc or sql/sp_head.cc (not sure which place it's broken).
[17 Mar 2006 3:10] Dean Ellis
Verified against current 5.0.20 BK tree.
[25 Mar 2006 19:39] Mike Hillyer
Documented 5.0.20 changelog:

 <listitem>
        <para>
          If a row was inserted inside a stored procedure using the
          parameters passed to the procedure in the INSERT statement, the resulting binlog entry was not escaped properly.
          (Bug #18293)
        </para>
      </listitem>