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.
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.
- First Order Attack
- Second Order Attack
- 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.
