/**
 * Note: This code is provided for demonstration purposes.
 *       In general, you want to add some application logic to validate
 *       the incoming parameters. You do not need to escape anything.
 */
$stmt = $pdo->prepare('SELECT * FROM blog_posts WHERE YEAR(created) = ? AND MONTH(created) = ?');
if ($stmt->execute([$_GET['year'], $_GET['month']])) {
    $posts = $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
if (empty($email)) {
    throw new \InvalidArgumentException('Invalid email address');
}

$qs = 'SELECT * FROM photos WHERE album = ?';
// Use switch-case for an explicit whitelist
switch ($_POST['orderby']) {
    case 'name':
    case 'exifdate':
    case 'uploaded':
       // These strings are trusted and expected
       $qs .= ' ORDER BY ' . $_POST['orderby'];
       if (!empty($_POST['asc'])) {
           $qs .= ' ASC';
       } else {
           $qs .= ' DESC';
       }
       break;
    default:
       // Some other value was passed. Let's just order by photo ID in descending order.
       $qs .= ' ORDER BY photoid DESC';
    }
$stmt = $db->prepare($qs);
if ($stmt->execute([$_POST['album_id']])) {
    $photos = $stmt->fetchAll(\PDO::FETCH_ASSOC); 
}

if (!preg_match('/^[A-Za-z][A-Za-z0-9_]*$/', $table)) {
    throw new AppSpecificSecurityException("Possible SQL injection attempt.");
}
// And now you can safely use it in a query:
$stmt = $pdo->prepare("SELECT * FROM {$table}");
if ($stmt->execute()) {
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// First method:
$pdo = new \PDO(
    'mysql;host=localhost;dbname=something',
    getenv('MYSQL_USERNAME'),
    getenv('MYSQL_PASSWORD')
);
$db = \ParagonIE\EasyDB\EasyDB($pdo);

// Second method:
$db = \ParagonIE\EasyDB\Factory::create(
    'mysql;host=localhost;dbname=something',
    getenv('MYSQL_USERNAME'),
    getenv('MYSQL_PASSWORD')
);

/**
 * As mentioned previously, you should perform validation on all input.
 * Not necessarily for security reasons, but because well-designed software
 * validates all user-supplied input and informs them how to correct it.
 * 
 * For the sake of easy auditing, you probably don't want to pass $_GET,
 * $_POST, other superglobals. Instead, validate and store the results
 * in a local variable.
 */
$data = $db->safeQuery(
    'SELECT * FROM transactions WHERE type = ? AND amount >= ? AND date >= ?', 
    [
        $_POST['ttype'],
        $_POST['minimum'],
        $_POST['since']
    ]
);

/**
 * Although safe from SQL injection, this example snippet does not
 * validate its input. In real applications, please check that any data
 * your script is given is valid.
 */
$rows = $db->run(
     'SELECT * FROM comments WHERE blogpostid = ? ORDER BY created ASC',
     $_GET['blogpostid']
);
foreach ($rows as $row) {
    $template_engine->render('comment', $row);
}

/**
 * Although safe from SQL injection, this example snippet does not
 * validate its input. In real applications, please check that any data
 * your script is given is valid.
 */
$userData = $db->row(
    "SELECT * FROM users WHERE userid = ?",
    $_GET['userid']
);

/**
 * Although safe from SQL injection, this example snippet does not
 * validate its input. In real applications, please check that any data
 * your script is given is valid.
 */
$db->insert('comments', [
    'blogpostid' => $_POST['blogpost'],
    'userid' => $_SESSION['user'],
    'comment' => $_POST['body'],
    'parent' => isset($_POST['replyTo']) ? $_POST['replyTo'] : null
]);

$statement = EasyStatement::open()
    ->with('last_login IS NOT NULL');

if (strpos($_POST['search'], '@') !== false) {
    // Perform a username search
    $statement->orWith('username LIKE ?', '%' . $db->escapeLikeValue($_POST['search']) . '%');
} else {
    // Perform an email search
    $statement->orWith('email = ?', $_POST['search']);
}

// The statement can compile itself to a string with placeholders:
echo $statement; /* last_login IS NOT NULL OR username LIKE ? */

// All the values passed to the statement are captured and can be used for querying:
$user = $db->single("SELECT * FROM users WHERE $statement", $statement->values());

// Statements also handle translation for IN conditions with variable arguments,
// using a special ?* placeholder:
$roles = [1];
if ($_GET['with_managers']) {
    $roles[] = 2;
}

$statement = EasyStatement::open()->in('role IN (?*)', $roles);

// The ?* placeholder is replaced by the correct number of ? placeholders:
echo $statement; /* role IN (?, ?) */

// And the values will be unpacked accordingly:
print_r($statement->values()); /* [1, 2] */

// Statements can also be grouped when necessary:
$statement = EasyStatement::open()
    ->group()
        ->with('subtotal > ?')
        ->andWith('taxes > ?')
    ->end()
    ->orGroup()
        ->with('cost > ?')
        ->andWith('cancelled = 1')
    ->end();

echo $statement; /* (subtotal > ? AND taxes > ?) OR (cost > ? AND cancelled = 1) */

$whiteListOfColumnNames = ['username', 'email', 'last_name', 'first_name'];
$qs = 'SELECT * FROM some_table';
$and = false;
if (!empty($where)) {
    $qs .= ' WHERE ';
    foreach (\array_keys($where) as $column) {
        if (!\in_array($column, $whiteListOfColumnNames)) {
            continue;
        }
        if ($and) {
            $qs .= ' AND ';
        }
        $qs .= $db->escapeIdentifier($column).' = ?';
        $and = true;
    }
}
$qs .= ' ORDER BY rowid DESC';

// And then to fetch some data
$data = $db->run($qs, \array_values($where));

$pdo = new PDO(/* Fill in the blank */);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT * FROM foo WHERE first_name = ? AND last_name = ?");
if ($stmt->execute([$_GET['first_name'], $_GET['last_name'])) {
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    // Handle error here.
}
$args = [
    json_encode($_GET)
    (new DateTime())->format('Y-m-d H:i:s')
];
$insert = $pdo->prepare("INSERT INTO foo_log (params, time) VALUES (?, ?);");
if (!$insert->execute($args)) {
    // Handle error here.
}

try {
    $stmt = $pdo->prepare("SELECT * FROM foo WHERE first_name = ? AND last_name = ?");
    $stmt->execute([$_GET['first_name'], $_GET['last_name']);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $args = [
        json_encode($_GET),
        (new DateTime())->format('Y-m-d H:i:s')
    ];
    $pdo->prepare("INSERT INTO foo_log (params, time) VALUES (?, ?);")
        ->execute($args);
} catch (PDOException $ex) {
    // Handle error here.
}