Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Welcome, Guest. Please Login.
Sep 22nd, 2018, 11:38am
News: Welcome to Pete Finnigan's Oracle security forum
Home | Help | Search | Members | Login
   Pete Finnigan's Oracle Security Forum
   Oracle Security
   Oracle Security
(Moderator: Pete Finnigan)
   SQL Injection Question
« Previous topic | Next topic »
Pages: 1  Reply | Notify of replies | Send Topic | Print
   Author  Topic: SQL Injection Question  (Read 1731 times)
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
SQL Injection Question
« on: Apr 8th, 2008, 12:37pm »
Quote | Modify

Hi,
 
Is this code secure from SQL injection?  If not, how would I exploit it and what would be a better (more secure) way of coding it?
 
Thanks
 
 
Code:

...
FOR row IN (
   SELECT EMPNO, ENAME
   FROM   SCOTT.DEPT
   WHERE  ENAME LIKE '%'||pName||'%'  
) LOOP
...
IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
Re: SQL Injection Question
« Reply #1 on: Apr 10th, 2008, 12:37am »
Quote | Modify

Assuming pName is a character variable there is no injection vulnerability here as the expression is a simple concatenation of literal values and variable value. There's no opportunity for any unexpected code to be executed.  
I'd guess that the p prefix signifies a parameter. If so, I might look at either assigning it to a local variable with a defined length or at least validating its length. If it was an insert or update, I'd definately do some validation on the input value (length, non-printing characters etc).  
For a LIKE check, you may (depending on requirements) check whether the parameter string already contains wildcard characters. If the end user isn't supposed to supply wildcards, then "instr(ename,pName) > 0 " may be a better filter.
 
Finally, if "eName" is a valid column name, I'd be concerned about allowing a variable called pName. If a column "pName" was added to the table, then the SQL would compare the two column values, not one column value against the value of the PL/SQL variable and so have unexpected side-effects.
It is best to have strict naming standards so that variable names and column names are easily distinguishable.
 
IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
Re: SQL Injection Question
« Reply #2 on: Apr 10th, 2008, 3:15pm »
Quote | Modify

Hi,
 
If the input for pNAME is : '' --
the that would be resolved to: '%' ||'' --||'%'
So all the content of the table will be displayed.  
Some other thing to try would be try to call a function like utl_http maybe?
 
regards,
 
Ivan
IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
Re: SQL Injection Question
« Reply #3 on: Apr 10th, 2008, 3:43pm »
Quote | Modify

Hi Guys,
 
Thank you both for your responses.
 
Gamyers...
You are indeed correct in assuming that the p indicates a parameter, we're checking that we've copied the all the values into local variables now.  And we are usually pretty careful with our column names (certainly more-so than SCOTT)
 
Isaez...
I still can't reproduce a succesful attack using your string...:
 
Code:

--
-- Prove that the code works
--
SCOTT @ test >  
SCOTT @ test > SET SERVEROUTPUT ON
SCOTT @ test >  
SCOTT @ test > accept Name prompt "name "
name R
SCOTT @ test > DECLARE
  2     lName VARCHAR2(20) := '&Name';
  3  BEGIN
  4     DBMS_OUTPUT.PUT_LINE('lName: '||lName);
  5  
  6     FOR user IN (SELECT ENAME, SAL
  7   FROM   EMP
  8   WHERE  ENAME LIKE '%'||lName||'%') LOOP
  9   DBMS_OUTPUT.PUT_LINE(user.ename||', '||user.sal);
 10     END LOOP;
 11  END;
 12  /
old   2:    lName VARCHAR2(20) := '&Name';
new   2:    lName VARCHAR2(20) := 'R';
lName: R
WARD, 1250
MARTIN, 1250
CLARK, 2450
TURNER, 1500
FORD, 3000
MILLER, 1300
 
PL/SQL procedure successfully completed.
 
--
-- Now try SQL injection
--
SCOTT @ test > SET SERVEROUTPUT ON
SCOTT @ test >  
SCOTT @ test > accept Name prompt "name "
name '''' --
SCOTT @ test > DECLARE
  2     lName VARCHAR2(20) := '&Name';
  3  BEGIN
  4     DBMS_OUTPUT.PUT_LINE('lName: '||lName);
  5  
  6     FOR user IN (SELECT ENAME, SAL
  7   FROM   EMP
  8   WHERE  ENAME LIKE '%'||lName||'%') LOOP
  9   DBMS_OUTPUT.PUT_LINE(user.ename||', '||user.sal);
 10     END LOOP;
 11  END;
 12  /
old   2:    lName VARCHAR2(20) := '&Name';
new   2:    lName VARCHAR2(20) := ''''' --';
lName: '' --
 
PL/SQL procedure successfully completed.
 

 
.... is it possible that you are mistaken?  Or are you able to craft an exploit string for the code above?
 
Smiley
 
RT
IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
Re: SQL Injection Question
« Reply #4 on: Apr 10th, 2008, 6:16pm »
Quote | Modify

RT,
 
You are right ''-- as input doesn't work but if you give an empty string then all data will be shown. Search in google for: oracel sql injection "like clause" and you will see possible attacks.
Also take a look at bind variables!
 
regards,
 
Ivan
IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pages: 1  Reply | Notify of replies | Send Topic | Print

« Previous topic | Next topic »

Powered by YaBB 1 Gold - SP 1.4!
Forum software copyright 2000-2004 Yet another Bulletin Board