匹配SQL查询中的绑定参数

Question

I use parameterized queries, like this:

SELECT * FROM foo WHERE bar = :p0 AND baz = :p1

My parameter names take the form:

^:[a-z]\d+$

I'm currently extending PDOStatement to provide a method for dumping a fully constructed query, because PDOStatement::queryString does not have the bind parameter values replaced.

What is the most accurate method for matching these parameters inside their query with a regular expression?

A not-so-accurate initial attempt:

$sql = "SELECT * FROM foo WHERE bar = 'bar:a0bar :u2 barbar :w4' AND baz = :q2 AND boz IN (:z6, :yy1, :q, :r22, :b7)";
$matches = array();
preg_match_all('/(:[a-z]\d+)\b/', $sql, $matches);
$params = $matches[1];

This fails because parameters within strings are matched, but I'm not sure it's feasible to avoid this.

Bear in mind that I know full well that no method will be 100% accurate, and this is just for dumping constructed queries to a log file to aid in debugging, so the resulting queries will not be sent to the database for execution.

Answer 1

I believe the solution I'm already currently using is probably the most practical. Anything beyond this would seem to add unnecessary complexity.