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.