Welcome Guest, Not a member yet? Register   Sign In
how to clear a field in database in x mins?
#1

I have been working on allowing users to reset their lost password. I send an email to the registered user's email address with a special code that allows them to get into the system so that they can update their password. I store this "special code" in a field in my database. I'd like to only allow use of that "backdoor" for a limited time (maybe an hour). But how should I do this? Does CI have a timer function that will fire every x minutes so I know to erase the "special code"  field?
proof that an old dog can learn new tricks
Reply
#2

You could put 'expiry' date/time set +1 hour in future in DB and when checking code check against it.

Because it's such a limited use feature and because there's time limit on after which you don't have to worry about extra codes being stored in DB, you could run clean-up script once a day or once a month even.

It's usually done via CRON job on server side, that calls php /pahttoyourproject/index.php cron/cleanup from command line.

Just make sure you check that such controllers are only called from command line, think the helper function for that in CI was is_cli().
Reply
#3

well I think I have a plan. Let me know if this is a problem? When I create the "specialcode" I will write the time into the dbase. When a user presses the email link to use it, first thing, I will get the code, find the record and if the time between now and the creation time is greater than x minutes, I will redirect to the login page.
proof that an old dog can learn new tricks
Reply
#4

When you create the code and store it in the DB, also create a timestamp:


Code:
insert into whatever_table
(user_id, code, created_at)
values( ?, ?, NOW() );


Then when you want to check for a valid code:


Code:
select * from whatever_table
where user_id = ?
and code = ?
and created_at > ( NOW() - interval 1 hour );
Reply
#5

(This post was last modified: 07-13-2018, 11:06 AM by richb201.)

Thanks Brian.  I have figured out that "my" code writes a time stamp into the database when it creates the "specialcode". So I when I check for the specialcode in the database, I  only want to consider records that have been created in the last 60 minutes. I wrote this

           $query = $this->db->get_where('users', array('forgot_pass_identity' => $fp_code),TIMESTAMPDIFF(MINUTE, NOW(), 'modified')=> '<60');//this is the user since the code matches using qb

The field in the database is called modified. I am not sure what the TIMESTAMPDIFF returns? The => is not working. I want to find records where the minutes is "less than 60".  Not sure if this is possible?
proof that an old dog can learn new tricks
Reply
#6

(This post was last modified: 07-13-2018, 11:50 AM by richb201.)

$array=array('forgot_pass_identity' => $fp_code,'TIMESTAMPDIFF(MINUTE, NOW(), modified)' => '<= 10');
$query = $this->db->get_where($array);//this is the user since the code matches using qb



Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' < 10'' at line 4
SELECT * FROM `users` WHERE `forgot_pass_identity` = '33075e45dbde6bb26388c01d0d5c8aed' AND TIMESTAMPDIFF(MINUTE, NOW(), 'modified') ' < 10'
Filename: C:/xampp/htdocs/sub_crud/system/database/DB_driver.php
Line Number: 691
proof that an old dog can learn new tricks
Reply
#7

This would work as your query:

PHP Code:
$query $this->db->query('
    SELECT * 
    FROM users
    WHERE forgot_pass_identity = ?
    AND modified > ( NOW() - INTERVAL 1 HOUR )
'
, [ $fp_code ] ); 
Reply
#8

(07-13-2018, 03:47 PM)skunkbad Wrote:
PHP Code:
AND modified > ( NOW() - INTERVAL 1 HOUR 

Ah interesting, I've always used DATE_SUB("2017-06-15", INTERVAL 10 DAY) function, so didn't think the simple minus or plus wouldn't work.

For some reason I've always preferred setting exact expiry time, not current timestamp then trying to work backwards from there, I guess both ways have some shortcomgins and some advantages.
Reply
#9

(07-14-2018, 01:35 AM)Pertti Wrote:
(07-13-2018, 03:47 PM)skunkbad Wrote:
PHP Code:
AND modified > ( NOW() - INTERVAL 1 HOUR 

Ah interesting, I've always used DATE_SUB("2017-06-15", INTERVAL 10 DAY) function, so didn't think the simple minus or plus wouldn't work.

For some reason I've always preferred setting exact expiry time, not current timestamp then trying to work backwards from there, I guess both ways have some shortcomgins and some advantages.

One advantage is that it reads nicely.
Reply
#10

(This post was last modified: 07-14-2018, 08:02 AM by richb201.)

Guys, I am a little confused. Is "INTERVAL 1 HOUR" and "INTERVAL 10 DAY" some kind of constants or are they pseudo code? I think the problem I am having is based on the "TIMESTAMPDIFF(MINUTE, NOW(), 'modified')" which is part of MariaDb. I can certainly subtract instead. But how do I get the Interval 1 Hour. Or even more precisely, how do I write the SQL that will only return the record that is both for the right fp_code and is less than 1 hour old? what I currently have is

           $sql="SELECT * FROM users WHERE TIMESTAMPDIFF(MINUTE, NOW(), modified) <= 10 AND forgot_pass_identity = ?";
           $query=$this->db->query($sql,$fp_code);
           $q=$query->result();
           $iCount=$query->num_rows();

This is returning zero records found ($iCount=0). How do I calculate the INTERVAL 1 HOUR? ie what format can be subtracted from NOW()? I found the date_sub() and can try to fool with that, but is it searchable in an SQL statement?
proof that an old dog can learn new tricks
Reply




Theme © iAndrew 2016 - Forum software by © MyBB