Just Say No, Unless You Can’t

Dynamic SQL is powerful, flexible, and fraught with risk. Use it only when absolutely necessary.

By Steven Feuerstein

January/February 2018

Whether you run Oracle Database in the cloud or in a virtual machine, you will need to write SQL statements to both fetch from and change the contents of tables. And when you want that SQL to run as quickly as possible, be as secure as possible, and be as easy to maintain as possible, you will write PL/SQL APIs in the form of packages around your SQL statements.

So far, so good. But then you need to decide the following: how am I going to write that SQL inside PL/SQL? You have two basic choices:

• Static SQL
• Dynamic SQL

A SQL statement is static (also referred to as embedded SQL) when the statement is parsed at the time your PL/SQL program unit is compiled. Here is an example of a code block with static SQL:

DECLARE
   l_count INTEGER;
BEGIN
   SELECT COUNT(*) INTO l_count
      FROM user_objects;
  DBMS_OUTPUT.PUT_LINE ('Object count = ' || l_count);
END;

A SQL statement is dynamic when the statement is parsed at runtime. Here is the dynamic SQL version of the previous code block:

DECLARE
   l_count   INTEGER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM user_objects'
      INTO l_count;
   DBMS_OUTPUT.put_line ('Object count = ' || l_count);
END;

Compared to languages such as Java and JavaScript, it is very easy to write both static and dynamic SQL in a PL/SQL block. But that doesn’t mean that both types of SQL are equal and can or should be used interchangeably in your code.

In fact, I suggest the following very simple rule for deciding whether to write static or dynamic SQL:

Construct and execute SQL at runtime only when you have to.

There are three very important reasons to follow this rule:

Security. Dynamic SQL opens the door to SQL injection, which can lead to data corruption and the leaking of sensitive data. It is impossible to inject malicious code into a static SQL statement.

Performance. Although the overhead of executing dynamic SQL has gone way down over the years, it is certainly still faster to use static SQL. It’s also easier to optimize static SQL, because you can analyze explain plans before they execute in production and modify your SQL accordingly—right from your code editor.

Maintainability. The code you write to support dynamic SQL is more—literally more code—and harder to understand and maintain. Don’t make your life (and the lives of developers coming after you) any more difficult than it has to be.

In this article, I explore various inappropriate or unnecessary uses of dynamic SQL that I have come across over the years. If, as you read a section, you hear alarms ringing in your head along the lines of “Gee, that looks like something I wrote last week” or “Wait a minute—that’s exactly what Joe wants to put into production next week,” it’s time to take action and convert that dynamic SQL to static SQL.

Your users and your coworkers will thank you.

Changing Values Do Not Dynamic SQL Make

You need to write dynamic SQL when you don’t have all the information you need to construct and parse the statement at compile time.

You do not, however, need to know the values of bind variables (for example, the specific department ID or the name of the customer) in order to parse a SQL statement.

Consider the following function:

FUNCTION name_from_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   c_table_name   CONSTANT VARCHAR2 (100) := 'the_table';
   l_the_name              the_table.the_name%TYPE;
BEGIN
   EXECUTE IMMEDIATE
      'select the_name from ' || c_table_name || ' where id = ' || id_in
      INTO l_the_name;

   RETURN l_the_name;
END;

At first glance, it sure seems to need to be dynamic. The author of this program is concatenating the name of the table at runtime and completing the WHERE clause with the concatenation of the ID value.

On closer inspection, however, the table name is not actually dynamic. It is a constant value of “the_table.” So concatenation is not needed there.

The developer apparently believed, further, that because the value of the ID can change, it needs to be concatenated to the SQL statement; therefore, it has to be done dynamically. That is not true. The only parts of a SQL statement that must be concatenated are pieces of syntax (for example, when you sometimes need a GROUP BY but other times you don’t) or object names, such as a table name or a column name—when and only when those names change at runtime.

The values of bind variables, however, do not need to be concatenated. And, in fact, there’s nothing dynamic about the query in this function. It can—and certainly should—be rewritten as follows:

FUNCTION name_from_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   the_table.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM the_table
    WHERE id = id_in;

   RETURN l_the_name;
END;

The bottom line is that every time you come across a program that contains EXECUTE IMMEDIATE or calls to DBMS_SQL, look closely at the SQL statement being constructed. Ask yourself, “What don’t I know at the time of compilation?”

If the answer is, “only the values to be assigned to a column” or “the values used in a Boolean expression in the WHERE clause,” and so on, there is no need for dynamic SQL.

Say No to “One Package Fits All” Dynamic SQL APIs

Over the years, I have occasionally received emails from developers that simultaneously delight and dismay me: delight, because I love to see developers who exult in creating generic utilities for themselves and others to use, and dismay, because the generic utilities they created should never be used.

A great example of such a utility is a package that can be used to execute “any” SQL statement needed for an application, dynamically. The body of the email usually sounds something like this:

“Rather than teach everyone on the team how to write SQL, I will give them a few packaged procedures to execute, and those procedures will construct and execute all the SQL for them. They will be more productive and can avoid really bad SQL.”

Typically, the specification of such a package might look like this:

PACKAGE exec_any_sql AUTHID CURRENT_USER
IS
   PROCEDURE delete_from (schema_in      IN     VARCHAR2 DEFAULT USER,
                          table_in       IN     VARCHAR2,
                          where_in       IN     VARCHAR2 DEFAULT NULL,
                          rowcount_out      OUT INTEGER);

   PROCEDURE insert_into (schema_in      IN     VARCHAR2 DEFAULT USER,
                          table_in       IN     VARCHAR2,
                          columns_in     IN     DBMS_SQL.varchar2_table,
                          values_in      IN     DBMS_SQL.varchar2_table,
                          rowcount_out      OUT INTEGER);

   PROCEDURE update_in (schema_in      IN     VARCHAR2 DEFAULT USER,
                        table_in       IN     VARCHAR2,
                        where_in       IN     VARCHAR2 DEFAULT NULL,
                        columns_in     IN     DBMS_SQL.varchar2_table,
                        values_in      IN     DBMS_SQL.varchar2_table,
                        rowcount_out      OUT INTEGER);
END;

(The package body for exec_any_sql is located in Listing 1.)

And if I want to delete some row from table XYZ, I simply execute this procedure call:

exec_any_sql.delete_from (table_in => 'XYZ', where_in => 'xyz_id = 15');

How cool is that? It is not cool at all! Seriously, this is a very bad idea.

First, it is virtually impossible to support the full range of syntax available for these data manipulation language (DML) statements—and I haven’t even tried to support SELECTs.

Second, it is all but impossible to optimize the performance of SQL statements constructed this way.

Third, no one will ever want to use it. They will hate the person who wrote it. You will hate yourself if you wrote it.

This sort of utility is way too generic to be anything but a disaster waiting to happen to your application. You might be tempted to write such utilities, but do so only as an exercise to satisfy your curiosity. Never try to use such a utility in a real application.

Skin-Deep Dynamic SQL

Sometimes a program’s requirements (or implementation) seem at first glance to be an obvious candidate for dynamic SQL. Only with further analysis—driven by that key rule to use dynamic SQL only when necessary—do you realize that a static SQL implementation is possible and, therefore, preferable.

Suppose you came across this function in your application’s source code:

FUNCTION name_from_id (table_in   IN VARCHAR2,
                       id_in      IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   VARCHAR2 (32767);
BEGIN
   EXECUTE IMMEDIATE
      'select the_name from ' ||
         table_in ||
      ' where id = ' || id_in
      INTO l_the_name;

   RETURN l_the_name;
END;

It looks like the developer had no choice, right? EXECUTE IMMEDIATE is required, because the name of the table wasn’t known at the time of compilation—a classic requirement for a dynamic SQL implementation.

But a single function does not an application make. Either this function is being called or it should be dropped. If it is being called, you should check to see where and how the function is used and see if that usage justifies the dynamic SQL implementation.

You could do a text search through your code (via an editor such as Sublime) or an object search (in Oracle SQL Developer). You could also use the PL/Scope in Oracle Database if you gathered identifier information across your source code.

Suppose that after doing this analysis, you find that the function is called twice as follows:

l_name := name_from_id (table_in => 'TABLE1', id_in => l_id);
l_recent_name := name_from_id (table_in => 'TABLE2', id_in => l_most_recent_id);

Now, you could shrug and say, “Yep, there are two different table names. I need to use dynamic SQL.” And then you could get to work on changing the function so that it is not quite so vulnerable to a SQL injection attack (with, among other things, the DBMS_ASSERT package).

But that would be a mistake. What you should think and say is this: “What? Just two different tables? I don’t need dynamic SQL for that. That’s just being lazy.”

And then you could (and I will, in this article) pursue one of two alternatives:

• Create two different functions.
• Change to static SQL inside that single function.

Two different functions. Really, why not write two different functions? It’s so easy and fast to write PL/SQL functions that call SQL. With a small amount of work, I can “fork” that single function into two, as follows:

FUNCTION name_from_table1_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   table1.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM table1
    WHERE id = id_in;

   RETURN l_the_name;
END;

FUNCTION name_from_table2_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   table2.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM table2
    WHERE id = id_in;

   RETURN l_the_name;
END;

There’s no more EXECUTE IMMEDIATE, and no more dynamic SQL. It’s true that I now have more code to debug and maintain, but this is very simple code. It should not cause any headaches.

Ah, but what if I then need the same functionality for 3, 4, or 10 tables? Doesn’t that get to be too much copying and pasting? You could argue both ways on this question.

SQL statements make up the heart of any Oracle Database application. Having a separate function for each distinct query is a small price to pay for clarity and careful optimization.

If, however, you worry about the proliferation of functions, you could also get “fancy” within a single function and support any number of tables.

One function, but no dynamic SQL. If you don’t want two, three, or many functions for the “same” functionality (for example, get the name for ID), fine. Keep them all in one subprogram, but switch from dynamic SQL to conditional logic.

FUNCTION name_from_id (table_in   IN VARCHAR2,
                          id_in   IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   VARCHAR2 (32767);
BEGIN
   CASE table_in
      WHEN 'TABLE1'
      THEN
         SELECT the_name
           INTO l_the_name
           FROM table1
          WHERE id = id_in;
      WHEN 'TABLE2'
      THEN
         SELECT the_name
           INTO l_the_name
           FROM table2
          WHERE id = id_in;
      ELSE
         raise_application_error (
            -20000,
            'name_from_id does not support fetching from ' || table_in);
   END CASE;

   RETURN l_the_name;
END;

With this approach, you simply add another WHEN clause to the CASE statement whenever you want to support another table.

I do not recommend this; separate functions make more sense, both in the short term and in the long term. But this is still better than a function that unnecessarily introduces dynamic SQL into the mix.

Finding Your Dynamic SQL

OK; so I’ve convinced you to use dynamic SQL only when absolutely necessary. And when you do use it, you will protect yourself from SQL injection through the use of bind variables and DBMS_ASSERT. This is great advice for all the future code you write.

But what about the tens of thousands of lines of existing code in your application? How can you find, fix, and possibly remove the dynamic SQL in that code?

The best way to do that is to use the PL/Scope tool in Oracle Database 12c Release 2. PL/Scope, first introduced in Oracle Database 11g, is a compiler tool that gathers information about all the identifiers (things with names) in your code. It makes it easy to answer questions such as

• Where is this variable modified in my program unit?
• Which exceptions are declared but never used (raised or handled)?

With Oracle Database 12c Release 2, however, PL/Scope has been extended to also analyze the SQL statements inside your PL/SQL program units. This means you can now answer even more interesting questions, such as

• Which SQL statements appear more than once in my code, and where are those duplicates?
• Where are the SQL statements with hints?
• Which programs execute UPDATE statements for specific tables?

And, most relevant to this article, where is dynamic SQL executed in my source code?

Native dynamic SQL can occur through two statements: EXECUTE IMMEDIATE and OPEN (for example, OPEN cursor_variable FOR ‘SELECT ...’). Even without PL/Scope, you could search the ALL_SOURCE data dictionary view for keywords, as in the following:

SELECT src.owner, src.name
  FROM all_source src
 WHERE UPPER (text) like '%EXECUTE%IMMEDIATE%

But you could get an awful lot of false positives—and miss any number of actual usages. (What if, for example, “EXECUTE” is on one line and “IMMEDIATE” is on another?)

Instead, you can and should enable PL/Scope whenever you connect to your session with the following statement:

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'

Then, compile your program units with this setting active. Then, execute the following statement:

SELECT st.owner, st.object_name
  FROM all_statements st
 WHERE st.TYPE IN ('EXECUTE IMMEDIATE', 'OPEN')

And then go hunting with a checklist of questions:

• Am I concatenating values instead of using bind variables?
• Once I convert to bind variables, is there anything left that is dynamic?
• Is it a fancy-pants implementation that could be done more simply with static SQL?
• If the code must remain dynamic, is it protected from SQL injection attacks?

Summary

The PL/SQL language makes it easy to implement dynamic SQL requirements, but easy should not be considered an invitation to do so. Because of the practical and potential downsides of dynamic SQL, using it should be considered a technique of last resort. Fortunately, the need for dynamic SQL might seem compelling at first, but it often disappears after a little bit of analysis.

So remember: say no to dynamic SQL—unless you can’t.

Next Steps

LEARN more about dynamic SQL in PL/SQL.

EXPLORE dynamic SQL scripts on the Live SQL website.