Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
949 views
in Technique[技术] by (71.8m points)

sql - Escaping command parameters passed to xp_cmdshell to dtexec

I am calling an SSIS package remotely using a stored procedure and a call to xp_cmdshell:

declare @cmd varchar(5000)
set @cmd = '"C:Program Files (x86)Microsoft SQL Server100DTSBinndtexec.exe" /Rep E /Sql Package /SET Package.Variables[User::ImportFileName].Value;c:foo.xlsx'
print @cmd
exec xp_cmdshell @cmd

This works fine, however I can not guarantee the variable value (c:foo.xslx) is not going to contain spaces so I would like to escape that with quotes like below:

set @cmd = '"C:Program Files (x86)Microsoft SQL Server100DTSBinndtexec.exe" /Rep E /Sql Package /SET Package.Variables[User::ImportFileName].Value;"c:foo.xlsx"'

But by doing this I get the error

'C:Program' is not recognized as an internal or external command, operable program or batch file.

Both of the above commands work fine if executed within cmd.exe so I am guessing that SQL Server is interpreting my double quotes and changing something, but I can't figure out what.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

In a nutshell, put CMD /S /C " at the beginning, and " at the end. In between you can have as many quotes as you like.

Here is how you do it:

declare @cmd varchar(8000)
-- Note you can use CMD builtins and output redirection etc with this technique, 
-- as we are going to pass the whole thing to CMD to execute
set @cmd = 'echo "Test" > "c:my log directorylogfile.txt" 2> "c:my other directoryerr.log" '


declare @retVal int
declare @output table(
    ix int identity primary key,
    txt varchar(max)
)


-- Magic goes here:
set @cmd = 'CMD /S /C " ' + @cmd + ' " '

insert into @output(txt)
exec @retVal = xp_cmdshell @cmd
insert @output(txt) select '(Exit Code: ' + cast(@retVal as varchar(10))+')'

select * from @output

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...