How to Create and Execute Dynamic SQL Queries (and Avoid SQL Injection)

Dynamic SQL can make your database interactions much more flexible. Unlike static SQL, which relies on hard-coded queries, Dynamic SQL is generated and executed at runtime. This allows you to reuse the same query template with different parameter values, making your code more efficient and adaptable.

Let’s explore how to write Dynamic SQL queries, methods to execute them, and precautions to avoid risks like SQL injection.

What Is Dynamic SQL?

Dynamic SQL is a technique where the SQL query is built and executed on the fly. Instead of hardcoding queries in your database interactions, you can pass parameters dynamically at runtime. This has several benefits:

• Reusability: Use the same query with different parameter values.
• Integration: Accept values from external applications or systems.
• Flexibility: Modify query components easily as needed.

For example, suppose you want to query an employees table:

SELECT * FROM dbo.Employees WHERE FullName LIKE 'A%'

With Dynamic SQL, instead of hardcoding 'A%', you can pass dynamic inputs like 'O%' or 'K%', making the query adaptable without rewriting it.

Setting Up a Dynamic SQL Query

Here’s a step-by-step process for writing Dynamic SQL.

Step 1: Define Parameters

First, define a parameter to store the value you want to pass dynamically. For instance:

DECLARE @Filter NVARCHAR(30) = 'A'

Here, @Filter stores the desired filter value ('A' in this example).

Step 2: Construct the Dynamic SQL Query

Concatenate query pieces to create the final SQL statement. For instance:

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT * FROM dbo.Employees WHERE FullName LIKE ''' + @Filter + '%'''

To include single quotes inside the string, escape them by doubling them up ('').

Step 3: Execute the Query

You can execute the query using the EXEC statement:

EXEC (@SQL)

Alternatively, use the sp_executesql stored procedure, which allows parameterized queries:

EXEC sp_executesql 
    @SQL, 
    N'@Filter NVARCHAR(30)', 
    @Filter = @Filter

This approach provides better parameter handling and helps prevent SQL injection attacks.

Benefits of Using sp_executesql

Using sp_executesql instead of EXEC has some added advantages:

• Parameter Handling: Simplifies working with variables directly in the query.
• Query Optimization: Caches query execution plans for reuse, improving performance for repeat queries.
• Output Parameters: Supports fetching query results into variables.

For instance, you can collect a query result into a variable:

DECLARE @Name NVARCHAR(50)

SET @SQL = 'SELECT TOP 1 @Name = FullName FROM dbo.Employees WHERE FullName LIKE @Filter'
EXEC sp_executesql 
    @SQL, 
    N'@Filter NVARCHAR(30), @Name NVARCHAR(50) OUTPUT', 
    @Filter = 'A%', 
    @Name = @Name OUTPUT

SELECT @Name AS FullName

This fetches the result into @Name for further processing, making your query even more dynamic.

Handling Risks: Avoiding SQL Injection

Dynamic SQL can introduce vulnerabilities like SQL injection, where attackers manipulate your query by injecting malicious code. For example:

DECLARE @Filter NVARCHAR(50) = 'O% DROP TABLE Employees'

SET @SQL = N'SELECT * FROM dbo.Employees WHERE FullName LIKE ''' + @Filter + ''''
EXEC(@SQL)

The above query will execute both the intended query and the injected DROP TABLE statement, leading to data loss.

Best Practices to Prevent SQL Injection

1. Use Parameterized Queries: Always prefer sp_executesql with properly defined parameters.
2. Validate Input: Ensure user inputs don’t include harmful keywords like DROP, DELETE, or ALTER.
3. Escape Characters: Carefully escape dangerous characters like single quotes (').
4. Limit Permissions: Restrict database user permissions to prevent unauthorized changes.
5. Use Stored Procedures: Encapsulate Dynamic SQL within stored procedures to control query construction.

A Simpler Approach to Quotes in sp_executesql

If dealing with multiple quotes feels cumbersome, sp_executesql simplifies the process by allowing direct parameter usage. For instance:

DECLARE @Filter NVARCHAR(30) = 'A%'

DECLARE @SQL NVARCHAR(MAX) = 
    N'SELECT * FROM dbo.Employees WHERE FullName LIKE @Filter'

EXEC sp_executesql 
    @SQL, 
    N'@Filter NVARCHAR(30)', 
    @Filter = @Filter

Here, you avoid concatenation and directly reference the parameter. This keeps your code clean and easier to maintain.

The Importance of Caution with Dynamic SQL

Dynamic SQL can save time and improve flexibility, but risks like SQL injection require careful handling. By following best practices, you can enjoy the benefits of Dynamic SQL without compromising database security.

Use tools like parameterized queries, stored procedures, and careful input validation to stay safe. Dynamic SQL is powerful, but as with any tool, it requires responsibility to avoid potential pitfalls.

Final Thoughts

Dynamic SQL opens possibilities for writing reusable and flexible queries. By dynamically providing parameters, you can reduce redundancy and handle multiple use cases with minimal changes. However, caution is key—SQL injection is a real risk with poorly written queries.

Stick to best practices like using sp_executesql and sanitizing inputs. By doing so, you’ll harness the power of Dynamic SQL while keeping the integrity of your database intact.

Have any tips or questions about Dynamic SQL? Share them in the comments! And don’t forget to subscribe for more SQL tutorials and tips.

Learn More

For any questions, reach out to learn@knowstar.org.

Consider expanding your expertise with these certifications:

• Google Data Analytics Professional Certificate: https://imp.i384100.net/OR37oQ
• Google Advanced Data Analytics Professional Certificate: https://imp.i384100.net/eK1WmQ

• Best SQL and Data Analytics Books

• T-SQL Fundamentals (By Itzik Ben-Gan) - https://amzn.to/4koKGdC
• Ace the Data Science Interview - https://amzn.to/3D2ne5n
•