Formating Date and Time in MySQL comperative query!

[uranuskid]

Hey folks,

I have aliile issue with my Date. I query the data base with a comperative query (see below) and want to get the date in a different format.

At first the query:

$query = mysql_query("SELECT var1, var2, RideTo, Date, Time,  FROM table WHERE var1 = '$_POST[var1]' AND var2 = '$_POST[var2]'
ORDER BY Date AND Time")
                        or die("SELECT error: ".mysql_error()); 
 
 

That all works fine, the resulting table is all good. However, I tried to format the date in the query by replacing Date with DATE_FORMATE('Date', '%e %b %y') to get the output date in a different style. It won't work. Similar thing with the Time field.
Anybody an idea how to solve that?

Cheers,
Frank

[pbmods]

Couple of things:

$query = mysql_query("SELECT var1, var2, RideTo, Date, Time, FROM table WHERE var1 = '$_POST[var1]' AND var2 = '$_POST[var2]'
ORDER BY Date AND Time")
or die("SELECT error: ".mysql_error());

[/code]

What would you do if $_POST[var1] === "'\c DROP TABLE `table`;"?
Also, s/b ORDER BY `Date`, `Time`.

That all works fine, the resulting table is all good. However, I tried to format the date in the query by replacing Date with DATE_FORMATE('Date', '%e %b %y') to get the output date in a different style. It won't work. Similar thing with the Time field.
Anybody an idea how to solve that?

Try DATE_FORMAT(`Date` instead of 'Date' (use backticks instead of quotes).

[uranuskid]

Couple of things:



What would you do if $_POST[var1] === "'\c DROP TABLE `table`;"?
Also, s/b ORDER BY `Date`, `Time`.




Try DATE_FORMAT(`Date` instead of 'Date' (use backticks instead of quotes).

Hey there,

Thanks for the hints. My input variable comes from a select box, so I think I won't get problems with SQL infiltration.

I tried the date_format with the backticks, however wouldn"t work. So I decided to formate the date with php function date(Y M d, strtotime($row['date from the sql query'])) in the output table and that's absolutely fine.

Cheers,
Frank

[pbmods]

My input variable comes from a select box, so I think I won't get problems with SQL infiltration.

Yes, but...

A cracker could build his own form with a text box. At the very least, you might want to consider using addslashes.

Admittedly, it depends on the nature of your site and the perceived importance of the private data thereof. But it's still a good habit to get into.