Description:
This happens when the C API for prepared statements is used.
A sample C source has been included.
After changing the value of a parameter and executing the query again, a wrong result set is returned. This result set is identical to the one returned on the first execute.
Query: select name from mysql.help_topic where name rlike ? order by name
Prepare, bind parameter- and result-buffers
copy '^C.*' to parameter buffer
Execute
Fetch result set --> 'CACHE INDEX', 'CASE', 'CAST' . . . .
copy '^R.*' to parameter buffer
Execute
Fetch result set --> 'CACHE INDEX', 'CASE', 'CAST' . . . .
This is wrong! The result set 'RADIANS', 'RAND' . . . .
should have been returned.
Please note that a similar query with 'like' instead of 'rlike' and patterns like 'C%' instead of 'C.*' works fine.
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;
/* Bind data */
MYSQL_BIND ParamBind[1];
char ParamStrData[8];
unsigned long ParamLength;
MYSQL_BIND ResultBind[2];
unsigned long ResultLength[2];
my_bool ResultIsNull[2];
int ResultIntData;
char ResultStrData[128];
char query[256];
int rows = 0;
*desc =
"MySQL-#.... Baan-040617 RLIKE operator does not pick up changes in parameters \n"
" ok: select str from T where str like ?\n"
" bind 'A%' exec fetch bind 'B%' exec fetch \n"
" err: select str from T where str RLIKE ?\n"
" bind '^A.*' exec fetch bind '^B.*' exec fetch \n";
if ( !(stmt = mysql_stmt_init(mysql) ) ) {
return BUG_UNKNOWN;
}
sprintf( query,
"select help_topic_id, name "
"from mysql.help_topic "
"where name rlike ? "
"order by name "
"limit 0, 5 "
);
if (mysql_stmt_prepare(stmt, query, strlen(query))) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Bind the param buffers */
ParamBind[0].buffer_type= MYSQL_TYPE_STRING;
ParamBind[0].buffer= ParamStrData;
ParamBind[0].is_null= 0;
ParamBind[0].length = &ParamLength;
if (mysql_stmt_bind_param(stmt, ParamBind)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Bind the result buffers */
ResultBind[0].buffer_type= MYSQL_TYPE_LONG;
ResultBind[0].buffer= (char *)&ResultIntData;
ResultBind[0].is_null= &ResultIsNull[0];
ResultBind[0].length= &ResultLength[0];
ResultBind[1].buffer_type= MYSQL_TYPE_STRING;
ResultBind[1].buffer= (char *)&ResultStrData;
ResultBind[1].is_null= &ResultIsNull[1];
ResultBind[1].length= &ResultLength[1];
if (mysql_stmt_bind_result(stmt, ResultBind)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Set actual Param value */
strcpy(ParamStrData, "^C.*" );
ParamLength = strlen( ParamStrData );
/* Execute */
if (mysql_stmt_execute(stmt)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Fetch all rows */
while (!mysql_stmt_fetch(stmt))
{
rows++;
if (ResultIsNull[0] || ResultIsNull[1])
return BUG_UNKNOWN;
printf ( "id = %d, name = %s", ResultIntData, ResultStrData );
if ( ResultStrData[0] != ParamStrData[1] ) {
/* The data is equal after revering the byte order */
printf ( "\tUnexpected Error: fetched data does not match pattern %s\n", ParamStrData );
return BUG_UNKNOWN;
}
printf( "\n" );
}
printf ( ">> Change actual parameter value and re-execute\n" );
/* Change actual Param value */
strcpy(ParamStrData, "^R.*" );
ParamLength = strlen( ParamStrData );
/* Re-Execute with different bind value ! */
if (mysql_stmt_execute(stmt)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Fetch all rows */
while (!mysql_stmt_fetch(stmt))
{
rows++;
if (ResultIsNull[0] || ResultIsNull[0])
return BUG_UNKNOWN;
printf ( "id = %d, name = %s", ResultIntData, ResultStrData );
if ( ResultStrData[0] != ParamStrData[1] ) {
/* The data is equal after revering the byte order */
printf ( "\tError: fetched data does not match pattern %s\n", ParamStrData );
return BUG_PRESENT;
}
printf( "\n" );
}
/* Close the statement */
mysql_stmt_close(stmt);
if ( rows ) {
return BUG_SOLVED;
} else {
return BUG_UNKNOWN;
}
}
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;
}
Suggested fix:
For rlike the regular expression should be re-compiled for each execute.
Description: This happens when the C API for prepared statements is used. A sample C source has been included. After changing the value of a parameter and executing the query again, a wrong result set is returned. This result set is identical to the one returned on the first execute. Query: select name from mysql.help_topic where name rlike ? order by name Prepare, bind parameter- and result-buffers copy '^C.*' to parameter buffer Execute Fetch result set --> 'CACHE INDEX', 'CASE', 'CAST' . . . . copy '^R.*' to parameter buffer Execute Fetch result set --> 'CACHE INDEX', 'CASE', 'CAST' . . . . This is wrong! The result set 'RADIANS', 'RAND' . . . . should have been returned. Please note that a similar query with 'like' instead of 'rlike' and patterns like 'C%' instead of 'C.*' works fine. 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; /* Bind data */ MYSQL_BIND ParamBind[1]; char ParamStrData[8]; unsigned long ParamLength; MYSQL_BIND ResultBind[2]; unsigned long ResultLength[2]; my_bool ResultIsNull[2]; int ResultIntData; char ResultStrData[128]; char query[256]; int rows = 0; *desc = "MySQL-#.... Baan-040617 RLIKE operator does not pick up changes in parameters \n" " ok: select str from T where str like ?\n" " bind 'A%' exec fetch bind 'B%' exec fetch \n" " err: select str from T where str RLIKE ?\n" " bind '^A.*' exec fetch bind '^B.*' exec fetch \n"; if ( !(stmt = mysql_stmt_init(mysql) ) ) { return BUG_UNKNOWN; } sprintf( query, "select help_topic_id, name " "from mysql.help_topic " "where name rlike ? " "order by name " "limit 0, 5 " ); if (mysql_stmt_prepare(stmt, query, strlen(query))) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Bind the param buffers */ ParamBind[0].buffer_type= MYSQL_TYPE_STRING; ParamBind[0].buffer= ParamStrData; ParamBind[0].is_null= 0; ParamBind[0].length = &ParamLength; if (mysql_stmt_bind_param(stmt, ParamBind)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Bind the result buffers */ ResultBind[0].buffer_type= MYSQL_TYPE_LONG; ResultBind[0].buffer= (char *)&ResultIntData; ResultBind[0].is_null= &ResultIsNull[0]; ResultBind[0].length= &ResultLength[0]; ResultBind[1].buffer_type= MYSQL_TYPE_STRING; ResultBind[1].buffer= (char *)&ResultStrData; ResultBind[1].is_null= &ResultIsNull[1]; ResultBind[1].length= &ResultLength[1]; if (mysql_stmt_bind_result(stmt, ResultBind)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Set actual Param value */ strcpy(ParamStrData, "^C.*" ); ParamLength = strlen( ParamStrData ); /* Execute */ if (mysql_stmt_execute(stmt)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Fetch all rows */ while (!mysql_stmt_fetch(stmt)) { rows++; if (ResultIsNull[0] || ResultIsNull[1]) return BUG_UNKNOWN; printf ( "id = %d, name = %s", ResultIntData, ResultStrData ); if ( ResultStrData[0] != ParamStrData[1] ) { /* The data is equal after revering the byte order */ printf ( "\tUnexpected Error: fetched data does not match pattern %s\n", ParamStrData ); return BUG_UNKNOWN; } printf( "\n" ); } printf ( ">> Change actual parameter value and re-execute\n" ); /* Change actual Param value */ strcpy(ParamStrData, "^R.*" ); ParamLength = strlen( ParamStrData ); /* Re-Execute with different bind value ! */ if (mysql_stmt_execute(stmt)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Fetch all rows */ while (!mysql_stmt_fetch(stmt)) { rows++; if (ResultIsNull[0] || ResultIsNull[0]) return BUG_UNKNOWN; printf ( "id = %d, name = %s", ResultIntData, ResultStrData ); if ( ResultStrData[0] != ParamStrData[1] ) { /* The data is equal after revering the byte order */ printf ( "\tError: fetched data does not match pattern %s\n", ParamStrData ); return BUG_PRESENT; } printf( "\n" ); } /* Close the statement */ mysql_stmt_close(stmt); if ( rows ) { return BUG_SOLVED; } else { return BUG_UNKNOWN; } } 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; } Suggested fix: For rlike the regular expression should be re-compiled for each execute.