C语言调用SQLite执行SELECT查询sqlite3_step返回无信息未知错误

你的代码存在多个直接触发错误、逻辑异常的硬伤，按影响优先级排序：

• 参数绑定索引起始值错误：SQLite中预编译语句的占位符?索引从1开始计数，不是0。你当前代码从索引0开始绑定参数，导致所有参数错位，最后1个占位符完全没有绑定有效值，执行时直接触发错误。经统计你的SQL语句总共有20个?占位符，正确的绑定索引范围是1_20，不是019。
• step返回值判断逻辑错误：sqlite3_step()执行SELECT语句时，正常返回值是SQLITE_ROW（查询到结果行）或SQLITE_DONE（结果集遍历完成），SQLITE_OK根本不是SELECT执行成功的返回码。你当前的判断逻辑会把所有正常执行的查询都判定为出错。
• 错误日志打印语法错误：你的printf调用格式字符串没有加双引号，属于C语法错误，这行代码本身就无法正常编译运行，自然打不出正确的错误信息，才会看到"无任何附加信息的未知错误"。
• LIKE通配符参数未实际使用：你编写代码拼接了带%前后缀的LIKEsearchWord1、LIKEsearchWord2，但绑定参数时传的是原始无通配符的searchWord1、searchWord2，属于逻辑错误，就算语句能跑，LIKE匹配结果也完全不对。
• （潜在风险）缓冲区溢出：你定义的搜索词缓冲区只有52字节，拼接前后缀后留给原始搜索词的长度只有50字节，如果传入的searchWord1/searchWord2长度超过50字节，会直接越界写内存，触发随机的未定义错误。
• （次要排查点）自定义排序规则缺失：如果你的C程序链接的是精简版SQLite，没有内置/加载KOREA排序规则，执行到ORDER BY的collate子句时会直接报错，DB Browser能正常运行是因为它自带的SQLite版本默认加载了该排序规则。

修正后的核心代码片段 #

int result = sqlite3_prepare_v2(db,
                                    "SELECT _id, name1, name2, name3, num1, type1, num2, typ2, " \
                                    "num3, typ3, num4, type4, num5, type5, " \
                                    "instr(name1||name2||name3, ?), " \
                                    "instr(num1, ?), instr(num2, ?), instr(num3, ?), " \
                                    "instr(num4, ?), instr(num5, ?), " \
                                    "unicode(substr(name1|| name2|| name3, 1, 1)) " \
                                    "FROM table1 " \
                                    "WHERE (name1||name2||name3 LIKE ? " \
                                    "OR name1||name3||name3 LIKE ? " \
                                    "OR REPLACE(name1||name2||name3, ',', '') LIKE ? " \
                                    "OR REPLACE(name1||name2||name3, ',', '') LIKE ? " \
                                    "OR num1 LIKE ? OR name2 LIKE ? " \
                                    "OR num3 LIKE ? OR num4 LIKE ? OR num5 LIKE ?) " \
                                    "ORDER BY CASE " \
                                    "WHEN ((instr(name1, ?) IS 1) AND name3 != '') THEN 1 " \
                                    "WHEN ((instr(name3, ?) IS 1) AND name1 = '') THEN 2 " \
                                    "WHEN ((instr(name1, ?) IS 1) AND name3 = '') THEN 2 " \
                                    "WHEN (instr(name1||' '||name2||' '||name3, ?) IS 1) THEN 3 " \
                                    "WHEN (instr(name1||', '||name2||', '||name3, ?) IS 1) THEN 3 " \
                                    "ELSE 4 END, UPPER(name1||name2||name3) LIMIT 1000 " \
                                    "collate KOREA",
                                    -1,
                                    &main_stmt,
                                    0
                                    );
// 缓冲区留足冗余长度，避免溢出
char LIKEsearchWord1[256];
char LIKEsearchWord2[256];

memset(LIKEsearchWord1, '\0', sizeof(LIKEsearchWord1));
memset(LIKEsearchWord2, '\0', sizeof(LIKEsearchWord2));

// 安全拼接通配符，避免溢出
snprintf(LIKEsearchWord1, sizeof(LIKEsearchWord1), "%%%s%%", searchWord1);
snprintf(LIKEsearchWord2, sizeof(LIKEsearchWord2), "%%%s%%", searchWord2);

if(result == SQLITE_OK)
{
    int param_idx = 1; // 索引起始值固定为1
    // 绑定SELECT部分instr函数的参数
    sqlite3_bind_text(main_stmt, param_idx++, searchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord2, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord2, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord2, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord2, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord2, -1, SQLITE_STATIC);
    // 绑定WHERE部分LIKE的参数，传入拼好通配符的变量
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord2, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord2, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord2, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord2, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, LIKEsearchWord2, -1, SQLITE_STATIC);
    // 绑定ORDER BY CASE部分的参数
    sqlite3_bind_text(main_stmt, param_idx++, searchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord1, -1, SQLITE_STATIC);
    sqlite3_bind_text(main_stmt, param_idx++, searchWord1, -1, SQLITE_STATIC);

    result = sqlite3_step(main_stmt);
    // 修正返回值判断逻辑
    if(result != SQLITE_ROW && result != SQLITE_DONE)
    {
        // 修正printf语法
        printf("SQL error: %s\n", sqlite3_errmsg(db));
    }
    // 后续遍历结果集逻辑...
    sqlite3_finalize(main_stmt);
}

注意：如果修正后还是报排序规则相关错误，要么去掉collate KOREA子句用默认排序，要么在你的C程序里手动注册KOREA排序规则到SQLite连接。

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