如何在动态 SQL 命令中添加引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1124723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Add Quotes to a Dynamic SQL Command?
提问by Ahmad Farid
I am storing and editing some field in a database that involves a long string of one or more sentences. whenever i enter a single quote in the textbox and want to save it it throws an exception like "Incorrect syntax near 'l'. Unclosed quotation mark after the character string ''."is there any idea to avoid that?
我正在存储和编辑数据库中的某个字段，该字段涉及一长串一个或多个句子。每当我在文本框中输入单引号并想要保存它时，它都会抛出一个异常，例如 “'l'附近的语法不正确。字符串''后的未关闭引号。” 有什么想法可以避免这种情况吗？
EDIT:The query is:
编辑：查询是：
SqlCommand com = new SqlCommand("UPDATE Questions SET Question = '[" +
tbQuestion.Text + "]', Answer = '[" +
tbAnswer.Text + "]', LastEdit = '" +
CurrentUser.Login +
"'WHERE ID = '" + CurrentQuestion.ID + "'");
回答by John Saunders
As KM said, don't do this!
正如 KM 所说，不要这样做！
Do thisinstead:
做此相反：
private static void UpdateQuestionByID(
int questionID, string question, string answer, string lastEdited)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
const string QUERY =
@"UPDATE Questions " +
@"SET Question = @Question, Answer = @Answer, LastEdit = @LastEdited " +
@"WHERE ID = @QuestionID";
using (var cmd = new SqlCommand(QUERY, conn))
{
cmd.Parameters.AddWithValue("@Question", question);
cmd.Parameters.AddWithValue("@Answer", answer);
cmd.Parameters.AddWithValue("@LastEdited", lastEdited);
cmd.Parameters.AddWithValue("@QuestionID", questionID);
cmd.ExecuteNonQuery();
}
}
}
回答by TheTXI
If you want to include a single quote into an SQL field, escape it using single quotes
如果要在 SQL 字段中包含单引号，请使用单引号将其转义
'''Test''' = 'Text'
This is for SQL Server.
这是针对 SQL Server 的。
回答by n8wrl
Write a stored produre to do your field editing and use SQL parameters to save the value. Quotes won't matter. If you don't want a stored proc at least build your SQL text with parameter markers and use SQL parameters with that.
编写一个存储的 produre 来进行字段编辑并使用 SQL 参数来保存值。报价无关紧要。如果你不想要一个存储过程，至少用参数标记构建你的 SQL 文本，并使用 SQL 参数。
回答by KM.
it is difficult to give you a specific answer, because you don't list the database or application language you are using.
很难给你一个具体的答案，因为你没有列出你正在使用的数据库或应用程序语言。
You must be building your SQL dynamically, and the quote within the sting is being interpreted as the end of the string. Depending on the database you are using, you need to escape the single quotes within each string you intend to use in your sql command. This can be seen by printing your query before you try to run it.
您必须动态构建 SQL，并且字符串中的引号被解释为字符串的结尾。根据您使用的数据库，您需要对打算在 sql 命令中使用的每个字符串中的单引号进行转义。这可以通过在尝试运行查询之前打印查询来查看。
You do not mention the application that you are calling the database from, but when you build you command you need to use a FIX_QUOTES() command that you write or if provided by your language:
您没有提及从中调用数据库的应用程序，但是在构建命令时，您需要使用您编写的或由您的语言提供的 FIX_QUOTES() 命令：
SqlCommand com = new SqlCommand("UPDATE Questions SET Question = '[" + FIX_QUOTES(tbQuestion.Text) + "]', Answer = '[" + FIX_QUOTES(tbAnswer.Text) + "]', LastEdit = '" + FIX_QUOTES(CurrentUser.Login) + "'WHERE ID = '" + FIX_QUOTES(CurrentQuestion.ID) + "'"); – A
This type of dynamic query is very easy for an sql injection attack.I would recommend calling the database with a stored procedure or with a parameter list.
这种类型的动态查询很容易被sql注入攻击。我建议使用存储过程或参数列表调用数据库。
回答by cjk
In MSSQL you can double up your quotes:
在 MSSQL 中，您可以将引号加倍：
my dodg'y test -> 'my dodg''y test'
my 'quoted' string -> 'my ''quoted string'''
'first and last quotes' -> '''first and last quotes'''
回答by awe
As some have already said, adding an extra quote will do the trick. I can confirm that this is also the case for Oracle (others have given this answer to be valid for MSSQL and SQL Server). I think that using stored procedures is overkill for this.
正如有些人已经说过的那样，添加额外的引号即可解决问题。我可以确认这也是 Oracle 的情况（其他人已给出此答案对 MSSQL 和 SQL Server 有效）。我认为为此使用存储过程是过度的。
