002-pdo-whitelist-input-parameters.php

<?php

/**********************************************************
* CREATES THE SAMPLE DATABASE/TABLE/DATA
/*********************************************************/
/*********************************************************

CREATE DATABASE IF NOT EXISTS scratch
  CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';

CREATE TABLE `scratch`.`pdo_whitelist` (
  `Country` VARCHAR(40) DEFAULT NULL,
  `County` VARCHAR(40) DEFAULT NULL,
  `Age` TINYINT(4) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `scratch`.`pdo_whitelist` (`Country`, `County`, `Age`)
VALUES ('USA', 'Nassau', 22), ('USA', 'Nassau', 21);

/*********************************************************/

// whitelist parameters
$post_input = ['Country' => 'USA', 'County' => 'Nassau', 'Age' => 21, 'EscalatePriviledge' => true, 'MakeMeSuperUser' => 1];
$whitelist  = ['Country', 'County', 'Age'];
$parameters = array_map('trim', array_intersect_key($post_input, array_flip($whitelist)));
$wheres     = array_map(function($fieldName){ return "${fieldName} = ?"; }, array_keys($parameters));
$whereClause= join(' AND ', $wheres);
// you should validate and normalize here as well (see: php.net/filter_var)

// connection parameters
$hostname = '127.0.0.1';
$database = 'scratch';
$username = 'root';
$password = 'rootpass';

// connection parameters (mysql specific)
$connectionString  = "mysql:host=${hostname};dbname=${database}";
$connectionOptions = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');

// sql statement(s)
$sql      = "SELECT * FROM pdo_whitelist WHERE ${whereClause}";

// execute the query
try {
  $pdo = new PDO($connectionString, $username, $password, $connectionOptions);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

  $statement = $pdo->prepare($sql);
  $statement->execute(array_values($parameters));
  $results   = $statement->fetchAll();

  var_dump($results);

  $pdo = $statement = null;
} catch(PDOException $e) {
  echo $e->getMessage();
}