Check if The user Already Exists mysqli Prepared

Question

im trying to get make the registration form check if user already exisits then give error else make the the account but somehow nto working (im little bit newbie to prepared statements) help me please thanks. And please don't downvote , i know there are already answer for this , i read them but not helped me.

<?php
// Include database connection and functions here.
include 'db_connect.php';
include 'functions.php';

// The hashed password from the form
$password = $_POST['p'];
// Create a random salt
$random_salt = hash('sha512', uniqid(mt_rand(1, mt_getrandmax()), true));
// Create salted password (Careful with the chilli)
$password = hash('sha512', $password.$random_salt);
$username = $mysqli->real_escape_string($_POST['username']);
$email = $mysqli->real_escape_string($_POST['email']);
    $stmt = $mysqli->prepare("SELECT * FROM members WHERE username = ? LIMIT 1"); 
    $stmt->bind_param('s', $username);
    $stmt->execute(); // Execute the prepared query.
    $stmt->store_result();
     if($stmt->num_rows == 1) {
        header("Location: '..\..\..\?registrationfailed=1'");
    }
    else
    {
    if ($insert_stmt = $mysqli->prepare("INSERT INTO members (username, email,     password, salt) VALUES (?, ?, ?, ?)"))
{
$insert_stmt->bind_param('ssss', $username, $email, $password, $random_salt);
// Execute the prepared query.
$insert_stmt->execute();
header("Location: '..\..\..\?success=1'");

}
else
{
header("Location: '..\..\..\?registrationfailed=1'");
}
    }

?>

Please explain in brief what is _not working_ for you. Post the error messages if possible. — Shankar Narayana Damodaran, Apr 06 '14 at 09:47
`mysqli` is not executing the function check if user exists and directly registring the user . — user3499463, Apr 06 '14 at 09:49
Check `var_dump($username);` after this `$username = $mysqli->real_escape_string($_POST['username']);` — Shankar Narayana Damodaran, Apr 06 '14 at 09:52
yup the `$username = $mysqli->real_escape_string($_POST['username']);` was the problem now working but why real_escape is not working? — user3499463, Apr 06 '14 at 09:54
[`Enable Error Reporting`](http://stackoverflow.com/a/6575502/1003917) — Shankar Narayana Damodaran, Apr 06 '14 at 09:56
You shouldn't escape anything manually when using bind vars, because binding escapes it for you, so you're double escaping — Mark Baker, Apr 06 '14 at 10:01
@MarkBaker okkayyyy got it :D ,,, i was still stuck on mysql escaping things didn't know about binding escaps — user3499463, Apr 06 '14 at 10:04
any of you post something as answer so i can check it as answerd — user3499463, Apr 06 '14 at 10:09

score 1 · Accepted Answer · answered Apr 06 '14 at 10:27

You're using mysqli_real_escape_string() in data that is passed to the MySQL server as a variable for a prepared statement. This won't work.

When you escape a string the function prepends an escape character to single- and double-quotes (amongst other characters). When this is assembled into a query the SQL parser interprets the escaped characters as you intend them.

With a prepared statement the query is separated from the variables. The prepared phase passes the query to the server which parses the query and prepares an execution plan.

When you bind the variables and execute the statement the variables are passed to the server and substituted in the execution plan (not the query). The variables are passed unescaped since the parser won't be parsing them. If you've already escaped them the data you pass includes escape characters that are then not interpreted by a parser. As a result your query will possibly fail.

This gives you protection against SQL injection because the mechanism by which SQL injection operates doesn't exist. This is a useful side effect of the real purpose of prepared statements, which is to parse and prepare a query once and then execute it repeatedly with different variable data.

Check if The user Already Exists mysqli Prepared

1 Answers1