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?

Answer 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);