Passing path scripting variable to sqlcmd when called using Invoke-Expression

Question

I'm trying to create a powershell script that creates a database. The relevant part of the createDb.ps1 is:

param([string] $server,
      [string] $dbName)
$scriptpath = "C:\script\path" 
$cDb = "master"
$line = "script.sql"
$outfile = "\log.txt"
$dbDir = "C:\database path\"
$command = @"
sqlcmd -b -S $server -d $cDb -i '$scriptpath\$line' -o '.$outfile' -v dbLocation='$dbDir' dbName=$dbName
"@

Invoke-Expression $command

I call the script with the following parameters:

createDb.ps1 -server localhost -dbName TestDb

However when I run this I get the following error:

sqlcmd: 'dbDir=C:\database path\" dbName=TestDb': Invalid argument. Enter '-?' for help.

When I execute the following from the command line, everything works as expected:

sqlcmd -b -S localhost -d master -i "C:\script\path\script.sql" -o ".\log.txt" -v dbLocation="C:\database path\" dbName=TestDb

Answer 1

Do not build your command line as a string first and then pass it to Invoke-Expression - not only is it unnecessary, it causes problems with argument partitioning.

Invoke the command directly:

sqlcmd -b -S $server -d $cDb -i $scriptpath\$line -o ".$outfile" -v dbLocation=`"$dbDir`" `
  dbName=$dbName

Note how ".$outfile" and dbLocation=`"$dbDir`" require special treatment:

• Due to a PowerShell parsing quirk (as of PSv5.1), .$ at the start of an unquoted token causes it to be broken into 2 arguments. Enclosing .$outfile in "..." prevents that problem.

• After PowerShell has parsed your command, it essentially rebuilds the command line with selective double-quoting of arguments before passing it to the system for execution. While this usually works as desired, there are edge cases, such as dbLocation=$dbDir:

  • Since the value of $dbDir - C:\database path\ - contains spaces, PowerShell would enclose the expanded result of dbLocation=$dbDir in "..." to ensure that it is recognized as a single argument, which yields "dbLocation=C:\database path\" - and sqlcmd may balk at that.

  • By explicitly embedding double quotes in the token - using `", which are escaped " chars. - PowerShell leaves the expanded result of dbLocation=`"$dbDir`" alone, which yields: dbLocation="C:\database path\"

  • Caveat:

    • Most target programs interpret \" not as having syntactic function (in this case: not as a closing " that happens to be preceded by a \), but as an escaped, embedded " resulting in broken argument parsing - depending on the target program, you may have to escape that final \ - or possibly all \ instances - as \\.

    • It is important to understand that on Windows it is ultimately always up to the target program to interpret the command line; see this answer for more information.

Generally:

• Variable references do not need double-quoting when they serve as arguments passed to an external utility, even when the referenced variables' values contain spaces (or other metacharacters).

  • That said, routinely double-quoting tokens that combine variable references with literals / other variable references is a good habit to form (e.g, "$scriptpath\$line" and ".$outfile"), because the precise rules when such tokens would otherwise be considered multiple arguments are not easy to remember - see the link at the bottom.

• However, you need to be aware of unquoted instances of PowerShell's own metacharacters - such as , in general, and @ at the start of a token - and `-escape them to use them as literals (not an issue in the case at hand).

• The quoting style matters only to PowerShell - once PowerShell has performed its own parsing and possibly expanded variable references, the then-literal tokens are reassembled into a command line that uses double quotes as needed behind the scenes to preserve argument boundaries, even if the original command line comprised single-quoted tokens.

  • For instance, PowerShell command line foo.exe 'bar baz' $env:ProgramFiles $env:OS would translate to foo.exe "bar baz" "C:\Program Files" Windows_NT on invocation of foo.exe.
    Note how double-quoting is employed on demand for the values with embedded spaces, irrespective of what quoting was used in the original command line.

For a comprehensive discussion of how PowerShell parses arguments, see this answer of mine.

Answer 2

Use double quotes instead to surround your attribute values when running sqlcmd:

$command = @" 
    sqlcmd -b -S $server -d $cDb -i "$scriptpath\$line" -o ".$outfile" -v dbLocation="$dbDir" dbName=$dbName 
"@