Android databse SqlLite and SQL Injection

SQL Injection is one of my favourite topics, sad but true. I think SQL injection is a very clever way of deceiving apps. This then encourages developers to be equally clever and thorough in their jobs to protect their data. I'm not for a moment endorsing such practices, what I'm saying is good developers should be aware of the possibilities.

In my last blog article I wrote a very brief introduction to SqlLite in Android.
http://webdeveloperpadawan.blogspot.ca/2013/07/android-app-using-database-sqllite.html

In this I deliberately left in some weak code so I could write a follow up on SQL injection. Look carefully at this string:

String DATABASE_ADD_USER  = "insert into "  + TABLE_NAME + " (muppetname) values (";

Obviously very susceptible to injection. If a user wrote

'; drop table theusers; --

We'd have a problem. Now fortunately database.execSQL() doesn't allow us to run more than one sql command in one execution. From developer.android.com:

Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

In some ways this limits the potential of SQL injection, but we don't want to rely on that and it is horribly bad practice. Lets try it on our app and see what happens, paste the above (drop table statement) into the pop-up that appears when you click the btnFuzzy. You'll see your app crash and a SQLException in logCat.

In coldFusion we have a very useful tag called cfqueryparam: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html This simple tag gives us basic protection from injection and even allows us to specify the datatype. Although we should always clean our user input anyway :) Android has something very similar, allowing us to insert data using database.insert.

All we have to do is slightly ammend our insert user function:

 public void addNewUserProperly(SQLiteDatabase database, String name){
     ContentValues values = new ContentValues();
     values.put("muppetname", name);
     database.insert(TABLE_NAME, null, values);
 }

Now you'll see we've hardly changed anything, we use contentValues to store a set of values, the column name and the value. Then we insert that with database.insert. Super simple. If we re-run this and try our SQL injection trick, no more critical error :)

Hope this is of some use.