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

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 Fix For: 3.2.0.GA Attachments: JDBCCacheLoader.java, JDBCCacheLoaderConfig.java 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.