sqlite: Perl regex for escaping single quotes in SQLite query

jeudi 21 janvier 2016

I'm working on improving a script to convert SQL to SQLite. It has a bug in this part:

perl -pe '
if (/^(INSERT.+?)\(/) {
  s/\\'\''/'\'\''/g;
}
'

Line 3 is supposed to replace escaped single quotes (SQL) with double quotes (SQLite), but it shouldn't do that when the next character is a comma.

For this test case:

INSERT INTO "test" VALUES ('Aujourd\'hui', 'other data');
INSERT INTO "test" VALUES ('\\jvx\\', 'other data');

I get this result:

INSERT INTO "test" VALUES ('Aujourd''hui', 'other data');
INSERT INTO "test" VALUES ('\\jvx\'', 'other data');

The first line is correct, the second isn't.

How do I get the regex s/\\'\''/'\'\''/g; to ignore single quotes when the next character is a comma to make both examples valid SQLite?

sqlite