CODEWITHSUNDEEP
androidsqlite
Janneman96
Janneman96

Reputation: 505

How to prevent sql injection in loop through VALUES

I'm trying to insert an ArrayList of Strings to a table in an sqlite database.

I've written a loop that adds Strings from an ArrayList to an sqlite database query using android.database.sqlite.SQLiteOpenHelper.

The query is vulnerable for sql injection and is not optimal. I've found these links using PreparedStatement, using SQLiteStatement. I haven't found examples of preventing sql injection in a loop like this. For demonstration's sake, I've put the initialization of the ArrayList in the example. 

ArrayList<String> descriptions = new ArrayList<>();

SQLiteDatabase db = this.getWritableDatabase();
String addBulkQuery = "INSERT INTO " + TABLE_DESCRIPTIONS + "(" + KEY_ID + ", " + KEY_DESCRIPTION + ") VALUES";
if (descriptions.size() > 0) {
    addBulkQuery += "(" + id + ", " + descriptions.get(0) + ")";
    for (int i = 1; i < descriptions.size(); i++) {
        addBulkQuery += ", (" + id + ", " + descriptions.get(i) + ")";
    }
}

What would be the most secure solution to add an ArrayList of Strings or Objects to the sqlite database?

Upvotes: 0

Views: 1668

Answers (1)

Gavin Harris
Gavin Harris

Reputation: 702

Try using a SQLiteStatement, something like the below:

ArrayList<String> descriptions = new ArrayList<>();
ArrayList<String> bindArgs = new ArrayList<>(descriptions.size() * 2);

String values = "";
if (descriptions.size() > 0) {

    values += "(?,?)";
    bindArgs.add(id);
    bindArgs.add(descriptions.get(0));

    for (int i = 1; i < descriptions.size(); i++) {
        values += ",(?,?)";
        bindArgs.add(id);
        bindArgs.add(descriptions.get(i));
    }
}

String addBulkQuery =  String.format("INSERT INTO %s (%s, %s) VALUES %s", TABLE_DESCRIPTIONS, KEY_ID, KEY_DESCRIPTION, values);

SQLiteStatement statement = db.compileStatement(addBulkQuery);

statement.bindAllArgsAsStrings( bindArgs.toArray(new String[0]) );

statement.executeInsert();

I have assumed the id is a String and therefore I can use the statement.bindAllArgsAsStrings method to bind the variables to the statement. (Sorry I also rewrote your Strings to use String.format, I find it easier to read, you can ignore if you like)

OLD Answer:

Look at using Bind Variables. Rewrite your SQL so that anywhere you accept user input, enter in a "?" and then use db.execSQL(sqlString, Arguments);

So you want the SQL to look like (before you execute):

INSERT INTO <tableName> (<key_id>, <key_description>)
VALUES (?, ?), (?, ?), ...

You will then need to flatten your ArrayList into an Object Array interspersed with whatever id is!

Upvotes: 1

Related Questions