Teams: "error rendering message" in Adaptive Card (Bot Framework) for some SQL content

Question

Update for below question: it is a confirmed bug and tracked here https://github.com/MicrosoftDocs/msteams-docs/issues/9392

Sometimes Teams would show me an error "Error encountered while rendering this message" without any further detail. I narrowed the problem down to this SQL query:

"SELECT COUNT(DISTINCT VisitorId) AS UniqueVisitors FROM sitedata WHERE eventdate>='2023-08-01' AND eventdate<'2023-08-08' AND Site =='foo.bar.com' AND Url RLIKE '^http?s://foo.bar.com/([^/]*)/answers/?'"

To render the card I'm using this code:

var cardContent = new AdaptiveCardTemplate(cardTemplate).Expand(
            new
            {
                Query = sql
            }
        );

        var replyActivity = MessageFactory.Attachment
        (
            new Attachment
            {
                ContentType = "application/vnd.microsoft.card.adaptive",
                Content = JsonConvert.DeserializeObject(cardContent),
            }
        );

        await turnContext.SendActivityAsync(replyActivity);

The card's relevant portion is this:

{
      "type": "TextBlock",
      "text": "${query}",
      "wrap": true,
      "fontType": "Monospace",
      "color": "Dark",
      "horizontalAlignment": "Left",
      "separator": true
    }

I tried to figure out what exactly it is that breaks the rendering but the results are non-conclusive. If I change the SQL to this, the output will break:

"SELECT COUNT(DISTINCT VisitorId) AS UniqueVisitors FROM sitedata WHERE eventdate>='2023-08-01' AND"

If I change it further and only remove the last AND, it renders just fine:

"SELECT COUNT(DISTINCT VisitorId) AS UniqueVisitors FROM sitedata WHERE eventdate>='2023-08-01'"

As a workaround I wrapped the assignment into WebUtility.HtmlEncode(sql)and that fixes the issue. I would love to understand what the problem is and how to solve it properly.

Answer 1

This is due to a confirmed bug and tracked here: https://github.com/MicrosoftDocs/msteams-docs/issues/9392