SQL Macros = SQL Injection ?

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 Smile)


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.

4 comments

  1. 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

    1. 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.

  2. 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?

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.