Exploiting SQL Injection : Enumerating the Database Schema (part 1) - SQL Server

You have seen a number of different techniques for extracting data from the remote database. To illustrate these techniques, we have retrieved only small pieces of information, so now it's time to extend our scope and see how to use these techniques to obtain larger amounts of data. After all, databases can be huge beasts, containing several terabytes of data. To mount a successful attack, and to properly assess the risk that is posed by an SQL injection vulnerability, performing a fingerprint and squeezing a few bits of information is not enough: You must show that a skilled and resourceful attacker is able to enumerate the tables that are present in the database and quickly extract the ones that he is interested in. In this section, a few examples will be illustrated of how you can obtain a list of all databases that are installed on the remote server, a list of all tables of each of those databases, and a list of all columns for each of those tables—in short, how to enumerate the database schema. We will perform this attack by extracting some of the metadata that databases use to organize and manage the databases they store. In the examples, we will mostly use UNION queries, but you obviously can extend the same concepts to all other SQL injection techniques.

Tip

To enumerate the tables/columns that are present on the remote database, you need to access specific tables that contain the description of the structure of the various databases. This information is usually called metadata (which means “data about other data”). An obvious precondition for this to succeed is that the user performing the queries must be authorized to access such metadata, and this might not always be the case. If the enumeration phase fails, you might have to escalate your privileges to a more powerful user.

SQL Server

Let's go back to our e-commerce application, with our vulnerable ASP page that returns the details of a specific article. As a reminder, the page is called with a URL such as the following:

http://www.victim.com/products.asp?id=12

The first piece of information that we usually want to extract is a list of the databases that are installed on the remote server. Such information is stored in the master..sysdatabases table, and the list of names can be retrieved with the following query:

select name from master..sysdatabases

We therefore start by requesting the following URL:

http://www.victim.com/products.asp?id=12+union+select+null,name,null,
     null+from+master..sysdatabases

The result will be the page shown in Figure 1

Figure 1. Using UNION to Enumerate All Databases Installed on the Remote DBMS

Not bad for a start! The remote application dutifully provided us with the list of the databases. The master database is obviously one of the most interesting, since it contains the metadata that describes all other databases (including the sysdatabases table we just queried!). The e-shop database also looks very promising, as it's probably the one that contains all the data used by this e-commerce application, including all customer data. The other databases on this list are shipped by default with SQL Server, and therefore are less interesting. If this query returns a large number of databases and you need to precisely identify which one is being used by the application you are testing, the following query can help you:

SELECT DB_NAME()

Now that we have the name of the databases, it's time to start enumerating the tables that compose them and that contain the data we are looking for. Each database has a table called sysobjects that contains exactly that information. It also contains a lot more data we're not necessarily interested in, and therefore we need to focus on user-defined objects by specifying that we are only interested in the rows where the type is U. Assuming that we want to delve a little deeper into the contents of the e–shop database, here's the query to inject:

SELECT name FROM e–shop..sysobjects WHERE xtype=‘U’

The corresponding URL is obviously the following:

http://www.victim.com/products.aspid=12+union+select+null,name,null,
  null+from+e–shop..sysobjects+where+xtype%3D‘U’--

The page that results will look something like the screenshot shown in Figure 2

Figure 2. Enumerating All Tables of a Specific Database

As you can see, there are some interesting tables, with customers and transactions probably being the ones with the most promising contents! To extract that data, the next step is to enumerate the columns of these tables. We will look at two different ways to extract the names of the columns of a given table (e.g., customers). Here is the first one:

SELECT name FROM e–shop..syscolumns WHERE id = (SELECT id FROM
  e–shop..sysobjects WHERE name = ‘customers’)

In this example, we nest a SELECT query into another SELECT query. We start by selecting the name field of the e-shops..syscolumns table, which contains all the columns of the e-shop database. Because we are only interested in the columns of the customers table, we add a WHERE clause, using the id field, that is used in the syscolumns table to uniquely identify the table that each column belongs to. What's the right id? Because every table listed in sysobjects is identified by the same id, we need to select the id value of the table whose name is customers, and that is the second SELECT. If you don't like nested queries and are a fan of joining tables, the following query extracts the same data:

SELECT a.name FROM e–shop..syscolumns a,e–shop..sysobjects b WHERE b.name =
    ‘customers’ AND a.id = b.id

Whichever approach you decide to take, the resultant page will be similar to the screenshot in Figure 3

Figure 3. Example of a Successful Enumeration of the Columns of a Specific Table

As you can see, we now know the names of the columns of the customers table. We can guess that both login and passwords are of type string, and we can therefore return them with yet another UNION SELECT, this time using both the Type and Description fields of the original query. This is performed by the following URL:

http://www.victim.com/products.aspid=12+union+select+null,login,password,
   null+from+e–shop..customers--

As you can see, this time we use two column names in our injected query. The result, which finally contains the data we were looking for, is in the screenshot shown in Figure 4

Figure 4. Finally Getting the Data: Username and Passwords, in This Case!

Bingo!! However, the result is not just a very long list of users. It seems that this application likes to store user passwords in clear text instead of using a hashing algorithm. The same attack sequence could be used to enumerate and retrieve any other table that the user has access to, but having arrived at this point, you might just call the client, tell them they have a huge problem (actually, more than just one), and call it a day.

Are You Owned?

Using Hash Functions to Store Passwords in Your Database

The scenario that was just illustrated, in which a few queries have been enough to retrieve a list of usernames and passwords unencrypted (in clear text), is not as unusual as you might think

The danger of storing users’ passwords in clear text poses other dangers: Because human beings have the tendency to reuse the same password for several different online services, a successful attack such as the one described might pose a threat not only to the users’ accounts on victim.com, but also to other areas of their online identity, such as online banking and private e-mail. And victim.com might even be liable for these additional break-ins, depending on the specific laws of the country where it resides!

Therefore, if you are responsible for a Web application or a database that handles the credentials of your users, make sure that such credentials are always stored using a cryptographic hash function. A cryptographic hash function transforms an arbitrary value (in our case, the user's password) into a fixed-length string (called the hash value). This function has several mathematical properties, but here we are mostly interested in two of them:

• Given a hash value, it is extremely difficult to construct a value that generates it.

• The probability that two different values generate the same hash value is extremely low.

Storing the hash value of the password instead of the password itself still allows users to authenticate, because it's enough to calculate the hash value of the password they provide and compare it with the stored hash value. However, it provides a security advantage, because if the list of hash values is captured, the attacker would not be able to convert them back to the original passwords without going through a brute-force attack.

When choosing the right hashing algorithm, do not rely on MD5, as several weaknesses have been found over the years. SHA1 provides a far higher level of security against attacks, and its more recent variants (such as SHA256 and SHA512) even more so, thanks to a longer hash value length. Using such an algorithm will not protect you against SQL injection attacks , but will greatly protect your customers in case the data falls into the wrong hands.