implementing a simple prepared query in PHP

Question

I'm trying to create a very simple database abstraction, one part of it using prepared queries.

Now, I have a function take a query string and an array of values like this:

$query = "SELECT `first_name`, `last_name` FROM ::table_name WHERE `id` = :id"
$values = array(
    'table_name' = $this->table_name,
    'id' = $user_id,
);

this will create a query like this:

SELECT `first_name`, `last_name` FROM `sometablename` WHERE `id` = '1234'

my problem is this:
I'm using preg_replace_callback to grab the ::identifiers and :identifiers from the query string, and then sending it to a sanitization function. The problem is, I also need to send the values array, so that the function can take the match from the regexp, get the item in the values array with that key, escape the value, wrap it in the right quotes and then return it.

But I can't pass any extra information to the callback. I could use a private static variable but this is very hacky.

What is another approach to this?

Answer 1

Doesn't vsprintf fit your needs?

Answer 2

you could also check out pdo, zend_db, and mdb2. good thing is they have named parameters and drivers that can properly create prepared statements (or emulate prepared statements) on many storage engines.

for example, zend_db will do some basic sql parsing to handle edge cases, like say when a regex embedded in a query is a character class with a colon...

Answer 3

One of the alternatives suggested by various comments in the manual is to use preg_replace() with the 'e' modifier as part of the regexp:

preg_replace("/pattern/e","strtoupper('\\1')",$subject);

Essentially you're specifying code to evaluate. I think this comment has a good example, whereby you create the function, and then a small string to evaluate it which allows you to pass extra parameters:

preg_replace('/pattern/e',"your_function(\$array,\$foo,\$bar,\$etc)",$str);