Page 1 of 1

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

Posted: Wed Nov 15, 2006 10:53 am
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

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

Posted: Wed Nov 15, 2006 11:15 am
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...)

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

Posted: Wed Nov 15, 2006 12:48 pm
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

Posted: Wed Nov 15, 2006 12:55 pm
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.

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

Posted: Wed Nov 15, 2006 1:48 pm
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....

thanks again

Posted: Wed Nov 15, 2006 2:29 pm
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

Re: thanks again

Posted: Thu Nov 16, 2006 1:15 am
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).