Why doesn't my variable interpolate correctly when I build up a Mysql query?

Question

I am trying to write a regex expresstion in mysql from a Perl program. I want to have query such as this:

WHERE a.keywords REGEXP '[[:<:]]something[[:>:]]'

However, in Perl when I make this query I am getting error when concatenating:

for($i=0;$i<$count;$i++){
    $where = $where . "'[[:<:]]$andkeywords[$i][[:>:]]' ";  #errors

Where as this does not give me an error:

for($i=0;$i<$count;$i++){
    $where = $where . "'[[:<:]] $andkeywords[$i] [[:>:]]' ";  #no error

In the 'no error' code notice that there are extra spaces. But if I have extra spaces then I do not get the resuls I want because in the DB there are no 'extra spaces'.

Answer 1

I've never really trusted the autoreplacment of variables in strings like that. You may want to consider explicitly doing the concatenation you want like this:

for($i=0;$i<$count;$i++){
    $where=$where . "'[[:<:]]" . $andkeywords[$i] . "[[:>:]]' ";

EDIT: As ephemient points out the generally accepted way to do this inline is

for($i=0;$i<$count;$i++){
    $where=$where . "'[[:<:]]${andkeywords[$i]}[[:>:]]' ";

Personally I find the first way more readable but as with all things Perl, TIMTOWTDI

Answer 2

The reason in this case is that "$andkeywords[$i][[:>:]]" is being interpreted as a multi-dimensional array, and :>: is not a valid array index.

I personally prefer Mykroft's approach, but you could also achieve the same result by escaping the final opening bracket as so:

$where=$where."'[[:<:]]$andkeywords[$i]\[[:>:]]' ";

Answer 3

Just for completeness sake, this works too:

for ($i = 0; $i < $count; $i++) {
    $where .= "'[[:<:]]${andkeywords[$i]}[[:>:]]' ";
}

${blah} isn't valid outside of a string, but inside of a interpolatable string, it's equivalent to $blah.

I would have thought that this pattern is more common than the other answers, though... after all, how else do you want to type "foo${var}bar"? Obviously "foo$var\bar" doesn't work, since \b is a recognized escape sequence.

Answer 4

<Obligatory security moan>

Please use a DBI parameter for each regex value instead of interpolating it. Why?

1. There are no longer any constraints on what characters are allowed. Currently, if any element of @andkeywords contains a quote, backslash or special regex character, things will break. E.g. the keyword "O'Reilly" will cause a database error.
2. People won't be able to construct malicious keywords to reveal information they shouldn't see or wreak havoc. (Imagine if a user entered "'; drop database;" as a keyword.) This is called an SQL injection attack, and the web is rife with poorly coded websites that are susceptible to them. Don't let yours be one of them.

Even if @andkeywords is not populated from user-entered data, it takes almost no extra effort to use DBI parameters, and your code will be safe for use in future unknown environments.

</Obligatory security moan>

Answer 5

It would be helpful if you would include the text of any error messages.

Something tells me that

for($i=0;$i<$count;$i++){
    $where=$where . "'[[:<:]]" . $andkeywords[$i] . "[[:>:]]' ";
    ...
}

Could be simplified to

for (@andkeywords) {
    $where .= qq('[[:<:]]${_}[[:>]]' );
    ...
}

Or perhaps

$where .= join ' ', map { qq('[[:<:]]${_}[[:>:]]') } @andkeywords;