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