CODEWITHSUNDEEP
phppostgresqlescapingsql-like
Yola
Yola

Reputation: 19019

How to run PostgreSQL LIKE query with PHP

Suppose i want to run query like this:

$q = "SELECT * FROM items WHERE name LIKE $1";
$r = pg_query_params($dbconn, $q, array("%" .  $ss . "%"));

The problem may arise if user has supplied string with % or _ for $ss. How can i tell engine to not consider % and _ in $ss as special symbols?

For now my approach is 

$q = "SELECT * FROM items WHERE name LIKE $1 ESCAPE '\'";
$r = pg_query_params($dbconn, 
         $q, 
         array("%" . str_replace("%", "\%", str_replace("_", "\_", $ss)) . "%"));

But what if escape character (\) is the last one in the string, then appended % will be escaped as well.

Upvotes: 0

Views: 1846

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The first suggestion is to eschew LIKE. Just do:

SELECT * FROM items WHERE position($1 in name) > 0;

Then you don't have to worry about special characters.

You can use ESCAPE . . . but you need something that is not in the string, say ~.

SELECT * FROM items WHERE name LIKE $1 ESCAPE '~'

Then when you bind the parameter:

$r = pg_query_params($dbconn, $q,
                     array("%" . str_replace(str_replace($ss, "_", "~_"), "%", "~%") . "%"));

You can escape the escape character by doubling it. That is just another str_replace():

$r = pg_query_params($dbconn, $q,
                     array("%" . str_replace(str_replace(str_replace($ss, "~", "~~"), "_", "~_"), "%", "~%") . "%"));

Alternatively, make the use of LIKE a feature, and let the users put in wildcards, because they then have more powerful search capabilities.

Upvotes: 3

Related Questions