SQL Injection Oracle Pl/SQL

SQL Injection is a kind of attack on Database, through which vulnerable code is inserted into strings(user inputs) that are later passed to database for processing. In simple way, for many of the cases we require user's input in our applications, if user inject some valid SQL command with the input which can be parsed as a fragment of SQL and executed as part of the programmer's sql statement, known as SQL injection.


The primary source of SQL injection is direct insertion of text(user's input) as a formal parameter of the subprogram, so called first order attack, or it may come indirectly from a table or meta-data which the subprogram reads and trust, called second order attack. Injected text will finally attack on database, so we will discuss the sql injection in PL/SQL during this document. Injection prescriptive we can categorize the SQL in two types :
Compile-time-fixed SQL - The SQL statements for which the statement text cannot change at run time and that can be confidently determined by reading the source code of stored program only. Means the complete text of SQL statement is written by the programmer and nothing is expected from user's end.

Run-time-created SQL - The text of a SQL statement that is not compile-time-fixed, and some part of statement will be incorporated at run time.

Compile-time-fixed SQL is full proof of injection since the text of SQL is prepared by the programmer before compilation and will not changed during execution but Run-Time-Created SQL are not safe and we can't stop it because it's our requirement.

Let's have some examples where one can inject vulnerable text.

Consider below PL/SQL block Where programmer is expecting an order_id as user's input :
--Code
...
quote constant varchar2(1) := '''';
Quoted_user_input constant varchar2(32767) :=
quote ||Raw_User_Input||quote ;
begin
SQLStatement :=
'SELECT City FROM Orders where Order_id = '||Quoted_user_input;
execute immediate SQLStatement bulk collect into vArr;
...

Where Raw_User_Input is a parameter of type in for this stored program. Now what will happen, if user supply :

' UNION SELECT username FROM All_users --'

Then the variable SQLStatement will have the text like :
SELECT City From Orders where Order_id = '' UNION SELECT username FROM All_users --'
Here the user's input started with single quote which is a closing quote for the equality test in programmers statement like Order_id = ''. User supplied -- at the end which will balance the last singe quote applied by programmer. So the SQL statement will execute as a union and the formatted SQL will look like :

SELECT City
FROM Orders
where Order_id = ''
UNION
SELECT username
FROM All_users --';

Which is a valid SELECT SQL and will return no data from Orders and username column from All_Users table.


Consider below PL/SQL block Where programmer is expecting a table name as user's input :
--Code
...
SQLStatement :=
'select column1 from '||Raw_User_Input||' where column2 = ''kamlesh''';
execute immediate SQLStatement bulk collect into vArr;
...

Where Raw_User_Input is a prameter of type in for this stored program. Now what will happen, if user supply :

table1 where 1=2
union
select Username from All_Users --

The statement will be prepared as  :

select column1 from table1 where 1=2
union
select Username from All_Users -- where column2 = 'kamlesh'

Which is again a valid SELECT SQL and will return all data from table1 and username column from All_Users table.



Sql injection can happen with date vales or number values also. Let's have some examples :

Consider below PL/SQL block Where programmer is expecting a from and to date values as user's input for generating a report :
--Code
...
quote constant varchar2(1) := '''';
Quoted_date_input1 constant varchar2(32767) := quote ||date1 ||quote ;
Quoted_date_input2 constant varchar2(32767) := quote ||date2 ||quote ;
SQLStatement := 'SELECT * from table1 where column1 > '|| Quoted_date_input1 || ' and column1  < ' || Quoted_date_input2 ;
execute immediate SQLStatement bulk collect into vArr;
...

Here programmer expecting two date values as 'in' prarmeters, what will happen if user is supplying
first parameter as ' OR 1=1 -- 
and second parameter as ' OR 1=1 -- 
Then the statement will be prepared at run time as :

SELECT * from table1 where column1 > '' OR 1=1 -- and column1  < '' OR 1=1 --
Which is a valid statement and will return all data of table1.

How to Stop injections in Oracle PL/SQL?
To stop most of the injection we must validate user's input, there must not any single occurrence of single quote. If it's happening then we should replace it with additional quote. We can write the validation functions and use to validate the input or we can use oracle supplied package DBMS_Assert for handling the single quotes in strings and date expressions in SQL. This package have the function Enquote_Literal(), which takes single parameter of varchar2 and returns varchar2. This function Verify that all single quotes except leading and trailing characters are paired with adjacent single quotes. If the supplied values is not having any additional single quote means it's formatted SQL text literal then the output will be same to input. but if the input is having single quotes which abnormally terminates the string, this function will raise a predefined exception Value_Erorr and we should handle the error in exception handling section.