MSSQL Union Based

Context

This guide focuses on exploiting MSSQL Union Based SQL Injection to extract sensitive data from databases. It's tailored for those familiar with SQL Union clauses, data type matching, and SQL query structures.

Theory

Strict Column Data Type Matching in MSSQL

MSSQL enforces strict data type matching when utilizing UNION queries. This means each SELECT statement must have the same number and type of columns. A common technique to bypass data type mismatches is to use NULL values. Since NULL can represent any data type, it effectively neutralizes data type constraints when crafting injection queries.

Using UNION ALL in SQL Injection

The UNION ALL operator allows attackers to append additional SELECT queries to an original query result. By injecting controlled queries using UNION ALL, attackers can overlay the intended data retrieval with one that exposes sensitive information from the database.

Limitations of LIMIT, TOP N, OFFSET-FETCH in MSSQL

Unlike some other SQL databases that use LIMIT to restrict the number of returned rows, MSSQL employs TOP N. The OFFSET-FETCH clause is an enhancement in SQL Server 2012+ that provides additional control over data paging. These limitations require special attention when adapting general SQL injection techniques to MSSQL environments.

Practice

MSSQL Union Based SQL Injection

Here's a step-by-step guide to exploiting MSSQL using Union Based SQL Injection:

• Determine Column Count

  Start by identifying the number of columns in the target query. Begin with injecting NULL values to test for column count matching.

  SELECT column1, column2 FROM table UNION ALL SELECT NULL, NULL --
  

  Adjust the number of NULLs until no error is returned.

• Identify Data Types

  Next, discern the data types of the columns by attempting to inject known data types. Start with strings and determine if the injection is successful.

  SELECT column1, column2 FROM table UNION ALL SELECT 'a', NULL --
  SELECT column1, column2 FROM table UNION ALL SELECT NULL, 'b' --
  

  Check which injections cause errors or valid responses to infer column data types.

• Validate Columns Using ORDER BY

  Sorting the result set can further confirm valid column positions.

  SELECT column1, column2 FROM table UNION ALL SELECT NULL, NULL ORDER BY 1 --
  

  Evaluate the query response; adjust the ORDER BY index for accuracy.

• Extract Sensitive Data

  Once column count and data types are confirmed, proceed to extract sensitive data. Replace the injected NULLs with actual column names from a table containing sensitive data.

  SELECT column1, column2 FROM table UNION ALL SELECT username, password FROM users --
  

  This completes the Union Based SQL Injection to retrieve sensitive information like usernames and passwords.

Tools

• sqlmap
• Burp Suite

This technique, when executed properly, can result in unauthorized access to sensitive data housed within an MSSQL database. As always, ensure you have proper authorization and permissions before testing these methods on any system.