0

Our website requires signing up. Lately it has become apparent that the signup process fails on many occasions. I implemented several logs in my PHP code to start tracking this. Including one to track outputs from mysql_last_error() when possible.

My next step was to write a script that generates sign-up requests with random values for the different required fields. My logic was that if this yields an issue - the problem is with the back-end logic, and if it doesn't - the problem is with server loads. (BTW, was this sound logic?)

When sending many of these (I'd say about 150) I got only three errors, and the mysql_last_error log showed this:

  • [Wed May 21 13:20:45.000000 2014] Incorrect date value: '1964-11-31' for column 'dob' at row 1

  • [Wed May 21 13:48:37.000000 2014] Incorrect date value: '1963-11-31' for column 'dob' at row 1

  • [Wed May 21 13:48:37.000000 2014] Incorrect date value: '1967-02-29' for column 'dob' at row 1

The request is sent in a JSON format, and its dob field is in mm/dd/yyyy format. The SQL in my PHP code subsequently looks like this:

"STR_TO_DATE('".$cleanUser['dob']."', '%m/%d/%Y')"

I tried performing this operation inside MySQL Workbench as well, to isolate it from possible bad code. but it failed there as well. I simply don't understand what's the problem, and why it occurs only on some dates.

All help will be greatly appreciated, Thanks in Advance

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
RonyHe
  • 890
  • 8
  • 11

2 Answers2

1

November only has 30 days, so the date 1964-11-31 doesn't exist.

And 1967 is not a bissextile year, so february only has 28 days this year.

BTW, use parameterized query instead of SQL concatenation to avoid leaving your queries open to SQL injection.

xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
  • Wow, that's embarassing :-) I wouldn't have though that MySQL validates that so it didn't even cross my mind. Thanks a lot for the help! About MySQL injection, all inputs from the client-side go through the mysql_real_escape_string() function. I don't know what a parameterized query is, so I'll go to Google and learn. If you have a link to a good article that'd be great. – RonyHe May 22 '14 at 08:01
  • See http://stackoverflow.com/questions/4712037/what-is-parameterized-query . It's quite easy to use, and really protects your queries, what `mysql_real_escape_string` does not (http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string). – xlecoustillier May 22 '14 at 08:13
0

I think error come from data format. %m means months in words. January, February .etc So could you please use mm or MM format to date

You can see more details at php manual page http://www.php.net/manual/en/datetime.formats.date.php

ThilinaAbey
  • 1
  • 1
  • I think that would be correct for the PHP functions regarding date and time, such as strtotime(), date_create() etc. I'm using the MySQL functions STR_TO_DATE(), so the proper specefiers for format strings are here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html – RonyHe May 22 '14 at 08:17