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.
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);
}
Previous answer was removed. As Cheatah said in the comments, it was unsafe.
