Security Alert

[Guest Anonymous]

It has recently been discovered that date and numeric field types under SQL *may* be prone to a particular type of injection attack.

Up till now, normally we correctly escaped all string fields, however it may be that more stringent checking will be required on the standard date and numeric field types. This will mean correct range checking and date validation however you should all being done this anyway.

It's not clear as to exactly which SQL databases are affected by this issue, however the advice is check everything, twice, then do it again just to make sure.

[Floydian]

Re: Security Alert

I recommend using a time stamp in an integer field. It might not be as fast as using a date field, and the value stored won't be "human readable" but it's super simple to ensure there is absolutely no possibility of injections.

Again, this is my personal recommendation coming from a person who is largely self taught, and has no specific credentials given by MySQL in the form of diplomas, degrees, or anything like that, but has studied hard and learned a lot.

[Guest Anonymous]

Re: Security Alert

Actually it's faster IIRC.

Assuming you use :

$sql = sprintf("UPDATE table SET last_modified = %u", time());

rather than

$sql = "UPDATE table SET last_modified = UNIX_TIMESTAMP();"

That way ALL times are based on the web-server's idea of time, NOT the data server's (which *could* be in a different time zone).

My peers all suggest I use the DB server's time functions so the data is human readable, ... well in some respects I agree with them, but I prefer having a simple integer. I know 99% of SQL engines have the DATE/TIME field types, however (as always) some work differently to others. -- Notably SQL Server IIRC which permits milli/micro second timestamps to be stored directly.

[Floydian]

Re: Security Alert

Glad you mentioned that. I've been using the mysql UNIX_TIMESTAMP more and more instead of inserting a php generated timestamp

which would cause interesting problems assuming it's using a New zealand time instead of the usa eastern time as I have php set to.

there's gotta be a way to tell MySQL what time zone to use, no?

[Guest Anonymous]

Re: Security Alert

There is .. but why bother...

Use the web server (PHP) to handle ALL timestamps - That way, you know you have consistency.

[Floydian]

Re: Security Alert

I ran a little test

 

$time = mysql_query('select unix_timestamp()');
list($time1) = mysql_fetch_array($time);

echo dayz($time1) . "<hr>" . dayz(time());

 

dayz() is a custom function that uses the date() function to return a formated time.

The web server is in new zealand time, and the php code has a time zone set to USA eastern time.

Both times came out the same. Considering the test, I dun see any problem using the mysql unix_timestamp() function vs inserting a php generated time()

[Guest Anonymous]

Re: Security Alert

The general accepted method is to use time() (from PHP) which is always IIRC in GMT.

That way you have a fixed system to start with...

Relying on a data server which *may* move can be dangerous.

What happens if you decide to enlarge your system , and use distributed data centers? Can you really rely on them having set the servers timestamp to match your web server - Nope.

Thinking ahead, saves a shedload of hastle in the future... ;)

[Floydian]

Re: Security Alert

I don't disagree with you at all.

I'm wondering though, when generating a timestamp, it seems to me that it's "timezoneless". As in, it's going to tell you the number of seconds from the unix epoch no matter what timezone you're in. I could be wrong. But I know for a fact that my web host is in a New Zealand time zone, and the PHP script is set to a USA time zone, and the timestamps generated in that test script still came out the same.

I could be wrong about the timestamp, but as far as I can tell, it genuinely does seem to be "timezoneless".