How to run PostgreSQL LIKE query with PHP

Question

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.

Gordon Linoff · Accepted Answer · 2017-02-26 14:51:00Z

3

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.

edited Feb 26, 2017 at 14:51

answered Feb 26, 2017 at 14:41

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yola Over a year ago

Any character could be in the string and in any position, that is one of my concerns

Gordon Linoff Over a year ago

@Yola . . . I modified the answer. But on second thought, see the first solution. LIKE is not the right operator for what you want to do.

Yola Over a year ago

Great! Thank you very much!

Collectives™ on Stack Overflow

How to run PostgreSQL LIKE query with PHP

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related