如何解决sqlite和c＃中的“'”问题？

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

2 个解决方案

#1

The solution presented by Robert will work (i.e. replacing ' by '').

罗伯特提出的解决方案将起作用(即替换'by'')。

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();

#2

Using parameters protects against sql injection, and makes the ' problems qo away.

使用参数可以防止sql注入,并使'问题慢慢消失。

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类似。

#1