What is SQL Injection?

SQL Injection as the name suggests is injecting arbitrary SQL commands or clauses into an executing program to defeat its purpose.

Why does one inject SQL commands to defeat the purpose of a procedure. The answer is 'hackers'. Hackers are always looking for easy preys to steal another ones information. In this age of Information Technology, unnoticed stealing is for information. The information thus gathered can be consolidated by an experienced hacker and cause 'hell lot of trouble'.

Now as we are clear of the intentions of defeating a program, we will understand the simple types of SQL Injection which are very well-known.

  1. First Order Attack
  2. Second Order Attack
  3. Lateral Injection
First Order Attack is caused when a hacker simply modifies the string passed to a procedure and adds a malicious string to make the program work even if without valid data. 

For example consider the following code:

create table users (username varchar2(20), password varchar2(20));

insert into users values('Anantha','111111');

commit;

create or replace function get_cnt (qry in varchar2) return number is
  lv_cnt number;
begin
  execute immediate qry into lv_cnt;
  return lv_cnt;
end;
/

begin
  if get_cnt('select count(1) from users where username=''Anantha'' 
                   and password=''111111''') = 0 then
    dbms_output.put_line('Invalid Credentials');
  else
    dbms_output.put_line('Valid Credentials');
  end if;
end;
/
In the above example an inexperienced programmer has written a procedure that accepts a string for a query. He has also written an EXECUTE IMMEDIATE which executes the query and returns the count of records (His assumptions are that the query will have a single select list with COUNT(1) in it. The values for username and password, say are being captured from front-end and modified in the string. 

It seems fair enough until the hacker injects a ALL-SUCCESS where clause to the password field. The hacker simply types 222' OR 1=1 in the password field. The resulting string passed to the procedure will be of the following form:

begin
  if get_cnt('select count(1) from users where username=''Anantha'' 
                   and password=''222'' OR 1=1') = 0 then
    dbms_output.put_line('Invalid Credentials');
  else
    dbms_output.put_line('Valid Credentials');
  end if;
end;
/

The procedure has been defeated. Now imagine a procedure safe-guarding your application. Isn't this a nightmare?

How do we defend such simple attacks? The inexperienced programmer has now some experience and rewrites his procedure as follows:

create or replace procedure validate_user(uname in users.username%type, pwd in users.password%type) is
  lv_cnt number;
begin
  select count(1) into lv_cnt from users u where u.username=uname and u.password=pwd;
  if lv_cnt = 0 then dbms_output.put_line('Invalid credentials');
  else dbms_output.put_line('Valid credentials');
  end if;
end;
/

Now what if the hacker attacks again? The procedure code will be like:

begin
  validate_user('Anantha','222'' OR 1=1');
end;
/

But the output always defeat the hacker's purpose of defeating the logic. How come this did not work in favor of the hacker. Because Oracle is intelligent enough to understand/bind value to a variable when already the query is known. The late binding of variable to the column does not allow the hacker to get his success.

The moral learnt is when using EXECUTE IMMEDIATE or similar query when it has been framed by the programe, always test it thoroughly.

Next issue, Second Order Attack.

PL/SQL Server Pages or PSP

  • Have you heard of JSP?
  • Have you heard of ASP?
  • Have you heard of PHP?
Of course I have heard of them all. They all does one function, generate web pages dynamically to deliver a nice and rich front-end to the web.

But Have you heard of PSP? What?
PSP is acronym for Oracle's PL/SQL Server Pages. Oracle has this kind of capability? Well Oracle always has this sort of capability but it was called in a rather different name. It was and is called PL/SQL Web Toolkit.

But rather unknown or less used fact is Oracle also has an extension to this. This is called as PSP. I have created a whitepaper which is here for you to read:



For those who are unable to view the presentation in this web page or to view in Full screen, click here.

Remove leading space in to_char

One point which is usually overseen with the usage of TO_CHAR() is the leading space that it returns when converting a number to character.

Example:
select length(to_char('109','000.00')) from dual;

TO_CHAR('109','000.00')
" 109.00"

Do you notice the leading space that TO_CHAR() has returned? If you have not noticed and surprised, execute this and find it for yourself.

This is not a bug.

The Reason is Oracle reserves the first character for Sign. If you won't beleive me, execute the following statement:

select to_char('-109','000.00') from dual;

TO_CHAR('-109','000.00')
"-109.00"

Notice that the length of the string has not increased with the negative value being converted by TO_CHAR().

Hold on your tendency of calling TRIM() function to trim out the space. TO_CHAR() has a format model that suppresses this trailing space.

To suppress this leading space simply use the FM (stands for Fill Mode) in your format string.

select to_char('109','fm000.00') from dual;

TO_CHAR('109','fm000.00')
"109.00"

In case you don't need any specific format for the string, but just to ignore the trailing space, use the TM (stands for Text Minimum) in your format string.

select to_char('109.90','tm') from dual;

TO_CHAR('109','tm')
"109"