带撇号的sql查询 [英] sql query with apostrophe

问题描述

我在dotnetnuke的SQLGridSelectedView中遇到问题，如果我在姓氏"文本框中键入 O''Connell ，我将无法获得详细信息，但是如果键入 Connell 而不是我''将获得所有详细信息.

例如:
名字= Daniel
姓氏= O''Connell


SQL语句(仅适用于SELECT或EXEC): SELECT姓氏AS [姓氏]，名字AS [姓氏]，办公室AS位置，部门，电话，"" + WindowsEmailAddress +'' ''作为来自crestron_directory的电子邮件，(姓氏不是空且姓氏<>'''')AND(姓氏不是空且姓氏<>'''')AND((((WindowsEmailAddress< ;>``[e-mail]'')AND(WindowsEmailAddress<> [e-mail]'')AND(WindowsEmailAddress<>``'')AND(WindowsEmailAddress不为null)) )


文本搜索
搜索子句:(姓氏类似''％[Search2:Text]％'')


我已经尝试过
-(LastName LIKE''％''''[[Search2:Text]％'')
-(LastName LIKE''％''''''[Search2:Text]％'')
-(LastName LIKE''％\''[Search2:Text]％'')
-(LastName LIKE''％''''[[Search2:Text]％'')
-(LastName LIKE''％"+ [Search2:Text] +"％'')
-(LastName LIKE''％"+ [Search2:Text] +"％"

")


谢谢

Hi,

I am facing a probleam in SQLGridSelectedView from dotnetnuke, If I type O''Connell in my LastName Textbox I coudn''t get the detail but if I type Connell than I''ll get all the details.

For Example :
First Name = Daniel
Last Name = O''Connell


SQL Statement(SELECT or EXEC Only): SELECT LastName AS [Last Name], FirstName AS [First Name],Office AS Location, Department, Phone, ''''+WindowsEmailAddress+'''' AS Email FROM crestron_directory WHERE (LastName IS NOT NULL AND LastName <> '''') AND (FirstName IS NOT NULL AND FirstName <> '''') AND (((WindowsEmailAddress <> ''[e-mail]'') AND (WindowsEmailAddress <> ''[e-mail]'') AND (WindowsEmailAddress <> '''') AND (WindowsEmailAddress is not null)))


Text Search
Search Clause: (LastName LIKE ''%[Search2:Text]%'')


I have tried
-(LastName LIKE ''% ''''[Search2:Text]%'')
-(LastName LIKE ''% ''''''[Search2:Text]%'')
-(LastName LIKE ''% \''[Search2:Text]%'')
-(LastName LIKE ''% ''''[Search2:Text]%'')
-(LastName LIKE ''%" + [Search2:Text] + "%'')
-(LastName LIKE ''%" + [Search2:Text] + "%''

")


Thank you

推荐答案

您可以轻松地找到如何转义撇号和其他方法，例如:
http://www.sqlteam.com/article/apostrophes-and-quotation-标记为sql-server的服务器 [ ^ ]或毕竟:
http://bit.ly/x5qYJi [ ^ ].

不过，这几乎是无关紧要的.您应该考虑非常不同的事情:"O''Connel这样的文本如何进入您的查询?我可以告诉你:它永远不会出现在查询中.什么，您在源代码中硬编码一个人的名字吗?不?然后，您可能会在运行时(甚至可能是交互式用户输入)中编写命令字符串.

您绝对不应该这样做.您需要使用参数化查询.请参阅:
http://msdn.microsoft.com/en-us/library/ms254953.aspx [ ^ ].

如果使用参数查询，甚至不会考虑撇号的问题:您将实际值分配给类型的参数.对于字符串，请按原样提供字符串值.即使里面有一个空字符. :-)

如果我的论据还不能使您信服，请考虑一下安全性:从输入中构成查询文本非常危险.请阅读有关 SQL注入的危险和参数化语句的作用:
http://en.wikipedia.org/wiki/SQL_injection [

You could easily find out how to escape the apostrophe and other things, for example:
http://www.sqlteam.com/article/apostrophes-and-quotation-marks-in-sql-server[^], or after all:
http://bit.ly/x5qYJi[^].

This is almost irrelevant though. You should think about very different thing: how come such text as "O''Connel" can get into your query? I can tell you: it should never appear in a query. What, do you hard-code a person''s name in the source code? No? Then you probably compose a command string from during run time, probably even from interactive user input.

You should never do this. You need to use parametrized queries. Please see:
http://msdn.microsoft.com/en-us/library/ms254953.aspx[^].

If you use parametrize queries, the problem of the apostrophe won''t even come into consideration: you assign actual values to the parameters, which are types. In the case of string, you supply a string value as it is. Even with a null character inside. :-)

If my arguments are not yet convincing to you, think about the security: composing the text of the query from the input is simply prohibitively dangerous. Please read about the danger of SQL Injection and the role of parametrized statements:
http://en.wikipedia.org/wiki/SQL_injection[^].

—SA

一个选项是用两个单引号替换单引号，类似于:

One option is to replace the single quote with two single quotes, similar to this:

replace(FIELD, "'", "''")

这篇关于带撇号的sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！