Cannot manipulate parsed string from another batch file because of space and quote breaking it

Question

I have a batch file which is calling another batch file. batch file 1 is parsing 5 parameters to batch file 2

When I parse the parameters from batch file 1 to batch file 2, it parses correctly but when I assigned those parsed parameters to use them in batch file 2, it breaks.

batch file 1:

ECHO
SET sql=SELECT MAX("Date") FROM SQ_TEST."Sample - Superstore Ran";
SET pref=W
SET num=0
SET day=Friday
SET config=SampleSuperStore.txt
CALL Z:\XXX\RunTableauRefreshAutomatic.bat %sql% %pref% %num% %day% %config%

batch file 2:

CALL C:\XXX\anaconda3\Scripts\activate.bat 
SET sql=%~1
SET pref=%~2
SET num=%~3
SET day=%~4
SET config=%~5
C:\XXX\anaconda3\python.exe Z:\XXX\pMainAutomaticDB.py %sql% %pref% %num% %day% %config%
PAUSE

Response from batch file 2:

Z:\XXX>CALL C:\XXX\anaconda3\Scripts\activate.bat

(base) Z:\XXX>SET sql=SELECT

(base) Z:\XXX>SET pref=MAX("Date")

(base) Z:\XXX>SET num=FROM

(base) Z:\XXX>SET day=SQ_TEST."Sample - Superstore Ran"

(base) Z:\XXX>SET config=W

(base) Z:\XXX>C:\XXX\anaconda3\python.exe Z:\XXX\pMainAutomaticDB.py SELECT MAX("Date") FROM DL_SQ_TEST."Sample - Superstore Ran" W

(base) Z:\XXX>PAUSE Press any key to continue . . .

Update: When I remove the double quotes in sql, it works as expected but I need them in it. Additionally I tried using ^ but the batch file 2 still breaks it differently

Answer 1

@echo off
setlocal
SET "sql=SELECT MAX("Date") FROM SQ_TEST.""Sample - Superstore Ran"";"
call :otherbatch "%sql%" two three
goto :eof

:otherbatch
@echo off
set "var=%~1"
set "var=%var:""="%"
echo one :%var%
echo two :%~2
echo thre:%~3
goto :eof

(it works the same whether calling a separate batch file or a subroutine, so I worked with the latter to show the principle.)

The trick is to ensure the parts with spaces (or other poisonous characters) are properly quoted.
Sadly, this involves some thinking, counting and trying. I see no safe way to automate this (besides a lot of code).

For this special string

SELECT MAX("Date") FROM SQ_TEST."Sample - Superstore Ran";

the string "%sql%" becomes:

"SELECT MAX("Date") FROM SQ_TEST."Sample - Superstore Ran";"
SiiiiiiiiiiiEooooSiiiiiiiiiiiiiiiEoooooooooooooooooooooooSiE

The line below shows, whether a part of the string is inside or outside of proper quoting with Start and End of quoting.
The next step is to ensure each SPACE (or any other delimiter) is quoted (inside) by adding quotes where needed (not around the spaces themselves, but around the substrings that contains them, using the positions of already present quotes).

This changes the string to :

"SELECT MAX("Date") FROM SQ_TEST.""Sample - Superstore Ran"";"
SiiiiiiiiiiiEooooSiiiiiiiiiiiiiiiESiiiiiiiiiiiiiiiiiiiiiiiESoE

Now every space is properly quoted (and you can see, it's difficult as other "unrelated" parts may change their inside/outside status). So it can be passed as a single parameter.

Of course that means, at the receiving side there are some superfluent quotes, which have to be deleted. We can easily (at least for this example) do that by replacing each "" with ":

 set "var=%~1"
 set "var=%var:""=%"

Note: that's very specific to the string to process, so I tried to explain step by step, what's to be done. You'll have to rethink the whole process for each specific string and I'm sure there are combinations where this approach would be useless.

Answer 2

You have potentially more problems than just spaces!

Poison characters like &, |, >, and < are another problem when passing parameters to a CALLed script. If the desired value is &"&" then it is impossible to pass that value as a literal string without escaping as the unquoted ^&"&" or as a quoted "&"^&"". But escaping dynamic strings is difficult / totally impractical. And sometimes a value must pass through multiple CALLs, each one needing its own round of escaping.

The situation is even worse when passing a caret - It is impossible to pass ^"^". You can double the unquoted ^, but the quoted "^" is a problem because the CALL command doubles quoted ^, and there is absolutely nothing you can do to prevent it. for example CALL ECHO ^^"^" yields ^"^^"!

For anyone doing any advanced scripting, one of the first tricks to learn is to pass values by reference instead of as literals. The calling script stores the value in a variable, and passes the name of the variable to the CALLed script. The CALLed script then uses delayed expansion to access the value. All the nasty batch problems are solved very simply :-)

modified batch file 1:

ECHO
SET sql=SELECT MAX("Date") FROM SQ_TEST."Sample - Superstore Ran";
SET pref=W
SET num=0
SET day=Friday
SET config=SampleSuperStore.txt
CALL Z:\XXX\RunTableauRefreshAutomatic.bat sql %pref% %num% %day% %config%

modified batch file 2:

setlocal enableDelayedExpansion
CALL C:\XXX\anaconda3\Scripts\activate.bat 
SET sql=!%~1!
SET pref=%~2
SET num=%~3
SET day=%~4
SET config=%~5

:: I doubt this next line works properly.
C:\XXX\anaconda3\python.exe Z:\XXX\pMainAutomaticDB.py !sql! %pref% %num% %day% %config%

:: You probably need to change your python script to read the sql value from an environment
:: variable so you can then pass the value by reference just as we did with batch.
::
:: C:\XXX\anaconda3\python.exe Z:\XXX\pMainAutomaticDB.py sql %pref% %num% %day% %config%
::
:: Otherwise you will need to escape your quote literals - I believe python uses `\"`

PAUSE

One additional thing to be wary of - String literals containing ! will be corrupted if accessed while delayed expansion is enabled. So in addition to all the "problem" cases listed above, you also should pass by reference if the value may contain !.

Note that your python script also has potential issues with parameters containing " literals. I discuss that in the comments in modified batch 2.