0

I am using select statement to retrieve certain data from sqlite. The result contains ' char which results error when selecting data. How can I ignore it?

Below is my sql statement:

string query = string.Format("select * from TableA where [Col]='{0}'",suraTName) 

Statement: select * from TableA where [Col]='An-Naazi'aat'

How to ignore ' char and have the correct the result?

Thanks!

ARH
  • 1,566
  • 3
  • 25
  • 56

1 Answers1

2

You should use a parameterized query like this

string query = "select * from TableA where [Col]=@colValue";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@colValue", suraTName);

In this way the job to correctly quote your value is passed to the SQLite provider that knows better. Also, there is no possibility of Sql Injections

Of course this is possible if you are using a ADO.NET provider like the one from System.Data.SQLite, if you are using other systems to retrieve your data, I can only suggest to double the single quote in your query

suraTName = suraTName.Replace("'", "''");
string query = string.Format("select * from TableA where [Col]='{0}'",suraTName);

But it is very risky option

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Thanks, There is no Parameters in sqlite (cmd.Parameters) – ARH Feb 12 '14 at 10:59
  • Are you sure? What version or what provider are you using, because here http://system.data.sqlite.org/index.html/artifact/dad394fdf2af9b24 seems that the Parameters collection is available being a class derived by DbCommand. – Steve Feb 12 '14 at 11:04
  • Oh, I am not using System.Data.Sqite (Where this comes from). I am using SQLite only. – ARH Feb 12 '14 at 11:05
  • Then I can only suggest to try to double your single quote. But it is very risky – Steve Feb 12 '14 at 11:08
  • I may not be able to user that, Just a question: Am I using the wrong dll? Though I installed SqliteX86 – ARH Feb 12 '14 at 11:10
  • 1
    It is not clear to me how do you interact with the SQLite database without a ADO.NET provider (SQLiteConnection, SQLiteCommand, SQLiteDataReader etc.). I strongly suggest to use an ADO.NET provider to interact with the database – Steve Feb 12 '14 at 11:15
  • Oh I didn't mention, I am using sqlite in windows phone8 and there is no such in windows phone8. You need to add the c++ wrapper class to extend using sqlite database. (http://blog.rajenki.com/2013/01/sqlite-on-windows-8-and-windows-phone-8/) – ARH Feb 13 '14 at 04:55
  • Did you try the workaround with the Replace call? Also, adding the tag WinPhone could help to get attention from more experts on the subject – Steve Feb 13 '14 at 07:48
  • 1
    Thanks, I sorted it out by passing ID instead of file name. That was the easiest solution i could follow. – ARH Feb 13 '14 at 07:55
  • Yes, rule #1 - Keep It Simple and Stupid - bye – Steve Feb 13 '14 at 07:57