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 learn.microsoft.com/en-us/sql/relational-databases/… — Keith
– Keith, Commented Aug 23, 2017 at 14:11
@Leonidas199x - sp_executesql works with parameters, and bulk insert doesn't support parameters in 'FROM' clause — Zack Stone
– Zack Stone, Commented Aug 23, 2017 at 14:13

Dan Guzman · Accepted Answer · 2017-08-23 14:03:18Z

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, 2017 at 14:03

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S3S · Accepted Answer · 2017-08-23 13:49:11Z

1

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.

answered Aug 23, 2017 at 13:49

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related