Yitzhak Andrade Yitzhak Andrade - 6 months ago 25
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'' )';

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download