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

Question

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?

Aside from some application-dependant checking (enforcing a name is not empty etc.) it seems OK, as in SQL-injection safe. Keep in mind you could also forgo the `->bindParam()` calls and just call `$dbh->execute($_POST);` — Wrikken, Sep 15 '10 at 00:31
+1 - It's questions like this which re-affirm my faith in developers. Usually people are so defensive about their inline SQL concatenations. — David, Sep 15 '10 at 00:33
They're equivalent to me. FYI: PreparedStatements can't help dynamic parameters for an IN clause - has to be done as dynamic SQL. — OMG Ponies, Sep 15 '10 at 00:34

score 1 · Answer 1 · edited May 23 '17 at 11:48

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

Thanks heaps for clarifying that. – willdanceforfun Sep 15 '10 at 23:53 — willdanceforfun, Sep 15 '10 at 23:53

score 1 · Answer 2 · answered Sep 15 '10 at 03:21

1

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

answered Sep 15 '10 at 03:21

philfreo

41,941
26
128
141

$dbh = new PDO('mysql:host=localhost;dbname=db', $user, $pass); – willdanceforfun Sep 16 '10 at 00:30
1

Is that the only other relevant code? Is it right above this line? Try doing print_r($dbh) – philfreo Sep 16 '10 at 02:54

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

2 Answers2