setParameter（）没有设置正确的引号

问题描述

我有以下代码：

 字符串searchText =...; 
 
 String sqlQuery = 
FROM Studio s+ 
WHERE fts（'english'，s.companyName，：q）= true; 
 
 Query q = JPA.em（）
 .createQuery（sqlQuery）
 .setParameter（q，searchText）; 
  

当我传递一个单词给 searchText ，它的工作原理：

 字符串searchText =one; 
  

当我传递两个单词，如

 字符串searchText =one two; 
  

我得到

当我通过一个然而，它再次起作用：

 字符串searchText ='one two'; 
  

SetParameter 不应设置正确的引号并转义文字？

PS fts 是一个PostgreSQL方言函数，定义如下：

  public class MyPostgreSQLDialect扩展PostgreSQLDialect {
 public MyPostgreSQLDialect（）{
 registerFunction（fts，new PostgreSQLFullTextSearchFunction（））; 
} 
} 
  

...

  public class PostgreSQLFullTextSearchFunction实现SQLFunction {
 
 public String render（@SuppressWarnings（rawtypes）列出参数，SessionFactoryImplementor工厂）{
 if（args.size（）！= 3）{
 throw new IllegalArgumentException（
该函数必须传递3个参数）; 
} 
 
 String ftsConfig =（String）args.get（0）; 
 String field =（String）args.get（1）; 
 String value =（String）args.get（2）; 
 
 String fragment = null; 
 if（ftsConfig == null）{
 fragment =to_tsvector（+ field +）@@+to_tsquery（'
 + value +'）; 
} else {
 fragment =to_tsvector（+ ftsConfig +:: regconfig，+ field +）@@
 +to_tsquery（+ ftsConfig +，+值+）; 
} 
 
 return fragment; 
 
 
 $覆盖
 public Type getReturnType（Type columnType，映射映射）
抛出QueryException {
 return new BooleanType（）; 
} 
 
 @Override 
 public boolean hasArguments（）{
 return true; 
} 
 
 @Override 
 public boolean hasParenthesesIfNoArguments（）{
 return false; 
 
 $ b @SuppressWarnings（rawtypes）
 @Override 
 public String render（Type arg0，List arg1，SessionFactoryImplementor arg2）
抛出QueryException { 
返回render（arg1，arg2）; 
 
 
  

它将查询转换为：

  to_tsvector（'english':: regconfig，studio0_.companyName）@@ to_tsquery（'english'，？）
  
 pre 
 
 PostreSQL日志：
 
 
  2014-02-11 15:10:39 CET错误：tsquery语法错误：one two
 2014-02-11 15:10:39 CET声明：select studio0_.uuid as uuid164_，studio0_.addressScore as addressS2_164_，studio0_.adwordsAktivity as adwordsA3_164_，studio0_.affDiversityScore as affDiver4_164_，studio0_.allSources as allSources164_，studio0_.backgroundImg as backgrou6_164_，studio0_.city as city164_，studio0_.clusterDiversityScore as clusterD8_164_，studio0_.companyName为companyN9_164_，studio0_.companyNameCount为company10_164_，studio0_.companyType为company11_164_，studio0_.completenessScore为complet12_164_，studio0_.contentTags为content13_164_，studio0_.crefoId为crefoId164_，studio0_.decisionMaker1 a s decisio15_164_，studio0_.decisionMaker2 as decisio16_164_，studio0_.description as descrip17_164_，studio0_.email as email164_，studio0_.email2 as email19_164_，studio0_.emailCount as emailCount164_，studio0_.fax1 as fax21_164_，studio0_.fax2 as fax22_164_，studio0_.faxImpressum as faxImpr23_164_ ，studio0_.fbProfileUrl如fbProfi24_164_，studio0_.formOfAddress如formOfA25_164_，studio0_.hasGeoCode如hasGeoCode164_，studio0_.house如house164_，studio0_.imprintDataExtracted如imprint28_164_，studio0_.industry1如industry29_164_，studio0_.industry2如industry30_164_，studio0_.internetIq如internetIq164_，studio0_ .internetIqCluster如interne32_164_，studio0_.lastActivityDelta如lastAct33_164_，studio0_.lastActivitySource如lastAct34_164_，studio0_.lastActivityTime如lastAct35_164_，studio0_.logoImg如logoImg164_，studio0_.mgUuid如mgUuid164_，studio0_.mgcUuid如mgcUuid164_，studio0_.minCriticalityLevel如minCrit39_164_，studio0_.numCheckins作为numChec40_164_，studio0_.numL ikes as numLikes164_，studio0_.numLocations as numLoca42_164_，studio0_.numMentions as numMent43_164_，studio0_.numRatings as numRatings164_，studio0_.numSEMKeywords as numSEMK45_164_，studio0_.numVouchersSold as numVouc46_164_，studio0_.phone1 as phone47_164_，studio0_.phone2 as phone48_164_，studio0_.phone3 as phone49_164_，studio0_.phoneImpressum as phoneIm50_164_，studio0_.premiumRecordUrls as premium51_164_，studio0_.premiumRecords as premium52_164_，studio0_.qualityPrediction as quality53_164_，studio0_.rating as rating164_，studio0_.ratingSources as ratingS55_164_，studio0_.reachabilityScore as reachab56_164_，studio0_.sectorNameScore as sectorN57_164_， studio0_.sectorOverlapScore as sectorO58_164_，studio0_.sectorScore as sectorS59_164_，studio0_.socialMedia as socialM60_164_，studio0_.sourcesWithNoAff as sources61_164_，studio0_.starsOverall as starsOv62_164_，studio0_.street as street164_，studio0_.successfulWebsiteLookup as success64_164_，studio0_.targetGroupTags as targetG 65_164_，studio0_.teaser如teaser164_，studio0_.totalSrcEntities如totalSr67_164_，studio0_.totalSrcEntitiesWithNoAff如totalSr68_164_，studio0_.totalSummaryScore如totalSu69_164_，studio0_.videoUrl如videoUrl164_，studio0_.voucherUrls如voucher71_164_，studio0_.vouchers如vouchers164_，studio0_.website如website164_， studio0_.website2 as website74_164_，studio0_.website3 as website75_164_，studio0_.websiteAvailability as website76_164_，studio0_.websiteCount as website77_164_，studio0_.zip as zip164_ from studio studio0_ where to_tsvector（'german':: regconfig，studio0_.companyName）@@ to_tsquery（ 'german'，$ 1）= true limit $ 2 
  
 
 
解决方案
只有我想出的解决方案是手动转义searchText：
 
 pre $ String $ escapepedSearchText = searchText.replace（'，''））; 
  

I have following code:

String searchText = "...";

String sqlQuery = 
        "FROM Studio s " +
        "WHERE fts('english', s.companyName, :q) = true";

    Query q = JPA.em()
            .createQuery(sqlQuery)
            .setParameter("q", searchText);

When I pass a single word to searchText, it works:

String searchText = "one";

When I pass two words, like

String searchText = "one two";

I get

When I pass a quoted string however, it works again:

String searchText = "'one two'";

Is SetParameter not supposed to set proper quotes and escape the text?

P.S. fts is a PostgreSQL dialect function defined as follows:

public class MyPostgreSQLDialect extends PostgreSQLDialect {
    public MyPostgreSQLDialect() {
       registerFunction("fts", new PostgreSQLFullTextSearchFunction());
    }
}

...

public class PostgreSQLFullTextSearchFunction implements SQLFunction {

   public String render(@SuppressWarnings("rawtypes") List args, SessionFactoryImplementor factory) {
      if (args.size() != 3) {
         throw new IllegalArgumentException(
               "The function must be passed 3 arguments");
      }

      String ftsConfig = (String) args.get(0);
      String field = (String) args.get(1);
      String value = (String) args.get(2);

      String fragment = null;
      if (ftsConfig == null) {
         fragment = "to_tsvector(" + field + ") @@ " + "to_tsquery('"
               + value + "')";
      } else {
         fragment = "to_tsvector(" + ftsConfig + "::regconfig, " + field + ") @@ "
               + "to_tsquery(" + ftsConfig + ", " + value + ")";
      }

      return fragment;

   }

   @Override
   public Type getReturnType(Type columnType, Mapping mapping)
         throws QueryException {
      return new BooleanType();
   }

   @Override
   public boolean hasArguments() {
      return true;
   }

   @Override
   public boolean hasParenthesesIfNoArguments() {
      return false;
   }

    @SuppressWarnings("rawtypes")
    @Override
    public String render(Type arg0, List arg1, SessionFactoryImplementor arg2)
            throws QueryException {
        return render(arg1, arg2);
    }
}

It converts the query into:

to_tsvector('english'::regconfig, studio0_.companyName) @@ to_tsquery('english', ?)

P.P.S.

PostreSQL log:

2014-02-11 15:10:39 CET ERROR:  syntax error in tsquery: "one two"
2014-02-11 15:10:39 CET STATEMENT:  select studio0_.uuid as uuid164_, studio0_.addressScore as addressS2_164_, studio0_.adwordsAktivity as adwordsA3_164_, studio0_.affDiversityScore as affDiver4_164_, studio0_.allSources as allSources164_, studio0_.backgroundImg as backgrou6_164_, studio0_.city as city164_, studio0_.clusterDiversityScore as clusterD8_164_, studio0_.companyName as companyN9_164_, studio0_.companyNameCount as company10_164_, studio0_.companyType as company11_164_, studio0_.completenessScore as complet12_164_, studio0_.contentTags as content13_164_, studio0_.crefoId as crefoId164_, studio0_.decisionMaker1 as decisio15_164_, studio0_.decisionMaker2 as decisio16_164_, studio0_.description as descrip17_164_, studio0_.email as email164_, studio0_.email2 as email19_164_, studio0_.emailCount as emailCount164_, studio0_.fax1 as fax21_164_, studio0_.fax2 as fax22_164_, studio0_.faxImpressum as faxImpr23_164_, studio0_.fbProfileUrl as fbProfi24_164_, studio0_.formOfAddress as formOfA25_164_, studio0_.hasGeoCode as hasGeoCode164_, studio0_.house as house164_, studio0_.imprintDataExtracted as imprint28_164_, studio0_.industry1 as industry29_164_, studio0_.industry2 as industry30_164_, studio0_.internetIq as internetIq164_, studio0_.internetIqCluster as interne32_164_, studio0_.lastActivityDelta as lastAct33_164_, studio0_.lastActivitySource as lastAct34_164_, studio0_.lastActivityTime as lastAct35_164_, studio0_.logoImg as logoImg164_, studio0_.mgUuid as mgUuid164_, studio0_.mgcUuid as mgcUuid164_, studio0_.minCriticalityLevel as minCrit39_164_, studio0_.numCheckins as numChec40_164_, studio0_.numLikes as numLikes164_, studio0_.numLocations as numLoca42_164_, studio0_.numMentions as numMent43_164_, studio0_.numRatings as numRatings164_, studio0_.numSEMKeywords as numSEMK45_164_, studio0_.numVouchersSold as numVouc46_164_, studio0_.phone1 as phone47_164_, studio0_.phone2 as phone48_164_, studio0_.phone3 as phone49_164_, studio0_.phoneImpressum as phoneIm50_164_, studio0_.premiumRecordUrls as premium51_164_, studio0_.premiumRecords as premium52_164_, studio0_.qualityPrediction as quality53_164_, studio0_.rating as rating164_, studio0_.ratingSources as ratingS55_164_, studio0_.reachabilityScore as reachab56_164_, studio0_.sectorNameScore as sectorN57_164_, studio0_.sectorOverlapScore as sectorO58_164_, studio0_.sectorScore as sectorS59_164_, studio0_.socialMedia as socialM60_164_, studio0_.sourcesWithNoAff as sources61_164_, studio0_.starsOverall as starsOv62_164_, studio0_.street as street164_, studio0_.successfulWebsiteLookup as success64_164_, studio0_.targetGroupTags as targetG65_164_, studio0_.teaser as teaser164_, studio0_.totalSrcEntities as totalSr67_164_, studio0_.totalSrcEntitiesWithNoAff as totalSr68_164_, studio0_.totalSummaryScore as totalSu69_164_, studio0_.videoUrl as videoUrl164_, studio0_.voucherUrls as voucher71_164_, studio0_.vouchers as vouchers164_, studio0_.website as website164_, studio0_.website2 as website74_164_, studio0_.website3 as website75_164_, studio0_.websiteAvailability as website76_164_, studio0_.websiteCount as website77_164_, studio0_.zip as zip164_ from studio studio0_ where to_tsvector('german'::regconfig, studio0_.companyName) @@ to_tsquery('german', $1)=true limit $2

解决方案

The only solution I came up with was to escape searchText manually:

String escapedSearchText = String.format("'%s'", searchText.replace("'", "''"));

这篇关于setParameter（）没有设置正确的引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！