Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

QUOTENAME considers 0xFFFF to be valid, but needs to return NULL if that character exists in input value

The QUOTENAME built-in function does not return NULL if the character U+FFFF (0xFFFF / 65535) is part of the string that is passed in. However, it should return NULL just like it does if character U+0000 is passed in. Those are the only two characters that are invalid in delimited identifiers, so QUOTENAME shouldn't reject one and not the other.

By not returning NULL if U+FFFF is found, the system stored procedure, sp_validname ( https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-validname-transact-sql ) will erroneously return 1 / true, even if U+FFFF is present in the name being checked (I will file a separate bug for that stored procedure since it can be updated to check for this code point and not rely on QUOTENAME for that).

---------------------------------------------
SELECT QUOTENAME(N'a' + NCHAR(0x0000));
-- NULL (correct)

SELECT QUOTENAME(N'a' + NCHAR(0xFFFF));
-- a row is returned (oops!)
---------------------------------------------

For details / research on valid/invalid characters, please see:

"The Uni-Code: The Search for the True List of Valid Characters for T-SQL Identifiers, Part 3 of 2 (Delimited Identifiers)" ( https://sqlquantumleap.com/2018/04/16/the-uni-code-the-search-for-the-true-list-of-valid-characters-for-t-sql-identifiers-part-3-of-2-delimited-identifiers/ )

1 vote
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Solomon Rutzky shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base