Escaping SQLCMD values that contain complex strings

[Aaronontheweb]

Working on creating an automatic versioning table that uses MSBuild properties injected into SQLCMD variables that are used in a post-deploy script:

<Project Sdk="MSBuild.Sdk.SqlProj/2.6.0">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>Sql150</SqlServerVersion>
        <!-- For additional properties that can be set here, please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <PropertyGroup>
        <!-- Refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#publishing-support for supported publishing options -->
    </PropertyGroup>

    <ItemGroup>
      <Content Include="..\Database\**\*.sql"/>
    </ItemGroup>

    <!-- standard Post-Deployment scripts -->
    <ItemGroup>
        <PostDeploy Include="..\Database\Scripts\PostDeploy\PostDeployment.AddVersionInformation.sql" />
    </ItemGroup>

    <!-- SQLCmd variables -->
    <ItemGroup>
    <SqlCmdVariable Include="SchemaVersionNumber">
      <DefaultValue>0.1.0</DefaultValue>
      <Value>$(VersionPrefix)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="SchemaVersionReleaseNotes">
      <DefaultValue>Release notes</DefaultValue>
      <Value>$(PackageReleaseNotes)</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

IF '[$(SchemaVersionNumber)]' = '[]'
BEGIN
    RAISERROR('The version number is an empty string and cannot be inserted.', 16, 1);
    RETURN; -- Exit the script
END
GO
--PRINT N'Attempting to insert the new version number [' + $(SchemaVersionNumber) + N'] into the SchemaChangeLog table.'

IF NOT EXISTS (
    SELECT 1 
    FROM [dbo].[SchemaChangeLog] 
    WHERE [VersionNumber] = N'$(SchemaVersionNumber)'  -- Notice the N'...' around the variable
)
BEGIN
    -- If it doesn't exist, insert the new row.
    INSERT INTO [dbo].[SchemaChangeLog] 
        ([VersionNumber], [TimeDeployed], [ReleaseNotes])
    VALUES
        (N'$(SchemaVersionNumber)', GETDATE(), N'$(SchemaVersionReleaseNotes)')  -- Notice the () instead of []
END

Problem I'm running into while running dotnet build -c Release:

  Unrecognized command or argument 'notes'.

C:\Users\aaron\.nuget\packages\msbuild.sdk.sqlproj\2.6.0\Sdk\Sdk.targets(242,5): error MSB3073: The command "dotnet "C:\Users\aaron\.nuget\packages\msbuild.sd k.sqlproj\2.6.0\Sdk\../tools/net7.0/DacpacTool.dll" build -o "obj\Release\netstandard2.0\Sdkbin.Sql.Build.dacpac" -n "Sdkbin.Sql.Build" -v "0.1.0" -sv Sql150
-i "obj\Release\netstandard2.0\Sdkbin.Sql.Build.InputFiles.txt"  -sc SchemaVersionNumber=0.1.0 -sc SchemaVersionReleaseNotes=Release notes  -dp IncludeComposi teObjects=true  --postdeploy ..\Sdkbin.Database\Scripts\PostDeploy\PostDeployment.AddVersionInformation.sql       " exited with code 1. [E:\Repositories\sdkbi n-2.0\sdkbin-sql\src\Sdkbin.Sql.Build\Sdkbin.Sql.Build.csproj]

My $(PackageReleaseNotes) is defined in Directory.Build.props and contains markdown syntax:

* Initial release

---
code sample
---

More text that belongs in the release notes

What can I do to escape the value of this MSBuild variable when it's passed into SQLCMD?

[baronfel]

I took a look at the MSBuild SDK here and I see a direct call to the Exec task during publishing:

MSBuild.Sdk.SqlProj/src/MSBuild.Sdk.SqlProj/Sdk/Sdk.targets

Line 294 in b29bc4d

<Exec Command="$(DacpacToolCommand)" />

I'd suggest instead of using Exec directly, y'all should make a task deriving from ToolTask and use the APIs there to safely provide command-line arguments to an executable. This will also give you more control over the way sqlcmd is invoked. A good example of this pattern is the .NET SDK Containerization feature (when run from Visual Studio). We made a Tooltask to wrap invoking a helper executable, and you can see that implementation here.

[Aaronontheweb]

As a work-around for the time being - wrote some PowerShell scripts to pass these SQLCMD variables in as part of an external SqlPackage call. I need to do that for some of our deployments anyway, but it'd be great if I could just use the data inside the .csproj file so I don't have to keep extra external variables lying around

[jmezach]

@baronfel Interesting, I didn't know about that when I first wrote this. Could be worth investigating I guess, just not sure when I'll find the time to do it though. It also complicates the setup a little bit as currently the SDK is only a bunch of MSBuild files and a command line tool that does the heavy lifting. This would require adding an additional assembly that would contain the MSBuild task. If anyone's up for it and willing to submit a PR that would be great.

To fix @Aaronontheweb's issue though I think we could fix the escaping with the current setup as well, at least regarding any SQLCMD parameters being passed.

[jmezach]

@Aaronontheweb We have a PR now that should fix this issue.

[jmezach]

Version 2.6.1 just got released on NuGet.org which contains the fix for this issue.

[Aaronontheweb]

Version 2.6.1 just got released on NuGet.org which contains the fix for this issue.

thank you!