C# 如何将带有 ( ' ) 的字符串插入到 sql 数据库中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11120148/
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 insert a string with ( ' ) in to the sql database?
提问by Arun Kumar
I have the strings which consists of ( ' ) quote mark like "mother's love" ...
我有由（ ' ）引号组成的字符串，如“母爱”......
While inserting the data by sql query from c#. It shows error. How can i rectify the problem and insert this kind of data successfully?
在通过 c# 中的 sql 查询插入数据时。它显示错误。如何解决问题并成功插入此类数据？
string str2 = "Insert into tblDesEmpOthDetails (EmpID, Interviewnotes) values ('" + EmpId + "','" + Interviewnotes + "')";
Interview notes consists the value like "Mother's love" (with single quote). While executing this query it shows error as "Unclosed quotation mark after the character string ')" how can i insert this type of strings?
采访笔记包含“母爱”之类的值（带单引号）。执行此查询时，它显示错误为“字符串') 后的未关闭引号”如何插入这种类型的字符串？
采纳答案by Otiel
I'm pretty sure you don't use SQL parameters:
我很确定你不使用 SQL 参数：
using (SqlCommand myCommand = new SqlCommand(
"INSERT INTO table (text1, text2) VALUES (@text1, @text2)")) {
myCommand.Parameters.AddWithValue("@text1", "mother's love");
myCommand.Parameters.AddWithValue("@text2", "father's love");
//...
myConnection.Open();
myCommand.ExecuteNonQuery();
//...
}
回答by Andy
Use named parameters and the SqlParameter.
使用命名参数和 SqlParameter。
From http://www.dotnetperls.com/sqlparameter
来自http://www.dotnetperls.com/sqlparameter
class Program
{
static void Main()
{
string dogName = "Fido"; // The name we are trying to match.
// Use preset string for connection and open it.
string connectionString =
ConsoleApplication1.Properties.Settings.Default.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Description of SQL command:
// 1. It selects all cells from rows matching the name.
// 2. It uses LIKE operator because Name is a Text field.
// 3. @Name must be added as a new SqlParameter.
using (SqlCommand command =
new SqlCommand("SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection))
{
// Add new SqlParameter to the command.
command.Parameters.Add(new SqlParameter("Name", dogName));
// Read in the SELECT results.
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int weight = reader.GetInt32(0);
string name = reader.GetString(1);
string breed = reader.GetString(2);
Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}", weight, name, breed);
}
}
}
}
}
回答by Steven Doggart
Although, you could replace all ' characters in the string with two ' characters (''), it's not a good idea. Because of this issue, and many other reasons (such as avoiding SQL-injection attacks), you definitely should be using named parameters rather than adding the values to your insert statement by concatenating them directly into the string. For instance:
尽管您可以用两个 ' 字符 ('') 替换字符串中的所有 ' 字符，但这不是一个好主意。由于这个问题以及许多其他原因（例如避免 SQL 注入攻击），您绝对应该使用命名参数，而不是通过将值直接连接到字符串中来将值添加到插入语句中。例如：
command.CommandText = "Insert into tblDesEmpOthDetails (EmpID, Interviewnotes) values (@EmpId, @Interviewnotes)";
command.Parameters.AddWithValue("EmpId", EmpId);
command.Parameters.AddWithValue("Interviewnotes", Interviewnotes);
回答by Jason Paddle
You need to use double ''
您需要使用双 ''
INSERT INTO something (Name) VALUES ('O''something''s')
This will insert O'something's. Another example i read is:
这将插入 O'something's。我读过的另一个例子是：
Lets assume we have a string:
假设我们有一个字符串：
SQL = "SELECT * FROM name WHERE LastName='" & LastName & "' "
and if we have lastname something like O'Brian, O'Reily etc. we got string like
如果我们有像 O'Brian、O'Reily 等姓氏这样的名字，我们就会得到像这样的字符串
SELECT * FROM name WHERE LastName='O'Brien'
the second ' will end the SQL statement. So the simplest solution here is to use double '' then we will have string like this:
第二个 ' 将结束 SQL 语句。所以这里最简单的解决方案是使用双 '' 然后我们将有这样的字符串：
SELECT * FROM name WHERE LastName='O''Brien'
回答by Eric Robinson
Add this line to a string your trying to input
将此行添加到您尝试输入的字符串中
say you string was
说你的字符串是
string test = "that's not working correctly"
test = replace(Request.QueryString(test), "'", "''")
then test is now
那么现在测试
"that''s not working correctly"
which is syntactically correct for SQL
这对于 SQL 在语法上是正确的
Regards
问候
回答by Marc Gravell
As a variant on the answers that (very correctly) point you at parameters: if this seems a lot of work, then avoid itwith a tool such as dapper:
作为答案的一个变体（非常正确地）将您指向参数：如果这看起来需要做很多工作，那么请使用诸如dapper 之类的工具来避免它：
int empId = 123;
string notes = "abc";
connection.Execute(@"insert into tblDesEmpOthDetails (EmpID, Interviewnotes)
values (@empId, @notes)", new {empId, notes});
Dapper will automatically take the empIdand notes(from the anonymous object) and add them as named/typed parameters. The similar Query/Query<T>extension-methods also allow for easy and highly-optimised queryingdirectly into an object model.
Dapper 将自动获取empIdand notes（来自匿名对象）并将它们添加为命名/类型参数。类似Query/Query<T>扩展方法还允许直接对对象模型进行简单且高度优化的查询。
