Without Bind Variables, Your Code is Less Secure

Not something entirely learned new today - but rather a revisited "something I learned".

When you don't use bind variables revolves around security, specifically the risk of SQL injection.
SQL injection occurs when an application program accepts arbitrary SQL from an untrusted source (think "end user"), blindly adds it to the application's SQL, and executes it. It would be similar to the operating system's taking a couple of lines of C code from a user, compiling it on the fly, and just executing it in the operating system kernel. It would stand a good chance of crashing something.

To understand how it works, suppose that we have an application that asks a user for a username and password. We execute this query using with and without Bind variables.

scott@10GR2> create table t(userid varchar2(10),passwd varchar2(100));

Table created.

scott@10GR2> insert into t values ('tom','top_secret_password');

1 row created.

scott@10GR2> commit;

Commit complete.

scott@10GR2> Create or replace procedure without_bind (p_username in varchar2,p_passwd in varchar2)
  2  as
  3     l_count number := 0;
  4     v_sql varchar2(500);
  5  begin
  6     v_sql := 'select count(*)
  7     from t
  8     where userid = '''||p_username ||''' AND  passwd = '|| p_passwd ;
  9
 10     execute immediate v_sql into l_count;
 11
 12     if l_count > 0 then
 13             dbms_output.put_line('Thanks for Correct user name and password');
 14     else
 15             dbms_output.put_line('Incorrect Credentials');
 16     end if;
 17
 18  end without_bind;
 19  /

Procedure created.

scott@10GR2> Create or replace procedure using_bind (p_username in varchar2,p_passwd in varchar2)
  2  as
  3     l_count number := 0;
  4  begin
  5
  6     execute immediate ' select count(*) from t where userid = :x and passwd = :y ' into l_count using p_username, p_passwd ;
  7
  8     if l_count > 0 then
  9             dbms_output.put_line('Thanks for Correct user name and password');
 10     else
 11             dbms_output.put_line('Incorrect Credentials');
 12     end if;
 13
 14  end using_bind;
 15  /

Procedure created.

scott@10GR2> exec without_bind('tom','''i_dont_know'' or 1 = 1');
Thanks for Correct user name and password
 
Note the password we just used. It incorporates a little SQL, 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.

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

scott@10GR2> exec using_bind('tom','''i_dont_know'' or 1 = 1');
Incorrect Credentials 

Bind variables add security to your applications.