All 7 Dynamic SQL questions
More in Dynamic SQL
What is dynamic SQL, and why would you need to build and execute a SQL statement as a string rather than writing it directly? What is SQL injection, and how does it relate to writing dynamic SQL unsafely? How would you build a dynamic SQL query where the table or column names themselves need to change based on input, since parameters cannot be used for object names? How do you debug a dynamic SQL statement when it fails or does not produce the expected results? How would you build a dynamic SQL statement inside a stored procedure that constructs a flexible search query based on several optional filter parameters? What are the performance tradeoffs of using dynamic SQL compared to static, precompiled SQL statements?
Advertisement
Back to results for “Security”
SQL Server › Dynamic SQL

How do you safely use sp_executesql to execute dynamic SQL with parameters, avoiding the risks of simple string concatenation?

Intermediate

Answer

sp_executesql lets you define a parameterized dynamic SQL statement and pass in parameter values separately, similar to how a regular stored procedure works, which both protects against SQL injection and allows SQL Server to reuse cached execution plans for better performance compared to plain string concatenation.
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Orders WHERE CustomerId = @CustId';
EXEC sp_executesql @SQL, N'@CustId INT', @CustId = 123;
Real-world example A reporting application safely filters orders by a customer id provided by the user, using sp_executesql with a proper parameter instead of concatenating the value directly into the SQL string, protecting against injection attacks.

Common follow-ups: Why does using parameters also improve performance through plan reuse?;What data types can be used as parameters with sp_executesql?

Query Optimization & Plans;SQL Server Security & Permissions
All 7 Dynamic SQL questions