2

I've been tracking a bug in how single quotes/apostrohpes are handled in an application, and I've found a suspect in a database trigger.

That trigger contains code like this: (shortened and anonymized)

insert into logTable ([NewRowData]) 
select 
  (select isnull(convert(nvarchar(max), [Foo]),'NULL') as Foo 
   from inserted i where  i.MyID = u.PrsID for xml raw('NewRowData')) 
from @updated_row u

In production, I get errors when "Foo" contains a single quote, (For example "O'Hara")

Incorrect syntax near 'Hara'. Unclosed quotation mark after the character string ',NULL)'.

However, I cannot replicate this. A manual insert/update that fires the trigger does not give an error.

Is is possible that the nested functions isnull and convert somehow messes up quotation escapes or am I looking in the wrong direction?

(Cannot find any dynamic SQL genereation anywhere, which naturally was my first suspicion.)

Database is SQL server 2012.

Addendum: I did a similar test to what Vérace posted below before posting here with the same result i.e no exception. If there IS a bug, it must be dependant on database version, settings or query execution plan.

  • Where is @updated_row being populated? That isn't a valid trigger as it stands – Mister Magoo Oct 11 '17 at 11:48
  • @MisterMagoo, that got lost in shortening. updated_row is a table variable containing keys from deleted and inserted. – Guran Oct 11 '17 at 11:53
  • Nothing to do with the XML bits? I'm not an expert on SQL Server but could the error be in there? – Vérace Oct 11 '17 at 12:30
  • What is your application? You can think "there is no dynamic SQL in there" but it still can be. For example, SSRS executes your plain queries by calling sp_executesql, and there is an issue with a single quote. Why don't you use Profiler to catch the code passed to server? – sepupic Oct 11 '17 at 12:33
  • Where are you capturing the error? – Mister Magoo Oct 11 '17 at 12:37
4

No - it is NOT your SQL!

To answer this, I did the following:

CREATE TABLE test
(
  num INTEGER,
  name VARCHAR (50)
)

INSERT INTO test VALUES (1, 'Paoul');
INSERT INTO test VALUES (2, 'Mary');
INSERT INTO test VALUES (3, 'O''Hara');
INSERT INTO test VALUES (4, NULL);

then,

SELECT * FROM test;

gives:

num     name
___     _____
1       Paoul
2       Mary
3       O'Hara
4       (null)

And then ran:

SELECT ISNULL(CONVERT(NVARCHAR(MAX), name), 'NULL') AS Foo FROM test;

And got the result:

Foo
____
Paoul
Mary
O'Hara
NULL        <----- String literal, not the value NULL!

Which is what you'd expect! Quite why one would want to insert the string literal NULL is beyond me - Hmm... Developers: "three-value logic, my head is melted - I'll just stick in the string and we can do an OR!". Or, was this system in Oracle at one point? Empty string and NULL string being the same thing?

So, whatever your problem is, it is not your SQL as shown above causing this problem! Check the SQLFiddle here (note: using MS SQL Server 2014!).

As for your point about it being a version bug - I find it difficult to believe that such a basic functionality would be buggy in a 2012 Server version. Such a bug would have broken loads of legacy code!

My hunch is that the problem lies somewhere with the XML part of the code and the bits we can't see. I would set up logging (of all queries) and check out the SQL text that is actually being sent to the server (on my test system).

|improve this answer|||||
  • @Vérace I'm accepting this as the answer, since I cannot replicate the error even after several variants. Perhaps someone else fixed this earlier, without closing the bug? Who knows. It's not critical to the application, and if it wasn't for the smell of sql injection vulnerability, I'd have dropped it long ago. – Guran Oct 13 '17 at 6:32
  • Thanks for that! Just as a matter of interest and out of pure curiosity, was there anything in the guess that the system was on Oracle at one time? – Vérace Oct 13 '17 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.