to_tsquery gives ‘ERROR: syntax error in tsquery’ when it contains a space

I had noticed a problem with my full text search solution when I was testing. If the input query contained a quote, an error resulted on the query. At the time, I attributed that to a poor input/text handling. Later, I realized I was wrong.

On the website, this PHP code is used:

$result = pg_query_params($this->dbh, $sql, array($this->Query));

Where $this->Query is what the user supplied on the search form. The pg_query_params documentation says

The primary advantage of pg_query_params() over pg_query() is that parameter values may be separated from the query string, thus avoiding the need for tedious and error-prone quoting and escaping.

So I don’t need to do anything special.

Here is some testing I tried while talking this over via IRC:

freshports.dev=# SELECT to_tsquery('english', $$Joe's stuff$$);
ERROR:  syntax error in tsquery: "Joe's stuff"

freshports.dev=# SELECT to_tsquery('english', $$Joes stuff$$);
ERROR:  syntax error in tsquery: "Joes stuff"

freshports.dev=# SELECT to_tsquery('english', $$Joe stuff$$);
ERROR:  syntax error in tsquery: "Joe stuff"

freshports.dev=# SELECT to_tsquery('english', $$Joestuff$$);
 to_tsquery 
------------
 'joestuff'
(1 row)

It turns out the solution is to use websearch_to_tsquery() instead. The documentation mentions:

Moreover, this function should never raise syntax errors, which makes it possible to use raw user-supplied input for search.

That sounds ideal, and it was. It completely fixes the issue.

freshports.dev=# SELECT websearch_to_tsquery('english', $$Joe's stuff$$);
 websearch_to_tsquery 
----------------------
 'joe' & 'stuff'
(1 row)

freshports.dev=# SELECT websearch_to_tsquery('english', $$Joes stuff$$);
 websearch_to_tsquery 
----------------------
 'joe' & 'stuff'
(1 row)

freshports.dev=# SELECT websearch_to_tsquery('english', $$Joe stuff$$);
 websearch_to_tsquery 
----------------------
 'joe' & 'stuff'
(1 row)

Great. Hope this helps.