我创建了一个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);然后从命令行工具运行SQL select
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一切都按预期工作。
现在我使用CAPI运行相同的select命令
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但我真正想做的是运行查询，寻找可变数量的匹配喜欢。所以我试着像这样使用sqlite3_bind_text
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我使用调试器检查rc是否总是返回为0（没有错误）（在真实代码中，rc是被检查的，但是我在这个测试/演示中删除了可读性检查。）
下面是演示应用程序的完整代码
#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";
}如何运行SELECT... IN（）查询是否包含可变数量的IN值？
解决方案，遵循@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";
1条答案
按热度按时间ecfdbz9o1#
bind可以用来绑定单个值。你不能在一个绑定命令中绑定多个参数。
绑定将创建以下查询：（在IN条件内使用单个参数）
你可以将它附加到查询字符串中（或者使用
sprintf...），但是你必须在一个bind方法中绑定每个参数。你不能在一个准备好的语句中创建一个具有不同元素计数的列表。您可以像这样创建查询：（不绑定，或仅绑定
userid）绑定必须防止SQL注入。如果你在参数中绑定了带有恶意代码的东西怎么办？例如：而不是
"1,2"，我将使用"1,2); delete from like where userid not in (-1"，它允许受限用户删除整个数据库。当它只有一个参数时，你不能注入它。