C - 如何使用变量作为 SQL 查询的一部分? [英] C - How to use variables as part of an SQL Query?
问题描述
简介
我正在尝试使用 C 将变量合并到查询中.我正在使用 sqlite tutorialspoint ,以及我第一次接触使用 SQL.本教程向我展示了如何使用查询,例如:
I'm attempting to incorporate variables into queries using C. I'm following this tutorial using sqlite tutorialspoint , and my first exposure to using SQL. The tutorial has shown me how to use Queries such as these:
查询
sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
"SELECT * from COMPANY";
*那么我将如何将变量合并到此语句中,例如,如果我想用分配给ID"的变量替换 1.
*So how would i go about incorporating variables into this statement, for example if i wanted to replace 1 with a variable assigned to 'ID'.
例如(我失败的尝试)
sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=" + variable + ";" \
"SELECT * from COMPANY";
我在谷歌上搜索过,但是我真的找不到任何关于使用 C 语言语法在 sql 查询中使用变量的材料.我将如何以正确和安全的方式处理此问题,以合并变量而不使程序容易受到 SQL 注入的影响?
I've googling around however I couldn't really find any material on using variables in sql queries using the C language syntax. How would i go about this in the correct and safe way, to incorporate variables and without making a program vulnereable to SQL injection?
推荐答案
C-API 提供了sqlite3_prepare_v2
和 sqlite3_bind
函数,以便您可以绑定参数到准备语句.这意味着,您可以使用占位符来替换字符串中的参数.
The C-API provides the functions sqlite3_prepare_v2
and sqlite3_bind
so that you can bind parameters to prepared statements. What that means is, you can use a placeholder where you want to substitute parameters within a string.
每个占位符都由一个索引引用,因此您可以使用任意数量的参数(最多可达 SQLITE_MAX_VARIABLE_NUMBER 设置的编译时限制).然后将参数绑定到指定索引处的占位符.
Each placeholder is referenced by an index, so you can use as many parameters as you like (up to the compile-time limit set by SQLITE_MAX_VARIABLE_NUMBER). You then bind a parameter to the placeholder at a specified index.
有许多函数和方法可以完成参数替换,但为了让您入门,这里有一个将整数绑定到 sql 语句中的第一个占位符的示例:
There are a number of functions and methods to accomplish parameter substitution, but to get you started, here's an example which binds an integer to the 1st placeholder in an sql statement:
int rc;
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
...
// here I assume you open the db, and provide any other working code as needed...
...
// the employee id number.
int id_num;
...
// create the sql statement, with a single placeholder marked by '?'.
char *sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=?";
// prepare the sql statement.
rc = sqlite3_prepare_v2(db, sql, strlen(sql)+1, &stmt, NULL);
if (rc != SQLITE_OK) {
printf("Failed to prepare statement: %s\n\r", sqlite3_errstr(rc));
sqlite3_close(db);
return 1;
}
else {
printf("SQL statement prepared: OK\n\n\r");
}
// bind an integer to the parameter placeholder.
rc = sqlite3_bind_int(stmt, 1, id_num);
if (rc != SQLITE_OK) {
printf("Failed to bind parameter: %s\n\r", sqlite3_errstr(rc));
sqlite3_close(db);
return 1;
}
else {
printf("SQL bind integer param: OK\n\n\r");
}
// evaluate the prepared statement.
rc = sqlite3_step(stmt);
// other successful return codes are possible...
if (rc != SQLITE_DONE) {
printf("Failed to execute statement: %s\n\r", sqlite3_errstr(rc));
sqlite3_close(db);
return 1;
}
// deallocate/finalize the prepared statement when you no longer need it.
// you may also place this in any error handling sections.
sqlite3_finalize(stmt);
...
// close the db when finished.
sqlite3_close(db)
...
// finish your code.
这篇关于C - 如何使用变量作为 SQL 查询的一部分?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!