Error in mysql query?

[hooligan2001]

Hey guys/gals i have a simple login script in a flash file that sends a user name and password to a php script and the php script processes it and sends back a response. Now this doesn't doesnt find a user.

<?php
    include "conn.php";

    $username = $_POST['user'];
    $pass = $_POST['pwd'];
    
    if(isset($username) && isset($pass)) {
        $query = "SELECT * FROM members " .
                 "WHERE user_name = '$username'" .
                 "AND password = (PASSWORD('$pass'))";
        
        $result = mysql_query($query)
            or die(mysql_error());
        
        if(mysql_num_rows($result) == 1) { 
            echo "msg=hello $username we found you";
        }else{
            echo "msg=Invalid username or password";
        }
    }
?>

And this one does find a match when i ommit the password search?

<?php
    include "conn.php";

    $username = $_POST['user'];
    $pass = $_POST['pwd'];
    
    if(isset($username) && isset($pass)) {
        $query = "SELECT * FROM members " .
                 "WHERE user_name = '$username'";
                 //"AND password = (PASSWORD('$pass'))";
        
        $result = mysql_query($query)
            or die(mysql_error());
        
        if(mysql_num_rows($result) == 1) { 
            echo "msg=hello $username we found you";
        }else{
            echo "msg=Invalid username or password";
        }
    }
?>

All passwords are encrypted before they are stored using the mysql PASSWORD. So i was wondering if i have written the query wrong?

[jonathan67]

How are the passwords encrypted?
If you are using MDA5 or SHA1 then you should put a var like this in your script before the sql statement
$pass = MDA5($_POST['pwd']);
or
$pass = SHA1($_POST['pwd']);

But having reread your post properly I see you are using the MySQL password function
the MySQL manual says you should do this - (same principle - different word) in the SQL statement
"AND password = PASSWORD($_POST['pwd'])";

Or if you are using a New version of mySQL try this
"AND password = OLD_PASSWORD($_POST['pwd'])";

As the password function has been updated in the 4.1 version of the app

Of course this could all be rubbish, let us know how you get on.

Jon 8O)

[hooligan2001]

I actually included it in my sql query and not using php sha1 or the other one.
Like this

$sql = "INSERT INTO confirm (valid, user_name, password, email, first_name, last_name, gender, address, suburb, city, postcode, country) " .
                       "VALUES ('$msgid', '$username', PASSWORD('$password'), '$email', '$firstname', '$lastname', '$gender', '$address', '$suburb', '$city', '$postcode', '$country');"; 

Thats how it is inserted ? It should retrieve in the same way. I was thinking it my be the way i have written my SELECT query?

[hooligan2001]

but if i get rid of the password part and change it to

<?php
    include "conn.php";

    $username = $_POST['user'];
    $pass = $_POST['pwd'];
    
    if(isset($username) && isset($pass)) {
      $query = "SELECT user_name, password FROM members " .
           "WHERE user_name = '" . $_POST['user'] . "' " .
           "AND password = '" . $_POST['pwd'] . "'";
        
        $result = mysql_query($query)
            or die(mysql_error());
            
        if(mysql_num_rows($result) == 1) { 
            echo "authen=ok&lvl=10&msg=hello $username we found you";
        }else{
            echo "authen=no&msg=Invalid username or password";
        }
    }
?>

and then past the encrypted one into the password box in the .swf
eg
*77BBA70E8B25F2F5293F28592BE194ADC08178F6
it works so obviosly its the password part.

[hooligan2001]

Hahah stupid stupid me, Just worked it out. How my register page works is it first stores the users info in a temp database and then sends an email and the user confirms that and it then moves it to the members table. But what i did wrong was encrypt the data in the confirm table and then ecrypt that again when i moved it over. So when it check the password against the one in the database ofcourse it wouldnt match. Phew.

Now how do i delete this dam thread