that sql is bound - it is not possible for the *static* sql in plsql to be subject to sql injection.

this:
begin
...
   select emp_name into l_var 
     from emp where emp_no = var1; 

....

is perfection.  this:

create or replace procedure p( p_where_clause in varchar2 )
is
   l_var varchar2(250);
begin
    
   execute immediate 'select emp_name from emp ' || p_where_clause 
      into l_var;




That, that is dangerous if p_where_clause comes from OUTSIDE YOUR SPHERE of control.  (not only that, but hey -- no binds :(  )

What if the end user submits the string


where function_you_can_run_but_they_cannot( ..... ) = 0


they just ran code *as you*.  Or more insidous, here is the example I used in the book:

<quote>
Without Bind Variables, Your Code Is Less Secure

Another concern when you don't use bind variables revolves around security, specifically the risk of SQL injection. To understand how it works, suppose that we have an application that asks a user for a username and password. We execute this query:

select count(*) from user_table where username = THAT_USER
and password = THAT_PASSWORD


This seems innocent enough right? Well, let's use SQL*Plus to test that theory and see what happens with and without bind variables.


ops$tkyte@ORA920> create table user_table 
  2 ( username varchar2(30), password varchar2(30) );
Table created.

ops$tkyte@ORA920> insert into user_table values 
  2 ( 'tom', 'top_secret_password' );
1 row created.

ops$tkyte@ORA920> commit;
Commit complete.

ops$tkyte@ORA920> accept Uname prompt "Enter username: "
Enter username: tom
ops$tkyte@ORA920> accept Pword prompt "Enter password: "
Enter password: i_dont_know' or 'x' = 'x


Notice the password we just used. It incorporates a little SQL there doesn't it? Since we are just gluing strings together, not binding, the end user can actually type in arbitrary SQL and have it executed! Our application takes this string now and continues on:


ops$tkyte@ORA920> select count(*)
  2    from user_table
  3   where username = '&Uname'
  4     and password = '&Pword'
  5  /
old   3:  where username = '&Uname'
new   3:  where username = 'tom'
old   4:    and password = '&Pword'
new   4:    and password = 'i_dont_know' or 'x' = 'x'

  COUNT(*)
----------
         1

Look at that. Apparently, the password `i_dont_know' or `x' = `x' is our password. But if we use bind variables instead and accept the exact input from the end user, we see this:


ops$tkyte@ORA920> variable uname varchar2(30);
ops$tkyte@ORA920> variable pword varchar2(30);
ops$tkyte@ORA920> exec :uname := 'tom'; 
ops$tkyte@ORA920> exec :pword := 'i_dont_know'' or ''x'' = ''x';

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select count(*)
  2    from user_table
  3   where username = :uname
  4     and password = :pword
  5  /

  COUNT(*)
----------
         0

We get the correct answer. 

Think about this the next time you put an application out on the Internet. How many hidden vulnerabilities might you have lurking in there if you develop your application using string concatenation instead of bind variables? Think of the "neat" side effects someone could have on your system. Consider this password:


ops$tkyte@ORA920> accept Pword prompt "Enter password: "
Enter password: hr_pkg.fire_emp( 1234 )


Whoops, this person just executed a stored function as the user who is connected to the database. While he might not get logged on, he nonetheless got connected to your system and fired someone. Is this unbelievable? Absolutely not. Search www.google.com for SQL Injection, and you'll see results 1 through 10 of about 15,800. Just consider the implications.

If you don't believe the performance-related arguments for using bind variables in your system, maybe this last bit will be enough to persuade you. Bind variables add security.
</quote>



So, if you do not "concatenate" (glue in, make part of the sql) inputs from outside of what you control -- you cannot be subject to "sql injection".  

As soon as you let what the end user type into a field be used in your SQL statement (not bound into, used as part of the sql) -- you are 100% vunerable to SQL injection.


<b>(i'll find any argument I can to further the "just bind will you" crusade), this one is awesome actually -- because

a) it is true
b) it happens
c) people are concerned about it

that I get nicely bound applications out of it just makes me happy :)</b>

 

perfectly valid -- as long as you use bind variables!  (in one way shape or form)

if you don't like sys_context, you can use decode

*that* would be even *worse*


 <jbo:ExecuteSQL appid="SomeBc4jModule" >
        BEGIN 
           emp_delete(<%=s_empno%>);
        END;         
      </jbo:ExecuteSQL>


consider this input:

s_empno = 55 ); execute immediate 'drop table emp';
s_empno = 55 ); update emp set sal = sal*2 where ename = 'TOM';

consider what the resulting block of code would look like....



sql statements in the procedure emp_delete are BOUND, but the statment you are executing -- IT is not.  

I'm sure if you check out the programmers guide, it might be revealed in there! (howto bind)