zNbmtbg4

1<?php
2
3/**********************************************************
4* CREATES THE SAMPLE DATABASE/TABLE/DATA
5/*********************************************************/
6/*********************************************************
7
8CREATE DATABASE IF NOT EXISTS scratch
9  CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
10
11CREATE TABLE `scratch`.`pdo_whitelist` (
12  `Country` VARCHAR(40) DEFAULT NULL,
13  `County` VARCHAR(40) DEFAULT NULL,
14  `Age` TINYINT(4) DEFAULT NULL
15) ENGINE=INNODB DEFAULT CHARSET=utf8;
16
17INSERT INTO `scratch`.`pdo_whitelist` (`Country`, `County`, `Age`)
18VALUES ('USA', 'Nassau', 22), ('USA', 'Nassau', 21);
19
20/*********************************************************/
21
22// whitelist parameters
23$post_input = ['Country' => 'USA', 'County' => 'Nassau', 'Age' => 21, 'EscalatePriviledge' => true, 'MakeMeSuperUser' => 1];
24$whitelist  = ['Country', 'County', 'Age'];
25$parameters = array_map('trim', array_intersect_key($post_input, array_flip($whitelist)));
26$wheres     = array_map(function($fieldName){ return "${fieldName} = ?"; }, array_keys($parameters));
27$whereClause= join(' AND ', $wheres);
28// you should validate and normalize here as well (see: php.net/filter_var)
29
30// connection parameters
31$hostname = '127.0.0.1';
32$database = 'scratch';
33$username = 'root';
34$password = 'rootpass';
35
36// connection parameters (mysql specific)
37$connectionString  = "mysql:host=${hostname};dbname=${database}";
38$connectionOptions = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
39
40// sql statement(s)
41$sql      = "SELECT * FROM pdo_whitelist WHERE ${whereClause}";
42
43// execute the query
44try {
45  $pdo = new PDO($connectionString, $username, $password, $connectionOptions);
46  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
47  $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
48
49  $statement = $pdo->prepare($sql);
50  $statement->execute(array_values($parameters));
51  $results   = $statement->fetchAll();
52
53  var_dump($results);
54
55  $pdo = $statement = null;
56} catch(PDOException $e) {
57  echo $e->getMessage();
58}