11.1d 2017-03-02 build: criteria values are mangled in actual query

[claudiobosticco]

SmartClient Version: SNAPSHOT_v11.1d_2017-03-02/Enterprise Deployment (built 2017-03-02)

Chrome on OSX

Hello, I've got a DMI method where I execute a query:

...
       Collection<GrantedAuthority> authorities = userDetails.getAuthorities();
        int authoritiesSize = authorities.size() + 1;
        Criterion[] criterions = new Criterion[authoritiesSize];
        criterions[0] = new SimpleCriterion("NOME_GRUPPO", "equals", LdapJuve.ROLE_GENERIC);
        int i = 1;
        for (GrantedAuthority grantedAuthority : authorities) {
            criterions[i++] = new SimpleCriterion("NOME_GRUPPO", "equals", grantedAuthority.getAuthority());
        }
        AdvancedCriteria advancedCriteria = new AdvancedCriteria(DefaultOperators.Or, criterions);
        DSRequest dsRequest = new DSRequest("GRUPPI", "fetch");
        dsRequest.setCriteria(advancedCriteria);
        roles = dsRequest.execute().getDataList();
...

after switching from 2017-02-28 build to 2017-03-02 build, the values which appear correct in the criteria ie as 'ROLE_TICKETING', are changed in the actual query in 'ROLE\_TICKETING', as you may see in the tomcat logs (db is Oracle):

2017-03-03 09:13:52,262 INFO  RequestContext URL: '/Jtk/', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36': Safari with Accept-Encoding header 
2017-03-03 09:13:56,999 DEBUG SQLDataSource DataSource 2939 acquired SQLDriver instance 284651400 during initialization 
2017-03-03 09:13:57,000 DEBUG DSRequest Caching instance 2939 of DS 'GRUPPI' from DSRequest.getDataSource() 
2017-03-03 09:13:57,000 DEBUG DSRequest Caching instance 2939 of DS GRUPPI 
2017-03-03 09:13:57,000 DEBUG DeclarativeSecurity Processing security checks for DataSource null, field null 
2017-03-03 09:13:57,000 DEBUG DeclarativeSecurity Request is not a client request, ignoring security checks. 
2017-03-03 09:13:57,000 ERROR AmbienteProperties httpServletRequest null, userà loadPropertiesWithRealPath 
2017-03-03 09:13:57,000 DEBUG DeclarativeSecurity Processing security checks for DataSource null, field null 
2017-03-03 09:13:57,000 DEBUG DeclarativeSecurity Request is not a client request, ignoring security checks. 
2017-03-03 09:13:57,000 DEBUG AppBase [builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application 
2017-03-03 09:13:57,000 DEBUG AppBase [builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation 
2017-03-03 09:13:57,001 INFO  SQLDataSource [builtinApplication.null] Performing fetch operation with
    criteria: {criteria:[{value:"Jtk",fieldName:"ID_APP",operator:"equals"},{criteria:[{value:"ROLE_GENERIC",fieldName:"NOME_GRUPPO",operator:"equals"},{value:"ROLE_TICKETING",fieldName:"NOME_GRUPPO",operator:"equals"}],operator:"or"}],operator:"and",_constructor:"AdvancedCriteria"}    values: {criteria:[{value:"Jtk",fieldName:"ID_APP",operator:"equals"},{criteria:[{value:"ROLE_GENERIC",fieldName:"NOME_GRUPPO",operator:"equals"},{value:"ROLE_TICKETING",fieldName:"NOME_GRUPPO",operator:"equals"}],operator:"or"}],operator:"and",_constructor:"AdvancedCriteria"} 
2017-03-03 09:13:57,001 INFO  SQLDataSource [builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause 
2017-03-03 09:13:57,002 INFO  SQLDataSource [builtinApplication.null] 2939: Executing SQL query on 'dbJFrame': SELECT GRUPPI.ID_REC, GRUPPI.NOME_GRUPPO, GRUPPI.DES_GRUPPO, GRUPPI.DES_GRUPPO_EN, GRUPPI.ID_APP, GRUPPI.SUPER_ADMIN FROM DBJFRAME.GRUPPI WHERE ((GRUPPI.ID_APP = 'Jtk' AND GRUPPI.ID_APP IS NOT NULL) AND ((GRUPPI.NOME_GRUPPO = 'ROLE\_GENERIC' AND GRUPPI.NOME_GRUPPO IS NOT NULL) OR (GRUPPI.NOME_GRUPPO = 'ROLE\_TICKETING' AND GRUPPI.NOME_GRUPPO IS NOT NULL))) 
2017-03-03 09:13:57,003 DEBUG PoolableSQLConnectionFactory [builtinApplication.null] makeObject() created an unpooled Connection '1176511095' 
2017-03-03 09:13:57,003 DEBUG SQLConnectionManager [builtinApplication.null] Borrowed connection '1176511095' 
2017-03-03 09:13:57,004 INFO  SQLDriver [builtinApplication.null] Executing SQL query on 'dbJFrame' using connection '1176511095': SELECT GRUPPI.ID_REC, GRUPPI.NOME_GRUPPO, GRUPPI.DES_GRUPPO, GRUPPI.DES_GRUPPO_EN, GRUPPI.ID_APP, GRUPPI.SUPER_ADMIN FROM DBJFRAME.GRUPPI WHERE ((GRUPPI.ID_APP = 'Jtk' AND GRUPPI.ID_APP IS NOT NULL) AND ((GRUPPI.NOME_GRUPPO = 'ROLE\_GENERIC' AND GRUPPI.NOME_GRUPPO IS NOT NULL) OR (GRUPPI.NOME_GRUPPO = 'ROLE\_TICKETING' AND GRUPPI.NOME_GRUPPO IS NOT NULL))) 
2017-03-03 09:13:57,007 INFO  DSResponse DSResponse: List with 0 items 
2017-03-03 09:13:57,007 DEBUG DSRequest About to free up resources for request of type fetch on DataSource GRUPPI 
2017-03-03 09:13:57,007 DEBUG SQLDataSource About to clear SQLDriver state for DS instance 2939 
2017-03-03 09:13:57,007 DEBUG SQLDriver Freeing SQLDriver dbConnection 1176511095 for SQLDriver instance 284651400 
2017-03-03 09:13:57,008 DEBUG SQLConnectionManager About to close $Proxy35 with hashcode "1176511095" 
2017-03-03 09:13:57,010 DEBUG SQLDataSource About to clear SQLDriver state for DS instance 2939 
2017-03-03 09:13:57,010 INFO  DSResponse DSResponse: List with 0 items 
2017-03-03 09:13:57,010 DEBUG DSRequest About to free up resources for request of type fetch on DataSource GRUPPI 
2017-03-03 09:13:57,010 DEBUG DSRequest Ignoring freeResources call because they have already been freed 
2017-03-03 09:13:57,010 DEBUG DSRequest Ignoring freeQueueResources call because they have already been freed

[Blama]

Hi,

this looks like escaping for Oracle LIKE, where "_" is the one-char placeholder (similar to '%').
IMHO this should only happen when really a LIKE is used in the generated SQL.

Best regards
Blama

[claudiobosticco]

and this is the dataSource 'GRUPPI':

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="GRUPPI" dataFormat="iscServer" serverType="sql"
            dbName="dbJFrame" schema="DBJFRAME"
            tableName="GRUPPI"
            requiresAuthentication="true"
>
    <fmt:bundle basename="i18nMessages" encoding="utf-8"/>
    <fields>
        <field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true">
            <sequenceName>SEQUENCE_REC</sequenceName>
        </field>
        <field sqlType="varchar" sqlLength="20" name="NOME_GRUPPO" type="text" required="true" length="15"
               canEdit="true"/>
        <field sqlType="varchar" sqlLength="30" name="DES_GRUPPO" type="text" required="true" length="30"/>
        <field sqlType="varchar" sqlLength="30" name="DES_GRUPPO_EN" type="text" required="true" length="30"/>
        <field sqlType="varchar" sqlLength="20" name="ID_APP" type="text" required="true" length="20" hidden="true"/>
        <field sqlType="varchar" sqlLength="1" name="SUPER_ADMIN" type="boolean" required="true"
               hidden="true" sqlStorageStrategy="singleCharTF"/>
    </fields>
    <operationBindings>
            
        <operationBinding operationType="fetch" serverMethod="fetch">
            <serverObject lookupStyle="new" className="com.juve.dmi.GruppiDMI"/>
        </operationBinding>
    </operationBindings>
</DataSource>

I may add that the bug seems due to a change in escapeValueUnquoted method of com.isomorphic.sql.OracleDriver class

[Isomorphic]

Confirmed and fixed. Fix will appear in the next nightly build - sorry we broke you!

[claudiobosticco]

SmartClient Version: SNAPSHOT_v11.1d_2017-03-05/Enterprise Development Only (built 2017-03-05)

I can confirm it's fixed, thank you very much.