Incorrect syntax near '?' while inserting HTML as NVARCHAR-CodePudding

We have a textarea in which the users are able to write anything, it is a dynamic view which will be loaded later. When the user is ready to save it, the textarea is sent so be saved in the database.

The problem is, sometimes it works and sometimes it doesn't. The times it doesn't work is because we received:

Incorrect syntax near '?'.

We have replaced all single quotes with two single quotes, and we have made sure that it reaches the database correctly.

ALTER STORED PROCEDURE dbo.SaveHtml @htmlValue NVARCHAR(MAX)
AS
DECLARE @SQL_QUERY NVARCHAR(MAX);

SET @SQL_QUERY  = N'UPDATE TEMP_TABLE SET BLOCK_1 = N{VALUE} WHERE ID = 12';

SET @SQL_QUERY = REPLACE(@SQL_QUERY,'{VALUE}', ISNULL(@htmlValue,NULL));

EXECUTE (@SQL_QUERY);

Basically that is what it does, we create a dynamic SQL because there are parameters which are dynamic, but for this example I omitted it.

It works, but sometimes when the user copies and pastes data from Word, the string arrives with characters like Ğ¸ĆÊô￴∀⠢Ĉ!ࠀސȁސ﷿ ױ﹗£΀ਮշωԊӍԊӍ̳¡﹩fȒ＠† ™ Ĳ囌垐輈׶č靀ミ, and later we received the indicated error.

Even sometimes it arrives with those characters (not necessarily those exactly or in the same order) and it is saved successfully.

For the example mentioned if I delete the first ސ character the string is saved successfully.

'<p  style="margin-top:0cm;margin-right:0cm;margin-bottom:0cm;
margin-left:36.2pt;text-align:justify;text-indent:-18.0pt;line-height:normal;Ğ¸ĆÊô￴∀⠢Ĉ!ࠀސȁސ﷿ ױ﹗£΀ਮշωԊӍԊӍ̳¡﹩fȒ＠† ™ Ĳ囌垐輈׶č靀ミ...'

When I debug to see the dynamic SQL and I print it, it looks like:

UPDATE TEMP_TABLE SET BLOCK_1 = N'<div class="note-editable" contenteditable="true" role="textbox" aria-multiline="true" ...' WHERE ...

As these are the strings that we saved.

This is currently in production because it works, but the times that it does not work are exceptional. When Chinese, Arabic and more special characters arrive it becomes a lottery that our code works.

How could I solve this problem?

CodePudding user response：

You are not quoting your string, nor treating it as a NVARCHAR. Instead of this:

SET @SQL_QUERY = REPLACE(@SQL_QUERY,'{VALUE}', ISNULL(@htmlValue,NULL));

You need this for syntactically correct SQL:

SET @SQL_QUERY = REPLACE(@SQL_QUERY,'{VALUE}', ISNULL('N'''   @htmlValue   '''','NULL'));

However the correct and safe way to do this is using sp_executesql with a proper parameter as follows:

SET @SQL_QUERY  = N'UPDATE #TEMP_TABLE SET BLOCK_1 = @VALUE WHERE ID = 12';

EXEC sp_executesql @SQL_QUERY, N'@VALUE nvarchar(max)', @HtmlValue;

Note: The way to debug dynamic SQL is by using the PRINT statement, which if you run on your original SQL gives:

UPDATE TEMP_TABLE SET
    BLOCK_1 = Ğ¸ĆÊô￴∀⠢Ĉ!ࠀސȁސ﷿ ױ﹗£΀ਮշωԊӍԊӍ̳¡﹩fȒ＠† ™ Ĳ囌垐輈׶č靀ミ
WHERE ID = 12

Which is clearly wrong, you need:

UPDATE TEMP_TABLE SET
   BLOCK_1 = N'Ğ¸ĆÊô￴∀⠢Ĉ!ࠀސȁސ﷿ ױ﹗£΀ਮշωԊӍԊӍ̳¡﹩fȒ＠† ™ Ĳ囌垐輈׶č靀ミ'
WHERE ID = 12