MSSQL errors/panics when parsing colums with NVARCHAR(MAX) datatype

cfsamson commented 1 year ago

Bug Description

NVARCHAR(MAX) doesn't parse correctly on Sql Server Express 16.0.1000.6.

On Sql Server 2016 version 12.0.6169.19 it simply panics with the error:

panicked at 'called `Result::unwrap()` on an `Err` value: encountered unexpected or invalid data: unknown value 
`0x00` for message type in token stream'

Minimal Reproduction

Assuming you have a local Mssql Express server running (quick setup guide if you don't):

Download sql server express and choose basic installation, once finished:
Remember to enable tcp/ip in Sql Server Configuration Manager (Sql Server Network Configuration -> Protocols for SQLEXPRESS -> TCP/IP -> right click "enable"
Restart SQLEXPRESS service
Get the port from `Sql Server Network Configuration -> Protocols for SQLEXPRESS -> TCP/IP -> "properties/ip addresses/IPAll"
Create "test" database and a "test" user in Sql Server Management Studio
Remember and go to server instance -> properties -> Secority and make sure "Allow Sql Server and Windows Authentication" is checked off (it's not by default).

On local sql server called "testserver" with a user test, pwd: test and port: 60000

CREATE TABLE test (col1 NVARCHAR(MAX) NULL);
INSERT INTO test VALUES ('sometext');

Setup project:

cargo new sqlx_nvarchar
cargo add sqlx --features "mssql runtime-tokio-rustls"
cargo add tokio --features full
$env:DATABASE_URL:"mssql://test:test@localhost:60000/testserver"

use std::error::Error;
use sqlx::MssqlPool;

#[derive(Debug)]
struct Test {
    col1: Option<String>,
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn Error>> {
    let pool = MssqlPool::connect(env!("DATABASE_URL")).await?;
    let test = sqlx::query_as!(Test, "SELECT col1 FROM test.dbo.test").fetch_one(&pool).await?;
    println!("{test:#?}");
    Ok(())
}

Run cargo run and you get the output:

Test {
    col1: Some(
        "\0\0\0\u{10}\0som",
    ),
}

Expected:

Test {
    col1: Some(
        "sometext",
    ),
}

On sql server 2016 this just panics.

Info

SQLx version: 0.6.2
Tokio version: 1.22.0
SQLx features enabled: "mssql", "runtime-tokio-rustls"
Database server and version: Sql Server Express 16.0.1000.6 and Sql Server 2016 12.0.6169.19
Operating system: Windows 10
rustc --version: 1.65.0

cfsamson commented 1 year ago

Temporary workaround:

If you know the max size of the data you work with you can change the SQL to:

SELECT CAST(col1 AS NVARCHAR(1024)) as col1 FROM test.dbo.test

Change the size to whatever size you want (it doesn't seem to matter just as long as there is an explicit size set).

codebling commented 1 year ago

Can confirm this. Can reproduce with docker image using default config:

docker run --rm -it -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=@Passw0rd" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

Also affects VARCHAR type.

codebling commented 1 year ago

Duplicate issues: #2286, #1838

lovasoa commented 1 year ago

I fixed multiple bugs related to string handling in mssql in https://github.com/lovasoa/sqlx

cfsamson commented 1 year ago

@lovasoa Great, I'll use that going forward 👍👍👍

launchbadge / sqlx