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
Back to results for “Security”
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