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

[Krzysztof Sobolewski (JIRA)]

    [ https://jira.jboss.org/jira/browse/JBCACHE-1534?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12482585#action_12482585 ] 

Krzysztof Sobolewski commented on JBCACHE-1534:
-----------------------------------------------

I'd like to point out that this bug is the same as JBCACHE-1461.

> 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.

-- 
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