sqlite3_bind_text for variable number of values

I create a SQLite database with:

sqlite3 *db;
char *dbErrMsg;
int rc = sqlite3_open("test.dat", &db);
if (rc)
{
std::cout << "cannot open database\n";
exit(2);
}
rc = sqlite3_exec(db,
"CREATE TABLE IF NOT EXISTS like ( userid, likeid );",
0, 0, &dbErrMsg);
rc = sqlite3_exec(db,
"DELETE FROM like;",
0, 0, &dbErrMsg);
rc = sqlite3_exec(db,
"INSERT INTO like VALUES "
"(1,1),(1,2),"
"(2,2),(2,3),"
"(3,3),(3,1),"
"(4,3),(4,1);",
0, 0, &dbErrMsg);

Then run a SQL select from the command line tool

C:\Users\James\code\sharedLikes\bin>sqlite3 test.dat
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> SELECT userid,likeid FROM like WHERE userid != 1 AND likeid IN (1,2);
2|2
3|1
4|1

All is working as expected.

Now I run the same select command using the C API

sqlite3_stmt *match;
rc = sqlite3_prepare_v2(db,
"SELECT userid,likeid "
"FROM like "
"WHERE userid != 1 "
"AND likeid IN ( 1,2 );",
-1, &match, 0);
int found = 0;
while ((rc = sqlite3_step(match)) == SQLITE_ROW)
{
found++;
}
sqlite3_reset(match);
std::cout << found << " rows found\n";

Output is good:

3 rows found

But what I really want to do is run queries looking for a variable number of matching likes. So I tried using sqlite3_bind_text like this

sqlite3_stmt *match2;
rc = sqlite3_prepare_v2(db,
"SELECT userid,likeid "
"FROM like "
"WHERE userid != ?1 "
"AND likeid IN ( ?2 );",
-1, &match2, 0);
rc = sqlite3_bind_int( match2, 1, 1);
rc = sqlite3_bind_text( match2, 2, "1,2", -1,0);
found = 0;
while ((rc = sqlite3_step(match2)) == SQLITE_ROW)
{
found++;
}
sqlite3_reset(match2);
std::cout << found << " rows found\n";

Output shows that no rows are found

0 rows found

I used the debugger to check that rc was always returned as 0 ( no errors ) ( In the real code rc is checked, but I have removed the checks for readability in this test/demo. )

Here is the complete code for the demo application

#include <iostream>
#include "sqlite3.h"
int main(int argc, char *argv[])
{
sqlite3 *db;
char *dbErrMsg;
int rc = sqlite3_open("test.dat", &db);
if (rc)
{
std::cout << "cannot open database\n";
exit(2);
}
rc = sqlite3_exec(db,
"CREATE TABLE IF NOT EXISTS like ( userid, likeid );",
0, 0, &dbErrMsg);
rc = sqlite3_exec(db,
"DELETE FROM like;",
0, 0, &dbErrMsg);
rc = sqlite3_exec(db,
"INSERT INTO like VALUES "
"(1,1),(1,2),"
"(2,2),(2,3),"
"(3,3),(3,1),"
"(4,3),(4,1);",
0, 0, &dbErrMsg);
sqlite3_stmt *match;
rc = sqlite3_prepare_v2(db,
"SELECT userid,likeid "
"FROM like "
"WHERE userid != 1 "
"AND likeid IN ( 1,2 );",
-1, &match, 0);
int found = 0;
while ((rc = sqlite3_step(match)) == SQLITE_ROW)
{
found++;
}
sqlite3_reset(match);
std::cout << found << " rows found\n";
sqlite3_stmt *match2;
rc = sqlite3_prepare_v2(db,
"SELECT userid,likeid "
"FROM like "
"WHERE userid != ?1 "
"AND likeid IN ( ?2 );",
-1, &match2, 0);
rc = sqlite3_bind_int( match2, 1, 1);
rc = sqlite3_bind_text( match2, 2, "1,2", -1,0);
found = 0;
while ((rc = sqlite3_step(match2)) == SQLITE_ROW)
{
found++;
}
sqlite3_reset(match2);
std::cout << found << " rows found\n";
}

How can I run a SELECT ... IN ( ) query with a variable number of IN values?

Solution, following suggestion by @SHR:

int owner = 1;
std::string ownerInterests = "1,2";
std::string query = "SELECT userid,likeid "
"FROM like "
"WHERE userid != " +
std::to_string(owner) +
" AND likeid IN ( " + ownerInterests + " );";
std::cout << query << "\n";
sqlite3_stmt *match3;
rc = sqlite3_prepare_v2(db, query.c_str(),
-1, &match3, 0);
found = 0;
while ((rc = sqlite3_step(match3)) == SQLITE_ROW)
{
found++;
}
sqlite3_reset(match3);
std::cout << found << " rows found\n";

bind can be used to bind a single value. you can't bind several arguments in a single bind command.

your bind creates the following query: (with a single argument inside the IN condition)

SELECT userid,likeid FROM like WHERE userid != 1 AND likeid IN ( '1,2' )

you can append it to the query string (or use a sprintf...), but you must bind each argument in a single bind method. you can't create a list with a different count of elements in a single prepared statement.

You can create the query like this: (without binding, or bind only userid)

char sql_buffer[256]
sprintf (sql_buffer, "SELECT userid,likeid FROM like WHERE userid != 1 AND likeid IN ( %s )", 1,"1,2");

The binding must prevent SQL injection. what if you bind something with malicious code in an argument? for example: instead of "1,2" I'll put "1,2); delete from like where userid not in (-1", It allow a restricted user to delete your entire DB. you can't inject it when it is only a single argument.