How to build a SQLite Query to handle string conta

问题:

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 + "''");


}

回答1:

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