处理SQLGrammarException：错误：tsquery中的语法错误： [英] Handling SQLGrammarException: ERROR: syntax error in tsquery:

问题描述

当Postgres FTS使用Hibernate时，tsquery看起来是合适的。但是，每次尝试输入'或其他一些奇怪字符的混合时，似乎都会出现主题异常。它甚至不接受两个词。如果用单引号括住字符串，则布尔运算符如|停止工作。我想知道是否有办法处理这个异常。我为每一种方法都添加了投掷，但它似乎把我的头撞在墙上。这里是我的代码：

 SELECT FROM cat c where+ args.get（0）+@@+to_tsquery （+ args.get（1）+）; 
  

这就是Hibernate生成的内容。

 Hibernate：select fulltextse0_.name as name2_ from posts fulltextse0_ where 
 fulltextse0_.textsearchable_index_col @@ to_tsquery（？）= true order by 
 fulltextse0_.id asc limit？  
  

arg0是全文字段的名称，arg1是从网页传递给它的字符串形成。 你的代码没有遵循SQL的基本正确做法，即：不要将用户提供的数据直接替换为SQL字符串。 这会让您开始 SQL注入漏洞，并且还会引发各种令人兴奋的错误。您已经发现了错误，幸亏有人利用您的数据库。

使用参数化语句。

When using Hibernate with Postgres FTS, tsquery seems like the suitable thing. However, the subject exception seems to appear every time I try to enter "'" or some other mix of strange characters. It doesn't even seem to accept two words. And if you surround the strings with single quotes, then the Boolean operators like | stops working. I wonder if there's anyway to handle this exception. I have added throws to every single method but it seems like banging my head against the wall. Here's my code:

"SELECT FROM cat c where "+ args.get(0)+" @@ "+ "to_tsquery("+ args.get(1) + ")";

And this is what Hibernate generates.

"Hibernate: select fulltextse0_.name as name2_ from posts fulltextse0_ where 
  fulltextse0_.textsearchable_index_col @@ to_tsquery(?)=true order by 
    fulltextse0_.id asc limit ?"

arg0 is the name of the fulltext field and arg1 is a string that gets passed to it from a web form.

解决方案

Your code fails to follow a basic proper practice with SQL, namely: Never substitute user supplied data directly into SQL strings. This will open you to SQL injection holes, and also cause a variety of exciting errors. You've discovered the errors, thankfully BEFORE someone exploited your database.

Use parameterized statements.

这篇关于处理SQLGrammarException：错误：tsquery中的语法错误：的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！