Hi there,
i am wondering what is the meaning of the expand inline in Advance query'parameter's attribute:
It is said like this, but i cannot distinguish it with Expand Inline = No.
What exactly happen when it is set Yes vs No?
regards and thanks
Hi ibox,
Like I said in that topic, an "expand inline" parameter should contain parts of the SQL you want to execute, while a non-expand-inline parameter is just a variable. If you really can't grasp the difference, it's safe to say you shouldn't use it, until you're more experienced with writing SQL queries and you do get it.
hi @ibox
try to go through this discussion.
https://www.outsystems.com/forums/discussion/82078/expand-inline-parameters/
hope this will help you.
Regards,
Prince
yes , i read it, but still cannot understand.
Can u explain step by step what is happening to @userid vs @additionalwhereclause, in time sequence? thanks
Hi @ibox,
When you enable expand inline it will treat parameters as part of SQL itself creating risk for SQL injection so for example if expand inline is enabled this sql
select {user}.[id] from {user} where {user}.[id] = @userid ; @userid = "1; delete from {user}; --"this will pose a risk of SQL injection
When you disable the expand inline it will be treated as a parameter and escaped so it will not pose the risk of SQL injection beware while using this feature
Hope this helpsKshitij Raheja
Hi,
By using expand inline parameter, you can insert SQL content inside OutSystems SQL query. This parameter isn't a SQL parameter, in the sense that it isn't created in the database. It's calculated during runtime and textually expanded inside the SQL call. https://success.outsystems.com/documentation/best_practices/performance_and_monitoring/avoid_expand_inline_parameters_for_dynamic_values/
Thanks
hi thanks,
but i am still confused what do u mean by this parameter isn't a SQL parameter... it is a SQL parameter - look in advance sql widget.
regards
You can learn more about here.
https://success.outsystems.com/documentation/11/reference/errors_and_warnings/warnings/sql_injection_warning/Thanks
select {MyEntity}.* from {MyEntity} where {MyEntity}.[UserId] = @UserId @UserId could be - Tim or it could also be a list of users or a garbage value
This code with expand inline as NO will not change the SQL query in DBMS
It will consider the value of @UserID as a parameter and not expand the passed value to it The same SQL Query with Expand Inline as YES will first re-write the SQL query in DBMS
select {MyEntity}.* from {MyEntity} where {MyEntity}.[UserId] = \Tim, John, Roy etc. \
and that will degrade the performance first and then possess a risk of injection to SQL query
Inline parameters that change too often don't allow the database to optimize execution plans, since the engine keeps generating different queries
Hope this helps
Simply i want to know, what is happening to both parameter types inside:
1. Outsystems
2. DBMS server
thanks.
Hi @ibox
As I mentioned in my previous answer the basic difference is the parameter being escaped or not, when enabled expand inline will not add escape characters to the parameters making it directly part of the SQL itself and when disabled the value will be escaped so it will not be part of the SQL and will be treated like data only
so basically
1) Change in being treated as data or as a part of SQL (escaped or not escaped)2) No Change
"when set to No it is not part of SQL", so what part is it? isn't is a Sql that is sent to DBMS?
lemme show you an example of what will be sent to the database in both cases
1) expand Inline Disabledselect {user}.[id] from {user} where {user}.[id] = \1;
2) expand Inline Enabledselect {user}.[id] from {user} where {user}.[id] = 1;
I have mentioned the problems there can be when enabling it in my first answerI also would suggest reading about SQL injection and SQL escapeSQL Escape guide