put the default value of datetime

[rwahdan1978]

Him
I have an OTP sysem where the user will enter the OTP that was sent to their email, in the DB it is saving the OTP and the expiry after 15 minutes. After they use it, I need to delete both from DB. I am able to set the number back to 0 since it is an integer. How to reset the dattime field to show 0000-00-00 00:00:00?

$sqlUpdate = "UPDATE login_users SET otp_reset = 0, otp_expiry = ??? WHERE username_email = '$myusername'";
if(mysqli_query($db,$sqlUpdate))
{
.....
}

 

[maxxd]

First off, don't inject variables directly into a query like that - use prepared statements. Secondly, if it's not too late already I recommend switching to the PDO database interface - it's much easier to work with and reason about.

Now, as to your actual question, set the value to null.

$qry = "
	UPDATE login_users
	SET	 otp_reset = 0
		,otp_expiry = :exp
	WHERE username_email = :email
";
$sql = $pdoObject->prepare($qry);
$result = $sql->execute($qry, [
	'exp' => null,
	'email' => $myusername
]);

Admittedly, I've been in the land of Laravel for about 5 years now and am not entirely sure of my syntax above, so take it with a grain of salt...

Edited April 26 by maxxd

[Suyadi]

On 4/26/2025 at 9:26 PM, maxxd said:

First off, don't inject variables directly into a query like that - use prepared statements. Secondly, if it's not too late already I recommend switching to the PDO database interface - it's much easier to work with and reason about.

Now, as to your actual question, set the value to null.

$qry = "
	UPDATE login_users
	SET	 otp_reset = 0
		,otp_expiry = :exp
	WHERE username_email = :email
";
$sql = $pdoObject->prepare($qry);
$result = $sql->execute($qry, [
	'exp' => null,
	'email' => $myusername
]);

Admittedly, I've been in the land of Laravel for about 5 years now and am not entirely sure of my syntax above, so take it with a grain of salt...

Lol, me too

That should be:

$sql = $pdoObject->prepare($qry);

$result = $sql->execute(['exp' => null, 'email' => $myusername]);

As the PDOStatement::execute only accept one parameter.

Reference: https://www.php.net/manual/en/pdostatement.execute.php

[jsanbae]

First of all, I think you should follow the above recommendations about using prepared statements. It's very risky and bad practice to use plain values directly in a query.

Regarding your concern, is there a specific reason for using 0000-00-00 00:00:00? This is an invalid date, and if you're using MySQL, the database might reject this value.

I suggest setting the otp_expiry value to NULL—I think it's better for validation. Just make sure the database table schema allows null values.

 

[gizmola]

On 5/9/2025 at 2:38 PM, jsanbae said:

 

Regarding your concern, is there a specific reason for using 0000-00-00 00:00:00? This is an invalid date, and if you're using MySQL, the database might reject this value.

 

 

Correct.  That is an invalid mysql date.  You can set a mode of mysql to accept that value, but that is not the way to handle this problem.  Instead, make sure that the OTP  expiry allows NULL.    Then set the value to NULL.

 

Personally, I would not design a system like this.  Instead I would have a related table that only holds "events".  I will usually have an user_event_type table that has different allowable authentication events.  For example:

 

user event type

id | Description

1.   Registration
2.   Password Reset
3.   One time Password
4.   Login
5.   Close account
6.   Failed Login
7.   Account locked

etc.

I don't know what your user table looks like but hopefully it has an ID other than "email".  I'll assume you do.  So you then have a user_event table with a structure like this: 

 

id | user_id | user_event_type_id | event_value | status | expire_date_time | created_at

100|  245    |        3           | xyzabc...   |    0   |    .....         | current_date

 

There are a few reasons to do this.  They include:

- you have an audit trail of events

- MySQL with InnoDB is optimized for insert queries, and they don't reduce concurrency unlike update queries. 

 

Instead of trying to overwrite the OTP, you can simply set the status from 0 to 1 (or whatever value you want).  You could have several status values if you want fine grain control over the changes in status.

Just to keep it simple, if the person logs in with the OTP, then it's used, so you set the status to 0.  A subsequent query of "SELECT * FROM user_event WHERE user_id = 245 and user_event_type = 3 AND status = 0 ORDER BY created_at DESC LIMIT 1" will always find the most recent OTP request.   You can then compare that with the OTP value.  Making event_value a varchar of a specific length is no cost if the field is unused, as varchars will use the bytes required.    So if you want to use event_value for additional data, you can do that, but if it's something simple like "login" event, you don't need to use it.  

Personally I would also have a client_ip field in a table like this, where I use a varbinary field to store the IP.  This works for both IPv4 and IPv6 addresses, but there are some tricks to doing that, and it is not specifically related to your question.  I mention it just to be complete.