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.
I believe the solution I'm already currently using is probably the most practical. Anything beyond this would seem to add unnecessary complexity.
When I use PDO I pass an array of bind parameters, then I can log the SQL statement and the array together.
If you try to substitute the parameters into the SQL statement the result will not take into account any escaping PDO will perform on your parameters.