Dynamic SQL and SQL Injection Prevention

Difficulty: Advanced
Generated on: 2025-07-10 02:30:57
Category: SQL Cheatsheet for Database Development

Dynamic SQL and SQL Injection Prevention Cheatsheet (Advanced)

1. Quick Overview

Dynamic SQL: Generating and executing SQL statements programmatically. Useful for creating flexible queries where parts of the SQL statement (e.g., table names, column names, WHERE clause conditions) are determined at runtime.

SQL Injection: A security vulnerability where malicious SQL code is injected into a query, potentially allowing attackers to bypass security measures, access sensitive data, modify data, or even execute operating system commands.

Why Use Dynamic SQL?

Flexible reporting
Parameterized search functionalities
Generic data manipulation routines
Database schema evolution

When to Use Dynamic SQL Sparingly:

Simple, fixed queries
When security is paramount, and static SQL with prepared statements can achieve the same goal.

2. Syntax

The syntax for dynamic SQL varies across different database systems. Here’s a breakdown:

SQL Server (T-SQL):

-- Example using EXECUTE
DECLARE @TableName SYSNAME = 'Customers';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE CustomerID = @CustomerID';

-- Important: Use sp_executesql for parameterized queries to prevent SQL injection
EXEC sp_executesql @SQL,
                   N'@CustomerID INT',
                   @CustomerID = 1;

-- Example using string concatenation (AVOID without QUOTENAME and proper validation)
DECLARE @ColumnName SYSNAME = 'City';
DECLARE @SearchValue NVARCHAR(255) = 'London';
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@ColumnName) + N' = ''' + @SearchValue + '''';  -- Very dangerous!

-- Correct way to parameterize:
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@ColumnName) + N' = @SearchValue';

EXEC sp_executesql @SQL,
                   N'@SearchValue NVARCHAR(255)',
                   @SearchValue = @SearchValue;

Oracle (PL/SQL):

-- Example using EXECUTE IMMEDIATE
DECLARE
  v_table_name VARCHAR2(30) := 'employees';
  v_sql        VARCHAR2(200);
  v_count      NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || dbms_assert.enquote_name(v_table_name) || ' WHERE department_id = :dept_id';  -- Use dbms_assert.enquote_name
  EXECUTE IMMEDIATE v_sql INTO v_count USING 90;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

-- Example with bind variables (Parameterization)
DECLARE
  v_table_name VARCHAR2(30) := 'employees';
  v_sql        VARCHAR2(200);
  v_count      NUMBER;
  v_dept_id    NUMBER := 90;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || dbms_assert.enquote_name(v_table_name) || ' WHERE department_id = :dept_id';
  EXECUTE IMMEDIATE v_sql INTO v_count USING v_dept_id;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

PostgreSQL (PL/pgSQL):

-- Example using EXECUTE
DO $$
DECLARE
  table_name TEXT := 'customers';
  sql_stmt TEXT;
  customer_count INTEGER;
BEGIN
  sql_stmt := format('SELECT COUNT(*) FROM %I WHERE city = %L', table_name, 'London'); -- %I for identifiers, %L for literals
  EXECUTE sql_stmt INTO customer_count;
  RAISE NOTICE 'Customer Count: %', customer_count;

  -- Parameterized example using USING
  sql_stmt := format('SELECT COUNT(*) FROM %I WHERE city = $1', table_name);
  EXECUTE sql_stmt INTO customer_count USING 'London';
  RAISE NOTICE 'Customer Count: %', customer_count;
END $$;

MySQL (Stored Procedures):

-- Example using PREPARE and EXECUTE
DELIMITER //
CREATE PROCEDURE dynamic_query(IN table_name VARCHAR(255), IN search_city VARCHAR(255))
BEGIN
  SET @sql = CONCAT('SELECT * FROM `', REPLACE(table_name, '`', '``'), '` WHERE city = ?'); -- Escape backticks!
  PREPARE stmt FROM @sql;
  SET @city = search_city;
  EXECUTE stmt USING @city;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

CALL dynamic_query('customers', 'London');

3. Common Use Cases

Dynamic Reporting: Generating reports based on user-selected criteria (e.g., date ranges, product categories, customer segments).
Metadata-Driven Queries: Building queries based on database metadata (e.g., column names, data types).
Schema Management: Creating or altering database objects dynamically (e.g., tables, indexes, stored procedures).
Data Migration: Transforming and loading data between different systems where the target schema is not fixed.
Search Functionality: Implementing flexible search features that allow users to search across multiple columns or tables.

Example: Dynamic Reporting

Imagine a reporting tool that allows users to select which columns to include in a report.

-- SQL Server Example
DECLARE @Columns NVARCHAR(MAX) = QUOTENAME('CustomerID') + N', ' + QUOTENAME('FirstName') + N', ' + QUOTENAME('LastName'); -- User-selected columns
DECLARE @TableName SYSNAME = 'Customers';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT ' + @Columns + N' FROM ' + QUOTENAME(@TableName);

EXEC sp_executesql @SQL;

4. Best Practices

Parameterization: Always use parameterized queries (bind variables) to prevent SQL injection. This ensures that user input is treated as data, not as executable code.
Input Validation and Sanitization: Validate all user input to ensure it conforms to expected formats and lengths. Sanitize input by escaping special characters or removing potentially harmful code. However, do not rely solely on sanitization; parameterization is still necessary.
Use QUOTENAME() (SQL Server), dbms_assert.enquote_name() (Oracle), %I (PostgreSQL), REPLACE(..., '’, ’“’)` (MySQL) for Identifiers: When constructing SQL statements dynamically, use the appropriate function to properly quote identifiers (table names, column names, etc.). This prevents attackers from injecting malicious code through identifiers.
Least Privilege Principle: Execute dynamic SQL with the minimum necessary privileges. Avoid using overly permissive accounts.
Code Reviews: Have dynamic SQL code reviewed by experienced developers to identify potential vulnerabilities and performance issues.
Logging and Auditing: Log all dynamic SQL statements that are executed, along with the user who executed them. This helps with debugging and security auditing.
Prepared Statements (where applicable): If the dynamic SQL is executed repeatedly with different parameters, consider using prepared statements to improve performance.
Avoid String Concatenation (where possible): Minimize string concatenation to build SQL statements. It is more prone to errors and SQL injection vulnerabilities. Prefer using parameterized queries and stored procedures.
Test Thoroughly: Test dynamic SQL code extensively with different inputs, including potentially malicious inputs, to identify vulnerabilities.

5. Examples

Example 1: Dynamic Filtering (SQL Server)

DECLARE @TableName SYSNAME = 'Orders';
DECLARE @FilterColumn SYSNAME = 'OrderDate';
DECLARE @FilterValue DATETIME = '2023-01-15';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@FilterColumn) + N' = @FilterValue';

EXEC sp_executesql @SQL,
                   N'@FilterValue DATETIME',
                   @FilterValue = @FilterValue;

Example 2: Dynamic Table Creation (PostgreSQL)

DO $$
DECLARE
  table_name TEXT := 'temp_table';
  sql_stmt TEXT;
BEGIN
  sql_stmt := format('CREATE TABLE %I (id SERIAL PRIMARY KEY, data TEXT)', table_name);
  EXECUTE sql_stmt;
  RAISE NOTICE 'Table % created', table_name;
END $$;

Example 3: Dynamic Column Selection (Oracle)

DECLARE
  v_table_name VARCHAR2(30) := 'employees';
  v_columns    VARCHAR2(200) := 'employee_id, first_name, last_name'; -- User-selected columns
  v_sql        VARCHAR2(400);
  TYPE cur_type IS REF CURSOR;
  v_cursor cur_type;
  v_emp_id NUMBER;
  v_first_name VARCHAR2(50);
  v_last_name VARCHAR2(50);
BEGIN
  v_sql := 'SELECT ' || v_columns || ' FROM ' || dbms_assert.enquote_name(v_table_name);
  OPEN v_cursor FOR v_sql;

  LOOP
    FETCH v_cursor INTO v_emp_id, v_first_name, v_last_name;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_first_name || ' ' || v_last_name);
  END LOOP;

  CLOSE v_cursor;
END;
/

Example 4: Dynamic Ordering (MySQL)

DELIMITER //
CREATE PROCEDURE dynamic_order(IN table_name VARCHAR(255), IN order_column VARCHAR(255), IN order_direction VARCHAR(10))
BEGIN
  SET @sql = CONCAT('SELECT * FROM `', REPLACE(table_name, '`', '``'), '` ORDER BY `', REPLACE(order_column, '`', '``'), '` ', order_direction);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

CALL dynamic_order('customers', 'LastName', 'ASC');

6. Common Pitfalls

Forgetting Parameterization: The most common and dangerous pitfall is forgetting to use parameterized queries. This opens the door to SQL injection attacks.
Incorrect Identifier Quoting: Failing to properly quote identifiers (table names, column names) can lead to syntax errors or, worse, SQL injection.
Insufficient Input Validation: Not validating user input allows attackers to inject malicious code.
Overly Complex Dynamic SQL: Creating overly complex dynamic SQL statements can make the code difficult to understand, maintain, and debug.
Performance Problems: Dynamic SQL can sometimes lead to performance problems if the database cannot optimize the queries effectively.
Ignoring Data Types: Not considering data types when constructing SQL statements can lead to errors or unexpected results. For instance, concatenating strings with numeric values.
Escaping Issues: Improperly escaping special characters in string literals can lead to syntax errors or SQL injection. Use parameterized queries instead of relying on escaping for data.
Not Handling Errors: Failing to handle errors properly can lead to unexpected application behavior.

Troubleshooting Tips:

Print the Generated SQL: Before executing the dynamic SQL, print the generated SQL statement to the console or log file. This allows you to examine the SQL and identify any errors or vulnerabilities.
Use a SQL Profiler: Use a SQL profiler to monitor the SQL statements that are executed by the application. This helps you identify performance bottlenecks and potential security issues.
Test with Malicious Input: Test the application with potentially malicious input to identify vulnerabilities.

7. Database Variations

Feature	SQL Server	Oracle	PostgreSQL	MySQL
Dynamic SQL Execution	`sp_executesql`, `EXECUTE`	`EXECUTE IMMEDIATE`	`EXECUTE`	`PREPARE`, `EXECUTE`
Identifier Quoting	`QUOTENAME()`	`dbms_assert.enquote_name()`	`%I` (format)	(backticks), `REPLACE(..., '`', '’)`
Parameterization	`@variable`	`:variable`	`$1, $2, ...`	`@variable`
String Concatenation	`+`	`		`
Security Considerations	Always use `sp_executesql` with parameters	Use bind variables and `dbms_assert.enquote_name()`	Use `format()` with `%I` and `%L` and parameterized queries	Use prepared statements and escape backticks

Important Notes:

SQL Server: sp_executesql is the preferred method for executing dynamic SQL in SQL Server because it allows for parameterization and helps prevent SQL injection.
Oracle: dbms_assert.enquote_name() is crucial for safely quoting identifiers in Oracle.
PostgreSQL: format() function with %I (for identifiers) and %L (for literals) is recommended for building dynamic SQL. Use USING clause for parameterization.
MySQL: Use prepared statements and REPLACE(..., '’, ’“’)` to escape backticks in identifiers.

This cheatsheet provides a comprehensive guide to dynamic SQL and SQL injection prevention. Remember to prioritize security by using parameterized queries and following the best practices outlined above. Thorough testing and code reviews are essential to ensure the robustness and security of your dynamic SQL code.