Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete multiple rows in SQLITE (android) [closed]

Tags:

android

sqlite

I want to delete all the rows from the table that match the ids in an array. I can do that by either of the below 2 methods (both are working). Can you please advice which one is better?

METHOD 1:

public void deleteRec(String[] ids) { //ids is an array
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NAME, KEY_ID+" IN (" + new String(new char[ids.length-1]).replace("\0", "?,") + "?)", ids);
        db.close();
    }

METHOD 2:

public void deleteRec(String[] ids) { //ids is an array
        String allid = TextUtils.join(", ", ids);
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL(String.format("DELETE FROM "+TABLE_NAME+" WHERE "+KEY_ID+" IN (%s);", allid));
       db.close();
    }
like image 759
abdfahim Avatar asked Oct 02 '22 11:10

abdfahim


1 Answers

Just forget about second method!

Your ids are all strings from numbers (otherwise SQL would fail), but for generic string data, passing data into SQL statements is never a good idea. Make you application vulnerable to SQL injection:

String.format("DELETE FROM t WHERE ID='%s', "1' AND 1=1 --")
// = "DELETE FROM t WHERE ID='1' AND 1=1 --'" => would delete all data!

and may fail your SQL statements:

String.format("DELETE FROM t WHERE v='%s', "It's me!")
// = "DELETE FROM t WHERE v='It's me!'" => syntactically incorrect (quote not escaped)!

EDIT: As ids are supplied as string array and probably KEY_ID refers a INT column, method 1 should be adapted to:

db.delete(TABLE_NAME, "CAST("+KEY_ID+" AS TEXT) IN (" + new String(new char[ids.length-1]).replace("\0", "?,") + "?)", ids);
like image 140
LS_ᴅᴇᴠ Avatar answered Oct 07 '22 17:10

LS_ᴅᴇᴠ