SQL query is not working when I am using Equals query-CodePudding

Database :

if (DatabaseUtils.queryNumEntries(foodDBHlpr.getWritableDatabase(),FoodDatabaseHelper.TABLE_NAME ) < 1) {

            foodDBHlpr.insertFood("John", "Mashed potato");
            foodDBHlpr.insertFood("Johnson", "Pineapple pie");
           
}

SQL query

public Cursor getFoodsWithProvidedFood(String provided_food) {
        return this.getWritableDatabase().query(
                TABLE_NAME,
                null,
                COL_2   " LIKE '%"   provided_food.replace("'",  "''")   "%' ",
                null,
                null,
                null,
                null
        );

mCsr = foodDBHlpr.getFoodsWithProvidedFood(alfabetsearch);
      
        while (mCsr.moveToNext()) {
          PreferredFood=mCsr.getString(mCsr.getColumnIndex(FoodDatabaseHelper.COL_3));

        }

When I use "Like " query everything is working fine, however when I look for John, it shows both results for John as Johnson as they are similar. But when I search John it must only show the result for John ( not Johnson).

So far I tried :

1. COL_2   "=" ""   provided_food.replace("'",  "''") ",
 1. COL_2   "="   provided_food.replace("'",  "''")   " ",

But it is not working LogCat :

E/SQLiteLog: (1) no such column: John in "SELECT * FROM dataset WHERE Food=John"

    --------- beginning of crash
2022-03-27 18:44:09.867 7571-7639/com.example.humanuz E/AndroidRuntime: FATAL EXCEPTION: Thread-3
    Process: com.example.humanuz, PID: 7571
    android.database.sqlite.SQLiteException: no such column: John (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM dataset WHERE Food=John
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1045)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:652)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1545)
        at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1392)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1263)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1431)
        at com.example.humanuz.FoodDatabaseHelper.getFoodsWithProvidedFood(FoodDatabaseHelper.java:60)
        at com.example.humanuz.MainActivity.Search(MainActivity.java:3805)

Please give me a hint.

CodePudding user response：

This is becuase of how SQL process your Query. In your when clause, SQL is referring John as a table column instead of String. As a result, SQL is trying to find John as a table column and failing. This can be fixed by simply adding quotes around the value in your codebase as follow:

COL_2   "'"   provided_food.replace("'",  "''")   "'"

COL_2   "\""   provided_food.replace("'",  "''")   "\""

The Like query only worked because you were placing the '(single quote) around the value. Similarly, once you put some quote (single or double) around the value, it would work.

CodePudding user response：

You should not concatenate Java strings to your sql code.
This is not safe and it is not recommended.

Use the 3d argument of query() to construct the sql statement's WHERE cause with ? placeholder(s) for the parameters that you want to pass and the 4th argument which should be an array of strings with the value(s) of the parameters:

public Cursor getFoodsWithProvidedFood(String provided_food) {
    String selection = COL_2   " LIKE '%' || ? || '%'";
    String[] selectionArgs = new String[] {provided_food};
    return this.getWritableDatabase().query(
        TABLE_NAME,
        null,
        selection,
        selectionArgs,
        null,
        null,
        null
    );
}

And if you want to use the = operator change the selection string to:

String selection = COL_2   " = ?";

This way, you don't worry about single quotes in the parameter's value, because they are taken care of by the method query().