[jbosscache-issues] [JBoss JIRA] Created: (JBCACHE-1534) JDBCCacheLoader does not escape wildcard characters in generated LIKE clause

[Andrew Duckworth (JIRA)]

JDBCCacheLoader does not escape wildcard characters in generated LIKE clause
----------------------------------------------------------------------------

                 Key: JBCACHE-1534
                 URL: https://jira.jboss.org/jira/browse/JBCACHE-1534
             Project: JBoss Cache
          Issue Type: Bug
      Security Level: Public (Everyone can see)
    Affects Versions: 3.1.0.GA
            Reporter: Andrew Duckworth
            Assignee: Manik Surtani


JDBCCacheLoader does not escape wildcard characters '_' and '%' in generated LIKE clause. Node removal and recursive child loading generates the following SQL:

   private String constructRecursiveChildrenSql()
   {
      return "SELECT " + fqnColumn + "," + nodeColumn + " FROM " + table + " WHERE " + fqnColumn + " = ? OR " + fqnColumn + " LIKE ?";
   }

   @Override
   protected String constructDeleteNodeSql()
   {
      return "DELETE FROM " + table + " WHERE " + fqnColumn + " = ? OR " + fqnColumn + " LIKE ?";
   }

If the FQN contains the underscore character, SQL treats this as a match any single character. Similarly if the FQN contains a '%' then it is treated as matching any substring. This means incorrect FQNs may be matched and also causes some RDBs to do an index scan rather than an index seek, leading to greater lock contention in the database and poor performance.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira