Aiming for elegance, one thought at a time

MySQL, parameterized queries, PHP Data Objects and stored procedure out parameters

Posted: April 4th, 2010 | Author: | Filed under: IT | 1 Comment »

For the first time in a decade, I’m doing some PHP development. That’s scary in itself. The usual thing: connect to a database, get some data, serve up a page. The usual CRUD. I’ve elected not to use a framework because this is a bit of an experimental project and I’m not sure what I need – which makes the choice of frameworks difficult.

So I’m doing the database connection myself. No big deal, but I was surprised to find that the traditional way to handle dynamic queries in PHP is by building your own query string. Naturally, this means that you need to protect against SQL injection attacks yourself. Now, perhaps this is my own fault for not using a framework, but I really don’t want to roll my own SQL injection protection. Thankfully, there’s PHP Data Objects (PDO) which provide parameterized queries – which pretty much come standard in every other language on the planet (including VBA, of all places… technically, it’s standard in the PHP install as well, but I get the impression that it’s not been used traditionally.)

The syntax will be familiar to anyone who’s used parameterized queries before:


// configuration

$dbhost     = "localhost";
$dbname     = "notes";
$dbuser     = "root";
$dbpass     = "password";

// database connection

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

$subject = $_POST['subject'];
$object = $_POST['object'];
// query
$sql = "CALL SetContent(?,?)";
$q = $conn->prepare($sql);
$q->execute(array($subject,$object));

$sql = "SELECT object from threestore where subject = ?";

$q = $conn->prepare($sql);
$q->execute(array($subject));
$object = $q->fetchColumn();

?>

This is a simplified example, with all non-essential code removed. It writes something to a database and then reads it back straight away: useful, no?

You’ll note that I don’t use a stored procedure to retrieve the object: that’s because MySQL version 5 doesn’t support out parameters properly, as detailed in this bug. The patch is scheduled for version 6, and given that the production release is at 5.1, it’s going to be quite a wait. There’s a few different ways of working around the bug, but I wasn’t that attached to using stored procs at this stage.

Tags: , , , , ,
Sign up for danielstudds.com
* = required field

One Comment on “MySQL, parameterized queries, PHP Data Objects and stored procedure out parameters”

  1. 1 Rune Jensen said at 8:05 am on January 24th, 2011:

    I have always felt that building secure websites must be harder in PHP, because noone ever mentioned parameterized queries. Which, you are right, are built-in into ASP Classic as well (and that is an OLD language). They can in ASP, but not in PHP?

    This is one of the reasons for me to stay away from PHP. I do not feel it is secure enough, and I do not trust it.


Leave a Reply