Prepared Statements + PDO

Question

first off, please know I am not using this as a real world login it is purely for practice. I am using PHP/MySQLi and I have attempted to implement prepared statements but I'm not doing it correctly. Here is my working code with just mysqli_escape_string:

$host="localhost";
$username="login";
$password="password";
$db_name="database";


$link=mysqli_connect("$host", "$username", "$password", "$db_name")or die("cannot connect to server"); 

$tbl_name=temp_members;

// Random confirmation code to be sent in email
$confirm_code=md5(uniqid(rand())); 

//from signup.php
$name=$_POST['name'];
$email=$_POST['email'];
$pwd=$_POST['password'];

$name = mysqli_real_escape_string($link, $name);
$email = mysqli_real_escape_string($link, $email);
$pwd = mysqli_real_escape_string($link, $pwd);

$pwd = password_hash($pwd, PASSWORD_DEFAULT);

// Insert data into temp_members
$sql="INSERT INTO $tbl_name(confirm_code, name, email, password)VALUES('$confirm_code', '$name', '$email', '$pwd')";
$result=mysqli_query($link, $sql);


if($result){

    //email
    $to=$email;
    $subject="(no reply)";
    $header="from: Ben Pappas <mrbenpappas@gmail.com>";

    // message 
    $message="Hello ".$name.", Thanks for signing up! \r\n";
    $message.="Click on this link to activate your account: \r\n";
    $message.="http://www.monger.us/verify/confirmation.php?       passkey=$confirm_code";

    // send email
    $sentmail = mail($to,$subject,$message,$header);
}

Here is my (poor) attempt at PDO/ prepared statements:

$host="localhost";
$username="login";
$password="password";
$db_name="database";


$link = new PDO("mysql:host=$host;dbname=$db_name",$username,$password) or die("cannot connect to server");

$tbl_name=temp_members;

// Random confirmation code to be sent in email
$confirm_code=md5(uniqid(rand())); 

//from signup.php
$name=$_POST['name'];
$email=$_POST['email'];
$pwd=$_POST['password'];

$name = mysqli_real_escape_string($link, $name);
$email = mysqli_real_escape_string($link, $email);
$pwd = mysqli_real_escape_string($link, $pwd);

$pwd = password_hash($pwd, PASSWORD_DEFAULT);

// Insert data into temp_members

$statement = $link->prepare("INSERT INTO $tbl_name(confirm_code, name, email, password)VALUES('$confirm_code', '$name', '$email', '$pwd')";
$statement->execute(array(
    $confirm_code => 'confirm_code',
    $email => 'email',
    $name => 'name'
));
$result = ($link, $statement);


if($result){

//email
$to=$email;
$subject="(no reply)";
$header="from: Ben Pappas <mrbenpappas@gmail.com>";

// message
$message="Hello ".$name.", Thanks for signing up! \r\n";
$message.="Click on this link to activate your account: \r\n";
$message.="http://www.monger.us/verify/confirmation.php?    passkey=$confirm_code";

// send email
$sentmail = mail($to,$subject,$message,$header);
}

I am getting an error on the line the statement variable is being called with a message saying unexpected ";"

please forgive any obvious mistakes I am a padawan

Answer 1

$statement = $link->prepare("INSERT INTO $tbl_name(confirm_code, name, 
   email, password)VALUES('$confirm_code', '$name', '$email', '$pwd')";

should read:

$statement = $link->prepare("INSERT INTO $tbl_name(confirm_code, name,
   email, password)VALUES(:confirm_code, :name, :email, :pwd)");
$statement->execute(array(
':confirm_code' => $confirm_code,
':email' => $email,
':name' => $name,
':pwd' => $pwd
));

Also the mysqli_real_escape_string code is not required: The bind takes care of escaping the data for you.

Answer 2

            $value = $_POST['value'];
            $stmt = $mysqli->prepare("INSERT INTO XYZ (field) VALUES (?)");
            $stmt->bind_param('s', $value);  
            $stmt->execute(); 

Statement should look like this.

"s" for string and "i" for int

Answer 3

You're missing the close brackets on $statement line:

try this

$statement = $link->prepare("INSERT INTO $tbl_name(confirm_code, name, email, password)VALUES(:confirm_code, :name, :email, :pwd)");
$statement->execute(array(
    ':confirm_code' => $confirm_code,
    ':email' => $email,
    ':name'  => $name,
    ':pwd' => $pwd
));