0

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?

Improve this question
5
  • 2
    Try 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). Note PrintMe='abc==' after quote removal is PrintMe=abc==. I would rather suspect some quirk (bug?) in parsing this by sqlcmd. 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.
    – Kamil Maciorowski
    Commented May 13 at 2:05
  • 1
    This likely has nothing to do with the shell but rather with how the database engine treats values containing equal signs. Try using ^ 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.
    – Kusalananda ♦
    Commented May 13 at 3:45
  • 2
    Alternatively to printf, enable set -x and run the program normally. The shell will print what it actually executed.
    – ilkkachu
    Commented May 13 at 6:59
  • that zsh = expansion is a fine guess, but it should only make a difference if the equals sign is at the start of a word. Like echo =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.
    – ilkkachu
    Commented May 13 at 7:22
  • To address the various comments: @KamilMaciorowski @ikkachu This is the output from 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 a sqlcmd 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.
    – Mark
    Commented May 13 at 13:55

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .