mysql - 使用 C API 的 MySQL 查询中输入参数的语法

Question

这正是我的问题，但在使用 MySQL 的 C API 时: Syntax for input parameters in a MySQL query

如何参数化 C 查询？

我尝试做的，虽然失败了:

int status = mysql_query(&conn, "set @id:=1; SELECT * FROM players WHERE player_id = @id;"); 

我假设 C 以其他方式处理？

错误消息基本上是说请查阅您的手册以获得正确的版本和语法。我正在使用客户端版本 6.1.10。当我不使用参数化时它执行得很好。

Answer 1

您应该使用准备好的语句。
https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

这是一个 C++ 的例子(它更短也更容易理解):
https://dev.mysql.com/doc/connector-cpp/en/connector-cpp-examples-prepared-statements.html

这是一个 160 行长的 C 示例的未经测试的简短版本，来自此处:
https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-execute.html

/* A SINGLE STATEMENT WITHOUT TRAILING SEMICOLON OR '\g' */
#define SELECT_QUERY "SELECT * FROM players WHERE palyer_id = ?"
#define SELECT_QSLEN strlen (SELECT_QUERY)

enum select_params
{
    par_player_id,
    par_count
};

/* WILL HOLD A POINTER TO THE STATEMENT STRUCTURE */
MYSQL_STMT * stmt

/* HOLDS THE DESCRIPTION OF QUERY PARAMETERS.
   MEMBER buffer IS A POINTER TO THE ACTUAL DATA */
MYSQL_BIND bind [par_count] = {0};

/* HOLDS THE VALUE FOR player_id.
   MAY STAY UNINITIALIZED UNTIL QUERY EXECUTION */
int data_player_id;


stmt = mysql_stmt_init (mysql);
if (NULL == stmt)
    exit (EXIT_FAILURE); /* OUT OF MEMORY */


{
    bool prepare_fail = mysql_stmt_prepare (stmt, SELECT_QUERY, SELECT_QSLEN);
    if (prepare_fail)
        exit (EXIT_FAILURE) /* SEE mysql_stmt_error(stmt) */
}


bind [par_player_id].buffer_type    = MYSQL_TYPE_LONG;
bind [par_player_id].buffer         = (char *) & data_player_id;


{
    bool bind_failed = mysql_stmt_bind_param (stmt, bind);
    if (bind_failed)
        exit (EXIT_FAILURE); /* SEE mysql_stmt_error(stmt) */
}


/* SHOULD BE INITIALIZED BEFORE mysql_stmt_execute */
data_player_id = 42;


{
    bool execute_fail = mysql_stmt_execute (stmt);
    if (execute_fail)
        exit (EXIT_FAILURE) /* SEE mysql_stmt_error(stmt) */
}


{
    bool close_fail = mysql_stmt_close (stmt);
    if (close_fail)

        /* AT THIS POINT stmt IS INVALID.
           USE mysql_error(mysql) RATHER THAN mysql_stmt_error(stmt) */

        exit (EXIT_FAILURE);
}

---

更新

先前的回答已被删除。作为Cheatah said in the comments ，这是不安全的。