I was writing a shell script for inserting a string value through a Microsoft sqlcmd
variable into a Microsoft SQL server database table and noticed some unexpected behavior. It appeared the trailing equals (=
) character and everything after were being truncated (the target column had no character limit). In attempt to narrow down the source of the behavior, I wrote a simple Microsoft SQL script (test.sql
) that prints a sqlcmd
variable:
PRINT '$(PrintMe)'
And then tested it with the following command:
sqlcmd -S localhost -U sa -P password -v PrintMe='abc==' -i test.sql
To my surprise, the output was abc
instead of the expected abc==
.
At first, I thought this was sqlcmd
doing something strange with my variable value, but altering the script:
:setvar PrintMe "abc=="
PRINT '$(PrintMe)'
printed abc==
as expected.
Now it made me wonder if zsh
or bash
were misinterpreting those characters for some reason (e.g. as macros/expansions). Using a double-quoted value made no difference in the output. In fact, I noticed a variable value like abc=123
output just abc
, truncating everything including and after the equals. Trying to escape the =
character using a value like 123\=
output 123\
. After reading about zsh =
expansion, though I doubted it was the cause, I tried running unsetopt EQUALS
but saw no change in the output.
I ran these commands with sqlcmd
1.6.0, zsh 5.9 (x86_64-apple-darwin21.3.0) on macOS Ventura 13.6.6 and again with bash 5.2.26(1)-release (x86_64-apple-darwin22.6.0) with no change in results.
Is there any explanation for what's going on here?
printf '%s\n' -S localhost -U sa -P password -v PrintMe='abc==' -i test.sql
and see if your shell passes the==
as==
(I bet it does). NotePrintMe='abc=='
after quote removal isPrintMe=abc==
. I would rather suspect some quirk (bug?) in parsing this bysqlcmd
. AFAIK it is supposed to support-v var1=v,var2=v2
, so parsing the option-argument is not as trivial as detecting the first=
only.^
to escape the equal signs, or use embedded single quotes to quote the value of the SQL variable. Unfortunately, I don't have access to this particular database engine locally.printf
, enableset -x
and run the program normally. The shell will print what it actually executed.=
expansion is a fine guess, but it should only make a difference if the equals sign is at the start of a word. Likeecho =foo
gives an error.echo foo=bar=
as here should be fine. (the linked Q&A says that too in the quote from the manual.) And, if it hits the expansion in some random case, you'll get a path like/usr/local/bin/foo
, or an error if that command isn't found.printf
/set -x
:sqlcmd -S localhost -U sa -P password -v PrintMe=abc== -i test.sql
. Clearly the quotes are removed but I'm not sure what the right combination of quotes is supposed to be. I've tried several variations. If we think it's asqlcmd
parsing problem, I can try raising an issue in that repo as I already searched and saw nothing about this. @Kusalananda, I suspect this issues lives in the shell /sqlcmd
layer since my example using:setvar
works fine.