Grab the code which does that escaping almost as you would find suitable.

In place of the "terminate scan at encountered NUL" condition, put a "terminate scan at length of scanned sequence" condition.

Call your new escaping function.

Am I missing something about this problem? (I expect so, but what?)

One unclear aspect is: What is the future of these data values? You should be aware that, while SQLite is perfectly able to store and retrieve TEXT with embedded NUL characters, its SQL functions generally treat such as being terminated by the NUL.

Thanks Larry!

I should've mentioned it before but here is more context: This is for an Android library which is a Java/Kotlin abstraction on top of SQLite.

The issue I have specifically is that the user of the library can create a Java/Kotlin function which takes a list of elements as a parameter and then they can write a query which looks like this:

"SELECT * FROM users WHERE id IN (:ids)"

Where :ids is a parameter placeholder. Then the library takes such query and transforms it so that it becomes:

"SELECT * FROM users WHERE id IN (?, ?, ?, ... ,?)"

and it automatically binds those ? parameters to the list of elements which the user provides as an argument when they call the function.

Now because of the SQLite limits, this argument with a list of elements can't be larger than 999/32766 (depending on the SQLIte version).

The library is currently relying on ? params, it compiles the statement and binds the parameter values. I thought about changing this behavior so that the parameter values are placed directly into the query. But in this case I need to properly escape those params before putting them inside the query.

So I'm either looking for a built in sqlite function which does that or for a robust escaping algorithm which I can implement in Java/Kotlin.

So I'm either looking for a built in sqlite function which does that...

https://www.sqlite.org/printf.html

You mention handling of NULLs, but if your particular binding case is only relevant for IN(...) then NULL handling is irrelevant, as a NULL value will never match an IN() value. Your conversion of :ids to an IN list can simply skip over any NULL values.

$ sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
...
sqlite> create table t(a);
sqlite> insert into t values(1),(NULL),(3);
sqlite> select * from t where a in (1, NULL, 3);
1
3
sqlite> select count(*) from (select * from t where a in (1, NULL, 3));
2

I'm more worried about strings containing NULL (\0) characters in the middle.

See also the carray extensions, although I'm not sure how it handles embedded NUL characters in char* arguments.

https://sqlite.org/carray.html

It's worth considering what you are enabling by allowing more than 999/32766 literals in a single query. A literal list is not really a table so I don't know if sqlite's query planner will consider indexing it, and thus this may be a recipe for poorly performing queries thanks to linear scans of the literal list against every candidate row. It's not clear to me that it's a pattern that should be encouraged :)

it doesn't handle NULL characters

SQLite allows NUL bytes inside strings, but they can cause problems elsewhere. To create NUL bytes for SQLite, you can use char(0). Alternatively, express the data as blobs using the x'<large hexadecimal number>' syntax, with no escaping problems.

There's a simple way to pass in an (effectively) unlimited number of parameters as a single parameter, thereby bypassing the 999/32766 parameter limit. Just encode all the ids into a single json array and deconstruct it on the database side using json_each. That way you still gain the protection of using prepared statements, but you only use one parameter.

i.e. instead of

SELECT * FROM users WHERE id IN (2, 3, 4); 

do

SELECT * FROM users WHERE EXISTS (
    SELECT 1 FROM json_each('[2,3,4]') WHERE json_each.value = users.id
)

example https://www.db-fiddle.com/f/piWeVosh79GTndqiafTCEc/0

slightly more compact:

SELECT * FROM users WHERE id IN (
  SELECT value FROM json_each( :ja )
);

and bind :ja to a json array of your IDs.