Passing Json to Python from PowerShell Script

tl;dr

Note: The following does not solve the OP's specific problem (the cause of which is still unknown), but hopefully contains information of general interest.

# Use "" to escape " and - in case of delayed expansion - ^! to escape !
$param = '{ ""job_start"": ""jdbc:mysql://127.0.0.1:3307/test&serverTimezone=UTC&more^!"" }'

• There are high-profile utilities (CLIs) such as az (Azure) that are Python-based, but on Windows use an auxiliary batch file as the executable that simply relays arguments to a Python script.
  • Use Get-Command az, for instance, to discover an executable's full file name; batch files, which are processed by cmd.exe, the legacy command processor, have a filename extension of either .cmd or .bat
• To prevent calls to such a batch file from breaking, double quotes embedded in arguments passed from PowerShell must be escaped as ""
• Additionally, but only if setlocal enabledelayedexpansion is in effect in a given target batch file or if your computer is configured to use delayed expansion by default, for all batch files:
  • ! characters must be escaped as ^!, which, however, is only effective if cmd.exe considers the ! part of a double-quoted string.

It looks like we have a confluence of two problems:

• A PowerShell problem with " chars. embedded in arguments passed to external programs:

  • In an ideal world, passing JSON text such as '{ "foo": "bar" }' to an external program would work as-is, but due to PowerShell's broken handling of embedded double quotes, that is not enough, and the " chars. must additionally be escaped, for the target program, either as \" (which most programs support), or, in the case of cmd.exe (see below), as "", which Python fortunately recognizes too: '{ ""foo"": ""bar"" }'

• Limitations of argument-passing and escaping in cmd.exe batch files:

  • It sounds like spark-submit is an auxiliary batch file (.cmd or .bat) that passes the arguments through to a Python script.

  • The problem is that if you use \" for escaping embedded ", cmd.exe doesn't recognize them as escaped, which causes it to consider the & characters unquoted, and they are therefore interpreted as shell metacharacters, i.e. as characters with special syntactic function (command sequencing, in this case).

  • Additionally, and only if setlocal enabledelayedexpansion is in effect in a given batch file, any literal ! characters in arguments require additional handling:

    • If cmd.exe thinks the ! is part of an unquoted argument, you cannot escape ! at all.

    • Inside a quoted argument (which invariably means "..." in cmd.exe), you must escape a literal ! as ^!.

      • Note that this requirement is the inverse of how all other metacharacters must be escaped (which require ^ when unquoted, but not inside "...").

      • The unfortunate consequence is that you need to know the implementation details of the target batch file - whether it uses setlocal enabledelayedexpansion or not - in order to formulate your arguments properly.

      • The same applies if your computer is configured to use delayed expansion by default, for all batch files (and interactively), which is neither common nor advisable. To test if a given computer is configured that way, check the output from the following command for DelayedExpansion : 1: if there's no output at all, delayed expansion is OFF; if there's 1 or 2 outputs, delayed expansion is ON by default if the first or only output reports DelayedExpansion : 1.

 Get-ItemProperty -EA Ignore 'registry::HKEY_CURRENT_USER\Software\Microsoft\Command Processor', 'registry::HKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor' DelayedExpansion

Workaround:

• Since you're technically calling a batch file, use "" to escape literal " chars. inside your single-quoted ('...') PowerShell string.

• If you know that the target batch file uses setlocal enabledelayedexpansion or if your computer is configured to use delayed expansion by default, escape ! characters as ^!

  • Note that this is only effective if cmd.exe considers the ! part of a double-quoted string.

Therefore (note that I've extended the URL to include a token with !, meant to be passed through literally as suffix more!):

$param = '{ ""job_start"": ""jdbc:mysql://127.0.0.1:3307/test&serverTimezone=UTC&more^!"" }'

If you need to escape an existing JSON string programmatically:

# Unescaped JSON string, which in an ideal world you'd be able
# to pass as-is.
$param = '{ "job_start": "jdbc:mysql://127.0.0.1:3307/test&serverTimezone=UTC&more!" }'

# Escape the " chars.
$param = $param -replace '"', '""'

# If needed, also escape the ! chars.
$param = $param -replace '!', '^!'

Ultimately, both problems should be fixed at the source - but that this is highly unlikely, because it would break backward compatibility.

With respect to PowerShell, this GitHub issue contains the backstory, technical details, a robust wrapper function to hide the problems, and discussions about how to fix the problem at least on an opt-in basis.

In this question Which characters need to be escaped when using Bash? , you will find all the characters that you should escape when passing them as normal characters in the shell, you will also notice that & is one of them.

Now I understand that if you tried to escape it, the JSON parser you are using will probably fail to parse the string. So one quick workaround would be to replace the & by any other special non-escapable symbol like @ or %, and do a step in your app where you replace it with & before parsing. Just make sure that the symbol you will use isn't used in your strings, and won't be used at any time.