替换asp.net中的撇号以防止SQL错误
我有一个带有名称字段的 Web 表单，我希望该字段能够接受单个撇号，例如名称 O'Leary，但是当尝试将此记录推送到 SQL 2005 服务器时，出现错误。我的问题不是这个。就是当我尝试使用此语句将记录插入数据库时...
Dim acctName As String = Replace(txtName.Text, "'", "''")
我在数据库中得到 O''Leary 而不是 O'Leary。认为 SQL 应该将这些双单撇号视为一个撇号？？？
I have a web-form with a Name field which I want to be able to accept single apostrophes, such as in the name O'Leary, but when trying to push this record to the SQL 2005 server, I get an error. My question is not this. It's that when I attempt to insert the record into the db using this statement...
Dim acctName As String = Replace(txtName.Text, "'", "''")
I get O''Leary in the database instead of O'Leary. Thought SQL was supposed to treat these double single apostrophes as one apostrophe???
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您最好使用参数化查询。这些将自动处理单引号，并更好地保护您免受 SQL 注入。
插入双单引号（我说得对吗？）是转义数据的一种方法。它应该有效，但这不是最佳实践。
请参阅这篇文章以获得更完整的答案：
http://msdn.microsoft.com/ en-us/library/ff648339.aspx
我建议的是第 3 步。
编辑 - 我应该更好地阅读问题
如果您已经在使用参数化查询， 或存储过程，并且您将 acctName 的值设置为参数的值，那么您不需要自己转义引号。这是自动处理的。
它还由多种工具处理，包括 Mirosoft 模式和实践数据库库。它有几个命令，您可以在其中传递用作参数值的语句和对象数组 - 也可以处理转义。
如果是其中一种情况，您可以完全消除替换值的代码行。
You'd be better off using parameterized queries. These will automatically handle the single quotes, and protect you better from SQL Injection.
Inserting the double single quotes (did I say that right?) is a way of escaping the data. It should work, but it's not a best practice.
See this article for a much fuller answer:
http://msdn.microsoft.com/en-us/library/ff648339.aspx
What I'm proposing is step 3.
Edit - I should read the question better
If you're already using parameterized queries, or a stored procedure, and you're setting the value of acctName to the value of a parameter, then you do not need to escape the quotes yourself. That's handled automatically.
It's also handled by several tools, including the Mirosoft Patterns and Practices Database library. That has several commands where you can pass in a statement and array of objects that are used as parameter values -that handles the escaping as well.
If either of those are the case, you can completely eliminate the line of code where you're replacing the values.
取决于您如何将数据插入数据库。
如果您使用动态 SQL 并自己构建 SQL 字符串，则您需要自己加倍引号。但是，如果您使用参数化查询（您应该这样做，也可能是这样），那么引擎将为您处理这个问题，并且如果您自己将引号加倍，您将在数据库中获得双引号。
请注意，如果您从动态 SQL 开始并切换到参数化查询，则在进行更改时会突然出现此问题。
Depends how you're INSERTing the data into the database.
If you're using dynamic SQL and building the SQL string yourself, you are responsible for doubling the quotes yourself. But if you're using a parameterized query (as you should be, and probably are) then the engine will take care of that for you and, if you double the quotes yourself, you'll get doubled quotes in the database.
Note, if you started with dynamic SQL and switched to paramterized queries, this issue would suddenly appear at the time you made the change.
即兴地，在不了解太多细节的情况下，我建议检查 SQL Server 上的
SET QUOTED_IDENTIFIER设置。可以在此处找到更多信息。让我知道这是否有帮助。Off-the-cuff, without knowing too much detail I'd recommend checking the
SET QUOTED_IDENTIFIERsetting on the SQL Server. More information can be found here. Let me know if this helps.这很大程度上取决于您实际提交的查询。如果您提交
''，那么这就是将被保存的内容。您确实需要加倍'但出于其他原因（主要是安全性，当然还有语法有效性）。请提交您用于提交查询的代码。
It highly depends what query you actually submit. If you submit
''then this is what will be saved. You do need to double the'but for other reasons (mainly security, but of course also syntax validity).Please submit the code that you use to submit the query.