Yitzhak Andrade Yitzhak Andrade - 30 days ago 7
SQL Question

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

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 Source

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