Do i need to use $stmt = $conn->prepare for this prepared statement?

Question

The current syntax for my prepared statements is incorrect, where I: (INSERT...?, ?, ?)

I have tried to copy syntax from different examples but it seems the more I try the more I break my login system. I have somewhat conflicting examples and am not sure which syntax is the correct one to use. Do I need to use $stmt = $conn->prepare before INSERT?

// create preprepared statement
$sql = "INSERT INTO `user` (username, password, email) VALUES (?, ?, ?)";

// check if sql statement is correct
if ($stmt = mysqli_prepare($connection, $sql)) {

    // Add the variables to the stmt
    mysqli_stmt_bind_param($stmt, "sss", $param_username, $param_hashed_password, $param_email);
    $param_username = $username;
    $param_password = $hashed_password;
    $param_email = $email;

    // Attempt to execute the stmt
    if(mysqli_stmt_execute($stmt)) {
        // If statement executed
        $_SESSION["username"] = $username;
        header("location: login.php");

At the moment it's not inserting any values into my db and user registration is failing.

EDIT:

$password = $_POST['password'];

$hashed_password = password_hash($password, PASSWORD_DEFAULT);

It has just occurred to me that this might be incorrect usage of password_hash?

Ah thanks for pointing that out, however still getting the same error after fixing typo. — Joe S., Feb 09 '19 at 16:28
@Progman "User Registration Failed because of: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?)' at line 1" — Joe S., Feb 09 '19 at 16:56
@JoeS. `Password` is a MySQL reserved word. You might want to rename that column or use backticks, see https://stackoverflow.com/questions/261455/using-backticks-around-field-names — Progman, Feb 09 '19 at 19:23

score 1 · Accepted Answer · answered Feb 09 '19 at 17:04

Do i need to use "$stmt = $conn->prepare" for this prepared statement? - in short, No! You must however use the prepare method to actually generate the prepared statement, it MUST be done before actually attempting to do the INSERT and it is wise to assign that to a variable so that you can fork the program logic dependant upon success/failure.

My preference is to do as below - use a try/catch block and use the return values or variables at various stages to determine whether to throw meaningful(?) exceptions to help debug - so as example you could do this

/*
    assumed that $username,$password & $email 
    are all defined and available at ths stage.

    also assumed that `session_start()` has 
    been called and that no html output occurs
    before this point ( unless using output buffering )
*/

try{
    # example

    $sql = "INSERT INTO `user` ( `username`, `password`, `email` ) VALUES (?, ?, ?)";
    $stmt = $connection->prepare( $sql );

    if( $stmt ){
        /* if there were no problems continue with the database operations */
        $stmt->bind_param('sss', $username, $hash, $email );

        /* not sure how the hash was generated so just put this in to be sure... */
        $hash = password_hash( $password, PASSWORD_BCRYPT );

        $result = $stmt->execute();
        $stmt->free_result();
        $stmt->close();

        if( $result ){
            $_SESSION['username'] = $username;
            exit( header( 'Location: login.php' ) );
        } else {
            throw new Exception('Bogus! There was some sort of problem...');
        }
    } else {
        throw new Exception('Failed to prepare sql query');
    }
}catch( Exception $e ){
    exit( $e->getMessage() );
}

Why do I need to call session_start() before this code, considering it is only the register page? — Joe S., Feb 09 '19 at 21:45

Do i need to use $stmt = $conn->prepare for this prepared statement?

1 Answers1