How do I get around the "'" problem in sqlite and c#?

Question

I'm working in Microsoft Visual C# 2008 Express with Sqlite.

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

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

Answer 1

You have to use two apostrophes in a row to replace the apostrophe.

Answer 2

The solution presented by Robert will work (i.e. replacing ' 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();

Answer 3

Agree with Robert's comment... you are WIDE OPEN for SQL Injection... just to test what COULD happen, try something like this for input text and see what happens to your table content (or actually, create a bogus table with a few records and try against that...

textbox entry has "; truncate table myTable"

The semi-colon terminates the prior instruction, and can then run the next which in this case, would delete all records in your table...

Although simple in its context, any SQL WITHOUT using parameters is dangerous..

Answer 4

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

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.

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.