Best way to solve warning using dynamic OrderBy in AvanceSQL

Hi there!

I have the typical advanced SQL with the order by passed with ExpandInline = Yes, where OrderBy parameter is the sort column in table, form example: Id Desc

Now, I have a warning that says: 

SQL Injection - When adding an expand inline parameter after a Group By or Order By, make sure to sanitize the parameter value. Otherwise, the action may be vulnerable to SQL injection.

In order to "sanitize the SQL parameters", do it in following best practices, and removing the IDE warning, what would be the best way?

  • If you use ENCODESQL, a new warning appear: "SQL Injection - EncodeSql should only be used to escape string literals. Check the EncodeSql documentation to learn how to use this function" ,so it doesn't seem to be the right choice
  • If try to use VerifySqlLiteral, warning disappear, but code fails, returning an exception form Sanitize function: "INVALID SQL LITERAL".
  • In other forums I have read, even more, that VerifySQLLiteral is deprecated, that should use "BuildSafe_InClauseTextList", but this generate an SQL like this:
    SELECT {User}.* from {User} order by 'Id Desc'
    and produce and SQL Error. I could cheat this, removing Quotes, doing something like this: SubStr(BuildSafe.out,1,Length(BuildSafe.out)-2), but it is like doing nothing, and it sure does not follow the best practices of Outsystems to avoid SQL Injection

So, how to do it following best practices and removing the warning from Service Studio?


Thanks,

Hi there!

I am not forgetting this post, but it took me a long time to get an official response.

I have been talking with support team, and their answer is:

  • don't use expand inline (but it makes no sense, there is no alternative to when you want to do a subquery, or use a list of values)
  • hide the message if I follow the best practices that we have mention in this post.

But they have recognize that there is no "official" way to remove the warning (not just hide), and do it following best practices, and has send the problem to "development team".


So can you check that on this post?

There is a really good explanation by @Dorine Boudry and Cristiana Umbelino

https://www.outsystems.com/forums/discussion/59321/sort-sql-query-in-reactive-web/#Post248415

Kind regards,

Márcio


Hi Márcio,

I had checked this post before, and talk about some topics that I believe don't resolve entire question. Talk about Building Dynamic SQL Statements the Right Way, that redirect to this other topic, How to enable dynamic sorting in a table fed by a SQL query, that, In addition to making a function with a series of very manual replacements, it does not solve the warning that Outsystems gives (even if it solves the vulnerability itself). In one of the screenshot in the link you could se that warning still appears

Other people here also offer an oml, with the same solution, but, also, the warning is still present.

Is there anyway to, on the one hand, to solve the warning in a correct way, and on the other hand, that Outsystems detects that it has been solved and does not show the warning?


Thanks,

In this case, you can still follow the best practices but the warning will still be there.

The topics/articles you shared demonstrates a nice way of secure parameters with the expand inline!

Attention that the BuildSafe_In is when you want to use the IN clause among a list of data.

So from what I know now on this topic, and is not completely true what I am going to say, is that the warning is nothing to be afraid of, because is there for us to know that maybe it might have a security breach through the input parameter that has the expand inline. So maybe you can hide the warning.

On the other hand, I would still ask the support on Outsystems why that still happen nowadays.

ps: I would want to know how to clean that warning, and why we cannot because I am working and I have worked where that warning is still there....

I believe that the right answer is "there is no way to remove warning, and do it right". I'll ask Outsystems, to try to get an official asnwser.

Hi,

Once try the below code.

If(TableSort = "", "1", EncodeSql(TableSort)) + If (OrderIsAscending , " ASC", " DESC")

Hi Vijay, 

I have checked your code, the problem is the same, Outsystems returns another warning 

Regards

Hi there!

I am not forgetting this post, but it took me a long time to get an official response.

I have been talking with support team, and their answer is:

  • don't use expand inline (but it makes no sense, there is no alternative to when you want to do a subquery, or use a list of values)
  • hide the message if I follow the best practices that we have mention in this post.

But they have recognize that there is no "official" way to remove the warning (not just hide), and do it following best practices, and has send the problem to "development team".


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.