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)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.