18.1 Features: SQL Injection Detection

A small feature that could have a huge impact on your code – we try to make sure you know your PL/SQL might be vulnerable.

SQL Injection is a very well known security risk in the database world. Nefarious people could ‘inject’ SQL into the code you’re about to execute via some some of user input you make available to them.

We even talk about this in the Oracle Database Docs and provide some examples of what it looks like and how to avoid it.

We thought it would be good to beef up our Code Insight feature to mark up any suspicious looking PL/SQL you have in an editor.

Let’s look at two of the examples, straight from the Docs.

Example One: Procedure Vulnerable to Statement Modification

CREATE OR REPLACE PROCEDURE get_record (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2,
  rec          OUT VARCHAR2
)
IS
  query VARCHAR2(4000);
BEGIN
  -- Following SELECT statement is vulnerable to modification
  -- because it uses concatenation to build WHERE clause.
  query := 'SELECT value FROM secret_records WHERE user_name='''
           || user_name 
           || ''' AND service_type=''' 
           || service_type 
           || '''';
  DBMS_OUTPUT.PUT_LINE('Query: ' || query);
  EXECUTE IMMEDIATE query INTO rec ;
  DBMS_OUTPUT.PUT_LINE('Rec: ' || rec );
END;
/

Let’s put that into SQLDev and see what happens.

When you mouse over that underlined text, you get the warning box with the diagnostic message. I’ve gone and highlighted the bits of code it’s referring back to.

Example Two: Procedure Vulnerable to Statement Injection

CREATE OR REPLACE PROCEDURE p (
  user_name    IN  VARCHAR2,
  service_type IN  VARCHAR2
)
IS
  block1 VARCHAR2(4000);
BEGIN
  -- Following block is vulnerable to statement injection
  -- because it is built by concatenation.
  block1 :=
    'BEGIN
    DBMS_OUTPUT.PUT_LINE(''user_name: ' || user_name || ''');'
    || 'DBMS_OUTPUT.PUT_LINE(''service_type: ' || service_type || ''');
    END;';
 
  DBMS_OUTPUT.PUT_LINE('Block1: ' || block1);
 
  EXECUTE IMMEDIATE block1;
END;
/

And SQLDev sees ‘this’:

So what do we do?

First, this is just a warning. We’re looking at your code, but we don’t know how you’re using it. You know that, so it’s possible we might have a false positive. If however, you think there’s something there, make sure to read up on the ‘Guarding Against SQL Injection’ section of the Oracle Docs.

I don’t like this.

You can turn it off then.

This feature will continue to be enhanced. Please send us your code samples if you feel it’s catching false positives.