Now that we know how to create a connection to our database using PHP Data Objects, let’s talk a bit about prepared statements. Now I’d like to think that we’ve all made this mistake before, taken user input and used it in an SQL query something like this:
$db->execute('SELECT * FROM `table` WHERE `column` = "' . $_POST['column'] .
As we all know now (or have found out the hard way) code like that is insecure and susceptible to SQL injection attacks (darn you Little Bobby Tables!). One approach to helping alleviate such scenarios would be to sanitize any user input to ensure that the values being passed in are of the correct format and quotes are properly escaped.
That’s all well and good, but I find prepared statements to be a more elegant solution. Not only do prepared statements eliminate 1st order (and 2nd order if you never use dynamically created statements) SQL injection scenarios without manual sanitation but also allows us to write SQL that can be used for a variety of data stores via PDO. PHP Data Objects allow you to write generic code that can have the data store flipped (let’s say from MySQL to PostgreSQL or SQLite) fairly easily (SQL nuances aside).
For those unfamiliar, the gist of a prepared statement is that you write a query with markers that are then replaced with sanitized values when you go to execute the statement. To take our example from before and applied prepared statements is pretty simple and would look something like this:
$query = $pdo->prepare('SELECT * FROM `table` WHERE `column` = :column;'
$query->bindParam(':column', $_POST['column'
$query->execute
It’s debatable as to whether the syntax for prepared statements is easier than calling a sanitation method for every user provided variable (or walking the entire $_REQUEST variable). I prefer letting PHP handle it so I don’t have to, even if the syntax is a bit longer. Alternatively if you’d prefer to not use the named markers or perhaps have to insert the same column multiple times, you can use this shorter syntax:
$query = $pdo->prepare('INSERT INTO `table` (column1, column2) VALUES (?, ?), (?, ?);'
$query->bindParam(0, $_GET['0_column1'
$query->bindParam(1, $_GET['0_column2'
$query->bindParam(2, $_GET['1_column1'
$query->bindParam(3, $_GET['1_column2'
$query->execute
This same sort of logic could be applied when doing a SELECT with the IN (?, ?, ?) type syntax.
Last example would be an even sorted syntax for our last example. Instead of using the bindParam() syntax, you can simplify it by passing the the parameters as an array:
$query = $pdo->prepare('INSERT INTO `table` (column1, column2) VALUES (?, ?), (?, ?);'
$query->execute(array($_GET['0_column1'], $_GET['0_column2'], $_GET['1_column1'], $_GET['1_column2']));
Wow, that’s way easier! Like many things in many languages there’s always a handful of ways to accomplish the same task. Now that you know how to use prepared statements, you’re well on your way to a more secure website or web app!
