What is Union in Oracle with example?
MySql and PostgreSql are very similar to each other so if you know one the other will come easy. However, Oracle is different from those two and will require some additional knowledge to successfully exploit it. As always when testing for this vulnerability I usually just throw a bunch of single and double quotes around until. I get an error message as shown below:
As shown above the error message starts with ORA. That’s a good sign that you are dealing with an Oracle database. Sometimes you can’t tell the database type from the error message. If that’s the case you need to return the database version from a sql query
Select banner from vs version
Note that similar to PostgreSql when you are selecting a column it must match .the type of the first select statement. You can also use the word ‘null’ as well if you don’t know the type. Another thing to note is that when using the select operator. You must default specify table in the above image was used.
What is Union SQL injection attack?
Just like MySql and PostgreSql the first step is to figure out. How many columns the select statement is using. Again this can be accomplished with the order by operator as mentioned in the previous sections. We increase the order by operator by one until you get an error. This will tell you how many columns there are.
As shown above an error was displayed once we got to column number Three. There must only be 2 columns used in the select statement. The next step is to retrieve a list of tables belonging to the database.
Oracle UNION ALL example
● union all select LISTAGG(table_name,’,’) within group (ORDER BY
table_name),null from all_tables where tablespace_name = ‘USERS’ —
If you’re used to using MySql or PostgreSql you would normally use the information_schema.tables. Table to get a list of tables but oracle uses the all_tables. You probably want to filter on the tablespace_name column value USERS otherwise. You will get hundreds of default tables which you have no use for. Also notice the “listagg()” function, this is the same as MySqls ‘group_concat()’ function
and is used to concatenate several rows into a single string. When using the listagg() function you must also use the ‘within group()’ operator to specify. The order of the listagg function results.
SQL injection in Oracle
Once you get your target table you need to get a list of the column names belonging to
that table as shown below:
● union all select LISTAGG(column_name,’,’) within group (ORDER BY
column_name),null from all_tab_columns where table_name = ‘EMPLOYEES’–
In MySql we would have queried the “information_schema.columns” table to get a list of columns. Belonging to a table but with oracle we use the “all_tab_columns” table to do this. Finally once you know the tables column names you can extract the information. You want using a standard sql query as shown below
Union all select email,phone_number from employees
As you might have noticed Oracle sql injection is a little different compared. MySQL and PostgreSQL but it is still very similar. The only difference is the syntax of a couple things, but the process remains the same. Figure out the target table name, get the tables columns, then finally extract the sensitive information.
Summary:-
SQL injection is one of the oldest tricks in the book yet it still makes the OWASP. Top 10 list every yearit’s relatively easy to search for and exploit plus. It has a high impact on the server since you are able to steal everything in the database. Including usernames and passwords. If you’re searching for this vulnerability you are bound to come across a vulnerable endpoint. Just throw single and double quotes everywhere and look for the common error messages. Unlike 90% of other hackers you should know how to exploit
the vast majority of databases. Just Mysql so when you do find this bug it shouldn’t be too hard to exploit.