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.

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.

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.

Prior to Oracle 11g, we have used PLS_INTEGER data type in PL/SQL programs. In 11g, a new data type SIMPLE_INTEGER has been introduced. It is a sub-type of PLS_INTEGER data type and has the same range as PLS_INTEGER.

The basic difference between the two is that SIMPLE_INTEGER is always NOT NULL. When the value of the declared variable is never going to be null then we can declare it with SIMPLE_INTEGER data type.

Another major difference is that it never gives numeric overflow error like its parent data type instead it wraps around without giving any error. When we don’t have to worry about null checking and overflow errors, SIMPLE_INTEGER data type is the best to use.

This time it is a review of a product which is freely available for download. The CSV Loader. It is a Java Application that can load data to databases such as Oracle, MySQL and PostgreSQL databases. The software is available for download here http://sourceforge.net/projects/csvloader/

What makes this tool unique is its integration with SunGard Banner software. It is a big replacement for SQL Loader provided by Oracle. You can either use its interface to specify the tables in which the data needs to be imported or pretty advanced mode where you can edit the configuration file and upload the details.

I loved the tool, and in initial attempt loaded about 1000s of records to a table. I as a developer became free to use the data to load it to appropriate tables.

I give a cool 4.5 star rating out of 5 for this tool. I reserve 0.5 points for little user-interface difficulties which prevent me from specifying the target table/column easily. I had to type the schema and table name each time, and then select the column.

From its interface it looks like target table can be multiple, have not tested it. Anyone interested in knowing more, I am happy to help.

Virtual Columns has been introduced in Oracle Database 11g Release 1. Here is a good tutorial I could find from Oracle-Base website. The link for the tutorial is at the bottom of this article. - AnanthaWhen queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.

Indexes defined against virtual columns are equivalent to function-based indexes.

Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.

Tables containing virtual columns can still be eligible for result caching.

Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:

Constraint on the virtual column must be disabled and re-enabled.

Indexes on the virtual column must be rebuilt.

Materialized views that access the virtual column must be fully refreshed.

The result cache must be flushed if cached queries have accessed the virtual column.

Table statistics must be regathered.

Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.

The expression used in the virtual column definition has the following restrictions:

It cannot refer to another virtual column by name.

It can only refer to columns defined in the same table.

If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.

The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

Disclaimer

This blog contains things about technology that I jot about, keep for my records. Some articles in this blog are not owned by me. They are for my reference only. In older posts the original links may not be there, its not intentional. Thanks for understanding.