The Apostrophe That Wasn't There — Until SQL Server Created It

A few weeks back, Redgate's Simple Talk pulled apart a SQL injection hole in one of Microsoft's own shipped system procedures, sys.sp_dbmmonitorupdate. That is the part that should stop you. Microsoft wrote it. This procedure is not careless. It is sanitized. But it doubled up its single quotes with REPLACE, exactly the way the textbook says to, and became a target anyway.

Last time I wrote about injection, the lesson was 'stop concatenating user input.' This is the sequel for anyone still concatenating and trying to make it safe with REPLACE. The problem is that the value can contain a Unicode character that looks exactly like an apostrophe, but isn't one. REPLACE looks only for the real apostrophe and lets the lookalike pass. Then the value lands in a char buffer, and SQL Server quietly converts the lookalike into a genuine apostrophe — without being asked.

Input:   Oʼreilly     (that mark is a lookalike, not a real apostrophe)

Step 1   REPLACE only doubles REAL apostrophes. It does not recognize
         the lookalike, so it changes nothing and passes it through.

Step 2   The value is copied into a char (non-Unicode) buffer. That
         copy silently converts the lookalike into a REAL apostrophe.

Result   A real, unescaped apostrophe is now sitting in the string you
         already 'sanitized'. The quote breaks out. Injection is live.

This has been labeled 'Unicode homoglyph' since Redgate's Simple Talk showed a real system-procedure case. In SQL Server, this means a character that visually resembles another character -- like lowercase 'L' and the number 1, or as in the example above, a Unicode apostrophe and a standard single quote. These homoglyphs become dangerous when an implicit Unicode-to-non-Unicode conversion performs a best-fit character mapping. In this case, SQL Server silently translated U+02BC into U+0027 after the quote-doubling had already occurred, effectively creating a new SQL delimiter that the sanitization step never saw.

Why doubling quotes stops working

First, one piece of vocabulary, because the rest of this leans on it. Every character has an ID number, its Unicode code point, written as 'U+' plus a hex value. The apostrophe on your keyboard is U+0027. It has a near-twin, the modifier letter apostrophe (U+02BC), a completely different character that happens to look identical in most fonts.

Now the failure. REPLACE matches on that ID number. You told it to find the keyboard apostrophe (U+0027) and double it, so when you hand it the twin (U+02BC) there is no match and it passes straight through. The damage lands one line later, when that value is assigned to a non-Unicode variable: SQL Server's best-fit mapping rewrites the twin into the real keyboard apostrophe. A genuine quote is now sitting in a string you already declared clean.

You can see it here. This part is deterministic, not a measurement:

DECLARE @n nvarchar(10) = N'ʼ';   -- U+02BC, MODIFIER LETTER APOSTROPHE
DECLARE @v varchar(10)  = N'ʼ';   -- implicit cast to non-Unicode

SELECT UNICODE(@n) AS AsNVarchar,   -- 700
       UNICODE(@v) AS AsVarchar;    -- 39  (that is U+0027, a real quote)

700 goes in, 39 comes out — and notice there is no CAST or CONVERT anywhere in that code. Just assigning the Unicode value (the N'...') to a varchar variable is enough. SQL Server converts it for you, silently, using best-fit character mapping. The implicit conversion is the whole bug.

demo time

IF OBJECT_ID('dbo.usp_FindCustomer','P') IS NOT NULL 
DROP PROCEDURE dbo.usp_FindCustomer;
GO
IF OBJECT_ID('dbo.Customer','U') IS NOT NULL 
DROP TABLE dbo.Customer;
GO

CREATE TABLE dbo.Customer
(
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(128) NOT NULL
);
GO

-- Three rows, including a REAL apostrophe surname. Row count hard-capped by the VALUES list.
INSERT dbo.Customer (LastName) 
VALUES ('Smith'), ('O''Brien'), ('Nguyen');
GO

The 'safe' procedure

It sanitizes. It doubles the quotes like the textbook says. Its only sin is landing the result in a char(256) buffer before building the command.

CREATE PROCEDURE dbo.usp_FindCustomer
    @Name NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- The step everyone trusts: double up single quotes.
    SET @Name = REPLACE(@Name, '''', '''''');

    -- The mistake: a NON-Unicode buffer. This is where U+02BC becomes U+0027.
    DECLARE @command CHAR(256);
    SET @command = N'SELECT TOP (100) CustomerId, LastName '
                 + N'FROM dbo.Customer WHERE LastName = ''' + @Name + N'''';

    PRINT @command;      -- inspect the built string instead of blindly running it
    -- EXEC (@command);  -- left commented ON PURPOSE
END;
GO

Case 1 — a normal apostrophe (this one is fine)

EXEC dbo.usp_FindCustomer @Name = N'O''Brien';

The REPLACE in the proc turned that one apostrophe into two, so the command it builds ends with the double ticks in the LastName. That is not a typo. In T-SQL, using '' inside a string is the escape for a single apostrophe. When SQL Server runs that, it reads the value back as O'Brien. Copy that SELECT and run it yourself. You'll see it is working correctly -- and that is what lulls you into trusting REPLACE.

Case 2 — the lookalike (this one is not)

-- The character before 'reilly' is U+02BC, NOT the U+0027 on your keyboard.
EXEC dbo.usp_FindCustomer @Name = N'Oʼreilly';

This time REPLACE finds no U+0027 to double, so it does nothing. The char(256) assignment converts U+02BC into a real apostrophe, and the output now carries a quote that cannot be used. Try to run that SELECT and you'll see the engine doesn't care for that unmatched quote:

You're only seeing this error because this payload was clumsy enough to break the syntax. Make the injected tail valid T-SQL and nothing breaks. SQL Server just runs it, no error, no warning, with whatever rights the caller holds. The unclosed quote syntax error is the best outcome on the table. A real attacker's version will run clean and provide no evidence of who is in your system doing what.

The fix: keep it Unicode, and stop concatenating

Two things, belt and suspenders. First, never let sanitized values fall into a non-Unicode buffer. The implicit conversion is what creates the vulnerability. Microsoft's fix for the reported system-procedure case was simple: they widened the command buffer to nvarchar(4000) so the U+02BC never gets a chance to collapse into a quote. Second, and better, do not concatenate the value into the SQL string at all. Parameterize it, so the value is data and never code.

CREATE OR ALTER PROCEDURE dbo.usp_FindCustomer_Fixed
    @Name NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Stays Unicode end to end: no best-fit collapse, no minted apostrophe.
    DECLARE @command NVARCHAR(4000);
    SET @command = N'SELECT TOP (100) CustomerId, LastName '
                 + N'FROM dbo.Customer WHERE LastName = @Name';

    -- @Name crosses as a PARAMETER, never concatenated into the text.
    EXEC sys.sp_executesql
         @command,
         N'@Name NVARCHAR(128)',
         @Name = @Name;
END;
GO

Now run both inputs with the fixed proc and there is no drama. The @Name value is not parsed as SQL. It is bound as a parameter, compared as data, and they both run properly. O'Brien returns and the lookalike matches no one and returns nothing. No conversion or broken quote. The empty result is the whole win. No REPLACE required, and that is the point. Hand-rolled escaping is a maintenance trap; parameters are the actual answer.

Cleanup

IF OBJECT_ID('dbo.usp_FindCustomer','P') IS NOT NULL DROP PROCEDURE dbo.usp_FindCustomer;
IF OBJECT_ID('dbo.usp_FindCustomer_Fixed','P') IS NOT NULL DROP PROCEDURE dbo.usp_FindCustomer_Fixed;
IF OBJECT_ID('dbo.Customer','U') IS NOT NULL DROP TABLE dbo.Customer;

Version notes

Nothing here needs a modern build. sp_executesql has shipped since forever, and the conversion behavior applies to any supported SQL Server version. One honest caveat -- best-fit mapping is collation dependent, so which Unicode lookalikes collapse into which ASCII characters can vary by the collation in play. The defense, however, does not vary. Keep the buffer Unicode and pass values as parameters, and the mapping never gets a turn.

More to Read

Simple Talk: Exposing a SQL injection vulnerability you have never heard of
Remus Rusanu: SQL Injection — casting can introduce additional single quotes
sqlfingers inc: AI Didn't Hack Mexico. SQL Injection Did