<?php

/*
Example showing correct way to use PHP's PDO api to avoid SQL injection attacks.
Gary "ChunkyKs" Briggs <chunky@icculus.org>

Little Bobby Tables:
   http://xkcd.com/327/
*/

header("Content-type: text/plain");

$dbfilename = ":memory:";

$db = new PDO("sqlite:$dbfilename");
if(!$db) {
	print("Error opening database $dbfilename\n");
	exit(1);
}

// Just going to blindly exec() this.
$createsql = "CREATE TABLE test (id INTEGER PRIMARY KEY, colA TEXT, colB TEXT)";

// Note that there's ":namedparam" where you would otherwise have put
//    the mysql_real_escape_string'd value [or similar]
$insertsql = "INSERT INTO test (colA,colB) VALUES (:colA, :colB)";

// Use exec() for things that can only pass or fail. Don't use it for
//    stuff the users pass you.
$result = $db->exec($createsql);
if(false === $result) {
	print("Error exec'ing create statement \"$createsql\"\n");
	print_r($db->errorInfo());
	exit(1);
} else {
	print("Successfully created db table\n");
}


// Imagine that we've collected some data from users with a
//     form. Something like this:
// $name = $_REQUEST['name']
// $addr = $_REQUEST['addr']

// Note that users and malicious attackers will put stuff in these
//  that would be a SQL injection attack.
// Please see Little Bobby Tables linked above, for an example.

$name = "ChunkyKs'); DROP TABLE test; --";
$addr = "1234 Example Lane";

// There are two different ways to correctly bind the parameters in PDO. I
//    will demonstrate both, feel free to pick whatever works for you. Note
//    that "colA" and "colB" are the named parameters prefixed with a ":"
//    in the SQL above.

// First, prepare the statement
$stmt = $db->prepare($insertsql);
if(!$stmt) {
	print("Error preparing statement \"$insertsql\"\n");
	print_r($db->errorInfo());
	exit(1);
} else {
	print("Successfully prepared insert statement\n");
}

// Bind mechanism 1: each parameter individually
$stmt->bindValue(':colA', $name);
$stmt->bindValue(':colB', $addr);
// And execute
$result = $stmt->execute();
if(false === $result) {
	print("Error inserting via individual binds:\n");
	print_r($db->errorInfo());
	exit(1);
} else {
	print("Successfully inserted via individual binds\n");
}

// Bind mechanism 2: All at once with a hash table
$result = $stmt->execute(array(':colA' => $name, ':colB' => $addr));
if(false === $result) {
	print("Error inserting via group bind:\n");
	print_r($db->errorInfo());
	exit(1);
} else {
	print("Successfully inserted via group bind\n");
}


// And check they all came out ok
$selectsql = "SELECT id,colA,colB FROM test;";
$selstmt = $db->prepare($selectsql);
if(!$selstmt) {
	print("Error preparing select:\n");
	print_r($db->errorInfo());
	exit(1);
} else {
	print("Successfully prepared select statement\n");
}

// Always need to tell a statement to execute, even if you don't bind parameters
$selstmt->execute();

print("\nNow going to read out all the rows:\n");
while(false != ($row = $selstmt->fetch(PDO::FETCH_ASSOC))) {
	print("\nAnother row:\n");
	print_r($row);
}


?>