How to prevent SQL injection in dynamic sql for bulk insert?

Question

I'm using dynamic SQL for bulk insert with a parameter (Bulk insert using stored procedure).

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT TblValues FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )';
EXEC(@sql);

But... How to avoid SQL injection?

Take a look at sp_executesql https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql — Keith, Aug 23 '17 at 14:11
@Leonidas199x - sp_executesql works with parameters, and bulk insert doesn't support parameters in 'FROM' clause — Zack Stone, Aug 23 '17 at 14:13

score 1 · Answer 1 · answered Aug 23 '17 at 13:49

One way would be to retrieve the file name versus pass it in... something like

DECLARE @fileLocation VARCHAR(128) = '\\some\folder\location'

IF OBJECT_ID('tempdb..#FileNames') IS NOT NULL DROP TABLE #FileNames
CREATE TABLE #FileNames(
    id int IDENTITY(1,1)
    ,subdirectory nvarchar(512)
    ,depth int
    ,isfile bit)
INSERT #FileNames(subdirectory,depth,isfile)
EXEC xp_dirtree @fileLocation, 1, 1

Then, in #FileNames will be all the files in that directory (where isfile = 1 of course). Then you can simply query the file name(s) from the temp table.

score 1 · Accepted Answer · answered Aug 23 '17 at 14:03

1

You could use QUOTENAME to surround the file name in single quotes:

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT TblValues FROM ' + QUOTENAME(@FileName,'''') + ' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )';
EXEC (@sql);

answered Aug 23 '17 at 14:03

Dan Guzman

43,250
3
46
71

How to prevent SQL injection in dynamic sql for bulk insert?

2 Answers2