创建动态 sql 查询

2021-09-10 00:00:00 tsql sql-server

请查看此 SQL 代码:

Please see this SQL code:

DECLARE @MyQuery nvarchar(max)
set @MyQuery = 'SELECT TOP 1 @TranslatedMessageOutput = 
               ' + @LanguageName + ' FROM local_translation WHERE English =
               '+CHAR(39)+CHAR(39)+Convert(nvarchar(50),
               (select English from inserted))
                +CHAR(39)+CHAR(39)+CHAR(39)+
               ' AND [' + @LanguageDateName + '] NOT LIKE ''%1900%'''

如果我为英语输入 abc 这个查询就可以正常工作了.

If I type abc for English this query works just fine.

但是如果我输入 'abc' 为英语查询会抛出一个错误:

But if I type 'abc' for English the query is throwing an error:

字符串"后的非封闭引号.."附近的语法不正确

Unclosed quotation mark after the character string "". Incorrect syntax near "."

我该如何解决这个问题?

How do I solve this?

推荐答案

我怀疑您需要转义此查询的输出:

I suspect you will need to escape the output of this query:

(select English from inserted)

如在

(select replace(English, '''', '''''') from inserted)

在将结果连接到您的动态 SQL 语句之前，上述内容将用 '' 替换 '.但是对于任何动态 SQL，我强烈建议您使用绑定值来防止此类 SQL 语法错误和 SQL 注入！IE.你应该能够写出这样的东西:

The above will replace ' by '' before concatenating the outcome to your dynamic SQL statement. But with any dynamic SQL, I strongly suggest you make use of bind values to prevent such SQL syntax errors and SQL injection! I.e. you should be able to write something like this:

declare @English;
select @English = English from inserted;
set @MyQuery = 'SELECT TOP 1 @TranslatedMessageOutput = 
               ' + @LanguageName + 
               ' FROM local_translation WHERE English = @English'
               ' AND [' + @LanguageDateName + '] NOT LIKE ''%1900%''';
-- ...

相关文章