How can I force a single quote (') or % character in my SQL LIKE query?

Need help Database
1

Hello, I’m trying to make the following query to my DB to find a keyword in a comma separated list.

SELECT box_url FROM images WHERE keywords LIKE ‘%$1%’ LIMIT 200

The % acts as a wildcard so I can search the row with comma-separated values for the keyword in $1. The quotes are causing an issue with Bubble, it thinks i want to send a literal “%$1%”, when I just need to include the ‘% and %’ , with the $1 in the middle. Does this make sense? Please let me know if you need clarification. Escape characters (\% , \’) don’t seem to work, unless I’m using them wrong.

This command works flawlessly when I put it into my DB, but introducing Bubble’s $1 variable and requiring single quotes in the query is making it complicated.

Thanks.

2

I’m trying to do the same thing here without any success.

This works, but it only does an exact match:
SELECT SIC2_Desc FROM Dim_SIC2 WHERE upper(SIC2_Desc) like upper(?) LIMIT 10 ;

These parse, but doesn’t fetch any data
SELECT SIC2_Desc FROM Dim_SIC2 WHERE upper(SIC2_Desc) like upper(’$1’) LIMIT 10 ;
SELECT SIC2_Desc FROM Dim_SIC2 WHERE upper(SIC2_Desc) like upper(’%$1%’) LIMIT 10 ;
SELECT SIC2_Desc FROM Dim_SIC2 WHERE upper(SIC2_Desc) like upper(’%’?’%’) LIMIT 10 ;

These don’t parse in Bubble:
SELECT SIC2_Desc FROM Dim_SIC2 WHERE upper(SIC2_Desc) like upper(’%?%’) LIMIT 10 ;
SELECT SIC2_Desc FROM Dim_SIC2 WHERE upper(SIC2_Desc) like upper(’%?%’) LIMIT 10 ; /* attempting to escape the % */
SELECT SIC2_Desc FROM Dim_SIC2 WHERE upper(SIC2_Desc) like upper(’%’$1’%’) LIMIT 10 ;

3

@topherwilliams did you ever figure this out?

4

Quick update, but this is a workaround not a fix.

This works and accomplishes the same thing (allowing a wildcard search) for this use case.

SELECT SIC2_Desc FROM Dim_SIC2 WHERE upper(SIC2_Desc) REGEXP upper(?) LIMIT 10 ;

But it doesn’t address the problem of being unable to use the % wildcard character.

For small queries this is fine, but REGEXP is a very expensive operation in the database and isn’t a good replacement in many scenarios.

5

Here’s what I got from Bubble support:

Short Answer: use this format: ‘%’ || $1 || ‘%’

Long answer:

We use Postgres’ query-escaping mechanism to safely send parameters to queries to avoid SQL injection attacks. So, we don’t actually replace the $1 on Bubble’s end. We use Postgres’s PREPARE statement (https://www.postgresql.org/docs/9.6/static/sql-prepare.html) to build a query with a placeholder, then send the value separately.

What this means is that you can only use $ values in places where postgres allows them. You can’t embed them in the middle of a string. Instead, you can use string concatenation to accomplish what you want to do here:

where keywords like ‘%’ || $1 || ‘%’

The || operator combines two strings into a longer one; more information here: https://www.postgresql.org/docs/9.6/static/functions-string.html

6

I am trying this but not getting it to work… Did you ever get it to work? If so, can you copy and paste and exact example? Thanks.

7

This works for me using PostgreSQL.

select img_url,id,camera from image where camera=$2 and keyword like ‘%’||$1||’%’ LIMIT 200

8

I am trying to use the sql plugin and my query looks like this:

select top 200
a.Id
from dbo.Opportunity a
where a.Active = 1
and
(OpTitle like ‘%’||@SearchTerm||’%’
or
OpDesc like ‘%’||@SearchTerm||’%’
or
ContactName like ‘%’||@SearchTerm||’%’)

9

Any more tips on how to make this work? I am trying to pass a list into the following query:

SELECT Name, Description, APIRef, Revenue FROM CDR.MonthlyCosts
WHERE APIRef IN (?) LIMIT 10;

Obviously if plug the variable directly into the code it works:
SELECT Name, Description, APIRef, Revenue FROM CDR.MonthlyCosts
WHERE APIRef IN (‘Roaming’, ‘Data’) LIMIT 10;

However when trying to pass in the variable I’m having no luck. I’ve tried:

  • Roaming||’, '||Data
  • ‘Roaming||’,’||Data’
  • Every other variation on this I can think of.

By removing the brackets on the variable you can see a little of the SQL being passed and try to debug, but all I can see is Bubble does the leading and trailing ’ properly and then escapes out any in the middle of the string. Is there any way around this?

Thanks,

Andy