Stubborn Ampersand with Invoke-Webrequest SQL & Powershell

Question

When I run powershell using invoke-webrequest on a URL without an ampersand everything works.

But my URL's have ampersands in them. If I surround them by double quotes it works from PowerShell, but not if I am doing it through my SQL Server.

Trying to get the right combination of escape characters is proving to be a pain in the butt. Here's an example of the command:

exec xp_cmdshell 'powershell Invoke-WebRequest -UseBasicParsing -Uri "https://example.com/getfile/12345&i=123" -outfile C:\Downloads\test.txt'

It is the ampersand on the &i=123 that is the issue.

If I change the ampersand in the -URI parameter to "&" it does not work. If I prefix with the gravy carat (little ` above tab) it doesn't work. I have also tried to replace it with %26.

Racking my brain here for hours. Any suggestions?

Answer 1

Add embedded "..."-quoting to the URL, which requires escaping as \"...\":

exec xp_cmdshell 'powershell Invoke-WebRequest -UseBasicParsing -Uri "\"https://example.com/getfile/12345&i=123\"" -outfile C:\Downloads\test.txt'

This is necessary, because PowerShell's CLI (powershell.exe for Windows PowerShell, pwsh for PowerShell [Core] v6+), when used with the (implied) -Command (-c) option^[1]:

• first removes "..." quoting around individual command-line arguments...
• and then joins the stripped arguments with spaces and then interprets the resulting string as a PowerShell command line - and at that point quoting around the URL is required, because & is a PowerShell metacharacter.

Note:

• Using \" to escape embedded " chars. inside a "..." string in a -Command (-c) CLI argument works robustly except when calling from cmd.exe, where the presence of cmd.exe metacharacters such as & (common in URLs, such as in this case) can break the call.

• In that event, use "^"" (sic) for powershell.exe and "" for pwsh.exe - see this answer for more information.

---

^{[1] Note that pwsh now defaults to -File, which expects a script file.}