UNION SQL Injection

Question

I'm trying to demo SQL Injection with a UNION query but I'm not getting results.

This is the C# code and the database is SQL Server 2008 R2:

        string cString = "server=.\\sqlexpress; database=testDB; trusted_connection=true";
        SqlConnection conn = new SqlConnection(cString);
        conn.Open();

        string sql = "select * from Users where UserName='" + userName + "' and Password='" + password + "'";
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;

        SqlDataReader reader = cmd.ExecuteReader();

        StringBuilder sb = new StringBuilder(1024);

        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                sb.Append(" " + reader.GetName(i));
                sb.Append(": ");
                sb.Append(reader[i]);
            }
            sb.Append("<br />");
        }
        dataLabel.Text = sb.ToString();

I have a username and password text boxes and the input is passed to this method.
I tried the following but no result:

' union select * from products --

the Users table and Products table have identical column types (int, nvarchar, nvarchar).
Can someone help? What am i missing?

Answer 1

In order for a union select statement work, both select statements must return the same number of columns. This is because a union select is concatenating both select statements and returning them as one dataset.

If the first table, Users has 4 columns, then the exploit would look like:

select * from Users where UserName='' union select producs.id,products.name,products.price,products.quantity,products from products-- ' and Password=''"

In this case the first select statement will not return a value because there is not a record in the database where UserName='', where as the 2nd statement would return information from the products table. Although a more likely exploit would be to select out the password's from the Users table in the position of the username so that it will be printed by reader.GetName(i).

Another way of exploiting this issue is providing your own data to be returned:

select * from Users where UserName='' union select 1,'admin','password','ADMINISTRATOR','[email protected]' -- ' and Password=''"

If the data returned by the query is used in a sensitive function, like being able to taint a variable used in fileio or code evaluation.

How do you know how many columns the first query has?
I use burp intruder to iterate over every possibility and filter for non-error'ed requests.

... or you could just use sqlmap

Answer 2

While testing, why not add a debugging statement that logs your SQL query before executing it?

Then try running it in your database directly and see what error message the DB returns (if any)?

If there's no inadvertent automatic escaping of ' in your framework, it seems your query:

string sql = "select * from Users where UserName='" + userName + "' and Password='" + password + "'";

becomes:

 string sql = "select * from Users where UserName='' union select * from products --' and Password=''";

Can you verify that a string like this appears in your log (that your framework didn't inadvertently escape ' characters)?

Can you execute that specific SQL string? Does it return an error message for your specific schema/tables? (I don't program in .NET, use SQL-server, or have your database (I tend to use linux stack; e.g., postgresql). )

But running on a sample DB I had where I tried a UNION of two tables like SELECT * FROM table_a where name='' union select * from table_b, I get a error from my DB: SELECTs to the left and right of union do not have the same number of result columns. My table_a had 7 columns and table_b had 11 columns. Thus a union of the 7 columns of table_a and the 11 columns of table_b won't work.

This makes sense if you look up what SQL UNION does:

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

So you have to guess the schema and make sure it matches. That is if the original SQL query had a select * from Users, and the columns of Users is user_id (INT), userName (VARCHAR), password (VARCHAR), you must make sure your UNION asks for three specific fields that have datatypes int, VARCHAR and VARCHAR that are present in the second table. Also if you don't want results from the first query you may want to add an AND 1=0 to your WHERE clause.