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.
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.
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:
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.'
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();