問題描述
我想進(jìn)行這樣的查詢:
SELECT lyrics FROM cache WHERE author=%s0, title=%s1 LIMIT 1;
其中字符串 %s0 和 %s1 應(yīng)該被替換.假設(shè)字符串未凈化，UTF-8編碼(作為數(shù)據(jù)庫(kù)本身)，簡(jiǎn)單的以空字符結(jié)尾的char*數(shù)組.我有什么選擇來(lái)做到這一點(diǎn)?SQLite (C API) 中是否有為此提供任何內(nèi)置函數(shù)?
where strings %s0 and %s1 should be substituted. Assuming strings are not sanitized, UTF-8 encoded (As database itself), simple null-terminated char* arrays. What are my options to do this? Are there any built-in functions in SQLite (C API) for this?
推薦答案
像注釋中提到的那樣，應(yīng)該使用已經(jīng)準(zhǔn)備好的語(yǔ)句.
Like mentioned in comments already prepared statements should be used.
為什么應(yīng)該支持準(zhǔn)備好的報(bào)表
當(dāng)您自己將 SQL 查詢創(chuàng)建為字符串時(shí)，它們幾乎總是包含用戶輸入的一部分.攻擊者可以利用這一點(diǎn)，例如，使用 ' 巧妙地更改查詢的語(yǔ)義，從而獲得對(duì)數(shù)據(jù)的未授權(quán)訪問或破壞數(shù)據(jù).
When you create SQL queries yourself as a string, they almost always contain parts of a user's input. An attacker can take advantage of this by, for example, cleverly changing the semantics of the query using ' and thus gaining unauthorized access to data or destroying data.
這稱為 SQL 注入，是最重要的安全風(fēng)險(xiǎn)之一，請(qǐng)參見此處:https://www.owasp.org/images/7/72/OWASP_Top_10-2017_%28en%29.pdf.pdf
This is called SQL injection and is one of the top most critical security risks, see here: https://www.owasp.org/images/7/72/OWASP_Top_10-2017_%28en%29.pdf.pdf
防御
使用帶有變量綁定(又名參數(shù)化查詢)的準(zhǔn)備好的語(yǔ)句是所有開發(fā)人員應(yīng)該首先學(xué)習(xí)如何編寫數(shù)據(jù)庫(kù)查詢的方式.
The use of prepared statements with variable binding (aka parameterized queries) is how all developers should first be taught how to write database queries.
https:///cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html#defense-option-1-prepared-statements-with-parameterized-queries
如何在 SQLite 中使用準(zhǔn)備好的語(yǔ)句
有關(guān)準(zhǔn)備好的語(yǔ)句，請(qǐng)參閱 https://www.sqlite.org/c3ref/stmt.html.
For prepared statements see https://www.sqlite.org/c3ref/stmt.html.
基本步驟是:
- 創(chuàng)建準(zhǔn)備好的語(yǔ)句
- 將值綁定到參數(shù)
- 運(yùn)行 SQL
- 銷毀對(duì)象以避免資源泄漏
示例
#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
void exit_with_error(sqlite3 *db, const char * msg) {
fprintf(stderr, "%s: %s\n", msg, sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
int rc = sqlite3_open("path-to-lyrics", &db);
if (rc != SQLITE_OK)
exit_with_error(db, "can't open db: ");
//create prepared statement
rc = sqlite3_prepare_v2(db, "SELECT lyrics FROM cache WHERE author=?1 AND title=?2 LIMIT 1;", -1, &stmt, 0);
if (rc != SQLITE_OK)
exit_with_error(db, "failure fetching data: ");
//bind values to parameters
sqlite3_bind_text(stmt, 1, "Don Brownrigg", -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, "Just Breathe", -1, SQLITE_STATIC);
//run the SQL
rc = sqlite3_step(stmt);
if (rc == SQLITE_ROW) {
printf("%s\n", sqlite3_column_text(stmt, 0));
}
//destroy the object to avoid resource leaks
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
構(gòu)建
使用 CMake，它可能看起來(lái)像這樣:
With CMake it could look like this:
cmake_minimum_required(VERSION 3.14)
project(sqlitequery C)
set(CMAKE_C_STANDARD 99)
add_executable(sqlitequery main.c)
target_link_libraries (sqlitequery sqlite3)
在命令行上可以使用以下內(nèi)容構(gòu)建:
On command line one could build with something like:
gcc -Wall -Wextra main.c -lsqlite3 -o sqlitequery
這篇關(guān)于如何在 C 中正確地進(jìn)行 SQLite SELECT 查詢?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！