Hi appreciate someone can help me with this
I wish to pull out a list of employee of this grade M1, M2, M3, M4.
I store the grade range in a table and pass to my advance sql query but the result is always blank
In my query ; i have the following
SELECT {Employee}.* from {Employee} where {Employee}.[grade] in (@Grade)
Grade is text
Thank you
HI Jerah ,
Set expand Inline true.
Best regards
Devendra
Thank you it work but i noticed i need to change my parameter from M1, M2, M3, M4 to 'M1', 'M2', 'M3', 'M4' else i will get syntax error.
Also it is warning me of sql injection if i expan inline to true, any concern ?
Yes as suggested by vinod and vikas kindly use build safe actions to prevent SQL injection
Hi Jerah,
Please use BuildSafe_InClauseIntegerList or BuildSafe_InClauseTextList Action of Sanitization API.
https://success.outsystems.com/Documentation/11/Reference/Errors_and_Warnings/Warnings/SQL_Injection_Warning
https://success.outsystems.com/Documentation/11/Reference/OutSystems_APIs/Sanitization_API#BuildSafe_InClauseIntegerList
Thanks
Vinod
If you use expand inline true, OS will give warning. Because as per best practice you should avoid expand inline true in query.
But still if its your requirement then you need to sanitize the input list first before passing it to sql. I think if you use sanitize (use the BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList functions to build "WHERE column IN (@values)" clauses. ) in that case you don't need to manually set the list as '1'. Also it will be more safe.
https://success.outsystems.com/Documentation/Best_Practices/Performance_and_monitoring/Avoid_expand_inline_parameters_for_dynamic_values
Regards
Thank you all for all the input
@Jerah Less elegant solution but I think valid and readable is to load your grades into a list and use a for loop to process through the list making the SQL input the current grade in the for loop.