SQL_INJ_005 security data_error ai_generated true

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

SQL injection in stored procedure due to dynamic SQL concatenation

ID: security/sql-injection-in-stored-procedure-dynamic-sql

其他格式: JSON · Markdown 中文 · English
92%修复率
87%置信度
1证据数
2024-04-12首次发现

版本兼容性

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

根因分析

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

English

A stored procedure uses dynamic SQL with string concatenation of user input (e.g., via EXEC or sp_executesql without parameterization), allowing SQL injection even though the application uses parameterized queries.

generic

官方文档

https://learn.microsoft.com/en-us/sql/relational-databases/security/sql-injection

解决方案

  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.

无效尝试

常见但无效的做法:

  1. Add input validation to the stored procedure (e.g., escape single quotes) 90% 失败

    Input validation is insufficient; attackers can bypass escaping with advanced techniques (e.g., second-order injection, using unicode). Parameterization is the only safe approach.

  2. Use a blacklist to block SQL keywords like 'DROP', 'SELECT' 95% 失败

    Blacklists are easily bypassed (e.g., using 'SeLeCt', comments, or encoding).