# RootPrompt.org   Nothing but Unix.[Home] [Features] [Programming] [Mac OS X] [Search]


 The Stored Procedure in the Article

Upon peeking through the code for the Security article "Detecting SQL Injection in Oracle", the designer of the stored procedure opens himself up to SQL Injection. If you modify your stored procedure to not use dynamic sql and to use bind variables, it will be impossible to accomplish SQL Injection.

 (Submitted by Rich Tue Jul 29, 2003 )

  When building PL/SQL programs, you must be careful to not open your code up to sql injection. SQL Injection can be avoided in your PL/SQL by not using dynamic sql and by using bind variables. I have rewritten the stored procedure get_cust which was used in the "Detecting SQL Injection in Oracle" article to show you how to avoid getting caught in this situation. In the new version of the stored procedure, I have replaced the dynamic sql call by defining a cursor. I also use a FOR loop rather which implicitly opens/closes the cursor (this is just a style preference). Here is the old code...
create or replace procedure get_cust (pv_surname in varchar2)
is
	type cv_typ is ref cursor;
	cv cv_typ;
	lv_phone	customers.customer_phone%type;
	lv_stmt		varchar2(32767):='select customer_phone'||
			' from customers '||
			'where customer_surname='''||
			pv_surname||'''';
begin
	dbms_output.put_line('debug:'||lv_stmt);
	open cv for lv_stmt;
	loop
		fetch cv into lv_phone;
		exit when cv%notfound;
		dbms_output.put_line('::'||lv_phone);
	end loop;
	close cv;
exception
	when others then
		dbms_output.put_line(sqlcode||sqlerrm);
end get_cust;
/
sho err
Here is the new version
create or replace procedure get_cust (pv_surname in varchar2)
is
	lv_phone	    customers.customer_phone%type;
	lv_loop_count   PLS_INTEGER := 0;
	
CURSOR my_cur 
IS      select  customer_phone
	    from    customers
        where   customer_surname= pv_surname;
begin

	dbms_output.put_line('debug: pv_surname =:'||pv_surname);
	
	FOR my_rec IN my_cur
	LOOP
	    lv_loop_count := lv_loop_count + 1;
	    lv_phone := my_rec.customer_phone;
		dbms_output.put_line('::'||lv_phone);
	
	END LOOP;

	dbms_output.put_line('Records found :='||lv_loop_count);
	
exception
	when others then
		dbms_output.put_line(sqlcode||sqlerrm);
end get_cust;
/
sho err
Upon executing the new code (while trying to inject sql), you will notice that the cursor never returns any records, because the value of the parameter is used as a bind variable for the cursor. Results...
SQL> exec get_cust('x'' select username from all_users where ''x''=''x');
debug: pv_surname =:x' select username from all_users where 'x'='x
Records found :=0

PL/SQL procedure successfully completed.

While designing any application, if at all possible you should avoid using dynamic sql. If you must use dynamic sql, you should learn how to use bind variables. In PL/SQL, Oracle will bind variables by default ONLY IF YOU ARE NOT USING DYNAMIC SQL. In languages like Java, using a prepared statement should bind the variable to the query.

Here is an example of using bind Variables in Perl.
http://www.sqlquery.com/perl_bind_variables.html

Here is a study of using bind variables in Java.
http://home.clara.net/dwotton/dba/java_insert.htm


More on Bind Variables... This is from an email I sent to a buddy some time ago...
--bind variables
Take a look at test case 1 and test case 3 on this URL.
http://home.clara.net/dwotton/dba/java_insert.htm
What are bind variables?
Let's start with the way Oracle handles the execution of queries.
When a query is executed against the database, Oracle looks to the SGA to see if it has parsed the query recently and if it finds the query it will not parse the query again and it will use the old access path to get to the data.
If it has NOT executed that exact query, it will parse the query, make sure it is the syntax is correct, figure out the access path to the data, and then execute the query.

So, in a nutshell, if you build a big sql query statement and then execute it (like you do in sql*plus),
select * from users_login where user_name = 'saltydog';
oracle (as well as the other db vendors) will store this whole query including
the saltydog in the sga, in hoping that the EXACT same query will come in again and be executed.
If you change the query in sql*plus to
select * from users_login where user_name = '&user_name';,
oracle will prompt you for the username, and only store the query in the SGA
without the word saltydog, therefore making this query more generic (and
hopefully it can be used more often). Another good reason to use bind
variables is so that hackers can not use "sql injection", that is a whole
different story.


Our content can be syndicated: Main page Mac Page

Copyright 1999-2005 Noel Davis. Noel also runs web sites about sailing and kayaking.
All trademarks are the property of their owners.
All articles are owned by their author