Without Bind Variables Your Code Is Also Less Secure

So here are some security issue highlights;

1- Greatest Risk is from

- External hack
- Rogue employee; Is the DBA a threat?
- Social Engineering; Single sign on is dangerous (why?)
- Hackers choose the easiest method for getting in not the one that is the most challenging.
- If there is a deadbolt on the front door, just go in via the left open fly screen on the backdoor.

2- Methods of Theft

- Steal the data; Hack in
- Steal the database; Backups, Copy the files
- Ask for the data; Prevent social engineering

3- Common Hacks

- Social Engineering; “Can I have your password?”, “Where can I plug in my laptop?”
- Theoretical Hacks; Trojan, Java, Wrapped PL/SQL

4- Code Injection

- Add a command to the URL to correctly validate or view data one shouldn’t

http://www.site/code/myproc?pw=SMITH

http://www.site/code/myproc?pw=X’’+or+1=1+or+passw=‘’

‘select username from table where passw = ‘’’ || ‘X’’ or 1=1 or passw=‘’’ || ‘’’’
‘select username from table where passw = ‘X’ or 1=1 or passw=‘’

- Web App prompts for Product Code and Returns Product description;

sql_stmt = “select prod_desc from products where prod_code =‘ ”& input_str & “‘”
Select product_desc from products where product_code = ‘123’

User enters Product code as 123’ UNION select username, password from dba_users where ‘1’=‘1

Resulting SQL statement is now

Select product_desc from products where product_code = ‘123’ UNION select username, password from dba_users where ‘1’=‘1’

To understand how “Code Injection” works lets work on a simple example. Suppose we have an application that asks a code and returns the bank account information related to that code;

create table my_top_secrets (
PK_SECRET    VARCHAR2(30) PRIMARY KEY,
BANK_ACCOUNT VARCHAR2(30),
AMOUNT       NUMBER(10)
);

insert into my_top_secrets values('1', 'Malta Bank - 123456',  123000000);
insert into my_top_secrets values('2', 'Isviçre Bank - 7890', 4567000000);
commit;

create or replace function get_bankaccount(p_secret varchar2) return varchar2 is
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy varchar2(64) := 'Fail';
BEGIN
OPEN l_rc FOR ’select BANK_ACCOUNT
from my_top_secrets
where PK_SECRET = ‘ || p_secret;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
return l_dummy ;
END;
/

set serveroutput on
declare
l_dummy1 varchar2(64) := '-1'; -- since I dont know a primary key value in the table
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail

PL/SQL procedure successfully completed.

-- SQL modification involves deliberately altering a dynamic SQL statement so that it executes in a way unintended by the application developer.
declare
l_dummy1 varchar2(64) := '-1' || ' or 1=1';
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Malta Bank - 123456

PL/SQL procedure successfully completed.

-- Statement injection occurs when a user appends one or more new SQL statements to a dynamically generated SQL statement.
declare
l_dummy1 varchar2(64) := '-1' || ' union select user from dual';
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
HR

PL/SQL procedure successfully completed.

What about if we binded instead of concatenation;

create or replace function get_bankaccount(p_secret varchar2) return varchar2 is
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy varchar2(64) := 'Fail';
BEGIN
OPEN l_rc FOR ’select BANK_ACCOUNT
from my_top_secrets
where PK_SECRET = :x’ USING p_secret;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
return l_dummy ;
END;
/

declare
l_dummy1 varchar2(64) := '-1'; -- since I dont know a primary key value in the table
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail

PL/SQL procedure successfully completed.

declare
l_dummy1 varchar2(64) := '-1' || ' or 1=1';
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail

PL/SQL procedure successfully completed.

declare
l_dummy1 varchar2(64) := '-1' || ' union select user from dual';
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail

PL/SQL procedure successfully completed.

If you develop your application using string concatenation instead of using bind variables, side effects on your system will not only be poor scalability as I mentioned in one of my previous posts but especially applications open to the Internet have many hidden vulnerabilities. Be carefull and research on “SQL Injection” topic through Google..

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :

Oracle® Database Application Developer’s Guide – Fundamentals 10g Release 2 (10.2) Chapter 8 Coding Dynamic SQL Avoiding SQL Injection in PL/SQL
“Defending and detecting SQL injection” thread on Asktom
SQL Injection article by Hakkı Oktay