Time comparison

I intend giving new users 24hours to verify their e-mail upon registration.
That means has some time comparison must be made with the time of registration stored in the database…what is the best time format you think the for storing the registration time in the database.

I assume it is UNIX timestamp…what do you think?

a DATETIME field. allows time calculations in SQL.

2 Likes

how exactly is DATETIME is going to help?
You have to be more specific…

The only thing that I can think off this happening is converting DATETIME in UNIX in PHP and do the calculations there…

because DATETIME allows date calculations in SQL:

SELECT
    user_id
FROM
    activation
WHERE
        token = ?
    AND NOW() < DATE_ADD(created, INTERVAL 24 HOUR)

This query will only return a dataset if there is a matching registration token and the request is made within 24 hours. No need for calculations in PHP.

1 Like

thanks…I will go for the implementation now and ask again if the need arises.

The solution I use most often doesn’t require storing any tokens in the database. It doesn’t even require storing any DATETIME, either. Just verifying if the link is valid and not expired. The link format sent to new users is like this:

domain.example.com/confirm-registration.php?time=12345678&email=tom@example.com&chsum=a87c87f6876eecb760

So time is the timestamp (from time()) of registration form submission. email - well, it’s obvious. chsum is a hash of time and email to prevent users from changing these values manually - I also append a constant (secret) salt just for added security. So for example, I calculate chsum like this:

$time = time();
$salt = 'any text I like, just keep it hidden and unchangeable...';
$chsum = sha1($time . $email . $salt);

When verifying the link I first verify the checksum and if it’s correct it’s easy to verify expiration time because I have plain timestamp of registration - just add any number of seconds to it and check if it’s lesser or greater than the current time.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.