如何在 C 中正确地进行 SQLite SELECT 查询?

问题描述

我想进行这样的查询:

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 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！