为何SQLite3库操作sqlite3_stmt时抛出异常？

问题背景 #

我封装了SqliteDatabaseManager类统一处理数据库交互，采用PImpl模式实现具体逻辑，其中SqliteQueryResult类负责封装sqlite3_stmt的操作。执行SELECT查询语句时，程序随机抛出异常，异常触发位置有时在executeQuery方法的返回处，有时在SqliteQueryResult的next方法调用sqlite3_step时。已通过SQLiteStudio验证查询语句正确性与数据库有效性，但问题仍未解决。

接口定义 #

#pragma once

#include <string>
#include <string_view>
#include <vector>
#include <memory>

class IQueryResult
{
public:
    virtual ~IQueryResult() = default;

    virtual bool next() = 0;
    virtual std::string getString(int columnIndex) const = 0;
};

/**
 * \brief 数据库接口（支持SQLite/PostgreSQL等）
 *
 * 使用示例:
 *  executeQuery("INSERT INTO Users (username, password) VALUES (?, ?);", {"username1", "password1"});
 */
class IDatabaseManager
{
public:
    virtual ~IDatabaseManager() = default;

    virtual bool open() = 0;
    virtual void close() = 0;
    virtual std::shared_ptr<IQueryResult> executeQuery(std::string_view query, const std::vector<std::string> &params) = 0;
};

SqliteDatabaseManager实现 #

#include <iostream>
#include "SqliteDatabaseManager.hpp"

#include <filesystem>
#include <fstream>
#include <memory>
#include <sstream>
#include <vector>
#include <string_view>

#include <sqlite3.h>

#define _M "SqliteDatabaseManager"

class SqliteQueryResult : public IQueryResult
{
    std::unique_ptr<sqlite3_stmt, decltype(&sqlite3_finalize)> stmt;

public:
    SqliteQueryResult(sqlite3_stmt *stmtPtr)
        : stmt(stmtPtr, sqlite3_finalize) {}

    bool next() override
    {
        int stepResult = sqlite3_step(stmt.get());
        if (stepResult == SQLITE_ROW)
        {
            return true;
        }
        else if (stepResult == SQLITE_DONE)
        {
            return false;
        }
        else
        {
            const std::string errorMsg = "SQL错误: " + std::string(sqlite3_errmsg(sqlite3_db_handle(stmt.get())));
            std::cerr << errorMsg << std::endl;
            return false;
        }
    }

    std::string getString(int columnIndex) const override
    {
        auto text = sqlite3_column_text(stmt.get(), columnIndex);
        return text ? reinterpret_cast<const char *>(text) : "";
    }
};

class SqliteDatabaseManager::Impl
{
public:
    std::string m_dbPath, m_createDatabaseScriptPath;
    std::unique_ptr<sqlite3, decltype(&sqlite3_close)> m_db{nullptr, sqlite3_close};

    Impl(std::string_view path, std::string_view createDatabaseScriptPath)
        : m_dbPath(path),
          m_createDatabaseScriptPath(createDatabaseScriptPath) {}

    bool open()
    {
        const auto fileExists = std::filesystem::exists(m_dbPath); // 使用C++17 std::filesystem检查文件是否存在

        sqlite3 *dbRaw = nullptr;
        if (sqlite3_open(m_dbPath.c_str(), &dbRaw) != SQLITE_OK)
        {
            std::cerr << "打开数据库失败: " << sqlite3_errmsg(dbRaw) << std::endl;
            if (dbRaw)
                sqlite3_close(dbRaw);
            return false;
        }

        m_db.reset(dbRaw);

        if (!fileExists && !initializeDatabaseSchema())
        {
            std::cerr << "初始化数据库 schema 失败。" << std::endl;
            return false;
        }

        std::cout << "数据库打开成功" << std::endl;
        return true;
    }

    void close()
    {
        m_db.reset();
        std::cout << "数据库关闭成功" << std::endl;
    }

    std::shared_ptr<IQueryResult> executeQuery(std::string_view query, const std::vector<std::string> &params)
    {
        std::cerr << "SQLiteDatabaseManager::executeQuery" << std::endl;
        std::cerr << "SQLiteDatabaseManager::executeQuery | 查询语句 = \"" << query << "\"" << std::endl;

        sqlite3_stmt *stmtRaw;
        if (sqlite3_prepare_v2(m_db.get(), query.data(), -1, &stmtRaw, nullptr) != SQLITE_OK)
        {
            const std::string errorMsg = "预处理语句失败: " + std::string(sqlite3_errmsg(m_db.get()));
            std::cerr << errorMsg << std::endl;
        }

        std::unique_ptr<sqlite3_stmt, decltype(&sqlite3_finalize)> stmtGuard(stmtRaw, sqlite3_finalize);

        // std::string debugQuery = query.data();
        for (size_t i = 0; i < params.size(); ++i)
        {
            if (sqlite3_bind_text(stmtRaw, static_cast<int>(i + 1), params[i].c_str(), -1, SQLITE_TRANSIENT) != SQLITE_OK)
            {
                const std::string errorMsg = "绑定第 " + std::to_string(i + 1) + " 个参数失败: " + sqlite3_errmsg(m_db.get());
                std::cerr << errorMsg << std::endl;
            }

            // 调试用：拼接最终查询语句
            // auto pos = debugQuery.find('?');
            // if (pos != std::string::npos)
            // {
            //     debugQuery.replace(pos, 1, "'" + params[i] + "'");
            // }
        }

        // std::cerr << "SQLiteDatabaseManager::executeQuery | 最终查询语句 = \"" << debugQuery << "\"" << std::endl;

        return std::make_shared<SqliteQueryResult>(stmtRaw);
    }

    bool initializeDatabaseSchema()
    {
        std::cerr << "SQLiteDatabaseManager::initializeDatabaseSchema" << std::endl;
        std::cerr << "初始化数据库 schema | 使用脚本: " << m_createDatabaseScriptPath << std::endl;

        if (!std::filesystem::exists(m_createDatabaseScriptPath))
        {
            std::cerr << "无法打开SQL脚本文件: " << m_createDatabaseScriptPath << std::endl;
            return false;
        }

        std::ifstream sqlFile(m_createDatabaseScriptPath);
        std::string sqlCommands((std::istreambuf_iterator<char>(sqlFile)),
                                std::istreambuf_iterator<char>());

        char *errMsg = nullptr;
        if (sqlite3_exec(m_db.get(), sqlCommands.c_str(), nullptr, nullptr, &errMsg) != SQLITE_OK)
        {
            std::cerr << "初始化数据库 schema 错误: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }

        std::cout << "数据库 schema 初始化成功" << std::endl;
        return true;
    }
};

SqliteDatabaseManager::SqliteDatabaseManager(std::string_view dbPath, std::string_view createDatabaseScriptPath)
    : pImpl(std::make_unique<Impl>(dbPath, createDatabaseScriptPath)) {}

SqliteDatabaseManager::~SqliteDatabaseManager() = default;

bool SqliteDatabaseManager::open()
{
    return pImpl->open();
}

void SqliteDatabaseManager::close()
{
    pImpl->close();
}

std::shared_ptr<IQueryResult> SqliteDatabaseManager::executeQuery(std::string_view query, const std::vector<std::string> &params)
{
    return pImpl->executeQuery(query, params);
}

调用代码 #

#include "SqliteDatabaseManager.hpp"

int main()
{
    SqliteDatabaseManager db(DB_PATH, SQL_FILE);
    db.open();
    db.executeQuery("SELECT id, username, password, roleId FROM Users WHERE username = ?;", {"admin"});
    db.close();
    return 0;
}

运行日志 #

数据库打开成功
SQLiteDatabaseManager::executeQuery
SQLiteDatabaseManager::executeQuery | 查询语句 = "SELECT id, username, password, roleId FROM Users WHERE username = ?;"
SqliteQueryResult::SqliteQueryResult

 *  终端进程已终止

问题根源 #

代码中存在**双重释放sqlite3_stmt**的严重问题：
在executeQuery方法中，你同时用两个std::unique_ptr管理同一个sqlite3_stmt指针：

1. 创建了stmtGuard来持有stmtRaw，该指针会在函数结束时自动调用sqlite3_finalize释放资源
2. 又将同一个stmtRaw传递给SqliteQueryResult的构造函数，后者内部也通过unique_ptr接管该指针

两个unique_ptr都会在各自生命周期结束时释放同一个sqlite3_stmt，导致随机崩溃：

• 若stmtGuard先析构，SqliteQueryResult中的stmt会变成野指针，后续调用sqlite3_step时触发崩溃
• 若SqliteQueryResult对象先被使用，后续stmtGuard析构时会释放已经被释放的资源，同样触发崩溃

修复方案 #

1. 移除冗余的stmtGuard #

删除executeQuery中的stmtGuard定义，让SqliteQueryResult的unique_ptr单独接管sqlite3_stmt的生命周期，避免双重释放。

2. 完善错误处理逻辑 #

• 当sqlite3_prepare_v2失败时，直接返回空的shared_ptr，避免返回无效的SqliteQueryResult
• 当参数绑定失败时，手动调用sqlite3_finalize释放已经预处理的stmt，避免内存泄漏

修正后的executeQuery方法 #

std::shared_ptr<IQueryResult> executeQuery(std::string_view query, const std::vector<std::string> &params)
{
    std::cerr << "SQLiteDatabaseManager::executeQuery" << std::endl;
    std::cerr << "SQLiteDatabaseManager::executeQuery | 查询语句 = \"" << query << "\"" << std::endl;

    sqlite3_stmt *stmtRaw = nullptr;
    if (sqlite3_prepare_v2(m_db.get(), query.data(), -1, &stmtRaw, nullptr) != SQLITE_OK)
    {
        const std::string errorMsg = "预处理语句失败: " + std::string(sqlite3_errmsg(m_db.get()));
        std::cerr << errorMsg << std::endl;
        // 预处理失败，返回空指针
        return nullptr;
    }

    // 移除stmtGuard，由SqliteQueryResult接管stmtRaw
    for (size_t i = 0; i < params.size(); ++i)
    {
        if (sqlite3_bind_text(stmtRaw, static_cast<int>(i + 1), params[i].c_str(), -1, SQLITE_TRANSIENT) != SQLITE_OK)
        {
            const std::string errorMsg = "绑定第 " + std::to_string(i + 1) + " 个参数失败: " + sqlite3_errmsg(m_db.get());
            std::cerr << errorMsg << std::endl;
            // 绑定失败，手动释放stmt
            sqlite3_finalize(stmtRaw);
            return nullptr;
        }
    }

    return std::make_shared<SqliteQueryResult>(stmtRaw);
}

验证修复 #

修复后，sqlite3_stmt的生命周期仅由SqliteQueryResult中的unique_ptr管理，不会再出现双重释放问题，随机崩溃现象会消失。同时调用端需要注意检查executeQuery返回的指针是否为空，避免空指针访问。

内容的提问来源于stack exchange，提问作者SageCat