SQL_INJ_005 security data_error ai_generated true

SQL injection in stored procedure due to dynamic SQL concatenation

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

Also available as: JSON · Markdown · 中文
92%Fix Rate
87%Confidence
1Evidence
2024-04-12First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
SQL Server 2022 active — — —
MySQL 8.0 active — — —
PostgreSQL 16 active — — —

Root Cause

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

中文

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

Official Documentation

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

Workarounds

  1. 95% success 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 
    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. 90% success Replace dynamic SQL with static SQL if possible, using CASE statements or conditional logic to avoid concatenation. 
    Replace dynamic SQL with static SQL if possible, using CASE statements or conditional logic to avoid concatenation.

中文步骤

  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.

Dead Ends

Common approaches that don't work:

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

    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% fail

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