Description:
This happens when the C API for prepared statements is used.
A sample C source has been included.
LIKE ... ESCAPE gives parse error (error 1064) on ESCAPE ?
The parser does not accept a parameter bind after the ESCAPE keyword.
The query: select str from T where str like ? escape ?
gives:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
The query: select str from T where str like ? escape '?'
parses, but then the literal '?' is taken as escape character instead of a parameter.
How to repeat:
#include <stdlib.h>
#include <mysql.h>
typedef enum {
BUG_UNKNOWN = -1, /* Error occurred */
BUG_SOLVED = 0,
BUG_PRESENT = 1,
} BugStatus;
char * str_bug_stat( BugStatus bugstat )
{
if ( bugstat == BUG_PRESENT ) {
return "Bug still present";
} else if ( bugstat == BUG_SOLVED ) {
return "Bug solved";
}
return "Status UNKNOWN (or error occurred)";
}
BugStatus do_the_bug_test( MYSQL * mysql, char ** desc )
{
MYSQL_STMT * stmt;
char * query;
*desc =
"MySQL-#.... Baan-040618 LIKE ... ESCAPE gives parse error on ESCAPE ? \n"
" ok: select str from T where str like ? escape '?' \n"
" but that is not a parameter but the literal question mark \n"
" err: select str from T where str like ? escape ? \n";
if ( !(stmt = mysql_stmt_init(mysql) ) ) {
return BUG_UNKNOWN;
}
query = "select name from mysql.help_topic where name like ? escape '?'";
if (mysql_stmt_prepare(stmt, query, strlen(query))) {
printf( "Prepare ERR: %s\n", query );
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
printf( "Prepare OK : %s\n", query );
query = "select name from mysql.help_topic where name like ? escape ?";
if (mysql_stmt_prepare(stmt, query, strlen(query))) {
printf( "Prepare ERR: %s\n", query );
mysql_stmt_close(stmt);
return BUG_PRESENT;
}
printf( "Prepare OK : %s\n", query );
mysql_stmt_close(stmt);
return BUG_SOLVED;
}
main()
{
MYSQL * mysql = mysql_init( NULL );
BugStatus bugstat;
char * desc;
/* Bind data */
if ( ! mysql_real_connect( mysql,
/* host */ NULL,
/* user */ User,
/* pwd */ Passwd,
/* dbase */ Dbase,
/* uint port */ 0,
/* char * socket */ NULL,
/* client_flag */ 0 ) )
{
fprintf(stderr, " mysql_real_connec() failed\n");
exit(0);
}
bugstat = do_the_bug_test( mysql, &desc );
fprintf(stdout, "%s\n%s\n\n", desc, str_bug_stat( bugstat ) );
return 0;
}
Description: This happens when the C API for prepared statements is used. A sample C source has been included. LIKE ... ESCAPE gives parse error (error 1064) on ESCAPE ? The parser does not accept a parameter bind after the ESCAPE keyword. The query: select str from T where str like ? escape ? gives: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 The query: select str from T where str like ? escape '?' parses, but then the literal '?' is taken as escape character instead of a parameter. How to repeat: #include <stdlib.h> #include <mysql.h> typedef enum { BUG_UNKNOWN = -1, /* Error occurred */ BUG_SOLVED = 0, BUG_PRESENT = 1, } BugStatus; char * str_bug_stat( BugStatus bugstat ) { if ( bugstat == BUG_PRESENT ) { return "Bug still present"; } else if ( bugstat == BUG_SOLVED ) { return "Bug solved"; } return "Status UNKNOWN (or error occurred)"; } BugStatus do_the_bug_test( MYSQL * mysql, char ** desc ) { MYSQL_STMT * stmt; char * query; *desc = "MySQL-#.... Baan-040618 LIKE ... ESCAPE gives parse error on ESCAPE ? \n" " ok: select str from T where str like ? escape '?' \n" " but that is not a parameter but the literal question mark \n" " err: select str from T where str like ? escape ? \n"; if ( !(stmt = mysql_stmt_init(mysql) ) ) { return BUG_UNKNOWN; } query = "select name from mysql.help_topic where name like ? escape '?'"; if (mysql_stmt_prepare(stmt, query, strlen(query))) { printf( "Prepare ERR: %s\n", query ); mysql_stmt_close(stmt); return BUG_UNKNOWN; } printf( "Prepare OK : %s\n", query ); query = "select name from mysql.help_topic where name like ? escape ?"; if (mysql_stmt_prepare(stmt, query, strlen(query))) { printf( "Prepare ERR: %s\n", query ); mysql_stmt_close(stmt); return BUG_PRESENT; } printf( "Prepare OK : %s\n", query ); mysql_stmt_close(stmt); return BUG_SOLVED; } main() { MYSQL * mysql = mysql_init( NULL ); BugStatus bugstat; char * desc; /* Bind data */ if ( ! mysql_real_connect( mysql, /* host */ NULL, /* user */ User, /* pwd */ Passwd, /* dbase */ Dbase, /* uint port */ 0, /* char * socket */ NULL, /* client_flag */ 0 ) ) { fprintf(stderr, " mysql_real_connec() failed\n"); exit(0); } bugstat = do_the_bug_test( mysql, &desc ); fprintf(stdout, "%s\n%s\n\n", desc, str_bug_stat( bugstat ) ); return 0; }