exec() blindly splits statements on semicolons, fails on valid SQLite statements

[CodingDoug]

The following fails due to an attempt to split statements on ";", even though it's valid SQL :

    db.exec("INSERT INTO t (value) VALUES ('x;y')`)

Similarly, SQL that attempts to create a trigger will fail because the trigger statements each need to end with a semicolon that's part of the greater SQL statement that creates it.

create trigger tr
after insert on tbl
for each row
begin
    update some stuff;  // this semicolon causes problems
end;

This failure is similar to the way the libsql shell previously failed on creating triggers when blindly splitting on semis: https://github.com/libsql/libsql-shell-go/issues/131

See functions js_exec_sync and js_exec_async, which both do the same thing.

Likely the only way to correctly handle semis in these situations is to use a tokenizing SQL parser that better understands SQLite syntax. It would be even better if this was a part of the common libsql core so all future client SDKs could use it without having to bring their own parser.

[honzasp]

Hi Doug, the client method that executes multiple statements separated by semicolons is named executeMultiple() and it should work correctly (it does not attempt to parse the SQL in the client at all, but uses the sequence request in Hrana that natively executes semicolon-separated statements).

In fact, there is no exec() method at all in this package; perhaps you meant another library?

[CodingDoug]

@honzasp Oops, I filed this in the wrong repo. It should have gone to here in response to this: https://github.com/libsql/libsql-experimental-node/commit/bf324326d56c074fb8504e417a2c0521cb7f090e