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 
Got some thoughts? Leave a comment