How can we do a table sort in a SQL Query in Reactive Web so that we don't have the warning of SQL Injection?

I know that the EncodeSQL is useless here and BuildSafe_InClauseList from Sanitization is also useless. Reactive automatically creates a TableSort variable and an action OnSort on the respective table but if we pass this client value as an Expand Inline it will give the correct warning of SQL Injection. This is specially dangerous since we're dealing with client side code where variables can be easily tampered.

Help, please.

The following documents describe your situation but it doesn't mention how to create an injectable without the warning. I don't think it possible, I never could find a solution for this warning.

https://success.outsystems.com/Documentation/11/Reference/Errors_and_Warnings/Warnings/SQL_Injection_Warning

Vincent Koning wrote:

The following documents describe your situation but it doesn't mention how to create an injectable without the warning. I don't think it possible, I never could find a solution for this warning.

https://success.outsystems.com/Documentation/11/Reference/Errors_and_Warnings/Warnings/SQL_Injection_Warning


Thanks, but I already went through the Outsystems documentation in this regard. That's why I'm lost if this is possible or not.

Any updates on this topic? 

Hi,

you're talking about advance queries, right?


Did you try this:

Cheers

Miguel Verdasca wrote:

Hi,

you're talking about advance queries, right?


Did you try this:

Cheers

"Deprecated Action

The VerifySqlLiteral action is being deprecated. Check the BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList actions instead." :)

 

Hi All,

As Nuno already said, those sanitation type of actions work on sql literals, not on parts of the sql clause, as sort by is.

So, as Vincent is saying, I don't think you can necessarily make the warning go away.  But you can still mitigate the dangers this warning is for, right ?

I don't know how complex your sorting is, but if it's just the standard "click on the column header to sort by that attribute, click again to sort descending" type of approach, then just build a function checking the TableSort string against the attributes in the entity, anything else, log a message and apply some standard sorting.

See attached oml for a simple reactive example, it will of course get more complex as soon as the table has data that are result of a join, or if sorting on several columns is combined, but that would just be some more complex logic in the function that turns a TableSort string into a safe OrderBy clause.

Have a look at the oml and let me know if this sort of thing is what you are looking for.

Dorine

Hi,

just to add, you can check this information:

Building Dynamic SQL Statements the Right Way

Cheers 

Dorine Boudry wrote:

Hi All,

As Nuno already said, those sanitation type of actions work on sql literals, not on parts of the sql clause, as sort by is.

So, as Vincent is saying, I don't think you can necessarily make the warning go away.  But you can still mitigate the dangers this warning is for, right ?

I don't know how complex your sorting is, but if it's just the standard "click on the column header to sort by that attribute, click again to sort descending" type of approach, then just build a function checking the TableSort string against the attributes in the entity, anything else, log a message and apply some standard sorting.

See attached oml for a simple reactive example, it will of course get more complex as soon as the table has data that are result of a join, or if sorting on several columns is combined, but that would just be some more complex logic in the function that turns a TableSort string into a safe OrderBy clause.

Have a look at the oml and let me know if this sort of thing is what you are looking for.

Dorine

 

Just to add some color to this reply.
The main concern is security and that's why that warning appears. As long as you can come up with a solution that you are confident it's reliable the warning is no longer a concern. Another thing to have in mind is that we probably will need to have a trade-off here. My two cents on this is that the most reasonable one is to have a bit more code than usual as long as it is easily understandable to ease on maintenance and troubleshooting. I tend to avoid dynamic SQL clauses because of that.


From what I could test, this was the best I could achieve:

Have the attributes listed mapped with an alias that will not be showed in runtime. The "Name" attribute could map with the "SortByName" alias and the OrderBy clause will only work for the attributes that are mapped, always falling back to a default one chosen at design time. The trade-off is that whenever there's a need to change attributes this code will also need to be changed. Also, the warning will still appear but you are now certain that your code is secure.