如何在SQLite C API中实现带可变IN值的SELECT查询？

问题重现 #

1. 创建测试数据库 #

使用以下C++代码创建SQLite数据库并插入测试数据：

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);

2. 命令行验证查询结果 #

通过SQLite命令行执行目标查询，结果符合预期：

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

3. 固定IN值的C API查询正常 #

使用C API执行固定IN值的查询，输出正确：

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";

输出：

3 rows found

4. 错误的可变IN值绑定尝试 #

尝试用sqlite3_bind_text绑定包含多个值的字符串，结果返回0行：

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";

输出：

0 rows found

完整测试代码：

#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";
}

问题原因 #

SQLite的参数绑定会将整个绑定的字符串视为单一值，不会解析其中的逗号分隔符。上述错误代码中，likeid IN (?2)实际是在匹配likeid等于字符串"1,2"，而非匹配1或2，因此没有符合条件的行返回。

解决方案 #

方案1：动态生成占位符并逐个绑定（推荐，避免SQL注入） #

根据IN值的数量动态生成对应的占位符（如?2,?3），然后逐个绑定每个值，既能支持可变数量的IN值，又能避免SQL注入风险。

示例代码：

int owner = 1;
std::vector<int> like_ids = {1, 2};

// 动态构建SQL语句，生成对应数量的占位符
std::string sql = "SELECT userid,likeid FROM like WHERE userid != ?1 AND likeid IN (";
for (size_t i = 0; i < like_ids.size(); ++i) {
    if (i > 0) sql += ",";
    sql += "?" + std::to_string(i + 2); // 占位符从?2开始
}
sql += ");";

sqlite3_stmt *match3;
rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &match3, 0);
if (rc != SQLITE_OK) {
    std::cout << "Prepare failed: " << sqlite3_errmsg(db) << "\n";
    return -1;
}

// 绑定第一个参数
rc = sqlite3_bind_int(match3, 1, owner);
// 逐个绑定IN值
for (size_t i = 0; i < like_ids.size(); ++i) {
    rc = sqlite3_bind_int(match3, i + 2, like_ids[i]);
}

found = 0;
while ((rc = sqlite3_step(match3)) == SQLITE_ROW) {
    found++;
}
sqlite3_reset(match3);
std::cout << found << " rows found\n";

方案2：拼接SQL字符串（仅适用于输入可控场景） #

直接将IN值拼接进SQL字符串中，这种方式简单但存在SQL注入风险，仅当输入值是可信的（如内部生成的数值）时使用。

示例代码：

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";

内容的提问来源于stack exchange，提问作者ravenspoint