1

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?

Sander
  • 3,942
  • 2
  • 17
  • 22
jimerb
  • 67
  • 9

1 Answers1

0

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.

mklement0
  • 382,024
  • 64
  • 607
  • 775
  • what about if you were to remove the exec xp_cmdshell and execute the command from another shell or from taskschd? `pwsh -c {Invoke-WebRequest -UseBasicParsing -Uri "\"https://example.com/getfile/12345&i=123\"" -outfile C:\downloads\test.txt}` – Shavelieva Feb 08 '22 at 02:08
  • @Shavelieva, `{ ... }` (script blocks) only work from _inside_ PowerShell (and there you don't need escaping). From the outside, one approach is to use `"..."` around the entire `-c` argument and use `""` to escape any embedded ones: `pwsh -c "Invoke-WebRequest -UseBasicParsing -Uri ""https://example.com/getfile/12345&i=123"" -outfile C:\downloads\test.txt"`. While using `\"` for escaping works too, it can break _when calling from `cmd.exe`_ - see [this answer](https://stackoverflow.com/a/49060341/45375). – mklement0 Feb 08 '22 at 13:47