Turning mysql field into 'password' ruins login

[kilbey1]

Hi all...

I've created a simple authentication using $PHP_AUTH_USER and $PHP_AUTH_PW. My passwords are just plain text however, because when I tried to turn them into passwords through MySQL, iti would no longer accept it as a valid password - as if it was looking for the LITERAL encrypted password as the name.

What gives?

[planetsim]

Wierd that. Have you tried the SQL in PHPMyAdmin to see if it works there usually if it doesnt work there its the SQL problem.

[kilbey1]

I'm actually using phpmyadmin to set the field as a PASSWORD field. It definitely changes it into an encrypted password. But the PHP app then won't allow logins to occur.

Example in plaintext:
eve / eve555
(login works with eve/eve555)

Encrypted:
eve / sj5T3i4r
(can't login using eve/eve555)

[planetsim]

Can we see the SQL from the PHP, might be that the password isnt being encrypted at all when going through the query.

[kilbey1]

Ok, well after doing some testing, my theory (and yours) is correct... it's not encrypting. When I put in eve/eve555, it doesn't go in...but putting in eve/0ba781055deb43a7 DOES work. 😛

Ok, the SQL looks like so in the code:

    // Formulate the query 

$sql = "SELECT * FROM users WHERE 
        username = '$PHP_AUTH_USER' AND 
        password = '$PHP_AUTH_PW'"; 

// Execute the query and put results in $result 

   $result = mysql_query( $sql ) 
        or die ( 'Unable to execute query.' ); 

// Get number of rows in $result. 

$num = mysql_numrows( $result ); 

if ( $num != 0 ) { 

    // A matching row was found - the user is authenticated. 

    $auth = true; 

} 

And I have attached the prcoess I went through in phpmyadmin to make the password a password. Find the zip file attached.

[planetsim]

You havent encrypted the password your sending try this SQL

$sql = "SELECT * FROM users WHERE 
            username = '".$_SERVER['PHP_AUTH_USER']."' AND 
            password = 'PASSWORD(".$_SERVER['PHP_AUTH_PW'].")'"; 

[tsinka]

Hi kilbey1,

besides the missing PASSWORD function (planetsim's post) in your query there is one additional problem. The way PASSWORD encrypts texts may change. I had the problem that I migrated from one of the 3.x MySQL versions to a recent 4.0.x MySQL version and authentication didn't work anymore since the PASSWORD algorithm had changed.

I chose to switch to md5 instead of PASSWORD.

Thomas

[kilbey1]

Alright, after trying the password function from planetsim - no username and password combo works. 🙂 I tried it with, and without, the $_SERVER global.

So my next step was to find out what version of MySQL my ISP is running: 3.23.58.

Version of PHP: 4.1.2.

Register globals: ON

I tried md5 as well, same result.

Why the heck wouldn't the password function work on my encrypted password? Can my ISP limit my ability to encrypt passwords?

[tsinka]

I think,

there is an error in planetsim's code.

Try the PASSWORD function without ' around it:

$sql = "SELECT * FROM users WHERE 
            username = '".$_SERVER['PHP_AUTH_USER']."' AND 
            password = PASSWORD('".$_SERVER['PHP_AUTH_PW']."')";

Using md5 means to also use md5 when storing the password.

$sql = "INSERT INTO users (..,password) VALUES (....,'".md5($pass)."')";

Thomas

[tsinka]

Hmmmm,

you might also use the MD5 function integrated into MySQL:

$sql = "INSERT INTO users (..,password) VALUES (....,MD5('$pass'))";

[kilbey1]

Excellent. Now, my PASSWORDed password is accepted, while none of the plain-text passwords are. Not a problem to change those.

If I encrypt my password with md5 instead of password, will this still work the way I want to? Or do I have to use a 'md5' function instead of 'password' as you've shown?

Thanks for the info on inserting passwords as md5 - haven't built that portion yet, but I would not have guessed to do this.

[tsinka]

Yes,

you would have to use a md5 function instead of PASSWORD. But recent MySQL versions support using MD5 in the query like in my last post.

The SELECT would look like:

$sql = "SELECT * FROM users WHERE 
            username = '".$_SERVER['PHP_AUTH_USER']."' AND 
            password = MD5('".$_SERVER['PHP_AUTH_PW']."')";

Use the PHP md5 function if your MySQL version doesn't support MD5 natively.

Thomas

[kilbey1]

Really does help to look at the PHP functions. 🙂 md5 did the job.

	$sql = "SELECT * FROM users WHERE
            username = '".$_SERVER['PHP_AUTH_USER']."' AND
            password = md5('".$_SERVER['PHP_AUTH_PW']."')";