How to use prepared statements with Postgres

Question

I know that I need prepared statements because I make more than one call to my database during one script.

I would like to get concrete examples about the following sentence

Look at typecasting, validating and sanitizing variables and using PDO with prepared statements.

I know what he mean by validating and sanitizing variables. However, I am not completely sure about prepared statements. How do we prepare statements? By filters, that is by sanitizing? Or by some PDO layer? What is the definition of the layer?

What do prepared statements mean in the statement? Please, use concrete examples.

Answer 1

What do prepared statements mean in the statement?

From the documentation:

This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed.

See pg_prepare

Example from the page linked above:

<?php
// Connect to a database named "mary"
$dbconn = pg_connect("dbname=mary");

// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", 'SELECT * FROM shops WHERE name = $1');

// Execute the prepared query.  Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));

// Execute the same prepared query, this time with a different parameter
$result = pg_execute($dbconn, "my_query", array("Clothes Clothes Clothes"));
?>

The MySQL documentation for Prepared Statements nicely answers the following questions:

• Why use prepared statements?
• When should you use prepared statements?

Answer 2

It means it will help you prevent SQL injection attacks by eliminating the need to manually quote the parameters.

Instead of placing a variable into the sql you use a named or question mark marker for which real values will be substituted when the statement is executed.

Definition of PDO from the PHP manual:
'The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.'

See the php manual on PDO and PDO::prepare.

An example of a prepared statement with named markers:

<?php
$pdo = new PDO('pgsql:dbname=example;user=me;password=pass;host=localhost;port=5432');

$sql = "SELECT username, password
FROM users
WHERE username = :username
AND password = :pass";

$sth = $pdo->prepare($sql);
$sth->execute(array(':username' => $_POST['username'], ':pass' => $_POST['password']));
$result = $sth->fetchAll();

An example of a prepared statement with question mark markers:

<?php
$pdo = new PDO('pgsql:dbname=example;user=me;password=pass;host=localhost;port=5432');

$sql = "SELECT username, password
FROM users
WHERE username = ?
AND password = ?";

$sth = $pdo->prepare($sql);
$sth->execute(array($_POST['username'], $_POST['password']));
$result = $sth->fetchAll();

Answer 3

How do we prepare statements:

You define a query one time, and can called it as often as you like with different values. (eg. in a loop)

$result = pg_prepare($dbconn, "my_query", 'SELECT * FROM shops WHERE name = $1');
$result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));
$result = pg_execute($dbconn, "my_query", array("row two"));
$result = pg_execute($dbconn, "my_query", array("row three"));

see: http://us2.php.net/manual/en/function.pg-execute.php

Answer 4

Reply to Karim79's answer

This

$result = pg_prepare($dbconn, "query1", 'SELECT passhash_md5 FROM users WHERE email = $1');

seems to be the same as this

$result = pg_prepare($dbconn, "query1", 'SELECT passhash_md5 FROM users WHERE email = ?');

---

Conclusion: the use of pg_prepare and pg_execute makes PHP much more efficient, since you do not need to consider sanitizing. It also helps you in the use of PDO.