1

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?

DevelopersDevelopersDevelopers
  • 228
  • 2
  • 6

5 Answers5

5

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.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
4

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.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
0

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.

Nick
  • 2,524
  • 17
  • 25
  • This gives a false sense of security. To see why, turn on server output ("set serveroutput on"), then, at the prompt for &1, try typing: 'a'; dbms_output.put_line('owned') – mormegil Oct 09 '14 at 19:31
  • In Oracle, bind variables prevent SQL injection. In my example, it's simply "printing out" what you provided it's not executing it. – Nick Oct 10 '14 at 12:48
  • The printing is merely evidence that one can get it to execute arbitrary PL/SQL using the '&1' string-replacement mechanism. PL/SQL can contain 'execute immediate ...' statements to run almost any SQL one can think of ('truncate table' for example). In other words, the bind variable is, as you say, safe from injection however the '&1' is definitely not safe and can be exploited. – mormegil Oct 10 '14 at 19:08
  • Not disagreeing with you that it will run any arbitrary statement, however if you look at my solution, I use a variable to store the result of the &1. Then via the variable I use it in the SQL statement. By using the variable to store the result of &1, and then using the variable in the SQL statement, you are protected from SQL injection. – Nick Oct 10 '14 at 19:13
-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.

S.Lott
  • 384,516
  • 81
  • 508
  • 779
  • We do various data manipulations on customer data, but for cost and maintenance reasons we try to keep our asynchronous processing business logic out of the database. It's infinitely easier and more portable to version control and schedule stuff using cron/sqlplus than Oracle jobs/stored procedures. – DevelopersDevelopersDevelopers Feb 23 '09 at 20:30
  • I should have mentioned that this is as much about breakage due to control characters as it is about attack. – DevelopersDevelopersDevelopers Feb 23 '09 at 20:33
  • I'm sorry, I meant programmatically significant characters, from the user input, successfully loaded into a database, that is subsequently used to generate output. For example, an essay question that will be on a generated PDF. How do I pass a string of 250 characters of user data to SQLPlus safely? – DevelopersDevelopersDevelopers Feb 23 '09 at 21:59
  • At that point, you should get a better scripting environment than SQL-plus. It's not the injection "Attacks" -- there are none -- it's the inconvenience factor. Python plus mxOracle is still scripted, but a LOT easier to live with. – S.Lott Feb 23 '09 at 22:27
  • @DevelopersDevelopersDevelopers: "It's infinitely easier to version control stuff using cron/sqlplus than Oracle stored procedures." -- Oh really? Did you consider putting the stored procedure code in a text file and checking it into version control? You know, like you would do with any other piece of code, regardless of the programming language? – ObiWanKenobi Jan 12 '11 at 19:18
  • @ObiWanKenobi. "putting the stored procedure code in a text file and checking it into version control". Works. But that may not be the version that's running in one of the various databases. That problem I've seen is that there's often a larger gap between source code and database structure than there is between source code and running application code. Mostly, I think, because Java and C# have nice build tools, were the database doesn't have such nice tools. – S.Lott Jan 12 '11 at 19:44
-2

You can user the following SQL Plus command:

SET DEFINE OFF
Taryn
  • 242,637
  • 56
  • 362
  • 405
Mohammad Makahleh
  • 1