Skip to Content (Press Enter)
UserImage.jpg
Jay-jay
911Views
9Comments
Solved
Advance SQL
Question

Hi experts,


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

Please see image below

0
0
02 Jul 2019
Copy Link
2019-01-14 10-48-23
João Pedro Batista
Staff
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


See solution in context
2
0
03 Jul 2019
Copy Link
2023-07-28 17-00-32
Marco Arede
 
MVP

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)

1
0
02 Jul 2019
Copy Link
2026-02-26 06-29-24
Rahul
 
MVP

Hi Jay,


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


Thanks

Rahul 

2
0
02 Jul 2019
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP

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.

0
0
02 Jul 2019
Copy Link
2019-09-24 18-41-25
Jorge Martins
 
MVP

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!

1
0
02 Jul 2019
Copy Link
2019-01-14 10-48-23
João Pedro Batista
Staff
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


2
0
03 Jul 2019
Copy Link
2023-07-28 17-00-32
Marco Arede
 
MVP

Very useful info, thanks João Pedro Batista

1
0
03 Jul 2019
Copy Link
2019-01-14 10-48-23
João Pedro Batista
Staff

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

2
0
03 Jul 2019
Copy Link
2019-09-24 18-41-25
Jorge Martins
 
MVP

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.

2
0
04 Jul 2019
Copy Link
2019-01-14 10-48-23
João Pedro Batista
Staff

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

1
0
08 Jul 2019
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.