如何解决 sqlite 和 c# 中的“'”问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/904796/
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 do I get around the "'" problem in sqlite and c#?
提问by adeena
I'm working in Microsoft Visual C# 2008 Express with Sqlite.
我正在使用 Sqlite 在 Microsoft Visual C# 2008 Express 中工作。
I understand that an apostrope (') in my text has problems in a query. My problem is that I thought I could replace it with \'. It doesn't seem to be working... Here's a parred down example of my code:
我了解我的文本中的撇号 (') 在查询中存在问题。我的问题是我认为我可以用 \' 替换它。它似乎不起作用......这是我的代码的一个简化示例：
string myString = "I can't believe it!";
cmd.CommandText = "Insert into myTable (myid,mytext) values (1,'" + myString.Replace("'","\'") + "');";
The error I get is: SQLite error: near "t": syntax error
我得到的错误是：SQLite 错误：“t”附近：语法错误
I've tried a couple other replacements... like the other slash. And I wrote my string and a replaced version of my string out to the console to make sure it was coming out right.
我试过其他几个替代品......就像另一个斜线。我将我的字符串和我的字符串的替换版本写到控制台，以确保它是正确的。
What stupid error am I making here?
我在这里犯了什么愚蠢的错误？
Thanks!
谢谢！
-Adeena
-阿迪娜
回答by Paulo Santos
The solution presented by Robert will work (i.e. replacing 'by '').
Robert 提出的解决方案将起作用（即替换'为''）。
Alternatively you can use parameters as in:
或者，您可以使用以下参数：
DbCommand cmd = new DbCommand();
DbParameter param = cmd.CreateParameter();
// ...
// more code
// ...
cmd.CommandText = "Insert table (field) values (@param)";
param.ParameterName = "param"
param.DbType = DbType.String;
param.Value = @"This is a sample value with a single quote like this: '";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
回答by tuinstoel
Using parameters protects against sql injection, and makes the ' problems qo away.
使用参数可以防止 sql 注入，并使问题 qo 消失。
It is also much faster because sqlite can reuse the execution plan of statements when you use parameters. It can't when you don't use parameters. In this example using a parameter makes the bulk insert action approximately 3 times faster.
它也快得多，因为当您使用参数时，sqlite 可以重用语句的执行计划。当你不使用参数时它不能。在此示例中，使用参数使批量插入操作快了大约 3 倍。
private void TestInsertPerformance() {
const int limit = 100000;
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:\testperf.db")) {
conn.Open();
using (SQLiteCommand comm = new SQLiteCommand()) {
comm.Connection = conn;
comm.CommandText = " create table test (n integer) ";
comm.ExecuteNonQuery();
Stopwatch s = new Stopwatch();
s.Start();
using (SQLiteTransaction tran = conn.BeginTransaction()) {
for (int i = 0; i < limit; i++) {
comm.CommandText = "insert into test values (" + i.ToString() + ")";
comm.ExecuteNonQuery();
}
tran.Commit();
}
s.Stop();
MessageBox.Show("time without parm " + s.ElapsedMilliseconds.ToString());
SQLiteParameter parm = comm.CreateParameter();
comm.CommandText = "insert into test values (?)";
comm.Parameters.Add(parm);
s.Reset();
s.Start();
using (SQLiteTransaction tran = conn.BeginTransaction()) {
for (int i = 0; i < limit; i++) {
parm.Value = i;
comm.ExecuteNonQuery();
}
tran.Commit();
}
s.Stop();
MessageBox.Show("time with parm " + s.ElapsedMilliseconds.ToString());
}
conn.Close();
}
}
Sqlite behaves similar to Oracle when it comes to the importance of using parameterised sql statements.
当谈到使用参数化 sql 语句的重要性时，Sqlite 的行为类似于 Oracle。
