MySQL datetime INSERT/UPDATE reverts to 0000-00-00 00:00:00

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

MySQL datetime INSERT/UPDATE reverts to 0000-00-00 00:00:00

Post by maxd »

I'm using 3 form fields to get my date and time criteria (the hourmin function below creates two select list menus for hour and minute values):

Code: Select all

<form name="editrelease" id="editrelease" method="post" action="<?php echo $PHP_SELF;?>?editreleasesubmit=1"-->
          <!--form name="editrelease" id="editrelease" method="post" action="phpAdminNewsSubmit.php?editreleasesubmit=1"-->
          <?php if ($edit){ ?>
          <input type="hidden" name="releaseid" value="<?php echo $id; ?>">
          <?php } ?>
		  <table width="100%" id="tables">
		  <tr>
		    <td align="left" valign="top" style="margin-top:4px;">Publish Date/Time:</td><td>&nbsp;&nbsp;<input type="text" name="date"<?php if($edit){ echo " value=$outputdate"; }?> size=10><a href="javascript:popCal('date','editrelease')"><img src="bbnadmin/images/calendar.gif" border="0" valign="absmiddle"></a>&nbsp;&nbsp;Time: <?php hourmin($hid = "hour", $mid = "minute", $hval = "$outputhour", $mval = "$outputmin"); ?><br />
		    <span class="smallcopy">MM/DD/YYYY | Time selection based on 24-hour clock, EST</span>
</td>
		  </tr>
Then, during the submission process, I build my datetime variable like so (the dateconvert function just takes the MM/DD/YYYY formulated submission and switches it around to MySQL's preferred YYYY/MM/DD):

Code: Select all

$date = $_POST['date'];
		$time = $_POST['hour'] . ":" . $_POST['minute'] . ":00";
		$date = dateconvert($date, 1);
		$timedate = "'$date'" . " " . $time;
and insert into DB like so:

Code: Select all

$query="INSERT INTO newsreleases (headline,subhead,bodycopy,aboutbbn,date) VALUES ('$headline','$subhead','$bodycopy','$aboutbbn','$timedate')";
I searched this forum, and found mentions of making sure to include the quotation marks, otherwise MySQL treats the "date" portion of the insert as math, which is why I tried formulating it the way it is above. I have also tried it without the quotation marks, to no avail. I'm sure it's something simple I'm missing. I'm not an experienced PHP/MySQL programmer.

When I submit the form and INSERT into MySQL, it accepts everything from the form, but alway inserts the MySQL default 0000-00-00 00:00:00 as the date. When I test submit this and just echo the variables to a browser window, the $timedate value comes through as:

'2006-11-14' 08:00:00

Is that not how MySQL wants it to look when it comes in? Any help is greatly appreciated.

max
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: MySQL datetime INSERT/UPDATE reverts to 0000-00-00 00:00

Post by timvw »

Do you really still code with register_globals on??

Btw, your script is vulnerable for XSS attacks (using $_SERVER['PHP_SELF']).. this could be easily handled by using action='#' instead... Variables like $_POST['id'] can't be replace by a constant, so i suggest that you at least use htmlentities there...

And you're not validating user-input, and not preparing that input for use in a mysql context...

And an answer to your question: You need to generate '2006-11-14 08:00:00' instead of '2006-11-14' 08:00:00 (notice that the quotes are around the complete datetime value...)
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

i'm not sure about your first two points...

Post by maxd »

timvw-

thanks for your reply. I eventually did figure out the quotation mark issue, and got the insert/update functionality working properly.

But now you've got me worrying about other issues!
Variables like $_POST['id'] can't be replace by a constant, so i suggest that you at least use htmlentities there
Sadly, I have no idea what you mean. Can you (or someone else) explain so a simpleton can fathom it?

I sort of assumed that register_globals was turned off (I thought that was standard these days), but this isn't my server. Upon running PHPinfo.php on the server, I found that you're right. It is "on". That's a security hole in itself, isn't it?

How did you know it was turned on? Am I relying on it somehow in my code? If so, I'd like to modify the code so that if they turn register_globals off, it won't disable my tools.

I will change the $_SERVER['PHP_SELF'] to #, as per your suggestion. A question, though. This is all happening on pages which rely on password authentication to run. Is there still a concern about XSS attacks in such an environment (as if I knew what an XSS attack is!). :wink:

Lastly, how should I validate user-input, and otherwise prepare it for use in mysql?

Thanks so much for helping me out. The more I learn, the fewer foolish question I'll clog the system with.

max
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

If you have single quotes around the $timedate variable in the SQL statement then it will FAIL when you try to insert this...

Code: Select all

'2006-11-14' 08:00:00
Remove the quotes around the date in your $timedate variable.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: i'm not sure about your first two points...

Post by timvw »

maxd wrote: Sadly, I have no idea what you mean. Can you (or someone else) explain so a simpleton can fathom it?
I recommend that you do a bit of research... here's an article i found when i searched for 'php xss explained' http://www.cgisecurity.com/articles/xss-faq.shtml.
maxd wrote: I sort of assumed that register_globals was turned off (I thought that was standard these days), but this isn't my server. Upon running PHPinfo.php on the server, I found that you're right. It is "on". That's a security hole in itself, isn't it?
It is off by default, but most servers turn it back on (in order to support old (didn't want to say outdated) scripts...
It isn't a security hole in itself, but it requires that you are extremely carefull with the data you use in your script (eg: $id is coming from the user input, or is it defined by you?)
maxd wrote: How did you know it was turned on? Am I relying on it somehow in my code? If so, I'd like to modify the code so that if they turn register_globals off, it won't disable my tools.
maxd wrote: Lastly, how should I validate user-input, and otherwise prepare it for use in mysql?
Since you're accepting an id, i would think the only possible values are numeric...Meaning: if there is a character in it, the value is invalid... (Write code to verify this).

And then, when you're going to use data in a mysql context you must make sure that it's in the format mysql expects (eg: all the quotes in a string have to be replaced with \'). The function http://www.php.net/mysql_real_escape_string does this (and more).. So all you have to do is $ready_for_mysql = mysql_real_escape_string($php_data) and then use $ready_for_mysql in your query....
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

thanks again

Post by maxd »

I will read up on XSS from your link.

I have implemented the mysql_real_escape_string() on all field submissions. I will need to implement stripslashes() on the retrieve and display of data, correct?

Thanks again for your tips. I'll endeavor to batten this down a bit more.

max
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: thanks again

Post by timvw »

maxd wrote: I have implemented the mysql_real_escape_string() on all field submissions. I will need to implement stripslashes() on the retrieve and display of data, correct?
I would recommend that you give it a try. Simply store something as "O'Reilly has some nice books" and see how it is returned... (And then see what happens if you stripslashes).
Post Reply