替换asp.net中的撇号以防止SQL错误 [英] Replacing apostrophe in asp.net to prevent SQL error

问题描述

我有一个带有名称"字段的Web表单，我希望它能够接受单撇号，例如名称O'Leary，但是当尝试将此记录推送到SQL 2005服务器时，出现错误.我的问题不是这个.就是当我尝试使用此语句将记录插入到数据库中时...

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, "'", "''")

我在数据库中得到O''Leary，而不是O'Leary.认为SQL应该将这些双单撇号视为一个撇号???

I get O''Leary in the database instead of O'Leary. Thought SQL was supposed to treat these double single apostrophes as one apostrophe???

推荐答案

使用参数化查询会更好.这些将自动处理单引号，并更好地保护您免受SQL注入的侵害.

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

我建议的是步骤3.

编辑-我应该更好地阅读问题

如果您已经在使用参数化查询，或存储过程，并且将acctName的值设置为参数的值，则无需自己对引号进行转义.这是自动处理的.

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.

它也由多种工具处理，包括Mirosoft模式和实践数据库库.那有几个命令，您可以在其中传递用作参数值的语句和对象数组-还可以处理转义.

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.

这篇关于替换asp.net中的撇号以防止SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！