It’s 2020, and SQL Injection is STILL a Serious Problem

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.

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.

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 –

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.