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.

Solution 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?

Solution 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();

Comments

[Michal aka Miki]

Do your examples have the same functionality as Robot's? They are much clearer to me than Robot's. He seems to use a lot of arrays which make them difficult to read for me.

[karim79]

@Masi - the examples I provided are from the pg_prepare doc page. They are easier to follow, but I would say @Glass Robot's examples are more real-world (for example, named markers). I suggest you get your hands dirty ASAP. There is no 'one example to rule them all'.

[Michal aka Miki]

Your answer does not use PDO. This suggests me that you can use prepared statements without PDO. PDO seems to offer database independence only.

[Michal aka Miki]

It seems that pg_query = pg_prepare + pg_pg_execution - ability_to_run_a_list_vars_to_a_statement.

[Michal aka Miki]

Please, see my reply to your answer above or at stackoverflow.com/questions/1247373/…