How do I turn the following INSERT into a prepared statement so that its secure?

Question

Hi everyone,

I'm trying to get my head around prepared statements.

Basically, I would do a insert like so normally:

$sql = '
    INSERT INTO customers 
      (customer_first, customer_last, customer_address, customer_email)
    VALUES
      (' . mysql_real_escape_string($_POST['customer_first']) . ', 
      ' . mysql_real_escape_string($_POST['customer_last']) . ', 
      ' . mysql_real_escape_string($_POST['customer_address']) . ', 
      ' . mysql_real_escape_string($_POST['customer_email']) . '  )  
  ';
  mysql_query($sql);

From what I've been told however there is a more secure way to do this using Prepared Statements.

So far I think it is done like so:

$stmt = $dbh->prepare("INSERT INTO customers (customer_first, customer_last, customer_address, customer_email) VALUES (:customer_first, :customer_last, :customer_address, :customer_email)");
$stmt->bindParam(':customer_first', $_POST['customer_first']);
$stmt->bindParam(':customer_last', $_POST['customer_last']);
$stmt->bindParam(':customer_address', $_POST['customer_address']);
$stmt->bindParam(':customer_email', $_POST['customer_email']);

$stmt->execute();

Is this correct? Or is there a better way to do what I'm trying to achieve? If I try the above I get an error "Call to a member function prepare() on a non-object" - what does that mean?

I'm using the examples @ http://php.net/manual/en/pdo.prepared-statements.php. It refers to a $dbh - where does it get that from? Is $dbh supposed to be referenced elsewhere - I'm assuming it is something to do with the database connection?

In addition to those questions, can I still use things like mysql_insert_id() or mysql_error() using prepared statements like above?

Answer 1

If you are new to OOP, you could start with mysqli for more immediately familiar functionality and syntax and support for functional programming, but it's worth reviewing the PDO class if you're ready to tackle OOP.

Worth reviewing to help decide where to start: mysqli or PDO - what are the pros and cons?

It refers to a $dbh - where does it get that from? Is $dbh supposed to be referenced elsewhere - I'm assuming it is something to do with the database connection?

$dbh = database handle (as defined in the PDO > Connections and Connection Management doc)

Can I still use things like mysql_insert_id() or mysql_error() using prepared statements like above?

PDO::lastInsertId, PDO::errorInfo

Answer 2

The error message indicates your problem is with the $dbh object -- how is it initialized?