| Bug #40365 | Prepared statements may insert invalid dates. | ||
|---|---|---|---|
| Submitted: | 28 Oct 2008 7:55 | Modified: | 8 Dec 2008 17:10 |
| Reporter: | Mikiya Okuno | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | any | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
[6 Nov 2008 4:14]
Meiji KIMURA
I did an additional test and looked into the source codes of MySQL 5.0.58(Windows 32-bit). I used an original C-API program.
In mysqld-nt.exe, date is checked in check_date() in my_time.cc.
When I insert an invalid date '2005-02-31', the call stack to check_date() like this, (Sorry there is some japanese, because I used Japanese Visual Studio).
[Call Stack]
mysqld-nt.exe!check_date(const st_mysql_time * ltime=0x01720040, char not_zero_date='', unsigned long flags=1, int * was_cut=0x0396df44) 行 90 C
mysqld-nt.exe!Field_datetime::store_time(st_mysql_time * ltime=0x01720040, enum_mysql_timestamp_type time_type=MYSQL_TIMESTAMP_DATE) 行 5607 + 0x38 バイト C++
mysqld-nt.exe!Item_param::save_in_field(Field * field=0x016e8848, int no_conversions=0) 行 2701 + 0x22 バイト C++
mysqld-nt.exe!fill_record(THD * thd=0x0170fbe8, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0) 行 5782 + 0x15 バイト C++
mysqld-nt.exe!fill_record_n_invoke_before_triggers(THD * thd=0x0170fbe8, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0, Table_triggers_list * triggers=0x00000000, trg_event_type event=TRG_EVENT_INSERT) 行 5827 + 0x15 バイト C++
mysqld-nt.exe!mysql_insert(THD * thd=0x0170fbe8, TABLE_LIST * table_list=0x0171efd0, List<Item> & fields={...}, List<List<Item> > & values_list={...}, List<Item> & update_fields={...}, List<Item> & update_values={...}, enum_duplicates duplic=DUP_ERROR, int ignore=1) 行 759 + 0x22 バイト C++
mysqld-nt.exe!mysql_execute_command(THD * thd=0x0170fbe8) 行 3583 + 0x47 バイト C++
mysqld-nt.exe!Prepared_statement::execute(String * expanded_query=0x0396f6a0, int open_cursor=0) 行 3049 + 0x36 バイト C++
mysqld-nt.exe!mysql_stmt_execute(THD * thd=0x0170fbe8, char * packet_arg=0x01712041, unsigned int packet_length=47) 行 2321 + 0x19 バイト C++
mysqld-nt.exe!dispatch_command(enum_server_command command=COM_STMT_EXECUTE, THD * thd=0x0170fbe8, char * packet=0x01712041, unsigned int packet_length=47) 行 1831 + 0x11 バイト C++
mysqld-nt.exe!do_command(THD * thd=0x0170fbe8) 行 1580 + 0x31 バイト C++
mysqld-nt.exe!handle_one_connection(void * arg=0x0170fbe8) 行 1186 + 0x9 バイト C++
mysqld-nt.exe!pthread_start(void * param=0x016d28e8) 行 85 + 0x9 バイト C
mysqld-nt.exe!_callthreadstart() 行 293 + 0xf バイト C
mysqld-nt.exe!_threadstart(void * ptd=0x01716078) 行 277 C
At that time, check_date() returned these values.
*was_cut= 2;
return TRUE;
The source code of caller is here. (in field.cc)
ltime have to be set to '0000-00-00'. But make_datetime didn't set.
Thus wrong date was inserted as it is.
if (check_date(ltime, tmp != 0,
(TIME_FUZZY_DATE |
(current_thd->variables.sql_mode &
(MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
MODE_INVALID_DATES))), &error))
{
char buff[MAX_DATE_STRING_REP_LENGTH];
String str(buff, sizeof(buff), &my_charset_latin1);
make_datetime((DATE_TIME_FORMAT *) 0, ltime, &str);
set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED,
str.ptr(), str.length(), MYSQL_TIMESTAMP_DATETIME,1);
}
I also check the call stack without using prepared statement.
[Call Stack]
> mysqld-nt.exe!check_date(const st_mysql_time * ltime=0x0386dfa4, char not_zero_date='', unsigned long flags=1, int * was_cut=0x0386df98) 行 88 C
mysqld-nt.exe!str_to_datetime(const char * str=0x0170863a, unsigned int length=10, st_mysql_time * l_time=0x0386dfa4, unsigned int flags=1, int * was_cut=0x0386df98) 行 430 + 0x1b バイト C
mysqld-nt.exe!Field_datetime::store(const char * from=0x01708630, unsigned int len=10, charset_info_st * cs=0x00b8b708) 行 5514 + 0x28 バイト C++
mysqld-nt.exe!Item::save_str_value_in_field(Field * field=0x016e8848, String * result=0x01708648) 行 367 + 0x28 バイト C++
mysqld-nt.exe!Item_string::save_in_field(Field * field=0x016e8848, int no_conversions=0) 行 4595 C++
mysqld-nt.exe!fill_record(THD * thd=0x016e7018, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0) 行 5782 + 0x15 バイト C++
mysqld-nt.exe!fill_record_n_invoke_before_triggers(THD * thd=0x016e7018, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0, Table_triggers_list * triggers=0x00000000, trg_event_type event=TRG_EVENT_INSERT) 行 5827 + 0x15 バイト C++
mysqld-nt.exe!mysql_insert(THD * thd=0x016e7018, TABLE_LIST * table_list=0x01708398, List<Item> & fields={...}, List<List<Item> > & values_list={...}, List<Item> & update_fields={...}, List<Item> & update_values={...}, enum_duplicates duplic=DUP_ERROR, int ignore=0) 行 759 + 0x22 バイト C++
mysqld-nt.exe!mysql_execute_command(THD * thd=0x016e7018) 行 3583 + 0x47 バイト C++
mysqld-nt.exe!mysql_parse(THD * thd=0x016e7018, const char * inBuf=0x017082f0, unsigned int length=49, const char * * found_semicolon=0x0386fd68) 行 6158 + 0x9 バイト C++
mysqld-nt.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x016e7018, char * packet=0x016ff829, unsigned int packet_length=50) 行 1874 + 0x1e バイト C++
mysqld-nt.exe!do_command(THD * thd=0x016e7018) 行 1580 + 0x31 バイト C++
mysqld-nt.exe!handle_one_connection(void * arg=0x016e7018) 行 1186 + 0x9 バイト C++
mysqld-nt.exe!pthread_start(void * param=0x016d28e8) 行 85 + 0x9 バイト C
mysqld-nt.exe!_callthreadstart() 行 293 + 0xf バイト C
mysqld-nt.exe!_threadstart(void * ptd=0x016cf538) 行 277 C
At this time, check_date() also returned same values.
*was_cut= 2;
return TRUE;
The source code of caller is here. (in my_time.cc)
if (check_date(l_time, not_zero_date != 0, flags, was_cut))
goto err;
:
err:
bzero((char*) l_time, sizeof(*l_time));
ltime was set to '0000-00-00', so '0000-00-00' was inserted.
[Suggest to fix]
When calling check_date() in field.cc. If check_date() returns TRUE,
ltime have to be set to '0000-00-00'. B
if (check_date(ltime, tmp != 0,
(TIME_FUZZY_DATE |
(current_thd->variables.sql_mode &
(MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
MODE_INVALID_DATES))), &error))
{
char buff[MAX_DATE_STRING_REP_LENGTH];
String str(buff, sizeof(buff), &my_charset_latin1);
make_datetime((DATE_TIME_FORMAT *) 0, ltime, &str);
set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED,
str.ptr(), str.length(), MYSQL_TIMESTAMP_DATETIME,1);
}
That's all.
[7 Nov 2008 0:16]
Omer Barnir
triage: setting tag SR51MRU
[18 Nov 2008 16:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/59091 2718 Sergey Glukhov 2008-11-18 Bug#40365 Prepared statements may insert invalid dates. set DATE|DATETIME value to 0 if ALLOW_INVALID_DATES sql_mode is not enabled.
[18 Nov 2008 16:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/59097 2718 Sergey Glukhov 2008-11-18 Bug#40365 Prepared statements may insert invalid dates. set DATE|DATETIME value to 0 if ALLOW_INVALID_DATES sql_mode is not enabled.
[18 Nov 2008 16:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/59099 2718 Sergey Glukhov 2008-11-18 Bug#40365 Prepared statements may insert invalid dates. set DATE|DATETIME value to 0 if ALLOW_INVALID_DATES sql_mode is not enabled.
[27 Nov 2008 12:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/60031 2715 Sergey Glukhov 2008-11-27 Bug#40365 Prepared statements may insert invalid dates. set DATE|DATETIME value to 0 if ALLOW_INVALID_DATES sql_mode is not enabled.
[2 Dec 2008 13:00]
Bugs System
Pushed into 5.0.74 (revid:sergey.glukhov@sun.com-20081127124125-mrhu4qr03i18636r) (version source revid:matthias.leich@sun.com-20081128184708-uyjg2dfrj2va5o3d) (pib:5)
[3 Dec 2008 20:04]
Paul DuBois
Noted in 5.0.74 changelog. Prepared statements allowed invalid dates to be inserted when the ALLOW_INVALID_DATES SQL mode was not enabled. Resetting report to NDI pending push into 5.1.x, 6.0.x.
[8 Dec 2008 10:19]
Bugs System
Pushed into 5.1.31 (revid:sergey.glukhov@sun.com-20081127124125-mrhu4qr03i18636r) (version source revid:ingo.struewing@sun.com-20081127152850-iwzy1vh9gqpg3s3x) (pib:5)
[8 Dec 2008 11:30]
Bugs System
Pushed into 6.0.9-alpha (revid:sergey.glukhov@sun.com-20081127124125-mrhu4qr03i18636r) (version source revid:ingo.struewing@sun.com-20081127214152-x1avx1b1cyfshx1a) (pib:5)
[8 Dec 2008 17:10]
Paul DuBois
Noted in 5.1.31, 6.0.9 changelogs.
[19 Jan 2009 11:27]
Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:05]
Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:11]
Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
Description: When I use the prepared statement on C API, I could insert invalid dates using a prepared statement. The statement is recorded on the binary log like below: # at 77231 #081028 16:33:12 server id 1 end_log_pos 77374 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1225179192/*!*/; INSERT INTO test.datetest VALUES(5726901946845802572,'4269-02-29') /*!*/; The prepared statement is like below: mysql> PREPARE st1 FROM "INSERT INTO test.datetest VALUES(?,?)"; The table definition is like below: mysql> show create table datetest\G *************************** 1. row *************************** Table: datetest Create Table: CREATE TABLE `datetest` ( `a` bigint(20) unsigned NOT NULL, `b` datetime default NULL ) ENGINE=InnoDB DEFAULT CHARSET=sjis 1 row in set (0.00 sec) How to repeat: Run the C program, which inserts random values to the given table. You can confirm that the values are in the table using the statement like below: mysql> select * from datetest where year(b)%4!=0 and month(b)=2 and day(b)=29; Suggested fix: Invalid dates should not be inserted unless ALLOW_INVALID_DATES sql_mode is enabled.