問題描述
我想進行這樣的查詢:
SELECT lyrics FROM cache WHERE author=%s0, title=%s1 LIMIT 1;
其中字符串 %s0 和 %s1 應該被替換.假設字符串未凈化，UTF-8編碼(作為數據庫本身)，簡單的以空字符結尾的char*數組.我有什么選擇來做到這一點?SQLite (C API) 中是否有為此提供任何內置函數?
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?
推薦答案
像注釋中提到的那樣，應該使用已經準備好的語句.
Like mentioned in comments already prepared statements should be used.
為什么應該支持準備好的報表
當您自己將 SQL 查詢創建為字符串時，它們幾乎總是包含用戶輸入的一部分.攻擊者可以利用這一點，例如，使用 ' 巧妙地更改查詢的語義，從而獲得對數據的未授權訪問或破壞數據.
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 注入，是最重要的安全風險之一，請參見此處: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
防御
使用帶有變量綁定(又名參數化查詢)的準備好的語句是所有開發人員應該首先學習如何編寫數據庫查詢的方式.
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 中使用準備好的語句
有關準備好的語句，請參閱 https://www.sqlite.org/c3ref/stmt.html.
For prepared statements see https://www.sqlite.org/c3ref/stmt.html.
基本步驟是:
- 創建準備好的語句
- 將值綁定到參數
- 運行 SQL
- 銷毀對象以避免資源泄漏
示例
#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;
}
構建
使用 CMake，它可能看起來像這樣:
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)
在命令行上可以使用以下內容構建:
On command line one could build with something like:
gcc -Wall -Wextra main.c -lsqlite3 -o sqlitequery
這篇關于如何在 C 中正確地進行 SQLite SELECT 查詢?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！