0

If the string company contains an apostrophe, this will cause an error.

Example: Company name like "William's store".

How to build an SQLite Query that will handle this kind of problem using SQLite-Net api.


I am using SQLite-Net api and I tried both and they did not work.

In SQLite-Net api, I think there is no Parameters. What other alternative that I can use?


private async void GetCustomerVATGroup(string Company)
{


1) 

string strChkName = Company.Replace("'", "''"); // or Company.Replace("'","\'");

var allUsers = await db.QueryAsync<Customer>(

"Select * From Customer Where CompanyName ='" + strChkName + "'");


2) 

var allUsers = await db.QueryAsync<Customer>(

"Select * From Customer Where CompanyName =''" + Company + "''");


}
MilkBottle
  • 4,242
  • 13
  • 64
  • 146
  • sqllite-net does have parameters: `QueryAsync (string sql, params object[] args)` I added a code snippet on my answer for reference – giammin Jan 24 '14 at 15:17

1 Answers1

6

From SqlLite Documentation:

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. ... A literal value can also be the token "NULL".

So you can escape it with a string replace but the best way to query a db is to avoid string concatenation for avoiding Sql injection.

The best practice is to use Parameterized Querys

In sqllite-net they are passed as argument with the method:

var allUsers = await db.QueryAsync<Customer>("Select * From Customer Where CompanyName ='?'", Company);
Community
  • 1
  • 1
giammin
  • 18,620
  • 8
  • 71
  • 89
  • I m using SQLite-Net api. Try both and not sure if this is correct: string strCpy = "William's store"; string strNewCpy = strCpy.Replace("'" ,"\'") ? – MilkBottle Jan 23 '14 at 09:51
  • @MilkBottle you have to escape as sql wants and not as the c# way: `strCpy.Replace("'","''")` – giammin Jan 23 '14 at 09:53
  • I am not familiar with this parameter. What If I have more parameters in the Select-statement? How to get the result out? I will try it soon. Appreciate if you have any example of this type. – MilkBottle Jan 29 '14 at 06:18
  • 1
    @MilkBottle You can use `?1` `?2` and pass a list of parameters – giammin Jan 29 '14 at 08:45