英文:
sqlite3_bind_text for variable number of values
问题
以下是代码的中文翻译部分：
// 创建一个SQLite数据库sqlite3 *db;char *dbErrMsg;int rc = sqlite3_open("test.dat", &db);if (rc){std::cout << "无法打开数据库\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查询C:\Users\James\code\sharedLikes\bin>sqlite3 test.datSQLite 版本 3.36.0 2021-06-18 18:36:39输入 ".help" 获取使用提示。sqlite> SELECT userid,likeid FROM like WHERE userid != 1 AND likeid IN (1,2);2|23|14|1
输出如预期：
3 行记录
接下来，我使用C API运行相同的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 << " 行记录\n";
输出也是正确的：
3 行记录
但是，我真正想要做的是运行查询以查找不定数量的匹配项。所以，我尝试使用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 << " 行记录\n";
输出显示未找到任何行：
0 行记录
我使用调试器检查了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 << "无法打开数据库\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 << " 行记录\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 << " 行记录\n";}
如何运行具有可变数量IN值的SELECT查询？
解决方案，根据@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 << " 行记录\n";
这将允许您运行具有可变数量IN值的SELECT查询。
英文:
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.datSQLite version 3.36.0 2021-06-18 18:36:39Enter ".help" for usage hints.sqlite> SELECT userid,likeid FROM like WHERE userid != 1 AND likeid IN (1,2);2|23|14|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";
答案1
得分: 1
bind 可以用于绑定单个值。你不能在单个 bind 命令中绑定多个参数。
你的 bind 创建了以下查询：（在 IN 条件中有一个单一参数）
SELECT userid, likeid FROM like WHERE userid != 1 AND likeid IN ( '1,2' )
你可以将其附加到查询字符串（或使用 sprintf ...），但你必须在单个 bind 方法中绑定每个参数。你不能在单个准备好的语句中创建具有不同元素数量的列表。
你可以像这样创建查询：（不绑定，或仅绑定 userid）
char sql_buffer[256];sprintf(sql_buffer, "SELECT userid, likeid FROM like WHERE userid != 1 AND likeid IN ( %s )", "1,2");
绑定必须防止 SQL 注入。如果你在参数中绑定了恶意代码会怎样？例如：而不是 "1,2"，我会放入 "1,2); delete from like where userid not in (-1"，这会允许受限制的用户删除整个数据库。当它只是一个单一参数时，你无法注入它。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论