Secure MySQL Access md5()

4rxsid

Hi all,

I've got:

WinNT
php 4.3.1
phpmyadmin 2.5.6
MySQL 4.0.18-nt

I can connect to my db via my php script using:

$connection = mysql_connect ("localhost", "root", "")

That works no problem.

This will not work however:

$somePass = md5("somePass");
$connection = mysql_connect ("localhost", "someID", $somePass)

gives me:

Access denied for user: 'someID@localhost' (Using password: YES)

I set someID up in the db using phpmyadmin, with it's password using the md5() function. I can see via phpmyadmin that someID does indeed have a 32 byte encrypted password stored.

I can then compare the stored md5 password to the md5 password I'm passing to try to connect...via $somePass. The encrypted passwords match exactly.

Why wouldn't the match be confirmed...allowing me to connect?

Thanks!

tekky

I have never used an encrypted password to connect to MySQL... via the _connect function.... just use the password itself...

$conn = mysql_connect('localhost', 'myusername', 'mypassword');

LordShryku

mysql uses md5 in it's password function when you create a password for a user, which it also uses when you login. By logging in, you are essentially saying

SELECT * 
  FROM users
 WHERE username = '$username'
   AND password = password('$password');

It should also be be noted that using md5 in php and using md5 in mysql will produce two completely different results. So unless you used php to make an md5 hash of the password before creating the user or updating it's password, then this won't work.

4rxsid

thanks for the reply tekky,

I need the connection to be secure and sending the password in plaintext won't meet the requirements I'm bound to at work.

Also...I've been told that using "localhost" is a bad idea and that I should use the I.P. of the machine instead. This however, is a secondary concern at this point since I can even get this to work using "localhost"

any other ideas/suggestions...anyone?

asfaw

I am having the same problem. I am sure there is a way to do it that I looked around could not figure it out.

Asfaw

tekky

Originally posted by 4rxsid
thanks for the reply tekky,

I need the connection to be secure and sending the password in plaintext won't meet the requirements I'm bound to at work.

Also...I've been told that using "localhost" is a bad idea and that I should use the I.P. of the machine instead. This however, is a secondary concern at this point since I can even get this to work using "localhost"

any other ideas/suggestions...anyone?

then I would suggest using https instead of just http, submitted data wont be plaintext then...

beyond that I couldnt think of any other suggestions

rachel2004

Originally posted by 4rxsid
Also...I've been told that using "localhost" is a bad idea and that I should use the I.P. of the machine instead. [snip]

any other ideas/suggestions...anyone?

You'll need to explain why this is a bad idea in relation to your own server set up. The only difference it makes to MySQL whether you specify 'localhost' or an IP address/hostname is whether MySQL will use a Unix socket or TCP/IP to connect to the db. Depending on where your MySQL server is in relation to the rest of your servers in your network, this might be 'local' or 'remote' and may warrant the use of an IP address. However, I don't see how either is "more bad" than the other.

Like tekky said, you can either use SSL to encrypt the path, a VPN if you want a tunnel, or you can go back to your idea of using a hashing algorithm. Those are your only choices. Some people get stuck on one way of solving a problem because that's they only thing they have experience with, but if you're looking for options, it comes down benefit-to-cost ratio for you company. If your company is not willing to pay for a digital certificate or introduce a VPN, you may as well focus on the hashing algorithm.

MarkR

You need to use the correct password when logging on the MySQL - calling md5() will change it into a string of rubbish which won't be right.

Using "localhost" to connect to mysql is VERY GOOD idea. This is because MySQL treats localhost slightly differently, and:

Uses a different (more efficient) communication library where relevant (Unix sockets / WinNT named pipes rather than IP)
MySQL security can be set to only allow connections from localhost
It won't be affected by firewalls etc
You can start mysql with the non-listening option and still connect

Mark

asfaw

Thank you all for you help. It was the way I configured Apache2, PHP5, and MySQL that gave all the problem. All seems fine now.

Asfaw

sfullman

Hi All,

I need to be able to generate, IN PHP, the same password string that I see in the mysql.user table

This was the closest thread I found on my search.

I have a database access front end for users. It sits on top of mySQL and is a commercial product.

I'm considering tying the login table with mysql.user so that the password can be stored in one place.

Looking at the mysql.user table, I notice that the password is 16 not 32 bytes long, AND it's not md5() because

(using php) md5('string') !== (using mysql) md5 | password('string')

How do I do this?

tekky

from my experiences I have always had to do this to match a MD5 string in MySQL with PHP

SELECT MD5($var) as pass
//or
$pass = md5($pass);
insert into table VALUES('$pass');
SELECT pass FROM table WHERE pass=$pass

in otherwords ONLY using PHP md5() or only using MySQL md5()