Posted by In my recent post about SQL Macros and their availability in 19c, I had some comments about parameter passing, these comments being related to a more general discussion what typically comes down to:
“Hmmmm … if we are changing the text of the SQL, and that change might be done based on user provided parameters, are we not just opening the door very very wide open and inviting Mr SQL Injection to the party?”
For example, consider a very trivial SQL Macro that just does some combining of two columns with a nominated separator. (We obviously do not need a macro to achieve this, but it will let us illustrate an important point).
SQL> create or replace
2 function combine(p_separator varchar2 default '-')
3 return varchar2 SQL_Macro is
4 l_sep_in_quotes varchar2(200) := chr(39)||p_separator||chr(39);
5 l_sql varchar2(200);
6 begin
7 l_sql :=
8 'select dname ||' ||
9 l_sep_in_quotes ||
10 '||loc from dept';
11
12 return l_sql;
13 end;
14 /
Function created.
A quick test of the code shows that it runs as expected
SQL> select * from combine();
DNAME||'-'||LOC
----------------------------
ACCOUNTING-NEW YORK
RESEARCH-DALLAS
SALES-CHICAGO
OPERATIONS-BOSTON
and we can throw in a DBMS_OUTPUT and deliberately force an error to see the SQL statement that is being generated. (Yes, I know I should be using DBMS_UTILITY.EXPAND_SQL_TEXT or DBMS_TF.TRACE, but I’ve gone with quick and simple here 
)
SQL> create or replace
2 function combine(p_separator varchar2 default '-')
3 return varchar2 SQL_Macro is
4 l_sep_in_quotes varchar2(200) := chr(39)||p_separator||chr(39);
5 l_sql varchar2(200);
6 begin
7 l_sql :=
8 'select dname ||' ||
9 l_sep_in_quotes ||
10 '||loc from dept';
11 dbms_output.put_line('sql='||l_sql);
12 return 1/0; -- l_sql;
13 end;
14 /
Function created.
SQL>
SQL> set serverout on
SQL> select * from combine();
select * from combine()
*
ERROR at line 1:
ORA-62565: The SQL Macro method failed with error(s).
ORA-01476: divisor is equal to zero
ORA-06512: at "MCDONAC.COMBINE", line 11
ORA-06512: at line 5
sql=select dname ||'-'||loc from dept
Any Oracle practitioner worth their salt will already have nervous tingles down their spine when they observe that I’m building up a SQL string using an input from the user, because the obvious giant gaping security hole waiting to be exploited is when someone with malicious intent starts to construct a call to the function along the lines of:
SQL> select * from combine(' from dept where 1=0 union all select username from all_users ....');
Rather then jump immediately on to the train to Hackerville, lets see what happens when I try to manipulate the parameter in a simpler fashion. (I’ve removed the divide by zero code to reset the function to working form)
SQL> select * from combine('~');
DNAME||''||LOC
---------------------------
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON
SQL> select * from combine('|');
DNAME||''||LOC
---------------------------
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON
Notice that the parameter seems to have been ignored. In all cases except the default, the value of the parameter is forced to null, no matter what we pass. As you would hope, when the SQL Macro feature was being built, a lot of thought has gone into the risks of SQL Injection that can manifest themselves whenever you start manipulating SQL text during execution.
A key part of understanding the construction of SQL with SQL Macros is that we do not build them with the literal value of parameters that are passed into the function. These parameters can be thought of as being similar to (but not the same as) place holders or binds, in that they are used to form a representation of the various components of the SQL. For example, consider this trivial macro
SQL> create or replace
2 function no_change(p_table dbms_tf.table_t)
3 return varchar2 SQL_Macro is
4 begin
5 return 'select * from p_table';
6 end;
7 /
Function created.
SQL>
SQL> select * from no_change(dept);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Notice that we do not build the SQL statement by concatenating the table name into the SQL string, the table name parameter “p_table” is embedded within the SQL string itself. The SQL execution engine is taking care of the appropriate mapping to the true table name. By nulling out the value of any varchar2 parameter, there is insulation against SQL injection attempts.
Returning to the initial function, it is easy now to see how to fix the function. The string concatenation is replaced with embedding the name of the separator parameter directly into the SQL string.
SQL> create or replace
2 function combine(p_separator varchar2 default '|')
3 return varchar2 SQL_Macro is
4 begin
5 return 'select dname || p_separator || loc from dept';
6 end;
7 /
Function created.
And now it works as desired.
SQL> select * from combine();
DNAME||P_SEPARATOR||LOC
----------------------------
ACCOUNTING-NEW YORK
RESEARCH-DALLAS
SALES-CHICAGO
OPERATIONS-BOSTON
SQL> select * from combine('~');
DNAME||P_SEPARATOR||LOC
----------------------------
ACCOUNTING~NEW YORK
RESEARCH~DALLAS
SALES~CHICAGO
OPERATIONS~BOSTON
For more on SQL Macros, there’s a great introduction here by Keith Laker.
Learning is not a spectator sport 
You make a very important point that will have to be repeated *a lot*: when we build the SELECT statement, we have to put the parameter *name* in the string, not the *value*.
What you don’t mention is that some parameters *are* names: when you call your no_change function, the parameter is the identifier “DEPT”, not the string ‘dept’. This parameter is not “dynamic”, it is an integral part of the SQL statement, so it can be checked and used at parse time to build the final, executable SELECT.
The fact that the parameter is an identifier makes the function “polymorphic” and not “dynamic”: it changes shape at parse time, not at execution time. It’s going to be fun understanding and explaining this again and again 🙂
(By the way, if I didn’t get this right I would appreciate a correction!)
Thanks and best regards,
Stew
I expect this is a point we’ll be making time and time again. It will take some for SQL macros to become “automatic” in terms of developers thinking “Hey, this is a perfect use case for a macro”. I think its a lot like analytic SQL and pattern matching. There will be the “expected” usage and then over time, people will come up with innovative “out of the box” uses for them. Exciting times ahead.
Hi Connor, Is there a way around the limitation that SQL Macros can’t be used with WITH Clause. E.g: ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported.
This prevents usage of SQL Macros based functions (parameterized views for e.g.) in Business Intelligence queries as most BI Tools use WITH clause quite extensively. For example, in some types of What If analysis, we can prepare an inline view as a proxy dimension with WITH clause taking config/user interface choices as input … and then proceed to operate on this dimenisonal view via star schema type join query. Wonder if there are any workarounds for the above restriction?
I’m doing some research internally
Thanks for the info.
More details:
I was able to overcome it in a simple sql script which was of type:
With Q1 as (
–fetch data from sql macro based function/view
select … from
)
–post processing
select …
from Q1
group by …
;
This gave rise to ORA-64630 mentioned above.
Modified it into a direct inline view invocation like:
–post processing
select …
from (
–fetch data from sql macro based function/view
select … from
)
group by …
;
Howver wonder if it can be used in places where sql is BI tool generated and where we cannot control the exact shape of the sql produced via UI. To be fair, Oracle Bi Tools like OBIEE or OAC have some config setting whereby WITH clause usage can be disabled and sql is generated w/o WITH clause. That’s ok for a demo/PoC usecase but is a rather harsh step to take for an Analytics environment with many people/groups using the DB/BI Tool.
Thanks again.