CODEWITHSUNDEEP
mysqlc
Phil
Phil

Reputation: 147

Syntax for input parameters in a MySQL query using the C API

This is exactly my question but in using the C API for MySQL: Syntax for input parameters in a MySQL query

How do I parameterize a C query?

What I attempted in doing, though it fails: 

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

I'm assuming C does it some other way?

The error message basically says consult your manual for the right version and syntax. I'm using client version 6.1.10. It executes fine when I don't use parameterization.

Upvotes: 1

Views: 1291

Answers (1)

user5368518
user5368518

Reputation:

You should use Prepared statements.
https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

Here is an example in C++ (it is much shorter and easier to understand):
https://dev.mysql.com/doc/connector-cpp/en/connector-cpp-examples-prepared-statements.html

This is a not tested short version of a 160 lines long example in C from here:
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);
}

Update

Previous answer was removed. As Cheatah said in the comments, it was unsafe.

Upvotes: 2

Related Questions