Don’t Rely On Square Brackets To Protect You From SQL Injection

Uno Mal


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!



One thought on “Don’t Rely On Square Brackets To Protect You From SQL Injection

Leave a Reply

Your email address will not be published. Required fields are marked *