Can't get my old PHP registration code working with prepared statements.

[kielly32]

Why is coding so much more easier without prepared statements than with? - I just can't seem to grasp how to code with prepared statements but I need my website to be safe from MySQL injection. Escape strings, check. I got them down path.

 

This is my code that just doesn't work: I'm sure there's multiple errors, but it's edited from a code that did work perfectly - besides the fact that it was exploitable.

It throws me Binding paramaters failed:(0)Prepare failed: (0)

<?php
/* Registration process, inserts user info into the database 
   and sends account confirmation email message
 */

session_start();

// Set session variables to be used on profile.php page
$_SESSION['email'] = $_POST['email'];
$_SESSION['first_name'] = $_POST['firstname'];
$_SESSION['last_name'] = $_POST['lastname'];

// Escape all $_POST variables to protect against SQL injections
$first_name = $mysqli->escape_string($_POST['firstname']);
$last_name = $mysqli->escape_string($_POST['lastname']);
$email = $mysqli->escape_string($_POST['email']);
$password = $mysqli->escape_string(password_hash($_POST['password'], PASSWORD_BCRYPT));
$hash = $mysqli->escape_string( md5( rand(0,1000) ) );
$igname = $mysqli->escape_string($_POST['igname']);
$profileurl = $mysqli->escape_string($_POST['profileurl']);
$rules = $mysqli->escape_string($_POST['rules']);
$username2 = $mysqli->escape_string($_POST['username']);

      
// Check if user with that email already exists
if(!($stmt = $mysqli->prepare("SELECT * FROM users WHERE email='?' OR username='?'"))){
 echo "Prepare failed: (" . $mysqli->errno . ")" . $mysqli->error;
}

if(!$stmt->bind_param('ss', $email, $username2)){
     echo "Binding paramaters failed:(" . $stmt->errno . ")" . $stmt->error;
}

if(!$stmt->execute()){
     echo "Execute failed: (" . $stmt->errno .")" . $stmt->error;
}

if($stmt->num_rows > 0) {
        $_SESSION['message'] = 'User with this email already exists!';
    	header("location: error.php");
    	exit();
}elseif ($stmt->num_rows > 0){
	$_SESSION['message'] = 'User with this username already exists!';
    header("location: error.php");
    exit();
}
else { // Email doesn't already exist in a database, proceed...

	//define the receiver of the email
$to = 'kielly@website.ca';
//define the subject of the email
$subject = 'NEWUSER'; 
//define the message to be sent. Each line should be separated with \n
$message = "Someone has registered"; 
//define the headers we want passed. Note that they are separated with \r\n
$headers = "From: general@website.ca\r\nReply-To: webmaster@example.com";
//send the email
$mail_sent = @mail( $to, $subject, $message, $headers );
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed" 
echo $mail_sent ? "Mail sent" : "Mail failed";
    // active is 0 by DEFAULT (no need to include it here)
	
	if(!($stmt = $mysqli->prepare("INSERT INTO users (first_name, last_name, email, password, hash, igname, profileurl, readrules, admin, username) VALUES (?,?,?,?,?,?,?,?,?,?)}"))){
 echo "Prepare failed: (" . $mysqli->errno . ")" . $mysqli->error;
}

if(!$stmt->bind_param('ssssisssss', $first_name, $last_name, $email, $password, $hash, $igname, $profileurl, $rules, 0, $username2)){
     echo "Binding paramaters failed:(" . $stmt->errno . ")" . $stmt->error;
}

if(!$stmt->execute()){
     echo "Execute failed: (" . $stmt->errno .")" . $stmt->error;
}
	
	
    if($stmt) {
        $_SESSION['active'] = 0; //0 until user activates their account with verify.php
        $_SESSION['logged_in'] = true; // So we know the user has logged in
		$_SESSION['admin'] = 0;
        $_SESSION['message'] =
                
                 "Thank you for applying. Please wait while admins check over your application. You should recieve an email shortly. (Check junk folders and allow up to 5 hours for a review)";
		header("location: usertest.php");
		exit();

    }
    else{
        echo "Registration failed";
    }
}

$mysqli->close();

   

Sorry in advance for the formatting and indenting. That's the result of being messy.

 

If anyone's willing to help me and would like the php pages (index.php includes the form) and (register.php which includes the code after the button is pressed) I'd be more than willing to upload them.

[mac_gyver]

if you use exceptions to handle the database statement errors, you won't have to add code around every statement that can fail. 

 

btw - the bind_param() operates wholly within the mysqli extension, and doesn't communicate with the database server, so there are no ->errno and ->error property values if it fails. if there is a bind_param() error, you would be getting a php errors.

 

to enable exceptions for the mysqli extension, add the following line before you make the database connection - 

mysqli_report(MYSQLI_REPORT_ALL);

this will cause any error with the mysqli database statements (connection, prepare, execute) to throw an exception. if you let php catch the exception, it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. error_reporting should always be E_ALL. when learning, developing, and debugging code and queries, display_errors should be set to ON. when on a live/public server, display_errors should be set to OFF and log_errors should be set to ON.

 

doing these things should provide you with useful information as to why the code isn't working. the most likely problem is because you are not actually fetching the data from the 1st SELECT query, you are/should-be getting an 'command out of sync' error. in general you should always fetch all the data that a query matches. if you do ever have a case with a prepared query where you won't fetch all the data, see the mysqli stmt close() method.

[Barand]

If you are using prepared statements, do not use mysqi_real_escape_string().

 

The placeholders (?) in the query should not be in single quotes. IE it should be

...WHERE email = ? OR username = ?

[gizmola]

If you are using prepared statements, do not use mysqi_real_escape_string().

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^