Help me help users identify themselves properly to mysql
September 29, 2007 4:41 PM Subscribe
How do I pass this password in the correct form to MySQL?
posted by Tuwa to computers & internet (12 answers total) 2 users marked this as a favorite
O'Reilly's PHP & MySQL proposes an authentication system with a login form which collects data, sends it to logincheck.php, hashes the password with md5(trim()), and checks it and the username against a function in authenticate.php.
authenticate.php uses the user 'lucy' to check authentication.users for a single correct user/password combination, allows access if there is one and only one match, and redirects the user to login.php if not.
Additional user data is stored in the database 'mysql'. The password there is not hashed with md5 but with the password() function.
I can pass the authentication challenge, getting the message that I am logged in as the user I want to be logged in as, but I can't actually interact with the database. What I get instead is the message "Access denied for user 'Oscar'@'localhost' (using password: YES)."
After far too long thinking about it I've realized that I can't interact with the database because I've got the session password stored as an md5 hash and am passing it back to the other pages, hashed, for all database interactions after login. (I think that's right--it seems intuitively right, since I definitely don't want everyone logged in as root and since the whole point of having additional users is to be able to grant and revoke permissions).
The trouble (I think) is that the mysql table doesn't expect an md5 hash; it expects whatever encryption MySQL's password() function uses.
The password() function works in MySQL but not in PHP.
I can't help feeling that this is an elemental question since the book didn't even go to the trouble of explaining how it's done, but it's been deviling me for the last few days and every site I've consulted seems to take it for granted that people know how to do this. And, well, I don't. So: how do I get the password in the form that MySQL wants it so that the password will match?
Also, is this three-table really the best structure for all this data, and if so, why? With a main database, an authentication database to check against, and an mysql database governing permissions it seems like it would be a chore to add and remove users as necessary. Is this structure necessary because of a security concern? Would it be okay to move the authentication details into the main database and grant 'lucy' SELECT on only that one table?