Description:
This happens when the C API for prepared statements is used.
A sample C source using the standard mysql.helptopic table has been included.
Query: select * from T where ? = ?
Both parameters are of the type MYSQL_TIME.
When the actual values of the parameters are set to a different date, the
where clause should evaluate to FALSE, and an empty result set should be returned.
This works fine, as long as no '0000-00-00' date is used. In that case the where clause evalueate to TRUE, causing the complete table to be fetched.
where ? /* 1999-01-01 */ = ? /* 2000-01-01 */ ----> FALSE
where ? /* 1999-01-01 */ = ? /* 0000-00-00 */ ----> TRUE
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;
MYSQL_BIND ParamBind[2];
unsigned long ParamLength;
my_bool ParamIsNull;
MYSQL_TIME left_date = { 0 };
MYSQL_TIME right_date = { 0 };
MYSQL_BIND ResultBind[1];
unsigned long ResultLength;
my_bool ResultIsNull;
char ResultStrData[ 128 ];
*desc =
"MySQL-#.... Baan-040621 wrong result with MYSQL_TIME binding \n"
" ok: empty result set on WHERE <date> ? = <date> ? \n"
" with date 'xxxx-xx-xx' and 'yyyy-yy-yy' \n"
" err: NON empty result set on WHERE <date> ? = <date> ? \n"
" with date 'xxxx-xx-xx' and '0000-00-00' \n"
;
if ( !(stmt = mysql_stmt_init(mysql) ) ) {
return BUG_UNKNOWN;
}
query = "select name from mysql.help_topic where ? = ? order by help_topic_id ";
if (mysql_stmt_prepare(stmt, query, strlen(query))) {
printf( "Prepare ERR: %s\n", query );
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Bind the param buffers */
ParamBind[0].buffer_type= MYSQL_TYPE_DATE;
ParamBind[0].buffer_length = sizeof ( MYSQL_TIME );
ParamBind[0].buffer = (char *) &left_date;
ParamBind[0].is_null= &ParamIsNull;
ParamBind[0].length = 0;
ParamBind[1].buffer_type= MYSQL_TYPE_DATE;
ParamBind[1].buffer_length = sizeof ( MYSQL_TIME );
ParamBind[1].buffer = (char *) &right_date;
ParamBind[1].is_null= &ParamIsNull;
ParamBind[1].length = 0;
if (mysql_stmt_bind_param(stmt, ParamBind)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Bind the result buffers */
ResultBind[0].buffer_type= MYSQL_TYPE_STRING;
ResultBind[0].buffer= ResultStrData;
ResultBind[0].is_null= &ResultIsNull;
ResultBind[0].length= &ResultLength;
if (mysql_stmt_bind_result(stmt, ResultBind)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/*
Test with 'normal' dates. As the dates are unequal, no rows should be fetched.
This works fine. The test is added to be sure everything is OK
*/
/* Set actual parameter values */
left_date.time_type = MYSQL_TIMESTAMP_DATE;
right_date.time_type = MYSQL_TIMESTAMP_DATE;
left_date.year = 2000;
left_date.month = 1;
left_date.day = 1;
right_date.year = left_date.year - 1; /* note left_date != right_date */
right_date.month = left_date.month;
right_date.day = left_date.day;
/* Execute */
if (mysql_stmt_execute(stmt)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
/* Fetch all rows */
while (!mysql_stmt_fetch(stmt))
{
/* As binds are unequal, no row should be returned */
return BUG_UNKNOWN;
}
/*
Now do the REAL test. Change one bind to '0000-00-00'
*/
/* Set actual Param value */
right_date.year = 0;
right_date.month = 0;
right_date.day = 0;
if (mysql_stmt_execute(stmt)) {
mysql_stmt_close(stmt);
return BUG_UNKNOWN;
}
while (!mysql_stmt_fetch(stmt))
{
if (ResultIsNull)
return BUG_UNKNOWN;
printf ( "fetched name = %s\n", ResultStrData );
return BUG_PRESENT;
}
/* Close the statement */
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 */ Pwd,
/* 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 using the standard mysql.helptopic table has been included. Query: select * from T where ? = ? Both parameters are of the type MYSQL_TIME. When the actual values of the parameters are set to a different date, the where clause should evaluate to FALSE, and an empty result set should be returned. This works fine, as long as no '0000-00-00' date is used. In that case the where clause evalueate to TRUE, causing the complete table to be fetched. where ? /* 1999-01-01 */ = ? /* 2000-01-01 */ ----> FALSE where ? /* 1999-01-01 */ = ? /* 0000-00-00 */ ----> TRUE 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; MYSQL_BIND ParamBind[2]; unsigned long ParamLength; my_bool ParamIsNull; MYSQL_TIME left_date = { 0 }; MYSQL_TIME right_date = { 0 }; MYSQL_BIND ResultBind[1]; unsigned long ResultLength; my_bool ResultIsNull; char ResultStrData[ 128 ]; *desc = "MySQL-#.... Baan-040621 wrong result with MYSQL_TIME binding \n" " ok: empty result set on WHERE <date> ? = <date> ? \n" " with date 'xxxx-xx-xx' and 'yyyy-yy-yy' \n" " err: NON empty result set on WHERE <date> ? = <date> ? \n" " with date 'xxxx-xx-xx' and '0000-00-00' \n" ; if ( !(stmt = mysql_stmt_init(mysql) ) ) { return BUG_UNKNOWN; } query = "select name from mysql.help_topic where ? = ? order by help_topic_id "; if (mysql_stmt_prepare(stmt, query, strlen(query))) { printf( "Prepare ERR: %s\n", query ); mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Bind the param buffers */ ParamBind[0].buffer_type= MYSQL_TYPE_DATE; ParamBind[0].buffer_length = sizeof ( MYSQL_TIME ); ParamBind[0].buffer = (char *) &left_date; ParamBind[0].is_null= &ParamIsNull; ParamBind[0].length = 0; ParamBind[1].buffer_type= MYSQL_TYPE_DATE; ParamBind[1].buffer_length = sizeof ( MYSQL_TIME ); ParamBind[1].buffer = (char *) &right_date; ParamBind[1].is_null= &ParamIsNull; ParamBind[1].length = 0; if (mysql_stmt_bind_param(stmt, ParamBind)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Bind the result buffers */ ResultBind[0].buffer_type= MYSQL_TYPE_STRING; ResultBind[0].buffer= ResultStrData; ResultBind[0].is_null= &ResultIsNull; ResultBind[0].length= &ResultLength; if (mysql_stmt_bind_result(stmt, ResultBind)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Test with 'normal' dates. As the dates are unequal, no rows should be fetched. This works fine. The test is added to be sure everything is OK */ /* Set actual parameter values */ left_date.time_type = MYSQL_TIMESTAMP_DATE; right_date.time_type = MYSQL_TIMESTAMP_DATE; left_date.year = 2000; left_date.month = 1; left_date.day = 1; right_date.year = left_date.year - 1; /* note left_date != right_date */ right_date.month = left_date.month; right_date.day = left_date.day; /* Execute */ if (mysql_stmt_execute(stmt)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } /* Fetch all rows */ while (!mysql_stmt_fetch(stmt)) { /* As binds are unequal, no row should be returned */ return BUG_UNKNOWN; } /* Now do the REAL test. Change one bind to '0000-00-00' */ /* Set actual Param value */ right_date.year = 0; right_date.month = 0; right_date.day = 0; if (mysql_stmt_execute(stmt)) { mysql_stmt_close(stmt); return BUG_UNKNOWN; } while (!mysql_stmt_fetch(stmt)) { if (ResultIsNull) return BUG_UNKNOWN; printf ( "fetched name = %s\n", ResultStrData ); return BUG_PRESENT; } /* Close the statement */ 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 */ Pwd, /* 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; }