db_sqlite can't correctly quote binary data

[zielmicha]

For example:

import db_sqlite

let a = db_sqlite.open(":memory:", nil, nil, nil)
a.exec(sql"create table foo (a blob);");
a.exec(sql"insert into foo values (?);", "\xe5\xf9\xb1\xbf\x16\x1e\n\xd0\"\x8a\xb2\x82\x07\xdc\xb3\x8ek\xed\xdd\xad\x00\xa5g\xa3\xa5B<\xa7f\x91\xd1\xa38\x84\xf8\xa7&Mh\xaan\x8e&\x99\x868\xf0\xb3WK\x81\xbdp+\xd3\xc0\x8b\xe9\x10Z\x15\x17\xf5\x9d\x9a\x87\xe4\xab(\x10\xd5=\x88B<%@Mj*\x87\xcch\xae\xec\x90K\xde\x1d\x83\x9ew\xf8\xbc/gH\x85\x0c\xec\x03\xe6\xa3\xd1\xa2\xa7t\xcd\xb2\x90\xb8\'\xae\xb8$c\xcc\xf2?\xea\x1e\x94[\xc6\x18\xbd\xfe\"");

raises exception, but should correctly insert the string into the database.

Traceback (most recent call last)
a.nim(5)                 a
db_sqlite.nim(138)       exec
db_sqlite.nim(104)       dbError
Error: unhandled exception: unrecognized token: "'����
�"���ܳ�k�ݭ" [DbError]

[xzfc]

Right now, db_sqlite uses `$` and dbFormat to prepare SQL statements. I think, it should use sqlite3_bind_* functions (doc) instead for proper binding.

[zielmicha]

There is also an issue when retrieving data that contains null bytes (it's clipped to the first null byte).

[xzfc]

As workaround, you can use my execEx function from db_sqlite_extras.nim instead of exec.

import db_sqlite
import db_sqlite_extras

let a = db_sqlite.open(":memory:", nil, nil, nil)
a.exec(sql"create table foo (a blob);");
a.execEx(sql"insert into foo values (?);", dbBlob "\xe5\xf9\xb1\xbf\x16\x1e\n\xd0\"\x8a\xb2\x82\x07\xdc\xb3\x8ek\xed\xdd\xad\x00\xa5g\xa3\xa5B<\xa7f\x91\xd1\xa38\x84\xf8\xa7&Mh\xaan\x8e&\x99\x868\xf0\xb3WK\x81\xbdp+\xd3\xc0\x8b\xe9\x10Z\x15\x17\xf5\x9d\x9a\x87\xe4\xab(\x10\xd5=\x88B<%@Mj*\x87\xcch\xae\xec\x90K\xde\x1d\x83\x9ew\xf8\xbc/gH\x85\x0c\xec\x03\xe6\xa3\xd1\xa2\xa7t\xcd\xb2\x90\xb8\'\xae\xb8$c\xcc\xf2?\xea\x1e\x94[\xc6\x18\xbd\xfe\"");

[hashbackup]

Check https://github.com/GULPF/tiny_sqlite. It handles blobs, caches prepared statements, etc.