I see a lot of scripts on the internet that use dynamic SQL, but leave people wide open to SQL injection attacks.
In many cases they’re probably harmless, hitting DMVs, object names, etc. But they set a bad example. From there, people will adapt whatever dynamic SQL worked elsewhere to something they’re currently working on.
Here’s a simple script to show you how just sticking brackets into a string doesn’t protect you from SQL injection:
DROP TABLE IF EXISTS #t CREATE TABLE #t(id int); DECLARE @s nvarchar(max) = N'[' + N'PRINT 1] DROP TABLE #t;--' + N']'; PRINT @s EXEC sys.sp_executesql @s; SELECT * FROM #t AS t; GO DROP TABLE IF EXISTS #t CREATE TABLE #t(id int); DECLARE @s nvarchar(max) = QUOTENAME(N'PRINT 1] DROP TABLE #t;--') PRINT @s EXEC sys.sp_executesql @s; SELECT * FROM #t AS t; GO
You can run this anywhere, and the results look like this:
[PRINT 1] DROP TABLE #t;--] Msg 2812, Level 16, State 62, Line 572 Could not find stored procedure 'PRINT 1'. Msg 208, Level 16, State 0, Line 583 Invalid object name '#t'. [PRINT 1]] DROP TABLE #t;--] Msg 2812, Level 16, State 62, Line 587 Could not find stored procedure 'PRINT 1] DROP TABLE #t;--'.
In the section where square brackets were used, the temp table #t got dropped. In the section where QUOTENAME was used, it wasn’t.
When you’re writing dynamic SQL, it’s important to make it as safe as possible. Part of that is avoiding the square bracket trap.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- SQL Server 2022 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity
- SQL Server 2022 Is Going To Mess Up Your Query Monitoring Scripts
- Another Reason Why I Love Dynamic SQL IN SQL Server: OUTPUT Parameters Can Be Input Parameters
- Signs You Need Dynamic SQL To Fix Query Performance Problems In SQL Server