how do you escape single quotes in a mysql script?

All we need is an easy explanation of the problem, so here it is.

I need to create a trigger via mysql script

CREATE TRIGGER sync_new_oauth_sessions
    AFTER INSERT
    ON oauth_sessions FOR EACH ROW
BEGIN
    SET @s = 'INSERT INTO oauth_sessions_new(id, client_id, owner_type, owner_id, owner_id_new) VALUES (?, ?, ?, ?, CAST(NULLIF(?,'''') AS UNSIGNED))';
    -- INSERT INTO oauth_sessions_new(id, client_id, owner_type, owner_id, owner_id_new) VALUES (NEW.id, NEW.client_id, NEW.owner_type, NEW.owner_id, CAST(NULLIF(NEW.owner_id,'') AS UNSIGNED));
    PREPARE stmt2 FROM @s;
    SET @sid = NEW.id;
    SET @s_client_id = NEW.client_id;
    SET @s_owner_type = NEW.owner_type;
    SET @s_owner_id = NEW.owner_id;
    SET @s_owner_id_new = NEW.owner_id;
    EXECUTE stmt2 USING @sid, @s_client_id, @s_owner_type, @s_owner_id, @s_owner_id_new;
END

This is the content of the script. I’m getting:
Error 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 '' at line 5

I don’t understand what I’m doing wrong. If I do \'\' I have the same problem.

Thanks

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

the correct error message is

Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).

see https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

use

DELIMITER //
CREATE TRIGGER sync_new_oauth_sessions
    AFTER INSERT
    ON oauth_sessions FOR EACH ROW
BEGIN

    INSERT INTO oauth_sessions_new(id, client_id, owner_type, owner_id, owner_id_new) VALUES (QUOTE(NEW.id), QUOTE(NEW.client_id), QUOTE(NEW.owner_type), QUOTE(NEW.owner_id), CAST(NULLIF(NEW.owner_id,'') AS UNSIGNED));
END
//

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply