We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a parameter. Is there any way to send that parameter value all the way in? When running this, the error message is: Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@RId".
Query:
DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';
DECLARE @SQL nvarchar(500);
SET @SQL
= N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = @RId" queryout @Path -t, -T -N -S SERVERNAME\INSTANCENAME''';
EXEC sp_executesql @SQL,
N'@RId nVARCHAR(500), @Path VARCHAR(500)',
@REPORTID,
@Path;
1 Answer 1
I think you'll have to externalize those parameters when generating the dynamic SQL. Something like:
DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';
DECLARE @SQL nvarchar(500);
SET @SQL
= N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''''' + @REPORTID + '" queryout ' + @Path + ' -t, -T -N -S SERVERNAME\INSTANCENAME''';
PRINT @SQL
--EXEC sp_executesql @SQL
The PRINT
statement reveals the generated dynamic SQL
master.dbo.xp_cmdshell 'BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''SomeID_Resend2019-02-20 11:38:24.040" queryout \\server\folder\_FileName.csv -t, -T -N -S SERVERNAME\INSTANCENAME'
-
But won't that negate the purpose of parameterization in sp_executesql? We wanted to use that to protect against injection. With your example I think it will be vulnerable to sql injection?GHauan– GHauan2019年02月20日 12:52:29 +00:00Commented Feb 20, 2019 at 12:52
-
@GHauan - I think you'll have to validate your input parameters (outside of the sp_executesql) before building the dynamic SQL to execute the BCP command. I'm not sure how susceptible BCP would be to SQL injection (especially the Queryout), but you could certainly code up a before sp_executesql with parameters to select for a valid reportid.Scott Hodgin - Retired– Scott Hodgin - Retired2019年02月20日 13:16:39 +00:00Commented Feb 20, 2019 at 13:16