FIX: similar topic pg error with trailing slashes (PR #10913)

If a title contains a trailing slash pg would throw an invalid statement error due to quotes being escaped when looking up similar topic titles.

GitHub

the amount of edge cases on top of simply using escape_string are a bit worrying here. Why is this only an issue for trailing slash?

Does quote_literal solve this as well more cleanly?

https://dba.stackexchange.com/questions/135030/filtering-special-characters-in-to-tsquery

Oh possibly! I’ll play around with it and see what I can figure out.

@tgxworld could you help out here actually?

I took a pass at adding quote_literal, but I’m not able to get it to work and keep running into pg syntax errors.

REPLACE(TO_TSQUERY('english', QUOTE_LITERAL(QUOTE_LITERAL('''Title with trailing slashes\\'':*A & ''no body'':*B')))::text, '&', '|')::tsquery
PG::SyntaxError: ERROR:  syntax error in tsquery: "E'E''''''Title with trailing slashes\\\\\\\\'''':*A & ''''no body'''':*B'''"

Another option from https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES is to use plainto_tsquery but the docs say

Note that plainto_tsquery will not recognize tsquery operators, weight labels, or prefix-match labels in its input:

And we are using some of those, so I’m not exactly sure how to work around them to get plainto_tsquery to work.

This is also an issue for trailing question marks.

1 Like

Closing this. New pr is #10938