# 由于动态 SQL 连接，存储过程中存在 SQL 注入

- **ID:** `security/sql-injection-in-stored-procedure-dynamic-sql`
- **领域:** security
- **类别:** data_error
- **错误码:** `SQL_INJ_005`
- **验证级别:** ai_generated
- **修复率:** 92%

## 根因

存储过程使用动态 SQL，通过字符串连接用户输入（例如，通过 EXEC 或 sp_executesql 而不进行参数化），即使应用程序使用参数化查询，也允许 SQL 注入。

## 版本兼容性

| 版本 | 状态 | 引入 | 弃用 |
|------|------|------|------|
| SQL Server 2022 | active | — | — |
| MySQL 8.0 | active | — | — |
| PostgreSQL 16 | active | — | — |

## 解决方案

1. ```
   Rewrite the stored procedure to use parameterized dynamic SQL with sp_executesql. Example in T-SQL:
CREATE PROCEDURE GetUser @username NVARCHAR(50), @tableName NVARCHAR(128)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'SELECT * FROM ' + QUOTENAME(@tableName) + ' WHERE username = @uname'
    EXEC sp_executesql @sql, N'@uname NVARCHAR(50)', @uname = @username
END
   ```
2. ```
   Replace dynamic SQL with static SQL if possible, using CASE statements or conditional logic to avoid concatenation.
   ```

## 无效尝试

- **Add input validation to the stored procedure (e.g., escape single quotes)** — Input validation is insufficient; attackers can bypass escaping with advanced techniques (e.g., second-order injection, using unicode). Parameterization is the only safe approach. (90% 失败率)
- **Use a blacklist to block SQL keywords like 'DROP', 'SELECT'** — Blacklists are easily bypassed (e.g., using 'SeLeCt', comments, or encoding). (95% 失败率)