3

This question has been asked before here and here. I want to provide full-text support using postgreSQL via to_tsquery. However, since escaping characters leads to complications, I think it is okay to simply filtering special characters that could provoke errors.

What are the characters I need to remove to use to_tsquery safely. So far, I have identified the following:

  1. Input that contains &, |, : might produce syntax errors
  2. Input with * might not work as expected (to_tsquery('a*b') produces 'b')
  3. quotes need to be escaped

What do you suggest in order to take user input in to_tsquery? What are other special characters I need to remove?

3

You may want to just leave it as users enter it, since you can safely use strings processed twice via quote_literal. In the to_tsquery documentation, the example for phrases within ts queries is:

SELECT to_tsquery('''supernovae stars'' & !crab');

Adding some junk to the phrase breaks it, unless you pass it twice via quote_literal, which makes any string safe to use:

SELECT to_tsquery(quote_literal(quote_literal('supernovae '':|stars')) || ' & !crab');
1
  • 1
    Thanks. I was playing with your last query but I can't figure out why you need to use quote_literal twice: Depending on what meant, this SELECT to_tsquery(quote_literal('supernovae '':|stars' || ' & !crab')); and this SELECT to_tsquery(quote_literal('supernovae '':|stars') || ' & !crab'); works. The only issue I can see is that quote_literal doesn't allow me to use the | operator instead of & and that quoting needs to be used correctly (as you did, in your example, with ''). Apr 12 '16 at 22:16
1

Input that contains &, |, : might produce syntax errors

Use plainto_tsquery() instead of to_tsquery(). It's meant to accept free-form user input.

Input with * might not work as expected (to_tsquery('a*b') produces 'b')

The text configuration matters. For instance,

select to_tsquery('simple','a*b');
 to_tsquery 
 ------------
  'a' & 'b'

select to_tsquery('english','a*b');
 to_tsquery 
------------
 'b'

quotes need to be escaped

The same as with any text literal.

Or they don't need to be escaped if using prepared or parametrized statements with $N-style parameters, if your client-side environment supports that.

1
  • 2
    Thank you. The issue with plainto_tsquery() is that it inserts the & operator and as far I know, that can't be changed. I think it is better if I use | instead. Yes, I'm using parameterized statements. Apr 12 '16 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.