• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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?
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.
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
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?
Reply

#6
$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
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, 04: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, 02:35 AM)Pertti Wrote:
(07-13-2018, 04: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
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?
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2018 MyBB Group.