On Jan 15, 2008 1:31 PM, Adam Williams <awilliam@xxxxxxxxxxxxxxxx> wrote:
> Andrew Ballard wrote:
> > All the more reason I would turn it into a timestamp or DateTime
> > object in PHP first. That will prevent trying to insert something like
> > what I used above. Then I would get rid of the MySQL STR_TO_DATE
> > function in the $mysqli_insert_sql value just replace it with
> > something like this:
> >
> > date('Y-m-d', $length_start)
> >
> > If you enter it in that format MySQL will get it right without regard
> > to locale settings.
> >
> > I hope that you are sanitizing the rest of the input as well, and not
> > just shoving unchecked POST data into a database. Your example is a
> > SQL injection attack waiting to be exploited.
> >
> > Andrew
> >
> >
>
> I'm running mysql_real_escape_string(); on all of the variables prior to
> inserting/updating them.
>
> I don't see the point in needing to convert it to a timestamp. The
> length_start and length_end fields in MySQL are defined as date fields.
> All I care about is the date, not the hours/minutes/seconds. If I
> insert it as date('Y-m-d', $length_start) then when I SELECT it back
> out, I will still have to do a date conversion back to MM-DD-YYYY when I
> display it to the user.
>
The reason I would convert it to a timestamp or a DateTime is simply
because it is a date, not a string. I know PHP is loosely typed, but I
still try to use the correct type for anything I store in a variable.
(I also wait to run variables through escape functions like
mysql_real_escape_string only when I pass them to something that
requires the escaping since the escape characters are not part of the
actual data.)
If you are using a date column in MySQL, it will be stored and
retrieved as YYYY-MM-DD no matter how you specify it. MySQL has a date
type that doesn't include a time portion, so you still wouldn't need
to worry about the hours/minutes/seconds. In fact, if you are using
STR_TO_DATE, you are letting MySQL do the same thing for you. The
difference is that if you check the value in PHP, you can catch an
invalid date before you send the query to insert the row, rather than
letting MySQL insert it and massage the value to NULL because it isn't
a real date. (STR_TO_DATE('20-10-2008', '%m-%d-%Y') returns NULL) At
any rate, you'll still get the date back as YYYY-MM-DD unless you use
MySQL's date functions to format it the way you want to see it.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
