android: how do I replace SQLite execSQL() to avoid injection attack?

Question

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

MikeT · Accepted Answer · 2018-11-19 04:51:52Z

1

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.

answered Nov 19, 2018 at 4:51

MikeT

57.9k16 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AJW Over a year ago

Ok, will give it a try. What is the advantage to using insertWithOnConflict rather than insert()?

AJW Over a year ago

Would you still recommend replacing the execSQL() with the ContentValues code?

MikeT Over a year ago

It will do INSERT OR REPLACE (or handle CONFLICT's (not Foreign Key conflicts)) instead of only INSERT OR IGNORE as insert does.

MikeT Over a year ago

uhhm, I wouldn't necessarily recommend. It's not as if a bolt of lightning will come down from above and strike you down. Personally I'd use the convenience methods (not execSQL/rawQuery) mainly because they, once you get the gist of them, are generally simpler to code. However, sometimes you have to, as they do have some limitations.

AJW Over a year ago

Ha, I want to avoid the lightning bolts too. Answer upvoted and accepted. Cheers.

Collectives™ on Stack Overflow

android: how do I replace SQLite execSQL() to avoid injection attack?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related