Advanced query sql into a inline parameter

I want to create a query filter with an inline parameter that has this code.

How to do that without the inject sql warning, EncodeSQL doesnt do that

SyntaxEditor Code Snippet

and   {table}.[attribute] IN ('a','b','c') OR {table}.[attribute] IN ('')

Hi Filipe,

Good Day.

I have couple of questions,

1. What is backend of your environment? MS-SQL or Oracle? 

2. Is your Query executing correctly despite the inject sql warning?


Thanks & Regards,

Swaroop

1. What is backend of your environment? MS-SQL or Oracle?  MySQL

2. Is your Query executing correctly despite the inject sql warning? Yes

Hi Filipe,

I think its related to the attributes you are using in query as inline parameters, Outsystems treats it as a possible way of SQL injection, so it displays warning as it is not an standard practice in Outsystems.

But as you specified query will work:)

The query works, I just want to get rid of the warnings

Hi Filipe, 

First of all bear in mind EncodeSQL will do nothing as far as preventing SQL injections, it's just a formatting tool.

Read more here:
https://success.outsystems.com/Documentation/10/Reference/Errors_and_Warnings/Warnings/SQL_Injection_Warning


Basically try to avoid this situation all together if possible. Be very careful with what you pass into your advanced SQL queries. 

If you consider the warning taken care of, just right click and hide it.

Yes I know that, the filter SQL is calculated by a function, I have notice the functions BuildIntegexpto by outsystems, but in this case it doesnt apply and it seems that outsystems is checking just by the function name. Also I know that I can hide the message  but I am asking if it has another way. Thanks

I'm afraid this is one of those warnings that will always be there because of the danger it could pose. I could be wrong though.

Hi, just curious what is the expected value of param1? If you're expecting it to be just a text/number value there isn't any need to turn on the expand inline. 

The below query returns just fine and returns all rows when the input is empty

Caulibeam wrote:

Hi, just curious what is the expected value of param1? If you're expecting it to be just a text/number value there isn't any need to turn on the expand inline. 

The below query returns just fine and returns all rows when the input is empty

the param1 is not the input parameter, I edited the syntax to avoid confusion

Filipe Lourenço wrote:

Yes I know that, the filter SQL is calculated by a function, I have notice the functions BuildIntegexpto by outsystems, but in this case it doesnt apply and it seems that outsystems is checking just by the function name. Also I know that I can hide the message  but I am asking if it has another way. Thanks

Hi Filipe,

Expand inline is the only option AFAIK and as Mariano said this warning will be there but there is no need to be afraid :)

This warning is for developers to be cautious while using this option.

You should not use this option if your parameter comes from a user input (client side) because this is when it becomes dangerous. But if your parameter is something which you generated in your action then you can use it and ignore (hide) the warning.

Hi,

There is no way to get rid of the warning.
The only thing you can do is hide it.

Cheers.

Filipe Lourenço wrote:

I want to create a query filter with an inline parameter that has this code.

How to do that without the inject sql warning, EncodeSQL doesnt do that

SyntaxEditor Code Snippet

and   {table}.[attribute] IN ('a','b','c') OR {table}.[attribute] IN ('')

Hi Filipe, 

If your intention is to have the {table}.[attribute] be defined by the parameter. then as mentioned by the other folks it won't be possible. 

However, if it is the ('a','b','c') you are looking to make dynamic, you can use this two functions in the Sanitization API BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList

https://success.outsystems.com/Documentation/Best_Practices/Development/Building_Dynamic_SQL_Statements_the_Right_Way



Caulibeam wrote:

Filipe Lourenço wrote:

I want to create a query filter with an inline parameter that has this code.

How to do that without the inject sql warning, EncodeSQL doesnt do that

SyntaxEditor Code Snippet

and   {table}.[attribute] IN ('a','b','c') OR {table}.[attribute] IN ('')

Hi Filipe, 

If your intention is to have the {table}.[attribute] be defined by the parameter. then as mentioned by the other folks it won't be possible. 

However, if it is the ('a','b','c') you are looking to make dynamic, you can use this two functions in the Sanitization API BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList

https://success.outsystems.com/Documentation/Best_Practices/Development/Building_Dynamic_SQL_Statements_the_Right_Way



And how do you do the empty part in case of text ?


I suppose if you pass in a blank list it will be populated as ('')