Jump to content
Larry Ullman's Book Forums

Login - Date Expires - Knowledge Is Power


Recommended Posts

Hi,

I just started reading this book the other day and came across this topic as the latest post, and think I can share some insight based on experience. I think there are multiple issues which I will address:

 

First if the password is being garbled, check the version of mySQL and check the mySQL server settings for the password hashing mechanism as well as any PHP settings that might affect the communication between the Apache server and the mySQL server. If the system is setup to hash using the older password hashing function, the hash will be shorter and less secure. It sound like this might be the case, and you need to explicitly configure phpmyadmin to use identical password hashing algorithms if you edit any rows in order for it to work with your server code.

I ran into this problem years ago after my site was updated from previous version of both PHP & mySQL server. It was solved by setting up a function to allow login with old passwords, and expire them, thus forcing users to update their passwords.

 

Second, during mySQL queries avoid logic tests such as the IF statement because that pollutes the separation between the data access model and the logic of the program. Instead, query for the presence of the name and password, and have the expiration date row returned. Then use PHP’s built in date function to test to see if the expiration date has passed. The problem with the technique outlined in your code is a lack of detail of the type of failure in returned data, leading to an obfuscation of the real issue. For instance: you are testing 3 thing: name & pass & expiration. If any of these are false the entire query fails and no one can log in. No matter which is incorrect, your logic does not expose which column is false. The result is that you cannot offer any meaningful help to either the end user because your logic does not show you which column is not being matched.

 

My suggestion:

Instead, do a name query, then a name and pass query, then an expiration query. Upon any failures assign a $failure variable either 1, 2 or 3, if all queries are successful, $failure = 0. Then return that result ($failure) from the function.

 

Then construct a error function that takes the $failure result and returns the appropriate error message:

1 or 2 = "the name and or password is invalid"

3 = "your account has expired, please click here to renew it."

0 = (allow entry to the site and redirect to whatever landing page you wish upon successful login.

 

If you are concerned about the performance hit making 3 queries instead of just one, use a persistent connection to the mySQL server by utilizing a data access class such as mysqli, or a custom built data access class. In my experience the biggest performance hit comes from making multiple connections, not from the queries themselves, unless they return a very large data set.

 

Cheers.

Link to comment
Share on other sites

mnoi, thanks for the help, but I'm going to have to disagree with most of what you say. 

 

First, it's been years since MySQL changed the hashing algorithm used by its PASSWORD() function. Also, it's been years since anyone has used PASSWORD() for hashing non-MySQL user passwords. That reference is a red herring, in my opinion. 

 

Second, using three queries to debug this problem is fine, but you would not want to use three queries on a live site. One reason is that you want the database to do as much work as possible. Pulling a date out of the database, turning that into a PHP date variable, and then using that variable is going to be much slower and more likely to introduce bugs. 

 

Third, using persistent connections is a really bad idea. Yes, a script should never make multiple connections, but a script can use the same connection for multiple queries. You might think that a persistent connection is a good answer, until you have 20 simultaneous requests for a PHP page and have only allocated 15 persistent connections, and so 5 PHP requests have no database access at all (as opposed to a slow database access). The only situation in which you should use persistent connections is one in which you have great server administration knowledge and ability and can make the proper decisions as to how many connections should be available based upon the demands of the site, the available server resources, etc.

 

Again, I appreciate that you're trying to help and don't want to dissuade you from trying to help anyone again, but it's important that the original poster here knows that your answers won't help this particular situation.

Link to comment
Share on other sites

mnoi, thanks for the help, but I'm going to have to disagree with most of what you say. 

 

First, it's been years since MySQL changed the hashing algorithm used by its PASSWORD() function. Also, it's been years since anyone has used PASSWORD() for hashing non-MySQL user passwords. That reference is a red herring, in my opinion. 

I have seen older technology in the wild than ancient versions of mySQL. I offered it as a potential cause to look at, not the cause itself. More than likely it is 2, incompatible ways to encode the password, and old style passwords is one possibility. Rather than rule out what it might be based of what it shouldn’t be, I prefer to verify that it is not something that shouldn’t be before I check it off the list — especially if checking takes less than 5 minutes. 

 

Second, using three queries to debug this problem is fine, but you would not want to use three queries on a live site. One reason is that you want the database to do as much work as possible. Pulling a date out of the database, turning that into a PHP date variable, and then using that variable is going to be much slower and more likely to introduce bugs. 

 

Third, using persistent connections is a really bad idea. Yes, a script should never make multiple connections, but a script can use the same connection for multiple queries. You might think that a persistent connection is a good answer, until you have 20 simultaneous requests for a PHP page and have only allocated 15 persistent connections, and so 5 PHP requests have no database access at all (as opposed to a slow database access). The only situation in which you should use persistent connections is one in which you have great server administration knowledge and ability and can make the proper decisions as to how many connections should be available based upon the demands of the site, the available server resources, etc.

 

Again, I appreciate that you're trying to help and don't want to dissuade you from trying to help anyone again, but it's important that the original poster here knows that your answers won't help this particular situation.

I use persistent connections in a php class that terminates once the script queries are returned, so each person has the server’s attention for a second or two at best. The connection does not persist if there is not object requesting the data. Knowing my average volume and peek volume gives me the luxury to to do this, and my hosting company likes that I keep their server load light with persistent, but conservative connections rather than most of the load being used to establish connections.

 

If the dev is told exactly what the problem is (1), instead of “there’s a problem” then it is more efficient than (1) get the notice, then (2) looking for the problem, (3) finding it, then (4) fixing it. He or she can skip steps 2 and 3 in this case. Since Marie is beginning, it would be more valuable to write test code that is clunky, but works reliably, than to refine it rather than have it not work to begin with. Having an instruction manual give examples that do not work or following advice that delays learning take the most momentum out of a person’s desire to learn than anything else in written instructions, IME.

 

Looking at this once again, I realize that instead of building up a query she could go with a more efficient graceful degradation of queries, meaning that she could do the reverse order of what I first wrote:

Check if A, B & C are true, then if not, Checking if at Least A & B are true which would return the error that the account is expired. While it is true the DB could do the expiration checking internally faster, the error handling would probably be handled more gracefully in PHP since SQL select returns are either rows of data, 0 rows or an error message. 

Sure she could write a stored procedure, which would be faster by returning the expired flag and have php handle the choices a user could make at that point. 

 

Larry, I want to be clear that while we might disagree on this topic, I want to thank you for writing this book. I have read your previous books on other topics and have found your writing an excellent example of clarity, conciseness without being to dry and academic. I have recommended your books to a friend learning PHP, and when I was searching for a good book on e-commerce, when your name came up, I chose this title because I knew I would get the best exchange of time for useful information.

 

Thanks.

Edited by mnoi
Link to comment
Share on other sites

  • 2 weeks later...

I have discovered that when I go into the phpMyAdmin - version 2.8.0.1and manually edit ANY of the columns, the password hash is always shortened.

 

I called my web host people and they say that it doesn't have anything to do with them. It is the original code.

 

However, I have discovered that when I add a script that updates the expiry date, the password does not change, just the same as it does not change when I update any of the other user profile information which my user has the option to do on another page.

 

So, I guess as long as I don't have to change anything manually then my user's password will be okay. Otherwise, I will have to ask them to go to the Login page and use the "Forgot Password" link to change their password back to the original. I don't think they should have to do this but I have looked at all the php code and don't know what could be wrong.

 

Marie

Link to comment
Share on other sites

If the password is being shortened every time you edit a column directly from phpMyAdmin, then the issue is not with your PHP code, but in how you have your DB structured.

I could be wrong, but I don't think that that sort of thing is supposed to ever happen with a properly formatted DB. As such, I'm tempted to believe that your DB is improperly structured, and that is the source of this problem.

Link to comment
Share on other sites

The following is how my table is structured. The collation is utf8_general_ci.

 

Field  id  Type  tinyint(3)

Field  type  Type  enum('member', 'admin')

Field  username  Type  varchar(30)

Field  email  Type  varchar(80)

Field  pass  Type  varbinary(32)

Field  first_name  Type  varchar(80)

Field  mid_initial  Type  varchar(20)

Field  last_name  Type  varchar(80)

Field  active  Type  tinyint(3)

Field  agree  Type  varchar(10)

Field  date_expires  Type  datetime

Field  date_created  Type  timestamp

Field  date_modified  Type  timestamp

 

Thanks

Link to comment
Share on other sites

This is what is in the mysql.inc.php script.

 

return mysqli_real_escape_string ($connect, hash_hmac('sha256', $password, 'c#haRl891', true));

 

The passwords seem to be working fine when my user logs in, changes the password etc.

Link to comment
Share on other sites

Just to clarify, the password hash value is only changing when you edit the table directly from phpMyAdmin, right?

And just to further clarify, the password hash is always getting shorter, right?

Does it always get shorter by the same number of characters? Does it keep getting shorter until the password hash is nothing? Is there a limit? Is the password hash simply being concatenated, or is it getting shorter and changing?

The more info you can provide, the better. Thanks.

 

I would like to test this in my own environment, so could you please provide the SQL needed to create the table in question and (if you don't mind) please provide some sample data so that I can test things in my local version of phpMyAdmin?

Thanks.

Link to comment
Share on other sites

Yes, the password has is changing when I edit the table directly from the phpMyAdmin and the password gets shorter. It is only doing this once though. So if I go in and change the username it changes, but if I go in and then change the person's email or name or whatever, it stays at the shorter version. It does not get any shorter than it does with the first edit. I tried copying and pasting the longer version back in but it won't accept it so the user has to log back in and click on the "Forgot Password" link. The password is always shortened the same way with every user.

 

The following is the longer proper version

 

I g@i~4J~v7

 

The following is the shorter version

 

I g@

 

The following is how the table should be structured - this should be close to what it is in the book.

 

CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` ENUM('member','admin') NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(80) NOT NULL,
  `pass` VARBINARY(32) DEFAULT NULL,
  `first_name` VARCHAR(80) NOT NULL,

  `mid_initial` VARCHAR(80) NOT NULL,
  `last_name` VARCHAR(80) NOT NULL,

  `active` TINYINT(3) NOT NULL,

  `agree` VARCHAR(10) NOT NULL,
  `date_expires` DATE NOT NULL,
  `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

As for data, anything entered would be what is entered in the Registration page. All of the fields have to be filled out or the user does not get registered. I looked over that code and I do not see anything in there that would seem to interfere. I am giving all my fake users the same password. This site is set up that way - everyone can have the same password. I guess it is the email that the code is checking.

 

So would it have to do with the "date created"? I just tried it and changed the date created to the current date and time - if that is possible and the table still did the same thing - shortened the password.

 

Don't know what else I can provide. The phpMyAdmin is provided by my Web Hosting company.
 

Link to comment
Share on other sites

My suspicion is that phpMyAdmin is not displaying the binary data properly. For example, the field is VARBINARY(32). phpMyAdmin may be displaying this as a text input that's 32 characters long, which can display half of the binary data. Then the update uses those 32 characters for the value. It's not a problem again, because the password has already been cut in length. That's what I think is happening.

Link to comment
Share on other sites

I don't know if this is a bug in phpMyAdmin, in which case upgrading it would fix the problem. Or maybe there's a setting in phpMyAdmin to have it not display form elements for binary data. Or you could use straight SQL commands (UPDATE...) within phpMyAdmin. Or you could create your own administrative page that would do this properly for you. Personally, I'd use straight SQL commands or create a new page for taking care of updating users and not worry about phpMyAdmin. It's not the right tool for regular, day-to-day management of a site anyway.

Link to comment
Share on other sites

 Share

×
×
  • Create New...