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? How do you safely use sp_executesql to execute dynamic SQL with parameters, avoiding the risks of simple string concatenation? 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
SQL Server › Dynamic SQL

What is SQL injection, and how does it relate to writing dynamic SQL unsafely?

Beginner

Answer

SQL injection is a security vulnerability where an attacker inserts malicious SQL code into an input field, which then gets concatenated directly into a dynamic SQL string and executed as part of your query, potentially letting them view, modify, or delete data they should never have access to.
-- Vulnerable: directly concatenating user input
DECLARE @UserInput NVARCHAR(100) = 'Test'' OR ''1''=''1';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Users WHERE Name = ''' + @UserInput + '''';
-- This could return all rows instead of just matching ones
Real-world example A poorly built login form that concatenates a username directly into a dynamic SQL query is exploited by an attacker who enters a specially crafted value, bypassing authentication entirely due to SQL injection.

Common follow-ups: How does using parameterized queries prevent SQL injection?;What other precautions help protect against SQL injection beyond avoiding string concatenation?

SQL Server Security & Permissions;Stored Procedures & Functions
All 7 Dynamic SQL questions