Check if Password Expired?

[doubledee]

I could use some help comparing Dates.

 

I need to check if a Temporary Password has expired, and am a bit shaky on "Date Math".   

 

The Temporary Password was created like this...

// Build query.
$q2 = "UPDATE member
		SET temp_password=?,
		temp_reset_on=NOW(),
		updated_on=NOW()
		WHERE email=?
		LIMIT 1";

 

So how do I see if temp_reset_on is more than 4-hour old and thus expired?

 

Thanks,

 

 

Debbie

 

[scootstah]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub

[doubledee]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub

 

I want to do this in PHP.

 

And I am Googling this topic, but I don't understand what format NOW() is in and which functions will work.

 

(Time/Date Math must be one of the trickiest parts of PHP...)

 

 

Debbie

 

[scootstah]

If you want to do it in PHP, you'll first want to convert the time to a UNIX timestamp. You can do that like...

SELECT UNIX_TIMESTAMP(temp_reset_on) AS temp_reset_on ... 

 

Then you'll want to subtract that from the current UNIX timestamp.

$diff = time() - $row['temp_reset_on'];

 

Lastly, you'll want to see if the difference is greater than 4 hours in seconds.

if ($diff >= 14400) {}

[doubledee]

If you want to do it in PHP, you'll first want to convert the time to a UNIX timestamp. You can do that like...

SELECT UNIX_TIMESTAMP(temp_reset_on) AS temp_reset_on ... 

 

When I stored them in MySQL I used Now() so I thought they were in Unix Timestamp format?

 

 

Then you'll want to subtract that from the current UNIX timestamp.

$diff = time() - $row['temp_reset_on'];

 

Lastly, you'll want to see if the difference is greater than 4 hours in seconds.

if ($diff >= 14400) {}

 

You're losing me.  (I know very little MySQL and am stuck with my Prepared Statement format...)

 

Here is what I do have...

// Check # of Records Returned.
if (mysqli_stmt_num_rows($stmt1)==1){
// Member Found by Email.

// Bind result-set to variable.
mysqli_stmt_bind_result($stmt1, $salt, $tempResetOn, $memberFirstName);

// Fetch record.
mysqli_stmt_fetch($stmt1);


// **********************************
// Check for Expired Temp Password.	*
// **********************************
if ()

 

 

So the variable $tempResetOn should have a "Timestamp" in it.  (And I thought that was a "Unix Timestamp"?)

 

In pseudo code I was going to do...

if (NOW()-$tempResetOn > 4 Hours){
echo "Your Temporary Password has expired!!;
}else{
process Password Reset...
}

 

And they Date/Time Math part is what I'm stuck on...

 

 

Debbie

 

[scootstah]

NOW() is a MySQL command, not a PHP command. NOW() gives the current timestamp in the DATETIME format, which is YYYY-DD-MM HH:MM:SS.

[kicken]

When I stored them in MySQL I used Now() so I thought they were in Unix Timestamp format?

 

Mysql has it's own format that it stores dates in, which allows it to store a large range of dates as well as do calculations on these dates.  NOW() is a mysql function that just returns the current date in the mysql format.  UNIX_TIMESTAMP() is another mysql function which will convert dates in it's format to a unix timestamp format.

 

 

Once you have it in unix timestamp format (which is just # of seconds since 1/1/1970 @ midnight) finding out the age is just a matter of subtracting that timestamp from the current timestamp (given by PHP's time() function).  This will give you the age in # of seconds.  4 hours = 60*60*4 = 14400 seconds.

 

 

All that said, it's often better to do any date calculations or formatting in your SQL query directly instead of having to translate them back-n-fourth between mysql and php.  Eg:

 

SELECT
    *,
    TIMESTAMP_DIFF(HOUR, NOW(), temp_reset_on) as tmpPassAge
FROM members
...

 

Then in your code you can just do if ($row['tmpPassAge'] >= 4)

 

[doubledee]

NOW() is a MySQL command, not a PHP command. NOW() gives the current timestamp in the DATETIME format, which is YYYY-DD-MM HH:MM:SS.

 

Yeah, so I ran a test - see I do do that! - and got...

Now (PHP time()) = 1328905349

$tempResetOn = 2012-02-10 12:19:12

 

What is the easiest way to proceed?

 

Can I get PHP to create a DATETIME format like MySQL?

 

Can I subtract two timestamps in the DATETIME format (YYYY-DD-MM HH:MM:SS) ??

 

 

Debbie

 

[doubledee]

When I stored them in MySQL I used Now() so I thought they were in Unix Timestamp format?

 

Mysql has it's own format that it stores dates in, which allows it to store a large range of dates as well as do calculations on these dates.  NOW() is a mysql function that just returns the current date in the mysql format.  UNIX_TIMESTAMP() is another mysql function which will convert dates in it's format to a unix timestamp format.

 

 

Once you have it in unix timestamp format (which is just # of seconds since 1/1/1970 @ midnight) finding out the age is just a matter of subtracting that timestamp from the current timestamp (given by PHP's time() function).  This will give you the age in # of seconds.  4 hours = 60*60*4 = 14400 seconds.

 

 

All that said, it's often better to do any date calculations or formatting in your SQL query directly instead of having to translate them back-n-fourth between mysql and php.  Eg:

 

SELECT
    *,
    TIMESTAMP_DIFF(HOUR, NOW(), temp_reset_on) as tmpPassAge
FROM members
...

 

Then in your code you can just do if ($row['tmpPassAge'] >= 4)

 

I know this sounds wimpy, but I really feel weak and confused with MySQL.  I mean, I know SQL - or at least in my day I did - but all of the syntax to connect to the DB and run queries and so on is just too much for me right now.

 

I have the code down for doing Prepared Statements which makes my code safer, and I'm just reusing what I have.

 

What you are talking about above sorta throws a wrench in that, PLUS my "create_password.php" script is already big enough. 

 

Isn't there an easy way to take $tempResetOn from my Prepared Statement results and do something with that?

 

OR, is there some easy way to tweak my Prepared Statement with a function to do some Date/Time Math?

 

Ideally I'd like to buy a book on MySQL and learn the modern OOP way, but in the mean time I'm afraid of breaking things, and database security worries me to no end, so I try not to get fancy!!

 

Make sense?

 

Need to see more of my current code?

 

 

Debbie

 

[scootstah]

You seem to always prefer to hack your code up in lieu of the proper way to do it. When you don't understand something in programming, it's better to stop what you're doing and learn about it instead of saying "nah that sounds too hard, I'll just do it this way for now and learn that later". That builds up a very bad habit and you will always look for the easy way out.

 

So here's your options:

- Use DATE_SUB() to only select rows that have a temp_reset_on older than 4 hours

- Convert the temp_reset_on to a UNIX timestamp and do the math in PHP

 

If you want to do the math in PHP, you can either convert the timestamp to a UNIX timestamp in the MySQL query (like I posted earlier) or you can do it in PHP with strtotime().

 

You seem to think prepared statements are altogether different from MySQL, but they are not. Any MySQL query will work exactly the same in prepared statements.

 

 

[doubledee]

Here is a little more of my code...

// ********************************
// Find Member by Temp Password.	*
// ********************************

// Connect to the database.
require_once(WEB_ROOT . 'private/mysqli_connect.php');

// Build query.
$q1 = "SELECT salt, temp_reset_on, first_name
	FROM member
	WHERE temp_password=?";

// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);

// Bind variables to query.
mysqli_stmt_bind_param($stmt1, 's', $tempPassword);

// Execute query.
mysqli_stmt_execute($stmt1);

// Store results.
mysqli_stmt_store_result($stmt1);

// Check # of Records Returned.
if (mysqli_stmt_num_rows($stmt1)==1){
// Member Found by Email.

// Bind result-set to variable.
mysqli_stmt_bind_result($stmt1, $salt, $tempResetOn, $memberFirstName);

// Fetch record.
mysqli_stmt_fetch($stmt1);

 

 

If I am understanding what scootstah and kicken were saying, would this work...

// Build query.
$q1 = "SELECT salt, UNIX_TIMESTAMP(temp_reset_on), first_name
	FROM member
	WHERE temp_password=?";

 

 

// Bind result-set to variable.
mysqli_stmt_bind_result($stmt1, $salt, $tempResetOn, $memberFirstName);

 

 

$hoursSincePasswordReset = (time() - $tempResetOn)/14400

if ($hoursSincePasswordReset > 4){
echo "Temporary Password Expired";
}else{
continue resetting password...
}

 

How does that look??

 

 

Debbie

 

[doubledee]

You seem to always prefer to hack your code up in lieu of the proper way to do it.

 

That's not fair!!!

 

Look, I am 2,000 miles away from home, living on the road, all of my PHP and MySQL books are back home, and I am over-my-head in learning web development?!  :'(

 

And actually it is the complete opposite...

 

I am trying to not BREAK what I have working, so I can get my site up and running, AND THEN go back and improve on areas where I am weak.  (It is unrealistic to try and become a guru with HTML, CSS, PHP, MySQL, SQL, etc all in one shot?!)

 

Over the last year I have developed solid HTML and CSS skills, and my PHP is moving forward.  But I have a long ways to go, and I *know* my MySQL knowledge is weak, and since databases are often where security goes wrong, I am trying to not get fancy since the Prepared Statements I do have working seem to be secure and function okay.

 

I am just being modest and trying to not go overboard the first time around!

 

And I am *clearly* not as knowledgeable as most of you...

 

 

When you don't understand something in programming, it's better to stop what you're doing and learn about it instead of saying "nah that sounds too hard, I'll just do it this way for now and learn that later". That builds up a very bad habit and you will always look for the easy way out.

 

See above.

 

 

So here's your options:

- Use DATE_SUB() to only select rows that have a temp_reset_on older than 4 hours

- Convert the temp_reset_on to a UNIX timestamp and do the math in PHP

 

If you want to do the math in PHP, you can either convert the timestamp to a UNIX timestamp in the MySQL query (like I posted earlier) or you can do it in PHP with strtotime().

 

See my crack at things.  (I tried to do it your better way if I followed you...)

 

 

You seem to think prepared statements are altogether different from MySQL, but they are not. Any MySQL query will work exactly the same in prepared statements.

 

But I don't have any good references to learn MySQL from the ground up, and what I do have has been reviewed and I was told it is secure, so I don't want to play in areas where I don't know what I am doing.  (Sure way to hang myself!)

 

I've come a l-o-n-g ways in the past 12 months.  (Wish I'd get some credit for that?!) 

 

 

Debbie

 

 

[kicken]

 

I know this sounds wimpy, but I really feel weak and confused with MySQL.  I mean, I know SQL - or at least in my day I did - but all of the syntax to connect to the DB and run queries and so on is just too much for me right now.

 

The code form the PHP side of things is the same for this query as it would be for any other SELECT query your running anywhere on your site.  You just have what some call a 'calculated column' which is essentially an additional column derived from the value of other columns.  To your PHP script it appears the same as any other column in your tables.

 

 

I have the code down for doing Prepared Statements which makes my code safer, and I'm just reusing what I have.

You really need to develop an understanding of how the functions work to accomplish a task, not just copy-n-paste them over and over.  Experiment with them, try different things.  90% of learning is trying and seeing what happens.  When you understand what the functions actually do things will make more sense and it'll be easier for you to adapt to new situations.

 

 

What you are talking about above sorta throws a wrench in that, PLUS my "create_password.php" script is already big enough. 

Your create_password script has no bearing on any of this, unless your trying to also use that script as your reset password script.  If you are, just separate the two into different scripts. 

 

OR, is there some easy way to tweak my Prepared Statement with a function to do some Date/Time Math?

You don't tweak the prepared statement.  You tweak the query, and you do so just like I showed by doing the date math in the query itself and getting the result as a new separate column.  After you run your statement for the SELECT query you use the mysqli_stmt_bind_result to assign a variable to each column your selecting.  All you have to do is assign the results of that date math to a variable for use in PHP.

 

 

This may not be entirely correct as it has been a long time since I used mysqli, an of course you'll have to update it to your table layout and needs, but essentially you would have something like this:

$sql = '
SELECT
TIMESTAMP_DIFF(HOUR, NOW(), temp_reset_on) as tmpPassAge
FROM members
WHERE
member_email=?
';

$stmt = mysql_prepare($sql);

mysql_stmt_bind_param($stmt, 's', $_GET['memberEmail']);

if (mysqli_stmt_execute($stmt)){
    mysqli_stmt_store_result($stmt);

    mysqli_stmt_bind_result($stmt, $tmpPassAge);

    if (mysqli_stmt_fetch($stmt)){
//record found, test for expired pass
if ($tmpPassAge >= 4){
	//pass expired
}
else {
	//continue
}
    }
}

 

but in the mean time I'm afraid of breaking things

If you want to learn anything well you have to be willing to take a chance that you might mess something up.  Of course, you want to try and ensure messing something up has as little impact as possible but sometimes in the spirit of learning you just gotta do stuff. 

 

You should get a test environment setup which is completely separate from your live website that you can do all this testing/possibly breaking stuff on.  Then if you do mess something up, you just restore your test environment and no harm done to your live data.  Once you have it all working like you want to push the change to your host and "go live".

 

It's pretty easy to setup a local copy of apache/mysql/php these days using setup programs like xampp.  Your host probably has some method of creating a backup of your mysql database which you can then import into your local setup so you can experiment without causing any harm to your live site.

 

 

 

side note: took me so long to type this ya'll posted a bunch of stuff already.  I must learn to type faster.

 

[scootstah]

I don't see anything standing out in your last snippet. Test it out and see if it works as expected.

 

But I don't have any good references to learn MySQL from the ground up, and what I do have has been reviewed and I was told it is secure, so I don't want to play in areas where I don't know what I am doing.  (Sure way to hang myself!)

 

There isn't much in the way of security that you can mess up with database queries. Sure, there is SQL injection - but prepared statements eliminates that all together. So really, you have nothing to worry about.

[KevinM1]

Debbie, this is where writing test cases/prototypes comes into play.  Looking at the process in the terms of "I'm going to study, study, study, but only touch code/the database in my live project" is the wrong way to go.  We all - even us admins, mods, and gurus - experiment with code we don't fully understand on the side.  You need to give yourself the web development equivalent of scratch paper: an area where you can make a bunch of PHP test files and database tables you can play with that's separate from your project space.

 

Reading documentation is good, but it's only half the equation.  Don't be afraid to write code.  Yes, it's time consuming, but is it any more wasteful than coming on here to ask a question that may not be answered to your liking in whatever time you have?  To say nothing of the benefit of actually seeing your code work/fail, seeing any unintentional side effects, and ultimately building your own suite of test cases you can fall back on in case you forget something down the line.

 

Testing and experimenting is an integral part of the process.  Don't neglect it.