Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Do You Really Need That SQL to Be Dynamic?

DZone's Guide to

Do You Really Need That SQL to Be Dynamic?

Sure, sometimes you really do need to use dynamic SQL. But *do* be sure that this extra technical debt is absolutely necessary.

· Database Zone ·
Free Resource

Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.  

Dynamic SQL refers to a SQL statement that is constructed, parsed, and executed "dynamically" at run time (vs. "statically" at compile time).

It's very easy to write static SQL in PL/SQL program units (one of the great joys of working with this database programming language). It's also quite easy to implement dynamic SQL requirements in PL/SQL.

But that doesn't mean you should. The bottom line regarding dynamic SQL is to construct and execute SQL at runtime only when you have to.

There are several good reasons to avoid unnecessary dynamic SQL:

  1. Security: Dynamic SQL opens up the door to SQL injection, which can lead to data corruption and the leaking of sensitive data.
  2. Performance: While the overhead of executing dynamic SQL has gone way down over the years, it is certainly still faster to use static SQL.
  3. Maintainability: The code you write to support dynamic SQL is more — literally more code — and harder to understand and maintain.

Sometimes, the misuse of dynamic SQL is obvious. Consider the following:

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

   RETURN l_the_name;
END;
/

The developer apparently believed, however, that since the value of the ID can change, it needs to be concatenated to the SQL statement, so it's gotta be done dynamically. Ha! There's nothing dynamic about this query. It should be rewritten to:

CREATE OR REPLACE 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;
/

Often, the need for dynamic SQL is compelling at first but then disappears with a little bit of analysis. Consider this function:

CREATE OR REPLACE 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;
/

Well, heck, if I don't know the table name until run time, I sure can't use a static SELECT statement, right? Of course, right!

So, first of all, if this code is really and truly necessary, you need to think about SQL injection.

Is the table name provided directly by the user? You should never allow user input to be stuffed directly into your dynamic statement. They could enter all sorts of nasty stuff.

Even if not passed directly from user fingertips to EXECUTE IMMEDIATE, you should use DBMS_ASSERT to make sure that the table name passed in is a valid DB object, as in:

CREATE OR REPLACE 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 ' || 
         SYS.DBMS_ASSERT.sql_object_name (table_in) || 
      ' where id = :id'
      INTO l_the_name
      USING id_in;

   RETURN l_the_name;
END;
/

Great, so the function is less vulnerable than before to injection. But does it really need to be dynamic?

The only way to answer that question is to check and see where and how the function is used. I could do a text search through my code (via an editor like Sublime) or an object search (in SQL Developer). I could also use PL/Scope if I'd gathered identifier information across my code base.

Suppose after doing my analysis, I 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);

OK, I could shrug and say, "Yep, two different table names. I need to use dynamic SQL."

But that would be a mistake. What I should think and say is, "What? Just two different tables? I don't need dynamic SQL for that. That's just laziness."

Instead, I could do either of the following.

  1. Create two different functions. Really, why not? It's so easy and fast to write PL/SQL functions that call SQL. Just change to:
    CREATE OR REPLACE 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;
    /
    
    CREATE OR REPLACE 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;
    /
  2. One function, no dynamic SQL. So you don't want two, three, etc. functions for the "same" functionality (get a name for ID). Fine, keep them all in one subprogram, but move the conditional logic inside.

    CREATE OR REPLACE 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;
    /

Sure, sometimes you really do need to use EXECUTE IMMEDIATE and dynamic SQL. In those situations, make sure you minimize the attack surfaces for SQL injection. Make sure you've got strong exception handling and logging.

But do make certain that this extra "technical debt" is necessary. There's a good chance you can get by without dynamic SQL. If so, your users, your fellow developers, and your manager will all thank you!

Download AnzoGraph now and find out for yourself why it is acknowledged as the most complete all-in-one data warehouse for BI style and graph analytics.  

Topics:
database ,sql ,pl/sql ,dynamic sql ,oracle

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}