Double/float values validation

[MaceWindu]

Recently we've got following error (due to error on our side) when passing double.NaN as parameter value

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@p"): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.

We were already aware of issue so it was easy to understand what's wrong, but in general this message is way to low-level for this situation.

Is it possible to add validation for float/double parameters against NaN/Infinity values on provider side so users get more easy-to-understand message?

I see that client already has such validation for some code:

SerializeDouble has validation (not sure which code path use it, I see it related to encryption somehow)

https://github.com/dotnet/SqlClient/blob/main/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/TdsParser.cs#L1915-L1925

and WriteDouble hasn't

https://github.com/dotnet/SqlClient/blob/main/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/TdsParser.cs#L1927-L1932

[saurabh500]

@MaceWindu Can I look at your usage of APIs to figure out whether adding this check is possible or not?

e.g. If SqlParameter is being used to add a value, without specifying the type (SqlDbType), then the driver will send the information as-is to the server, because the Driver has no idea about the schema of the table on the server. Hence for many of these cases, where the driver is unsure about the intended SqlType, the server performs the conversion to the appropriate column type.

In such cases, the driver relies on the server to send back any error messages to be surfaced to the application.

SqlTypes are likely the right choice, in case the application knows about the column type on the server, and I see that SqlTypes when instantiated, validate the datatype as well.

[saurabh500]

@MaceWindu .

In cases of SqlParameter usage, the problem is that the driver is not aware of the semantics in which parameter is being used. The parameter type could be a double and value may be a double, but then the parameter needs to be interpreted by the server. In some cases the parameter could be casted to a string on the server due to the parameterized query or stored proc being executed.
Hence the client cannot determine if this validation should be done or not. In this case, if there is a problem with the input that it cannot map to the internal sql type, then it would better to have the Application validate the input.

[MaceWindu]

Still not sure how it will be possible to pass nan/infinity value to server as from error it looks like it is rejected by server on receive.

But taking into account that TDS spec doesn't prohibit such values I'm fine with current behavior

[saurabh500]

@MaceWindu have you tried using SqlDouble instead of the CLR types.
I don't know if the ORM allows these mappings, but the SqlTypes can allow for faithful reproduction of what SqlServer supports. I know that many constructors of SqlTypes would throw an exception if the type doesn't adhere to Sql server supported types.

[saurabh500]

Also can you provide a problematic value of the p.Value = value that you are trying to add to the server? Also what does the column look like?

[MaceWindu]

Looks like we are talking about different things. My concern here is that user could pass special values for double\float like NaN, Infinity as parameter value and SqlClient will not validate it despite validation being implemented for another code-path (see original message). This leads to obscure error from SQL Server (because it doesn't support such values) instead of well-worded error from client.