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"

Difference between two timestamp in seconds

Question: How can I get difference between two timestamp variables in seconds in Oracle?


Answer: Use interval datatype to store the difference. Example code is below.

DECLARE
  l_start_time TIMESTAMP;
  l_end_time   TIMESTAMP;
  l_sec        INTERVAL DAY(9) TO SECOND(6);
BEGIN
  l_start_time := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    NULL;
  END LOOP;
  l_end_time := systimestamp;


  l_sec := l_end_time - l_start_time;
  dbms_output.put_line('Seconds past=' || abs(extract(SECOND FROM l_sec) + extract(minute FROM l_sec) * 60 + extract(hour FROM l_sec) * 60 * 60 + extract(DAY FROM l_sec) * 24 * 60 * 60));
END;


Output is:
Seconds past=.001144

Oracle XE - Ubuntu - Solution to 'cannot access http://127.0.0.1:8080/apex'

I received some queries lately from various users regarding unable to open page http://localhost:8081/apex or http://127.0.0.1:8081/apex.

The linux environment reported was Ubuntu and here is how to solve this error:

How to import a Java Jar to Oracle Forms


The steps involved are simple as below:

1. Save the jar file where forms jar files are saved
   Usually {FormsInstalledPath}\forms\java folder

2. Open Registry editor (REGEDIT)
   Browse to the following Key
   HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_DevSuiteHome1

3. Add Java archive name in the Name FORMS_BUILDER_CLASSPATH
 

4. Now open Form Builder and create new form.


5. Open Menu Program > Import Java Classes and find your java class added and Import.

Enjoy

CSV data to multiple rows - Oracle Query

I have a data in CSV format (comma separated values) like follows:

first,second,third,fourth,fifth 

I want the output as follows:
column_value
first
second
third
fourth
fifth

The easiest way to achieve is to use a CONNECT BY clause in DUAL table and to populate the results. Here is the query:


SELECT substr(str, instr(str, ',', 1, LEVEL) + 1, instr(str, ',', 1, LEVEL + 1) - instr(str, ',', 1, LEVEL) - 1) column_value
FROM   (SELECT ',' || '&mystring' || ',' str FROM dual)
CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) - 1;


Pass value first,second,third,fourth,fifth to mystring.

11g New Feature - SIMPLE_INTEGER

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.

Posted by decipherinfosys, More information check Oracle Documentation

Load CSV data to Oracle or other databases


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/

The installation instructions are also available through this website http://csvloader.sourceforge.net/

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 in Oracle Database 11g Release 1

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. - Anantha

When 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.


CREATE TABLE employees (
 id          NUMBER,
 first_name  VARCHAR2(10),
 last_name   VARCHAR2(10),
 salary      NUMBER(9,2),
 comm1       NUMBER(3),
 comm2       NUMBER(3),
 
salary1     AS (ROUND(salary*(1+comm1/100),2)),
 salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
 CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;

Querying the table shows the inserted data plus the derived commission-based salaries.


SELECT * FROM employees;

       ID FIRST_NAME LAST_NAME      SALARY      COMM1      COMM2    SALARY1    SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        1 JOHN       DOE               100          5         10        105        110
        2 JAYNE      DOE               200         10         20        220        240

2 rows selected.
The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the
[DBA | ALL | USER]_TAB_COLUMNS views.

COLUMN data_default FORMAT A50
SELECT column_name, data_default
FROM   user_tab_columns
WHERE  table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1                        ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2                        ROUND("SALARY"*(1+"COMM2"/100),2)

8 rows selected.


Notes and restrictions on virtual columns include:
  • 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.
Here is an example of Virtual Column-Based Partitioning.

For more information see:
Brought to you from