To use prepared statements by PHP for Postgres


Question

This is a similar question to this "When not to use Prepared statements?", but with the "how-to" -part and for PostgreSQL.

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.

1
9
5/23/2017 10:27:52 AM

Accepted Answer

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?
5
8/7/2009 11:25:24 PM

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

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon