MySQL SHA() doesn't work

问题:

So basically I encrypted password in my registration form like so:

    $query = "INSERT INTO users(first_name, last_name, email, password, 
                         username) VALUES ('$fn', '$ln', '$em', SHA('$pw1'), '$un')"; 

Now the password is hashed, but when I try to use it in my login script it doesn't want to work and function mysql_num_rows returns 0.

    <?php
                ob_start();
                    //If login button is pressed
                    if(isset($_POST['submitted'])){
                        //Username clean up
                        if(preg_match('%^[a-zA-Z0-9_-]{6,20}$%', stripslashes(trim($_POST['username'])))){
                            $u = escape_data($_POST['username']);
                        } else {
                            $u = FALSE;
                            echo '<p><font color="red" size="+1">Please enter valid username</font></p>';
                        }

                        //Password clean up
                        if(preg_match('%^[a-zA-Z0-9_-]{6,20}$%', stripslashes(trim($_POST['password'])))){
                            $p = escape_data($_POST['password']);
                        } else {
                            $p = FALSE;
                            echo '<p><font color="red" size="+1">Please enter valid password</font></p>';
                        }

                        //Check if both matched
                        if($u && $p){
                            $query = "SELECT * FROM users WHERE username='$u' AND password=SHA('$p')";
                            $result = mysql_query($query);
                            $count = mysql_num_rows($result);
                            $row = mysql_fetch_array($result, MYSQL_NUM);
                            if($count != 0){
                                $_SESSION['username'] = $row[1];
                                $_SESSION['password'] = $row[3];
                                header("Location: login_confirmed.php");
                            } else {
                                echo "Wrong username or password!";
                            }
                        } 
                    }
                    ob_end_flush();
                ?>

回答1:

It's probably because of your password field type. Is your password field VARCHAR? How long is it? It seems to me that SHA is generating a string longer than it is allowed by the field so it gets cutoff when it is stored and it does match when you regenerate it to check.

MySQL documentation says that you need 40 characters to store the output of SHA.

Calculates an SHA-1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hex digits, or NULL if the argument was NULL. One of the possible uses for this function is as a hash key.