SqliteClient > Statement escape

[michaelpeterlee]

String parsing appears to be happening somewhere, breaking this longstanding cross-platform SQLite statement, below.

This statement escapes by presenting double-quotes twice.

const sqlite = new SqliteClient(FILENAME, WORKER_PATH);
await sqlite.init();

sqlite.executeSql(
        'CREATE TABLE IF NOT EXISTS queue' +
        '(' +
            'id INTEGER PRIMARY KEY AUTOINCREMENT, ' +
            'object VARCHAR(5000), ' +
        ')'
);

const sql = 'UPDATE queue SET object = REPLACE(REPLACE(object,"processing","queued"),"attempt"":5","attempt"":0")';
sqlite.executeSql(sql);

[tomayac]

FYI @etiennenoel.

[sgbeal]

Note the subtle SQL syntax error in that (copy/pasted verbatim here):

sqlite.executeSql(
        'CREATE TABLE IF NOT EXISTS queue' +
        '(' +
            'id INTEGER PRIMARY KEY AUTOINCREMENT, ' +
            'object VARCHAR(5000), ' +
        ')'
);

Count the commas.

Tip: i don't know how executeSql() is handling the first argument, but if it's just passing it on to the underlying exec() call then the above can be written more legibly (IMO) with:

sqlite.executeSql([
        'CREATE TABLE IF NOT EXISTS queue(',
            'id INTEGER PRIMARY KEY AUTOINCREMENT, ',
            'object VARCHAR(5000), ', // the extra comma is here, btw
        ')'
]);

[michaelpeterlee]

Thanks and apologies, I hastily condensed that CREATE TABLE statement for brevity [is more abstract in the actual codebase]. The UPDATE statement is what is generating error.

[michaelpeterlee]

As a workaround, I refactored to escape the statement differently:

const sql = 'UPDATE queue SET object = REPLACE(REPLACE(object,\'processing\',\'queued\'),\'attempt":5\',\'attempt":0\')';

[tomayac]

Obsoleted via #45.