# SQL injection in stored procedure due to dynamic SQL concatenation

- **ID:** `security/sql-injection-in-stored-procedure-dynamic-sql`
- **Domain:** security
- **Category:** data_error
- **Error Code:** `SQL_INJ_005`
- **Verification:** ai_generated
- **Fix Rate:** 92%

## 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.

## Version Compatibility

| Version | Status | Introduced | Deprecated |
|---------|--------|------------|------------|
| SQL Server 2022 | active | — | — |
| MySQL 8.0 | active | — | — |
| PostgreSQL 16 | active | — | — |

## Workarounds

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

## Dead Ends

- **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% fail)
- **Use a blacklist to block SQL keywords like 'DROP', 'SELECT'** — Blacklists are easily bypassed (e.g., using 'SeLeCt', comments, or encoding). (95% fail)