How to search for fields with single quotes from a postgres db?

Question

I have a field that may contain single quotes and it is stored in the db with the single quotes. For example the fields may be like this.

1|xyz's friend|21.3

2|hello, tty's friend|42.2

The user inputs a search query based on which the values need to be displayed.

For example if the user inputs--> xyz's which is stored in a variable (PHP)

I can't do

select * from table where field LIKE '%variable%' 

because the variable already has quotes in it. and postgres isn't allowing me to use double quotes around the %variable%

I also tried doing something like this

select * from table where field LIKE E'%variable%' 

to use escape sequences but that doesn't work too. Any help?

Answer 1

Don't try to quote things yourself, use the library. In this case, you want pg_escape_string. So add the percent wrappers and then feed it to pg_escape_string and then put the result of that in your SQL.

As an aside, the proper way to escape a single quote in a PostgreSQL string literal is to double it, if you have "it's" as a string then the database wants to see 'it''s' in the SQL.