Not only is it still a serious problem, it’s a major component of the NUMBER ONE problem according to the fine folks at the Open Web Application Security Project (OWASP).

We at Oracle have been talking about the perils of SQL Injection in your code, specifically PL/SQL since that’s OUR programming language of the Oracle Database, for more than a decade.

Tom Kyte’s unofficial middle name was ‘:BindVariables’ – that’s how seriously he was regarded in tackling this issue.

As a quick aside, I’m not joking about the ‘more than a decade’ and ‘bind variables’ – Tom first answered that question in 2004, and almost every single talk he delivered to customers featured the benefit of Bind Variables!

Seriously though, if you’re curious about how to make your PL/SQL code more secure, then we have this White Paper that tackles the subject in greater detail.

Tools can help!

SQL Developer, your PL/SQL IDE, will show you when things are less than good. And we adopted this feature back in 2018.

As George Takei would say, Oh My!

In SQL Developer, our parser is trying to highlight the issues before you even have a chance to compile the code – note the ‘grammar squiggle line’ underneath the block1 in line 18.

SET CODESCAN in SQLcl

SQL Developer might be your PL/SQL IDE, but SQLcl is your modern command line interface for the database. And I imagine you might be deploying a ton of PL/SQL through it to your database.

You’ll never guess what THIS does 😉

Here’s an example of some vulnerable PL/SQL code straight from the Oracle Docs –

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;
/

Anytime you see something being appended into a string which is then later executed…that’s a big, red flag.

Now, if we send that through to the database from our SQL prompt, we’ll see this –

I like how we ‘connect the dots’ for you. Lines 2, 11, and 17 could allow very bad things to happen!

Code Reviews are expensive, but worth every penny!

If you aren’t able to spend as much time as you’d like reviewing your application source code by hand, at LEAST deploy tools that can do automated scans. There are plenty of them out there.

Just because you’ve been hearing about SQL Injection for 15 years doesn’t mean that it’s not a problem any more. Perhaps the best thing you can do is help your junior staff out by showing them the patterns to avoid in their code.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

2 Comments

  1. Hi Jeff,

    I do not totally agree with your title “It’s 2020, and SQL Injection is STILL a Serious Problem”

    SQL injection should be treated by the middleware IMHO

    I do not feel frightened by them .. maybe because I am using RoR that provides a lot of middleware tools

    great article anyway 🙂

    Julien

  2. I haven’t test this yet but wondering with storing JSON in clob and using Oracle’s JSON functions whether it’s vulnerable to SQL Injection.

    I would assume that Oracle has done their Due Diligence…

Write A Comment