Frequently, when dynamic sql is used it takes a form similar to this:
v_sql :=
'select '
|| CASE WHEN choice1 IN ('A', 'B') THEN 'col1, col2' WHEN choice = 'C' THEN 'col2,col3' ELSE 'col4,col5' END
|| ' from '
|| CASE WHEN choice2 IN ('X', 'Y', 'Z') THEN 'table1' ELSE 'table2' END
|| ' where '
|| CASE ...
Such a construct will certainly work but I find it can become unwieldy. If the bits of static syntax are complex with many dynamic parts to be concatenated it’s often difficult to tell what the query is supposed to look like let alone what it should do. It can get even more complicated if the construction is spread over a variety of different conditional blocks…
v_sql := 'UPDATE employees SET last_updated = sysdate';
IF p_year_end_review
THEN
v_sql := v_sql || ',review_grade = :new_grade';
END IF;
IF p_transfer
THEN
v_sql := v_sql || ', department = :new_dept';
END IF;
v_sql := v_sql || ' where employee_id = :emp_id';
Other complications occur when the string has sub-strings within it. In an example like the one below, using the “q” string syntax could help but would still lead complicated mess to try to maintain.
v_sql :=
'select col1,col2,col3 from '
|| table1
|| ' inner join '
|| table2
|| ' on '
|| table1
|| '.id = '
|| table2
|| '.parent_id where '
|| table1
|| '.status = ''OPEN'' and '
|| table2
|| '.active = ''Y''';
Instead of building SQL by concatenation as I used to, I’ve found SQL templates with REPLACE to be an easier form to work with. That is, I create a single string that represents the entire statement with stub values where the dynamic portions will be injected.
Using this model, the first statement above might be rewritten as follows:
v_sql := 'select ~columns~ from ~table~ where ~conditions~';
v_sql :=
REPLACE(
v_sql,
'~columns~',
CASE WHEN choice1 IN ('A', 'B') THEN 'col1, col2' WHEN choice = 'C' THEN 'col2,col3' ELSE 'col4,col5' END);
v_sql := REPLACE(v_sql, '~table~', CASE WHEN choice2 IN ('X', 'Y', 'Z') THEN 'table1' ELSE 'table2' END);
v_sql := REPLACE(v_sql, '~conditions~', ...The initial template is quite plain, it reads like a simple SQL select statement. While the conditions deciding which values will be injected are somewhat complex, the overall structure is clearly laid out in the first line. I’ve intentionally simplified the WHERE-clause to a mass replacement of all conditions but if the structure is known ahead of time it could be mapped to multiple, targeted injections.
The update statement utilizes a key feature of SQL: white space is irrelevant. If either of the conditions are false, the replacement value will be NULL and the resulting Update SQL will simply have an extra line and some spaces in it where that SET clause could have been inserted.
v_sql := 'UPDATE employees SET last_updated = sysdate
~grade_change~
~dept_change~
where employee_id = :emp_id';
v_sql := REPLACE(v_sql, '~grade_change~', CASE WHEN p_year_end_review THEN ',review_grade = :new_grade' END);
v_sql := REPLACE(v_sql, '~dept_change~', CASE WHEN p_transfer THEN ', department = :new_dept' END);
The last query I think really demonstrates the benefit when the SQL statement starts to become large with embedded sub-strings. Here it was only a few lines long and two sub-strings, but the resulting code is still much easier to read.
v_sql := q'[SELECT col1, col2, col3
FROM ~table1~ INNER JOIN ~table2~
ON ~table1~.id = ~table2~.parent_id
WHERE ~table1~.status = 'OPEN'
AND ~table2~.active = 'Y']';
v_sql := REPLACE(v_sql, '~table1~', table1);
v_sql := REPLACE(v_sql, '~table2~', table2);
One obvious critique of this method is it’s always possible to simply display the resulting SQL constructions before or after executing them, but if there is an error it might be quite difficult to find exactly where in a series of concatenations the particular syntax error is creeping in.
The template model will, hopefully, provide a more clear construct of what the final SQL is supposed to look like. In fact, for this third example I took a valid sql statement and simply stubbed out the tables to create the template. I find this helpful in initial development because I can use real statements that actually work as my base and then, instead of ripping them into pieces that will need to be concatenated back together, I just take a framework I already know works and create stubs for the parts that will change. In cases where I’m adding or subtracting entire conditions or columns it’s not a perfect mapping but it still helps as a first pass to get something working and then add or change as needed to fulfill the rest of the dynamic requirements.
Another benefit for me is the mnemonic of using REPLACE for syntax injection. When concatenating it might be difficult to see some of the holes where a SQL injection vulnerability might creep in. By creating explicit stubs that self-identify as injection points they are hopefully easier to find and then the injection step via REPLACE for each stub value will hopefully highlight the vulnerability areas.
I also use this technique to help teach developers the difference between injection of a variable value versus binding of the variable in statement construction.
I try not to use dynamic SQL unless it’s truly warranted, but when I do I’ve found this template method to be faster to write, and easier to get right than the concatenation method. I hope you find it helpful as well. Questions and comments, as always, are welcome.