Hi experts,


Im using the Advance SQL to use IN, but it does not display the results.

Please see image below

Hi Jay-jay,

An IN statement (using an expression string input in line) is something like:

Hope it helps (and see if your variable has the correct string format)

Hi Jay,


Please set "ReferenceNhmber" property Expand Inline "Yes".


Thanks

Rahul 

What Rahul said. If you don't do that, SQL will see just a single value in the "IN", and doesn't parse the comma. In addition, you need to put single quotes around the items you want to search for, like Marco said.

Hi Jay-jay,

Both Marco and Rahul are correct:

  1. If you are trying to build your SQL dynamically, the inputs that are to be used as is need to be marked Expand Inline = Yes. You also need to make sure they are safe, by using EncodeSQL on the parts that may come directly from the user (to avoid code injection);
  2. Once problem 1. is fixed, if Request.ReferenceNumber is a Text, then the correct syntax for the right side of the IN would be ('SUL-...','SUL-2019...'). What you have would be (SUL-...,SUL-2019...) - notice the lack of quotes.

Hope this helps!

Edit: ooops, seems Kilian beat me to it... and way more concise too!

Solution

Jorge Martins wrote:

Hi Jay-jay,

Both Marco and Rahul are correct:

  1. If you are trying to build your SQL dynamically, the inputs that are to be used as is need to be marked Expand Inline = Yes. You also need to make sure they are safe, by using EncodeSQL on the parts that may come directly from the user (to avoid code injection);
  2. Once problem 1. is fixed, if Request.ReferenceNumber is a Text, then the correct syntax for the right side of the IN would be ('SUL-...','SUL-2019...'). What you have would be (SUL-...,SUL-2019...) - notice the lack of quotes.

Hope this helps!

Edit: ooops, seems Kilian beat me to it... and way more concise too!

You are correct, but now, you can accomplish step 1 and 2 by using the BuildSafe_InClauseTextList on the SQL parameter with the enabled Expand Inline.
This function "Returns a comma-delimited text value with the encoded version of all the text values provided as input. The returned value can be safely used in a SQL "IN" clause."

The SQL Injection Warning reference documentation talks a bit about this use-case (IN clauses) and the recommendation is to use the BuildSafe_InClause functions:
"Make sure you avoid the following bad practices of using EncodeSql:
(...)
Do not build "WHERE column IN (@values)" clauses by wrapping all the values in a EncodeSql call:
values = EncodeSql(name1 + "," + name2 + "," + name)
This approach will not protect you from SQL injection.
Instead, use the BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList functions to build "WHERE column IN (@values)" clauses.
"

The new BuildSafe_InClause functions from the Sanitization extension were added on OutSystems 11 Platform Server Release Apr.2019 and OutSystems 10 Platform Server 10.0.1005.2.


For anyone interested in reading a bit more about this topic, check out the best practices on Building dynamic SQL statements the right way.

Cheers


Solution

Very useful info, thanks João Pedro Batista

You are welcome Marco!

I forgot to mention it originally, but the BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList functions can be found in the Sanitization extension. (I edited my previous post to include this information).

Hi João,

Great news! I believe I had read about it already, but totally forgot... thanks for pointing it out, and always good to see the platform evolving and making the developers' life easier and applications safer all in one fell swoop.

Jorge Martins wrote:

Hi João,

Great news! I believe I had read about it already, but totally forgot... thanks for pointing it out, and always good to see the platform evolving and making the developers' life easier and applications safer all in one fell swoop.

You are more than welcome Jorge, and it is really wonderful to read your feedback regarding the feature!

Cheers