I have a situation in where I had to create a dynamic where clause that is based on filter options for a certain overview screen.
This dynamic filter is made on client side where they can select different options to build the dynamic filter.
This is more like a discussion and question together to see if I get the best practices of Outsystems.
below here you see the client action that is triggered when the filter is being changed by the user.This will trigger the server action RefreshFetchAllTassk
The following is the server action, here you notice the problem which is that the sql will give injection warning because of expand inline is on true.
The user doesn't have any input that they can use to type, it's all toggles and booleans. But I suppose that a decent programmer knows how to manipulate the client side input CustomFilter that is being passed to the server action which means that the sql is still vulnerable on server side.
How can I secure the customfilter so that I don't have the risk of sql injection?
Since Encoding it won't help me in this case I don't see any other way of trying to make it that the customfilter is being created on serverside or to make a custom validation action on the customfilter.
Hi @Owen Corstens ,
Since the users will select the "options", I would suggest to pass the selected options to the server action and only construct the custom filter in the server instead of constructing it in the client. In this way, there will be no chance for an attacker to manipulate the custom filter in the client since you are only constructing it in the server.
As for the inline parameter warning, you can follow these articles https://success.outsystems.com/documentation/best_practices/development/building_dynamic_sql_statements_the_right_way/ and https://success.outsystems.com/documentation/11/reference/errors_and_warnings/warnings/sql_injection_warning/
Regards,
Bryan
This sounds like basically one of the things that I've been considering doing. I was looking for such a confirmation or other valid ways of handling it.
Thanks!
Hi Have you been able to see this documentation?https://success.outsystems.com/documentation/best_practices/development/building_dynamic_sql_statements_the_right_way/
I would add a boolean to your query to check if the filter is needed; this way, you can manually filter and use multiple options simultaneously.
WHERE (@DateFilter_Enabled = 0 OR {entity}.[Date] = @DateFilter)
AND (@NameFilter_Enabled = 0 OR {entity}.[Name] LIKE '%' + @NameFilter + '%')
Good alternative, bit harder to follow up on if you're using this kind of logic in multiple places. But with the right documentation it should be fine I suppose.
Def a solution to consider! thanks :)
Hello @Owen Corstens
You can use BuildSafe_InClauseIntegerList or BuildSafe_InClauseTextList to prevent SQL injection.
https://success.outsystems.com/documentation/11/reference/errors_and_warnings/warnings/sql_injection_warning/
Regards
Nikhil Kumar Vijay