0

I feel like I am missing something, but I could not find it in the documentation on GH.

What are the escape characters for Blazer when searching in a string that contains a ' or ".

Example:

SELECT * FROM "search_filters" 
where "params" like '%with_vehicles_id"=>[%'
LIMIT 100

Update: The underlying database is Postgres 11. This is a blazer tool question, as the query above works just fine in a tool like dBeaver, or console. For some reason, I believe this is related to how Blazer is parsing the query before it is sent.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
wdenny3885
  • 3
  • 3

1 Answers1

0

I'm not very familiar with Blazer but it looks like it's a BI tool that lets your run SQL queries against your database and there's a playground here.

For PostgreSQL you don't need to do anything special for a double-quote inside of single quotes. The query as you wrote it would execute in a postgres terminal and the same approach works in the blazer playground.

SELECT * FROM "search_filters" 
where "params" like '%text"text%'
LIMIT 100

To query on a string that includes a single quote, PosgreSQL has you use two sequential single quotes, like this:

SELECT * FROM "search_filters" 
where "params" like '%text''text%'
LIMIT 100

Here's a link with more information: https://www.prisma.io/dataguide/postgresql/short-guides/quoting-rules

-- UPDATE --

Based on your error message ("syntax error at or near "LIMIT" LINE 3: LIMIT 100 LIMIT 1000") it looks like there are two "LIMIT" clauses being added to the SQL query. Do you have gems/plugins that are modifying the query and is there a way to disable them to see if that's causing the problem?

melcher
  • 1,543
  • 9
  • 15
  • Yes, I understand how to do it directly with PG. This is a Blazer question, to try and figure out how to do it w/ that tool. There are many others in the business that do not have direct db access, but use this as a quick discovery tool, and this question comes up all the time when looking through str fields. – wdenny3885 Aug 25 '21 at 16:02
  • Can you include the error that you're seeing? – melcher Aug 25 '21 at 16:10
  • I don't see any issues filed for the blazer gem related to escaping of quotes. The issues that users have filed related to quotes have to with how smart variables are quoted, which can cause unexpected issues. Are you using smart variables in your query? see https://github.com/ankane/blazer/issues/340 – melcher Aug 25 '21 at 16:17
  • `syntax error at or near "LIMIT" LINE 3: LIMIT 100 LIMIT 1000 /*blazer,user_id:189,user_name:Removed^` – wdenny3885 Aug 25 '21 at 16:22
  • removing " in the middle resolves all issues, except obviously, that we are looking for the ", but yea, you get the idea. – wdenny3885 Aug 25 '21 at 16:22
  • Using https://blazer.dokkuapp.com/queries/new the queries `SELECT * FROM "movies" where title ilike '%"%' limit 10` and `SELECT * FROM "movies" where title ilike '%''%' limit 10` execute the expected SQL and cause no syntax/sql errors. Is there anything non-standard in your blazer setup or customization of the blazer gem? Are you using any plugins? – melcher Aug 25 '21 at 17:19