c - 如何在C中正确进行SQLite SELECT查询？

Question

我想进行这样的查询:

SELECT lyrics FROM cache WHERE author=%s0, title=%s1 LIMIT 1;

其中字符串 %s0 和 %s1 应该被替换。假设字符串未经过清理、UTF-8 编码(作为数据库本身)、简单的以 null 结尾的 char* 数组。我有什么选择来做到这一点？ SQLite(C API)中是否有任何内置函数可以实现此目的？

Answer 1

就像评论中提到的那样，应该使用已经准备好的语句。

为什么应优先考虑准备好的陈述

当您自己将 SQL 查询创建为字符串时，它们几乎总是包含用户输入的一部分。攻击者可以利用这一点，例如，使用 ' 巧妙地更改查询的语义，从而获得对数据的未经授权的访问或破坏数据。

这称为 SQL 注入(inject)，是最严重的安全风险之一，请参见此处: 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 。

基本步骤是:

• 创建准备好的语句
• 将值绑定(bind)到参数
• 运行 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 它可能看起来像这样:

cmake_minimum_required(VERSION 3.14)
project(sqlitequery C)

set(CMAKE_C_STANDARD 99)

add_executable(sqlitequery main.c)

target_link_libraries (sqlitequery sqlite3)

在命令行上，可以使用以下内容进行构建:

gcc -Wall -Wextra main.c -lsqlite3 -o sqlitequery