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 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.
- T-SQL Tuesday: Dynamic SQL, The Data Type
- Stored Procedures vs sp_executesql In SQL Server: Is One Better Than The Other?
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does PSP Work With Dynamic SQL?
- SQL Server 2022 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity