界: Input Validation and Representation

输入验证与表示问题是由元字符、交替编码和数字表示引起的。安全问题源于信任输入。这些问题包括：“Buffer Overflows”、“Cross-Site Scripting”攻击、“SQL Injection”等其他问题。

SQL Injection: PartiQL

C#/VB.NET/ASP.NET
Java/JSP

Abstract

通过来自不可信数据源的输入构造动态 PartiQL 语句，攻击者就能够修改语句的含义或者执行任意 PartiQL 命令。

Explanation

在以下情况下会发生 SQL Injection: PartiQL 问题：

1.数据从一个不可信数据源进入程序。

2.数据用于动态地构造 PartiQL 查询。
示例 1：以下代码可动态地构造并执行 PartiQL 查询，以便搜索与指定名称相匹配的条目。该查询仅会显示条目的owner与当前经过身份验证的用户的名称一致的条目。


...
string userName = identity.User;
string itemName = apiGatewayProxyRequest.QueryStringParameters['item'];
string statement = $"SELECT * FROM items WHERE owner = '{userName}' AND itemname = '{itemName}'";

var executeStatementRequest = new ExecuteStatementRequest();
executeStatementRequest.Statement = statement;
var executeStatementResponse = await dynamoDBClient.ExecuteStatementAsync(executeStatementRequest);
return displayResults(executeStatementResponse.Items);
...

查询计划执行以下代码：


	SELECT * FROM items
	WHERE owner = <userName>
	AND itemname = <itemName>;

但是，由于该查询是动态构造的，由一个常数基本查询字符串和一个用户输入字符串连接而成，因此只有在 itemName 不包含单引号字符时，该查询才能正常运行。如果一个用户名为 wiley 的攻击者为 itemName 输入字符串“name' OR 'a'='a”，则该查询会变成：


	SELECT * FROM items
	WHERE owner = 'wiley'
	AND itemname = 'name' OR 'a'='a';

如果添加条件 OR 'a'='a'，where 子句的值将始终为 true，这样该查询在逻辑上就等同于一个更为简单的查询：

阻止 SQL Injection 攻击的一种传统方法是将其作为一种输入验证问题来处理，只接受列在安全值允许列表中的字符，或者识别并避免列表中的潜在恶意值（拒绝列表）。检查允许列表是一种非常有效的方法，它可以强制执行严格的输入验证规则，但是参数化的 SQL 语句所需的维护工作更少，而且能提供更好的安全保障。而对于通常采用的拒绝列表实现方式，由于总是存在一些小漏洞，所以并不能有效地防止 SQL Injection 攻击。例如，攻击者可以：

- 将未引用的字段作为目标
- 寻找方法以绕过某些需要转义的元字符
- 使用存储过程隐藏注入的元字符

手动转义 PartiQL 查询输入中的字符有一定的帮助，但是并不能完全保护您的应用程序免受 PartiQL Injection 攻击。

References

[1] S. J. Friedl SQL Injection Attacks by Example

[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine

[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus

[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press

[5] PartiQL - A SQL-Compatible Query Language for Amazon DynamoDB

[6] Standards Mapping - Common Weakness Enumeration CWE ID 89

[7] Standards Mapping - Common Weakness Enumeration Top 25 2019 [6] CWE ID 089

[8] Standards Mapping - Common Weakness Enumeration Top 25 2020 [6] CWE ID 089

[9] Standards Mapping - Common Weakness Enumeration Top 25 2021 [6] CWE ID 089

[10] Standards Mapping - Common Weakness Enumeration Top 25 2022 [3] CWE ID 089

[11] Standards Mapping - Common Weakness Enumeration Top 25 2023 [3] CWE ID 089

[12] Standards Mapping - DISA Control Correlation Identifier Version 2 CCI-001310, CCI-002754

[13] Standards Mapping - FIPS200 SI

[14] Standards Mapping - General Data Protection Regulation (GDPR) Indirect Access to Sensitive Data

[15] Standards Mapping - NIST Special Publication 800-53 Revision 4 SI-10 Information Input Validation (P1)

[16] Standards Mapping - NIST Special Publication 800-53 Revision 5 SI-10 Information Input Validation

[17] Standards Mapping - OWASP Application Security Verification Standard 4.0 5.3.4 Output Encoding and Injection Prevention Requirements (L1 L2 L3), 5.3.5 Output Encoding and Injection Prevention Requirements (L1 L2 L3)

[18] Standards Mapping - OWASP Mobile 2014 M7 Client Side Injection

[19] Standards Mapping - OWASP Mobile Application Security Verification Standard 2.0 MASVS-CODE-4

[20] Standards Mapping - OWASP Top 10 2004 A6 Injection Flaws

[21] Standards Mapping - OWASP Top 10 2007 A2 Injection Flaws

[22] Standards Mapping - OWASP Top 10 2010 A1 Injection

[23] Standards Mapping - OWASP Top 10 2013 A1 Injection

[24] Standards Mapping - OWASP Top 10 2017 A1 Injection

[25] Standards Mapping - OWASP Top 10 2021 A03 Injection

[26] Standards Mapping - Payment Card Industry Data Security Standard Version 1.1 Requirement 6.5.6

[27] Standards Mapping - Payment Card Industry Data Security Standard Version 1.2 Requirement 6.3.1.1, Requirement 6.5.2

[28] Standards Mapping - Payment Card Industry Data Security Standard Version 2.0 Requirement 6.5.1

[29] Standards Mapping - Payment Card Industry Data Security Standard Version 3.0 Requirement 6.5.1

[30] Standards Mapping - Payment Card Industry Data Security Standard Version 3.1 Requirement 6.5.1

[31] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2 Requirement 6.5.1

[32] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2.1 Requirement 6.5.1

[33] Standards Mapping - Payment Card Industry Data Security Standard Version 4.0 Requirement 6.2.4

[34] Standards Mapping - Payment Card Industry Software Security Framework 1.0 Control Objective 4.2 - Critical Asset Protection

[35] Standards Mapping - Payment Card Industry Software Security Framework 1.1 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation

[36] Standards Mapping - Payment Card Industry Software Security Framework 1.2 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation, Control Objective C.3.2 - Web Software Attack Mitigation

[37] Standards Mapping - SANS Top 25 2009 Insecure Interaction - CWE ID 089

[38] Standards Mapping - SANS Top 25 2010 Insecure Interaction - CWE ID 089

[39] Standards Mapping - SANS Top 25 2011 Insecure Interaction - CWE ID 089

[40] Standards Mapping - Security Technical Implementation Guide Version 3.1 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[41] Standards Mapping - Security Technical Implementation Guide Version 3.4 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[42] Standards Mapping - Security Technical Implementation Guide Version 3.5 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[43] Standards Mapping - Security Technical Implementation Guide Version 3.6 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[44] Standards Mapping - Security Technical Implementation Guide Version 3.7 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[45] Standards Mapping - Security Technical Implementation Guide Version 3.9 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[46] Standards Mapping - Security Technical Implementation Guide Version 3.10 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[47] Standards Mapping - Security Technical Implementation Guide Version 4.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[48] Standards Mapping - Security Technical Implementation Guide Version 4.3 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[49] Standards Mapping - Security Technical Implementation Guide Version 4.4 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[50] Standards Mapping - Security Technical Implementation Guide Version 4.5 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[51] Standards Mapping - Security Technical Implementation Guide Version 4.6 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[52] Standards Mapping - Security Technical Implementation Guide Version 4.7 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[53] Standards Mapping - Security Technical Implementation Guide Version 4.8 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[54] Standards Mapping - Security Technical Implementation Guide Version 4.9 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[55] Standards Mapping - Security Technical Implementation Guide Version 4.10 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[56] Standards Mapping - Security Technical Implementation Guide Version 4.11 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[57] Standards Mapping - Security Technical Implementation Guide Version 4.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[58] Standards Mapping - Security Technical Implementation Guide Version 5.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[59] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[60] Standards Mapping - Security Technical Implementation Guide Version 5.3 APSC-DV-002530 CAT II, APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[61] Standards Mapping - Security Technical Implementation Guide Version 6.1 APSC-DV-002530 CAT II, APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[62] Standards Mapping - Web Application Security Consortium Version 2.00 SQL Injection (WASC-19)

[63] Standards Mapping - Web Application Security Consortium 24 + 2 SQL Injection

desc.dataflow.dotnet.sql_injection_partiql

Abstract

通过来自不可信数据源的输入构造动态 PartiQL 语句，攻击者就能够修改语句的含义或者执行任意 PartiQL 命令。

Explanation


...
String userName = identity.getUser();
String itemName = apiGatewayProxyRequest.getQueryStringParameters('item');
String statement = String.format("SELECT * FROM items WHERE owner = '%s' AND itemname = '%s'", userName, itemName);
ExecuteStatementRequest executeStatementRequest = new ExecuteStatementRequest();
executeStatementRequest.setStatement(statement);
ExecuteStatementResponse executeStatementResponse = dynamoDBClient.executeStatement(executeStatementRequest);
return displayResults(executeStatementResponse.items());
...

查询计划执行以下代码：


	SELECT * FROM items
	WHERE owner = <userName>
	AND itemname = <itemName>;


	SELECT * FROM items
	WHERE owner = 'wiley'
	AND itemname = 'name' OR 'a'='a';

如果添加条件 OR 'a'='a'，where 子句的值将始终为 true，这样该查询在逻辑上就等同于一个更为简单的查询：
阻止 SQL Injection 攻击的一种传统方法是将其作为一种输入验证问题来处理，只接受列在安全值允许列表中的字符，或者识别并避免列表中的潜在恶意值（拒绝列表）。检查允许列表是一种非常有效的方法，它可以强制执行严格的输入验证规则，但是参数化的 SQL 语句所需的维护工作更少，而且能提供更好的安全保障。而对于通常采用的拒绝列表实现方式，由于总是存在一些小漏洞，所以并不能有效地防止 SQL Injection 攻击。例如，攻击者可以：

- 将未引用的字段作为目标
- 寻找方法以绕过某些需要转义的元字符
- 使用存储过程隐藏注入的元字符

手动转义 PartiQL 查询输入中的字符有一定的帮助，但是并不能完全保护您的应用程序免受 PartiQL Injection 攻击。

References

[1] S. J. Friedl SQL Injection Attacks by Example

[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine

[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus

[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press

[5] PartiQL - A SQL-Compatible Query Language for Amazon DynamoDB

[6] IDS00-J. Prevent SQL Injection CERT

[7] INJECT-2: Avoid dynamic SQL Oracle

[8] Standards Mapping - Common Weakness Enumeration CWE ID 89

[9] Standards Mapping - Common Weakness Enumeration Top 25 2019 [6] CWE ID 089

[10] Standards Mapping - Common Weakness Enumeration Top 25 2020 [6] CWE ID 089

[11] Standards Mapping - Common Weakness Enumeration Top 25 2021 [6] CWE ID 089

[12] Standards Mapping - Common Weakness Enumeration Top 25 2022 [3] CWE ID 089

[13] Standards Mapping - Common Weakness Enumeration Top 25 2023 [3] CWE ID 089

[14] Standards Mapping - DISA Control Correlation Identifier Version 2 CCI-001310, CCI-002754

[15] Standards Mapping - FIPS200 SI

[16] Standards Mapping - General Data Protection Regulation (GDPR) Indirect Access to Sensitive Data

[17] Standards Mapping - NIST Special Publication 800-53 Revision 4 SI-10 Information Input Validation (P1)

[18] Standards Mapping - NIST Special Publication 800-53 Revision 5 SI-10 Information Input Validation

[19] Standards Mapping - OWASP Application Security Verification Standard 4.0 5.3.4 Output Encoding and Injection Prevention Requirements (L1 L2 L3), 5.3.5 Output Encoding and Injection Prevention Requirements (L1 L2 L3)

[20] Standards Mapping - OWASP Mobile 2014 M7 Client Side Injection

[21] Standards Mapping - OWASP Mobile Application Security Verification Standard 2.0 MASVS-CODE-4

[22] Standards Mapping - OWASP Top 10 2004 A6 Injection Flaws

[23] Standards Mapping - OWASP Top 10 2007 A2 Injection Flaws

[24] Standards Mapping - OWASP Top 10 2010 A1 Injection

[25] Standards Mapping - OWASP Top 10 2013 A1 Injection

[26] Standards Mapping - OWASP Top 10 2017 A1 Injection

[27] Standards Mapping - OWASP Top 10 2021 A03 Injection

[28] Standards Mapping - Payment Card Industry Data Security Standard Version 1.1 Requirement 6.5.6

[29] Standards Mapping - Payment Card Industry Data Security Standard Version 1.2 Requirement 6.3.1.1, Requirement 6.5.2

[30] Standards Mapping - Payment Card Industry Data Security Standard Version 2.0 Requirement 6.5.1

[31] Standards Mapping - Payment Card Industry Data Security Standard Version 3.0 Requirement 6.5.1

[32] Standards Mapping - Payment Card Industry Data Security Standard Version 3.1 Requirement 6.5.1

[33] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2 Requirement 6.5.1

[34] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2.1 Requirement 6.5.1

[35] Standards Mapping - Payment Card Industry Data Security Standard Version 4.0 Requirement 6.2.4

[36] Standards Mapping - Payment Card Industry Software Security Framework 1.0 Control Objective 4.2 - Critical Asset Protection

[37] Standards Mapping - Payment Card Industry Software Security Framework 1.1 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation

[38] Standards Mapping - Payment Card Industry Software Security Framework 1.2 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation, Control Objective C.3.2 - Web Software Attack Mitigation

[39] Standards Mapping - SANS Top 25 2009 Insecure Interaction - CWE ID 089

[40] Standards Mapping - SANS Top 25 2010 Insecure Interaction - CWE ID 089

[41] Standards Mapping - SANS Top 25 2011 Insecure Interaction - CWE ID 089

[42] Standards Mapping - Security Technical Implementation Guide Version 3.1 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[43] Standards Mapping - Security Technical Implementation Guide Version 3.4 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[44] Standards Mapping - Security Technical Implementation Guide Version 3.5 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[45] Standards Mapping - Security Technical Implementation Guide Version 3.6 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[46] Standards Mapping - Security Technical Implementation Guide Version 3.7 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[47] Standards Mapping - Security Technical Implementation Guide Version 3.9 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[48] Standards Mapping - Security Technical Implementation Guide Version 3.10 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II

[49] Standards Mapping - Security Technical Implementation Guide Version 4.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[50] Standards Mapping - Security Technical Implementation Guide Version 4.3 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[51] Standards Mapping - Security Technical Implementation Guide Version 4.4 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[52] Standards Mapping - Security Technical Implementation Guide Version 4.5 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[53] Standards Mapping - Security Technical Implementation Guide Version 4.6 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[54] Standards Mapping - Security Technical Implementation Guide Version 4.7 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[55] Standards Mapping - Security Technical Implementation Guide Version 4.8 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[56] Standards Mapping - Security Technical Implementation Guide Version 4.9 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[57] Standards Mapping - Security Technical Implementation Guide Version 4.10 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[58] Standards Mapping - Security Technical Implementation Guide Version 4.11 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[59] Standards Mapping - Security Technical Implementation Guide Version 4.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[60] Standards Mapping - Security Technical Implementation Guide Version 5.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[61] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[62] Standards Mapping - Security Technical Implementation Guide Version 5.3 APSC-DV-002530 CAT II, APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[63] Standards Mapping - Security Technical Implementation Guide Version 6.1 APSC-DV-002530 CAT II, APSC-DV-002540 CAT I, APSC-DV-002560 CAT I

[64] Standards Mapping - Web Application Security Consortium Version 2.00 SQL Injection (WASC-19)

[65] Standards Mapping - Web Application Security Consortium 24 + 2 SQL Injection

desc.dataflow.java.sql_injection_partiql