dotnet / dotnet/SqlClient

ExecuteXmlReader uses XmlReaderSettings that are incompatible with SQL Server

Open
#1,445 6 comments 0 reactions 0 assignees View on GitHub
Repro Available :heavy_check_mark:
Dominant language
C#
Stars
989
Forks
340
Avg merge
4d 19h
Merged PRs (30d)
72

Description

### Describe the bug

I am trying to read a data from a system that stores SMS message bodies. The field is `NVARCHAR(MAX)` and each message is terminated with an EOT (end of transmission) character. The EOT character has a decimal value of 4.

When reading this as XML generated by SQL Server's `FOR XML RAW` the XML is rendered with `` in it, like so:

```

```

This is not a valid character, and `XmlReader` disallows it by default:

```
System.Xml.XmlException: '', hexadecimal value 0x04, is an invalid character. Line 1, position 233415.
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args)
at System.Xml.XmlTextReaderImpl.ParseNumericCharRefInline(Int32 startPos, Boolean expand, StringBuilder internalSubsetBuilder, Int32& charCount, EntityType& entityType)
at System.Xml.XmlTextReaderImpl.ParseNumericCharRefAsync(Boolean expand, StringBuilder internalSubsetBuilder)
at System.Xml.XmlTextReaderImpl.HandleEntityReferenceAsync(Boolean isInAttributeValue, EntityExpandType expandType)
at System.Xml.XmlTextReaderImpl.ParseAttributeValueSlowAsync(Int32 curPos, Char quoteChar, NodeData attr)
at System.Xml.XmlTextReaderImpl.ParseAttributesAsync()
at System.Xml.AsyncHelper.ReturnTrueTaskWhenFinishCoreAsync(Task task)
at System.Xml.XmlWriter.WriteNodeAsync_CallAsyncReader(XmlReader reader, Boolean defattr)
at ...
```

However SQL Server generates this invalid XML, and I have an expectation that `Microsoft.Data.SqlClient` would be able to consume it. This is quite achievable:

https://github.com/dotnet/SqlClient/blob/d4f69fdc1b302a4039a618ae61538a4f98be3b0b/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlTypes/SqlTypeWorkarounds.cs#L25-L27

These `XmlReaderSettings` need `CheckCharacters = false`.

### To reproduce

```c#
using Microsoft.Data.SqlClient;

const string connectionString = "...";

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
await using var command = connection.CreateCommand();

command.CommandText = "SELECT N'Sorry running late' + CHAR(4) AS SMS_RECEIVED FOR XML RAW";

using var reader = await command.ExecuteXmlReaderAsync();

while (await reader.ReadAsync())
;
```

### Expected behavior

`Microsoft.Data.SqlClient` is able to parse all the XML SQL Server generates.

### Further technical details
Microsoft.Data.SqlClient version: 4
.NET target: .NET 6
SQL Server version: 2019
Operating system: Windows 11

**Additional context**

I can try my hand a pull request if you agree that `Microsoft.Data.SqlClient` ought to be able to interpret the XML generated by SQL Server, and that `CheckCharacters = false` is the solution.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.