Help me help users identify themselves properly to mysql

September 29, 2007 4:41 PM

How do I pass this password in the correct form to MySQL?

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?

posted by Tuwa to computers & internet (12 answers total)

"Access denied for user 'Oscar'@'localhost' (using password: YES)."

Means that you are failing to log into the database at all. Are you hashing your MySQL password before you send it to the database? If you're doing that, MySQL will re-hash it to compare it to the stored value for that user, and it will fail.

Again, this isn't on your login script, this is on your mysql_connect() details. Make sure you have the right password, and that you're not hashing it before passing it.
posted by disillusioned at 4:47 PM on September 29, 2007

Interesting. The book lists connection checks like if (!($connection = @ mysql_connect("localhost", "fred", "shhh")))
showerror();, but I can't leave that hard-coded, else every user is authenticated as, say, "Oscar," but actually interacting with the database as "fred."

How do you pass the plaintext password back to all pages after login without storing it as a session variable in plaintext? I'd gotten the impression from a metatalk thread awhile back that plaintext passwords was a bad bad bad idea.
posted by Tuwa at 4:56 PM on September 29, 2007

...else every user is authenticated as, say, "Oscar," but actually interacting with the database as "fred."

Normally you'll have a fixed login or set of logins that your PHP scripts use, and if you implement user authentication yourself with your own users table, not the mysql.* tables.
posted by Khalad at 5:05 PM on September 29, 2007

How do you pass the plaintext password back to all pages after login without storing it as a session variable in plaintext? I'd gotten the impression from a metatalk thread awhile back that plaintext passwords was a bad bad bad idea.

That's the point of generating the MD5 hash. You're only passing a temporary session authentication token to the application, which--if written correctly--will always cross check to see that the token you pass to it corresponds to the hash which was generated from the original password authentication stage.

In other words, you need to store the temporary session auth hash in a table, in order to be able to do this crosschecking.
posted by melorama at 5:07 PM on September 29, 2007

I may be misunderstanding what you're asking, but I think you're having trouble abstracting the actual MySQL connection with any application-level authentication system.

For example, I have a web site where users can log in to edit their profile. Their user names and passwords are stored, the passwords are md5 hashed and that's that. But in order to retrieve any data from the database, I have to log in through mysql_connect with my MySQL user name and password; different from any application-level authentication schemes I may have built.

You have no reason to pull your password back to the pages. The script will run, the MySQL connection will be established and authenticated and that will be that. Typically, you store this in a configuration file of sorts that is outside of the web root, built into a PHP file that returns nothing and is only plaintext there.

Moral of the story: When creating a user system, you only EVER have one single MySQL user. The database has its own set of users, permissions and authentication and your application should have its own separate layer. The MySQL user is only ever interacted with on the backend, and should never be visible to the user at all.

Now, if your application is being built to interact directly with MySQL, ala phpMyAdmin, then you'll use MySQL's tables for user management. But those are already built natively...
posted by disillusioned at 5:07 PM on September 29, 2007

I think I'm definitely not understanding. Fred, Barney, and Wilma all login with their own credentials but every page interacting with the database passes the hardcoded credentials "Betty" and "Bettyspassword" instead? Is that right?

What are the distinct users in MySQL for, then? What condition would prompt someone to grant or revoke update, select, and so forth?
posted by Tuwa at 5:40 PM on September 29, 2007

Yes, almost certainly you want to be having authentication and access control in the app itself, not faffing about making MySQL users for people.

Note, you should always salt your passwords, ala:

salt = randomstring();
hashpw = md5(salt + password);

Then store the hash and salt. This ensures that 100 users all using "password" don't get the same stored hash, and makes brute force and time-memory trade-off attacks much more difficult.
posted by Freaky at 5:48 PM on September 29, 2007

MySQL users are access control for talking to MySQL directly; e.g. a hosting company might give each account on a shared server a seperate MySQL user with access to their own database, or a single user might allocate different applications their own accounts to keep them isolated from each other.

This probably isn't the system you want to be interacting with when providing accounts to people on a web application -- the web application authenticates itself with the database, and from there it has its own authentication system for the application's users, based on tables it manipulates in its own database.
posted by Freaky at 5:54 PM on September 29, 2007

Wow, this sounds much easier than what I'd had in mind, and also makes me wonder what other assumptions I'm making which are fundamentally flawed.

The book did mention salting the hash, but I hadn't yet understood how to reproduce a random salt on login (I'd thought that there wouldn't be much point in storing the salt with the hashed password if the table itself is vulnerable).

Thanks for the clarifications, everyone.
posted by Tuwa at 6:10 PM on September 29, 2007

What are the distinct users in MySQL for, then? What condition would prompt someone to grant or revoke update, select, and so forth?

The "distinct users" (i.e, the Users table in the "mysql" database), for all intents and purposes, work under the same concept as a Unix password file. There is always at least one superuser, who can do anything with the system, but you can also delegate other users who can still have access, albeit in a limited way that you, as the superuser, define.

So, for example, you can have a "distinct" MySQL user that only has select privledges, but no update privledges.

What I think is happening here is that you're confusing "MySQL users", with "People who need to access data which is stored in the MySQL database". The former is a backend, MySQL system level user, and the latter is a front-end, application-level user (i.e. the data that your PHP/Perl/Rails etc etc app needs to work).

The PHP mysql_connect() directive is referring to the MySQL system level user...in other words, a user defined in the "user" table, and NOT a front-end "user" for your web-app.

It's a bit confusing, i know, but maybe this page will do a better job than I can explaining it.
posted by melorama at 6:12 PM on September 29, 2007

What I think is happening here is that you're confusing "MySQL users", with "People who need to access data which is stored in the MySQL database".

Yes, that was exactly it.
posted by Tuwa at 6:16 PM on September 29, 2007

I'm salting the hashes now, but you know what security risk only now occurred to me? The system I'm using would still be sending the passwords in plaintext to be processed by the server.

Looking into encryption, public keys, etc. now, and wondering if there isn't some simpler secure possibility I'm missing.

I vastly underrated the complexity of this project when I started it. ^_^
posted by Tuwa at 9:56 AM on October 6, 2007