DS.XML & IN operator in the WHERE clause & Criteria

[code13]

Good day!

Can i use Criteria() for create list of values in "WHERE value IN (value1,value2,...)" ?

I tried to use an array, but it did not give the desired effect

<whereClause>
requeststatusplanid IN ($criteria.requeststatusplanid)
</whereClause>

Thanks!

[Isomorphic]

You can use the "inSet" AdvancedCriteria operator to have this SQL formed for you.

Otherwise, if you have some reason to directly form the SQL text yourself, you can look at the Velocity language reference or you can do it in Java and use dsRequest.addToTemplateContext() to make the final SQL string available in the template.

[DevMo]

The DS.XML files are Velocity templates. You can unpack the array elements using the Velocity expression language, like so:

<whereClause>
org_id IN ( 
#foreach($org_id in $criteria.ORG_ID_ARRAY)
 '$org_id'
 #if( $velocityCount != $criteria.ORG_ID_ARRAY.size())
  ,
 #end 
#end
)
</whereClause>

Lines above are just for readability, you can keep it all on a single line if you want. The nested #if inserts a ", " between elements, but not after the last one.

The Velocity manual is here: https://velocity.apache.org/engine/releases/velocity-1.7/user-guide.html It's a quick read, pretty straightforward.

I hope this is helpful to you.

[Isomorphic]

Thanks DevMo.

Just for completeness, the other approaches mentioned (AdvancedCriteria / addToTemplateContext) may be preferable since the approach shown above does not protect against SQL injection attacks.

[DevMo]

Thanks DevMo.

Just for completeness, the other approaches mentioned (AdvancedCriteria / addToTemplateContext) may be preferable since the approach shown above does not protect against SQL injection attacks.

Thanks so much, very informative. I was operating under the mistaken assumption that because I was submitting criteria to an OperationBinding template, I was protected against SQL injection attacks. I won't submit arrays this way anymore.

I'll used the AdvancedCriteria w/ inSet operator from now on.

[code13]

DevMo , Isomorphic

thanks for your help!

[Isomorphic]

Right.

Just to clarify for others:

1. whenever you use one of SmartGWT's built-in Velocity variables directly in a template, for example just $criteria.someField, without using #foreach or some other Velocity directive, you are safe from SQL injection attacks

2. if you construct AdvancedCriteria with operators like that arrays (like inSet), client or server-side, you are protected from SQL injection attacks

3. it's only if you use Velocity directives to iterate down an Array, the keys and values of a HashMap, or in some other way directly access the underlying criteria values using Velocity that you could possibly introduce SQL injection attacks. Using Velocity in this way is essentially the same as hand-forming SQL in Java, and requires the same attention to escaping unsafe inputs.

[DevMo]

I have a query that I think requires a UNION ALL join, so I'm using a customSQL OperationBinding. Stop me right there if I'm off track already.

SUCCESS
If I create an AdvancedCriteria with an Array...

String[] statusTypes = { "approved", "queued", "submitted" };
AdvancedCriteria advancedCriteria = new AdvancedCriteria();
advancedCriteria.addCriteria(REQUESTS.STATUS, OperatorId.IN_SET, statusTypes);

and submit it to an OperationBinding via FetchData(advancedCriteria) like...

<customSQL>
    ...WHERE ${defaultWhereClause}
</customSQL>

...then I can see in the log that it executes as expected:

...
WHERE    (((REQUESTS_AUTO.STATUS IN ('approved', 'queued', 'submitted')) AND REQUESTS_AUTO.STATUS IS NOT NULL))

FAIL
However, if I try to access the criteria as an individual item...

...
WHERE    REQUESTS_AUTO.STATUS IN ${advancedCriteria.STATUS}

...the formulated query is not formatted correctly and so returns zero results:

WHERE    REQUESTS_AUTO.STATUS IN '[approved, queued, submitted]'

Please advise, thanks.

SmartClient Version: v9.1p_2016-06-17/PowerEdition Deployment (built 2016-06-17)
Oracle 12.1.x w/ OJDBC7.jar