Jump to content
Larry Ullman's Book Forums

Login - Date Expires - Knowledge Is Power


Recommended Posts

I have gone into the database and explored what might happen after PayPal has been activated and is live. So I have manually changed some of the expiry dates of my users.

 

This is my code when the registrant originally joins:

 

$q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, agree, date_expires) VALUES ('" . stripslashes($u) . "', '$e', '" . get_password_hash($p) . "', '" . stripslashes($fn) . "', '" . stripslashes($mi) . "', '" . stripslashes($ln) . "', '$agree', SUBDATE(NOW(), INTERVAL 1 DAY) )";

 

Thus their expiry date is one day less than when they joined.

 

The whole time I have been testing my site I have left these dates and my registrants have always been allowed into the site as long as their passwords and email are correct, even though the login form is asking for a date that is greater than or equal to NOW so that doesn't make sense to me.

 

I am assuming NOW means right this minute.

 

$q = "SELECT id, username, type, IF(date_expires >= NOW(), true, false) FROM users WHERE (email='$e' AND pass='" . get_password_hash($p) . "')";

 

SO I went in and changed some of the years from the current year to a year in the future. THESE registrants are not being allowed into the site. These registrants are getting the following error message when they try to login.

 

$login_errors['login'] = 'The email address and password do not match those on file. ';

 

This doesn't make any sense to me and I am wondering if this alone would keep the registrant from entering the site once paypal is integrated.

 

I have found that one can only do so much testing when the site is "live" with PayPal because if you try to use a credit card too many times, PayPal blocks you. So I want to try to figure out as many bugs as I can beforehand.

 

Marie

Link to comment
Share on other sites

  • 3 weeks later...

I have been testing and trying several different ways of inserting the information into the database, etc. I have compared my code to that in the book and it is almost exactly the same and when changing anything back to the original code I still receive the same result. The login process is allowing a person with an expired date to enter the website.

 

I did notice on page 92, the following line does not have an = sign after the > which shouldn't make any difference to the outcome of result in the example. On page 93 the equals sign is included.

 

$q = "SELECT id, username, type, IF(date_expires > NOW(), true, false) FROM users WHERE (email='$e' AND pass='" .

 

The following code is from the book =

 

$q = "SELECT id, username, type, IF(date_expires >= NOW(), true, false) FROM users WHERE (email='$e' AND pass='" . get_password_hash($p) . "')";

$r = mysqli_query ($dbc, $q);

 

if (mysqli_num_rows($r) == 1) { // A match was made.

 

// Get the data:

$row = mysqli_fetch_array ($r, MYSQLI_NUM);

 

// If the user is an administrator, create a new session ID to be safe:

// This code is created at the end of Chapter 4:

if ($row[2] == 'admin') {

session_regenerate_id(true);

$_SESSION['user_admin'] = true;

}

 

// Store the data in a session:

$_SESSION['user_id'] = $row[0];

$_SESSION['username'] = $row[1];

 

// Only indicate if the user's account is not expired:

if ($row[3] == 1) $_SESSION['user_not_expired'] = true;

 

} else { // No match was made.

$login_errors['login'] = 'The email address and password do not match those on file.';

}

 

} // End of $login_errors IF.

 

I understand what is going on there and have echoed out certain results after changing things around etc.

 

My database is being queried and returning a result but as I say it is letting in users with the subdate and not letting in the users with the date that expires in the future.

 

Everything else in my site is working very well.

 

Any thoughts would help.

 

Marie

Link to comment
Share on other sites

The site is not letting anyone in when I input a bad email or password, which of course, should happen.

 

I just did a bit more testing and it still is not letting in anyone with an expiry date in the future, such as 2015-01-20 18:47:44.

 

It is still letting in registrants who have the expiry date that is in the past – 2013-01-17 19:43:05.

 

Thanks

 

Marie

Link to comment
Share on other sites

I should have more fully looked at your code before I made my last post. Sorry.

 

Looking at your code though, I can tell you with 100% certainty that that error message is output whenever the number of returned rows is not 1. That could mean 0 or it could mean 2 or more.

I would start by echoing out the value of mysqli_num_rows($r) before the if statement. I would then load phpMyAdmin, and execute a few queries by manually inputting appropriate user names and password hashes and see what the results are.

 

I have a feeling that your SQL query is off, but I can't say that confidently, as I've never used the IF function before.

Link to comment
Share on other sites

Any help is appreciated.

 

When I echo out row 3 with the expired users, I get a 0 but the user is allowed into the site. When the user has a expiry in the future, nothing gets echoed out and I get the usual message that says the user name and password is not on file.

 

So, I would agree that my SQL query if off but it is exactly what is in the book or so it appears. I am certainly willing to try something else but I really don't see what I am missing from the codes that are in the book.

 

I also went into the phpMyAdmin and was getting similar results -

 

SELECT id, username,
TYPE , IF( date_expires >= NOW( ) , true, false )
FROM users
WHERE (
email = '$e'
AND pass = '" . get_password_hash($p) . "'
)
LIMIT 0 , 30

 

 

Marie

Link to comment
Share on other sites

Marie, if you're going to use the SQL console in phpMyAdmin to directly execute a query, you can't use PHP functions and the like. You need to enter the exact values. For example:

 

SELECT id, username, type, IF(date_expires >= NOW(), true, false)
FROM users
WHERE email = 'abc-at-aol.com' AND pass = 'hasiyashashjasdhjasdu128ad88as'
LIMIT 0, 30;

 

In other words, use actual values in the DB for the email and (hashed) password. If you're still getting 0 results, then you query is off. If you are getting results, then your PHP logic is off (for example, the get_password_hash function may not be generating the correct hash value).

As another thought, I've had problems before with not properly quoting field names that coincide with keywords in MySQL. For example, I don't think this is the case, but if type (or is it TYPE?) is a keyword in MySQL, then to specify the field name "type", you need to place grave accent marks around it (i.e., `type`).

 

Please let us know what you find.

  • Upvote 1
Link to comment
Share on other sites

I actually was using the values but an interesting thing was happening that I didn't notice before HOWEVER it is still not working properly.

 

Just to recap a bit -

 

I have been working on this site for several months and not realizing until I was trying to integrate the PalPal that my site was letting in registrants that have an expiry date of yesterday in the database. So all along, I have been logging and and they have been accepted. What I had also been doing was giving everyone the same password to make things easier. The way this site is set up EVERYONE can have the same password but one has to have a different email address.

 

SO when I was just testing I was copying and pasting the SQL query but just changing the email address in the query.

 

Now comes another problem. I was looking at the database and noticed that in the rows that contain the registrants where I had gone in and manually changed the expiry date, the phpAdmin was changing the password. It appears like it was chopping some of it off at the end. I didn't notice this. SO when I was logging in with these registrants they were being rejected because the password and email didn't match - as it should.

 

So I went in and made sure that all the registrants had an email and password that matched and I changed all passwords to something different. I left the expiry dates alone.

 

Now my site is accepting everyone - people with ex expire date and those with a date in the future.

 

When I clicked on the "explain SQL" I received a lot of Nulls plus the following -

 

"Impossible WHERE noticed after reading const table . . . "

 

So at this point there are further concerns, one being if PayPal is integrated and the date is updated or changed will that also change the users password. As I mentioned before, one cannot do a lot of testing when it is live – at least not with the same credit card, since they block you.

 

Also I tried putting quotes around the word type and that didn't seem to make a difference.

 

Thanks,

 

Marie

Link to comment
Share on other sites

Sorry forgot to mention that with the registrants who had an expiry date in the future, they were being let in as they should but I was getting a 1 which I am hoping means that the query was true. With the registrants who had an earlier expiry date the query was returning a 0 but still letting them into the site.

 

Marie

Link to comment
Share on other sites

No I don't. The queries that I made above were directly from the database in the phyMyadmin and I receive a response but I get a

LIMIT 0, 30;.

I hope I am understanding what you mean. If the registrant is expired and not allowed into the site, what kind of response should I be getting? Or what should I be getting if the registrant has an expiry date in the future?

 

Marie

Link to comment
Share on other sites

If the registrant has an expiry date earlier than now, $row[3] will return a 0 for false. If the expiry date is in the future you should get a 1 for true. I'm confused by the LIMIT clause, you are only querying the table for 1 record so what is the LIMIT clause there for?

Link to comment
Share on other sites

Yes, that is what I have been getting a 0 for false and a 1 for true. However, the site is allowing both types of registrant to enter the site.

 

I didn't add any limit clause myself. This is what is returned when I queried the database directly.

 

The following is what I have been entering except that I have been entering the specific email and password. –

SELECT id, username, type, IF(date_expires >= NOW(), true, false) FROM users WHERE (email='$e' AND pass='" . get_password_hash($p) . "')

 

Thanks,

 

Marie

Link to comment
Share on other sites

SELECT id, username, type, IF(date_expires >= NOW(), true, false) FROM users WHERE (email='$e' AND pass='" . get_password_hash($p) . "')

 

when you enter the above query directly into phpadmin with a valid combination of email and password you should get something like this

id username type IF(date_expires >= NOW(), true, false)

3 marie admin 0

where

id=3, username=marie, type=admin, IF(date_expires >= NOW(), true, false) = 0

 

 

 

"Impossible WHERE noticed after reading const table . . . "
means the WHERE clause is not being met by any rows in the table, so that's your first port of call.
  • Upvote 1
Link to comment
Share on other sites

Okay, when I took off everything after the word users I got the kind of response that I should get although it gives me all the rows in the table. I tried many combinations and I could not get it to work when I was attempting to pull out one row.

 

Also, still leaves the problem of all registrants being allowed through no matter what kind of expiry date is in the database and the problem of the password being truncated when the expiry date is changed although I am hoping that might not happen when the update is originated from a script.

 

So I have to keep experimenting.

Link to comment
Share on other sites

Pick a random email and password hash combo and try the following query:

 

SELECT id, username, type
FROM users
WHERE email = 'some-email' AND pass = 'some-password-hash' AND date_expires >= NOW();

 

Do you get what you want?

If so, I think you should just ditch the IF function.

I get a feeling that the problem is that even when the IF function returns false (i.e., the account has expired), you're still getting a returned row, which is correct according to your query, but probably not what you want.

Link to comment
Share on other sites

Thanks for your input. I think I did try something like that previously. I suppose what I need to do is redirect those registrants whose account has expired, to another page.

 

I will experiment with that and get back to the post.

Link to comment
Share on other sites

That's exactly what you want to do. The whole point of checking the expiry date is to handle it gracefully - if the user's account has not expired, show him the content. If the user's account has expired, display a polite message encouraging him to renew his account. I did this example some time ago so can't remember the details precisely but I recall that some content is available to users with expired accounts and the site makes use of a session variable to determine what content a user can access.

 

After your query you need to check the expiry date and if it is false set a session variable - that's what this line does

if ($row[3] == 1) $_SESSION['user_not_expired'] = true;

Are you sure that you are making use of this variable before displaying content?

Link to comment
Share on other sites

There is a slight twist with my particular website. I do not want any of my users to enter without paying first. So I need something to keep them from attempting to enter right away as soon as they have registered. I want them to register and follow up with paying. They still may not do this or may not do this for days later for whatever reason. So the login page as I have set it up now could be realistic, well at least so far. So I think I would be making use of the above variable. As for the logic I may find out it doesn't work when I actually implement the listener script. I am not sure what is going to happen.

Link to comment
Share on other sites

The following code seems too easy but after several different combinations I decided to enter a code that only pulls the data that I really want in the first place. Everyone else is redirected.

 

$q = "SELECT id, username, type FROM users WHERE (email='$e' AND pass='"  .  get_password_hash($p) .  "') AND date_expires >= NOW() ";

 

So as long as the listener script will update the database then the process should work.

 

The phpMyAdmin is still changing the password if I go in and manually change the expiry date so I don't know what is up with that.

 

Marie

Link to comment
Share on other sites

Yes, very weird. It has happened each time, on different days. When I look at the password hash I can see that it is almost cut in half. Something that I wouldn't expect. This is my original code from the Registration page which is almost the same as the book.

 

  $q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, agree, date_expires) VALUES ('" . stripslashes($u) . "', '$e', '"  .  get_password_hash($p) .  "', '" . stripslashes($fn) . "', '" . stripslashes($mi) . "', '" . stripslashes($ln) . "', '$agree', SUBDATE(NOW(), INTERVAL 1 DAY) )";

Link to comment
Share on other sites

 Share

×
×
  • Create New...