Agfdhyk

I have a method in my MainActivity resetSortIndexes that runs a save() in the model class that runs an SQLite database "execSQL()" method. Now I've read that I should not be using execSQL() to avoid SQL injection attacks and that I should not be using rawQuery() for any INSERT operation. So should I use ContentValues() and insert()?

MainActivity.java

...

public static void resetSortIndexes() {



    int index = allList.size();

    for (ListItem s : allList) {

        s.setSortorder(index);

        s.save(sqLiteDB); 

        index--;

    }

}   



ListItem.java

...

public void save(SQLiteDB helper){



    String sql = "INSERT OR REPLACE INTO " + TABLE_NAME + "(_id,type,typecolor,todo,note1,note2," +

            "duedatentime,timestamp,notiftime,notiftime2,randint,sortorder,listone,listtwo," +

            "listthree,listfour,listfive,listsix,listseven,listeight,listnine,listten,listeleven," +

            "listtwelve,listthirteen,listfourteen,listfifteen,listsixteen,listseventeen," +

            "listeighteen,listnineteen,listtwenty) VALUES" +

            "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    // The object parameters from the ListItem class.

    Object params = new Object{_id,_type,_typecolor,_todo,_note1,_note2,_duedatentime,

            _timestamp,_notiftime,_notiftime2,_randint,_sortorder,_listone,_listtwo,

            _listthree,_listfour,_listfive,_listsix,_listseven,_listeight,_listnine,

            _listten,_listeleven,_listtwelve,_listthirteen,_listfourteen,_listfifteen,

            _listsixteen,_listseventeen,_listeighteen,_listnineteen,_listtwenty};

    // A method in the SQLiteDB class.

    helper.executeQuery(sql,params);

}



SQLiteDB.java

...

public void executeQuery(String sql, Object params) {



    SQLiteDatabase db = getReadableDatabase();



    db.beginTransaction();

    try {

        **db.execSQL(sql, params);**



    db.setTransactionSuccessful();

    } finally {

        db.endTransaction();

    }

    if(db.isOpen()) {

        db.close();

    }

} 

You can use the method insertWithOnConflict(TABLE_NAME,null,contentvalues,SQLiteDatabase.CONFLICT_REPLACE);

Where contenvalues is a ContenValues populated using it's put(column_name,value) method for each value to be inserted.

The code would be along the lines of :-

ContentValues cv = new Contentvalues();

cv.put("_id",the_id);

cv.put("type",the_type);

..... etc

long result =  helper.insertWithOnConflict(TABLE_NAME,null,cv,SQliteDatabase.CONFLICT_REPLACE);

• result will be the rowid of the inserted row or -1.

insertWithOnConflict

CONFLICT_REPLACE

P.S. using execSQL as you have, would offer protection from SQL injection as the SQL itself is not subject to user input and the values are bound/passed as arguments.