TSI25 TSI25 - 29 days ago 16
PHP Question

Construction SQL using preg_replace?

We are hitting an issue where we are using PHP to construct an SQL statement, and that is using the preg_replace function. Ive found the documentation here

but I'm having trouble understanding what our pattern means (this is legacy code that I've never tinkered with before.)

static function buildQuery ($sql, $params)
{
error_log ("1");
error_log (print_r($params, true));

foreach ((array) $params as $k => $v)
{
$v = pg_escape_string ($v);
error_log ("2");
error_log ($v);
$sql = preg_replace ("/:$k(?=\W|$)/i", "'$v'", $sql);
}

error_log ("3");
error_log ($sql);

return $sql;
}


The issue we are trying to fix is that before the preg_replace line is called we have a value going into the statement that is "\ \test" with two backslashes (had to add the space for it to show up here). After that line the two backslashes has evaluated into "\test" and when that gets read into our client from the database it becomes a tab followed by "est" which is not what we want. I understand that the /i has to do with recognizing pretty much any case. the \W seems to be a meta-character to find a word? $k is the key i assume for that column of the table but the rest of it i don't understand, and I don't know why it would result in trimming a backslash.

I'm pretty much a beginner on this, I'll keep looking through the documentation and maybe something will dawn on me but I could use pointers on what this means, where I can find more information about it, and how I might fix this issue.

Thanks

EDIT its a postgres database, version 9.3 if that's relevant.

Answer

The "\t" is likely getting interpreted by PHP's string interpolation. You could try using a single-quoted string for the pattern (you'll need to concatenate the $k variable in) but if you're able to replace this with actual parameter binding it'd be safer and more reliable.

Here's Regex101's explanation of that pattern, if it's any help:

/:test(?=\W|$)/i
:test matches the characters :test literally (case insensitive)
Positive Lookahead (?=\W|$)
Assert that the Regex below matches
1st Alternative \W
\W matches any non-word character (equal to [^a-zA-Z0-9_])
2nd Alternative $
$ asserts position at the end of the string, or before the line terminator
right at the end of the string (if any)

Global pattern flags
i modifier: insensitive. Case insensitive match (ignores case of [a-zA-Z])