Prepared statement changes value of SHA1 vs. non-prepared

[tork]

Here's the non-prepared MySQLi procedural code for registering a user - the appropriate parts are for the password ($p) protection:

 

.

.

    $trimmed = array_map('trim', $_POST);

    // Assume invalid values:
    $fn = $ln = $e = $p = FALSE;
.

.

    // Check for a password and match against the confirmed password:
    if (preg_match ('/^\w{4,20}$/', $trimmed['password1']) ) {
        if ($trimmed['password1'] == $trimmed['password2']) {
            $p = mysqli_real_escape_string ($dbc, $trimmed['password1']);
        } else {
            echo '<p class="error">Your password did not match the confirmed password!</p>';
        }
    } else {
        echo '<p class="error">Please enter a valid password!</p>';
    }
.

(identical code up to here in both scripts - actually, untouched)

.

            $qa = "INSERT INTO nm_users (email, pass, first_name, last_name, active, registration_date) VALUES ('$e', SHA1('$p'), '$fn', '$ln', '$a', NOW() )";
            $ra = mysqli_query ($dbc, $qa) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
.

.

(end this part of script)

 

When logging in to the saved password, it works perfectly. I decided to use prepared statements instead, so here is the equivalent code:

 

.

.

            $pw = SHA1('$p');

            $qa = "INSERT INTO nm_users (email, pass, first_name, last_name, active, registration_date) VALUES (?, ?, ?, ?, ?, NOW() )";

 

            $ra = mysqli_prepare($dbc, $qa) or trigger_error("Query: $qa\n<br />MySQL Error: " . mysqli_stmt_error($dbc));

            mysqli_stmt_bind_param($ra, 'sssss', $e, $pw, $fn, $ln, $a);  

            mysqli_stmt_execute($ra);

            mysqli_stmt_close($ra);

 

(end this part of script)

 

The prepared code vs.the standard code generates a different hex value for the same passwords ($p) even before the prepared statements start! How can this be? There were no changes to the front code. And when logging in after successful registration and activation, the same password used to register is rejected as you'd expect.

 

Does anyone know what's going on here?

 

 

[Ch0cu3r]

Remove the single quotes around $p

$pw = SHA1('$p');

Variables are not parsed inside of single quotes.

 

Also if you're using prepare statements then there is no need to use mysqli_real_escape_string

Edited November 10, 2013 by Ch0cu3r

[tork]

Perfect! Thanks Ch0cu3r.