[Bug] Edit a trigger with quoted strings

[expired user #8405]'s profile image [expired user #8405] posted 9 years ago in General Permalink
Hello,

there is a bug with HeidiSQL 8.3.0.4822. If you recall a trigger which contains strings, the ' character isn't quoted. So the systax check is wrong and if you save this trigger you produce an error. All string values in trigger (and possible stored functions and procedures) must be quoted! This is missing.

Greetings Martin
[expired user #8405]'s profile image [expired user #8405] posted 9 years ago Permalink
Sorry, i forget to write, this bug occured by mysql!
ansgar's profile image ansgar posted 9 years ago Permalink
Cannot reproduce here. When creating a trigger, HeidiSQL executes this query:
CREATE TRIGGER `tritest` BEFORE INSERT ON `foo` FOR EACH ROW BEGIN
insert into part (`Month`) values ('3');
END;

When I then load the trigger again, it displays exactly this:
BEGIN
insert into part (`Month`) values ('3');
END

After modifying the code, HeidiSQL fires this one:
DROP TRIGGER `tritest`;
CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `tritest` BEFORE INSERT ON `foo` FOR EACH ROW BEGIN
insert into part (`Month`) values ('4');
END;
[expired user #8405]'s profile image [expired user #8405] posted 9 years ago Permalink
Use a string with a ' character, which must be escaped!

INSERT INTO `part` (`text`) VALUES ('Can\'t do it!');
ansgar's profile image ansgar posted 9 years ago Permalink
Yes, I see the problem now.

MySQL seems to silently remove single backslashes from the trigger when running the CREATE TRIGGER query. Reading the trigger contents with "SHOW TRIGGERS FROM `yourdatabase`" does not show it. So how to fix that?
[expired user #8405]'s profile image [expired user #8405] posted 9 years ago Permalink
You are right, it's a problem by mySQL with
SHOW TRIGGERS FROM `database`;


But if you read the trigger via
SHOW CREATE TRIGGER `database`.`trigger`;
it seem's the quotes are okay!
ansgar's profile image ansgar posted 9 years ago Permalink
Yes, looks fine here. Will check if that would not break something else.
Code modification/commit from ansgar.becker, 9 years ago, revision 4825
Get trigger code from SHOW CREATE TRIGGER instead of using SHOW TRIGGERS, to fix a single quote escaping bug. See http://www.heidisql.com/forum.php?t=16501
ansgar's profile image ansgar posted 9 years ago Permalink
r4825 should fix that. Please test and give feedback.
[expired user #8405]'s profile image [expired user #8405] posted 9 years ago Permalink
Thanks, that's working! No problems detected.

Why do you call 'SHOW TRIGGERS FROM' before every 'SHOW CREATE TRIGGER'?
ansgar's profile image ansgar posted 9 years ago Permalink
To get the definer, table name, timer and the event.
TODO for me: parse these details out of the SHOW CREATE TRIGGER code.
ansgar's profile image ansgar posted 9 years ago Permalink
Now I know why I had preferred SHOW TRIGGERS over SHOW CREATE TRIGGER: See here.

Please login to leave a reply, or register at first.