microsoft / sqltoolsservice

SQL Tools API service that provides SQL Server data management capabilities.
Other
439 stars 153 forks source link

Microsoft.SqlTools.ManagedBatchParser does not offer an option to disable variable substitution/parsing [was: Invoke-Sqlcmd does not parse correctly some string literal containing strings that resemble variables] #1936

Closed Matteo-T closed 1 year ago

Matteo-T commented 1 year ago

Simple repro (compile for net7.0 after adding a reference to Microsoft.SqlTools.ManagedBatchParser.dll, then run):

using Microsoft.SqlTools.ServiceLayer.BatchParser;
using System;
using System.IO;

class C : ICommandHandler
{
    public bool ResolveVariables = false;

    public static void Main()
    {
        var c = new C();

        using var parser =
            new Parser(commandHandler: c, variableResolver: null, reader: new StringReader("SELECT N'$(ESCAPE_SQUOTE(SRVR))'"), name: null)
            {
                ThrowOnUnresolvedVariable = true
            };

        parser.Parse();
    }

    public BatchParserAction ProcessBatch(string str, int num)
    {
        for (var i = 0; i < num; i++) Console.WriteLine($"Processing: {str}");
        return BatchParserAction.Continue;
    }

    public BatchParserAction Go(TextBlock batch, int repeatCount, SqlCmdCommand tokenType)
    {
        batch.GetText(resolveVariables: ResolveVariables, out var text, out _);
        return ProcessBatch(str: text, num: repeatCount);
    }
    public BatchParserAction Include(TextBlock _, out TextReader __, out string ___) => throw new NotImplementedException();

    public BatchParserAction OnError(Token _, OnErrorAction __) => throw new NotImplementedException();
}

Result:

Unhandled exception. Microsoft.SqlTools.ServiceLayer.BatchParser.BatchParserException: Incorrect syntax was encountered while parsing '$(ESCAPE_SQUOTE('.
 [... callstack ...]

Expected:

Processing: SELECT N'$(ESCAPE_SQUOTE(SRVR))'

This repro originated from the https://github.com/microsoft/SQLServerPSModule repo, but in reality, it's a bug in the ManagedBatchParser. I've

Original title was Invoke-Sqlcmd does not parse correctly some string literal containing strings that resemble variables

Run the following cmdlet:

invoke-sqlcmd -ServerInstance SQLTOOLS2022-3  -DisableVariables -Query "SELECT N'  ''`$(ESCAPE_SQUOTE(SRVR))'' '"

Result:

Invoke-Sqlcmd:
Line |
   1 |  invoke-sqlcmd -ServerInstance SQLTOOLS2022-3  -DisableVariables -Quer …
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Incorrect syntax was encountered while parsing '$(ESCAPE_SQUOTE('.

Expected:

Column1
-------
  '$(ESCAPE_SQUOTE(SRVR))'
Matteo-T commented 1 year ago

This is a fairly old issue introduced in v21 of the PS6+ version of Invoke-Sqlcmd (the same v21 on PS5 is working fine). Most likely a bug in the Managed BatchParser code that is not parsing the code correctly. I'll follow-up with an issue in the SqlToolsService repo soon.