How to Prevent SQL Injection in Oracle SQLPlus?

Question

Obviously if I am using JDBC/ODBC, I can use bind variables and prepared statements to prevent SQL injection. However, when data is passed to batch processes that end up invoking Oracle SQLPlus, is there a way to prevent SQL injection? For example:

query.sql:

select '&1' from dual;
exit;

If I call this script from SQLPlus thusly:

$ sqlplus SCOTT/TIGER @query.sql "x','y"

I will get the following output:

old   1: select '&1' from dual
new   1: select 'x','y' from dual

' '
- -
x y

As you can see, SQLPlus command line parameters are using simple macro substitution. Is there an alternative method that I am missing? Otherwise, how do I prevent this from being exploitable?

Answer 1

You can't really have SQL injection attacks unless you're to expose your SQL*Plus scripts to malicious strangers on the internet. People who would actually attack your company.

It appears you're talking about batch jobs run in-house; entirely behind the fire wall, where the rest of the networks users are your co-workers, correct?

Unless they're seriously crazy sociopaths, "attack" doesn't have any meaning. Any SQL Injection problems that did arise would be either really abysmally bad design or reason for termination. That's just ordinary management of people's access and their skills in using the available tools.

---

Edit

While I can't pretend to know what "breakage due to control characters" means, I can provide some additional advice.

If you're suspicious that you may have a programming problem that -- via SQL injection -- will cause breakage at run-time, you need a test environment.

Clone your production schema to make a test schema. Load with a known subset of data. Run your SQL scripts on the test schema. If you have "somehow" put "control characters" into your SQL script, you'll have a test script that demonstrates this.

Answer 2

I would recommend using bind variables instead. In your script you should be able to do the following:

variable my_var varchar2(100);
begin
:my_var := &1;
end;
/
select :my_var from dual;

The "my_var" variable will be bound to the query. Instead of a simple find/replace.

Answer 3

Passing parameters through SQL*Plus through the command line will be open to more than just SQL injection, as the command line will be interpreted through the OS first. So you also need to consider what the OS user might be able to do.

Personally, I'd ditch SQL*Plus and go with something like Perl. You have a proper programming language to wrap your SQL statements in, with much better handling of variables and exceptions.

Answer 4

If people have sql*plus access to your database and you have the user id and password of a privileged user out here in a script for them to read, then you've just dropped your pants to them anyway. SQL injection is the least of your worries.

Answer 5

You can user the following SQL Plus command: SET DEFINE OFF