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:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: Sergey Glukhov
Triage: Triaged: D2 (Serious)

[28 Oct 2008 7:55] Mikiya Okuno
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.
[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)