SQLite database and SQL injection attack

Software developers are quite familiar with SQL – a standard language for accessing database. In particular, developers make use SQL to manipulate data on Mysql server, SQL server etc.,. Leveraging the advantages of SQL, Android OS provides developers SQLite an open source SQL database with full features of a relational database. This mechanism enables Android developers to manage their app’s database such as contact information or local data in general. In this blog, we will not talk about how to create, maintain or upgrade a SQLite database. Instead, we will discuss SQL injection attack in the scope of SQLite database as I mentioned in my previous post.

 

Similar to traditional SQL database, four most popular operations that come with SQLite are: Select, Insert, Update, Delete

  1. Select (vulnerable to SQL injection attack)
    • Cursor c = db.rawQuery(“SELECT * FROM table_name WHERE name = ‘”+name+“‘”, null);
  2. Insert/Delete/Update (vulnerable to SQL injection attack)
    • db.execSQL(“UPDATE DB_TABLE SET YOUR_COLUMN='”+ value + “‘ WHERE id=” + id);

The above examples  have one thing in common that is, they are vulnerable to SQL injection attack since part of the query (name and id for the first and second example respectively) comes from user’s raw input. A malicious user can provide a string that escapes your query/command with his arbitrary SQL query/command. More precisely, in case of the “select” example, he can provide a string with the value of ” ‘ or 1=1;” all records will be returned regardless of the column ‘name’. Similarly, for the “update” example, he does not need to provide the exact id, he can rather provide a string with the value of ” or 1 =1″, all the record will be updated*.

With these concerns, developers are highly recommended to build SQLite query/command following the bellow examples in which your SQLite query/command are protected against SQL injection attack**.

  1. Select
    • Cursor c = db.rawQuery(“SELECT * FROM table_name WHERE name = ‘?‘”, new String[]{name});
  2. Insert/Delete/Update 
    • db.execSQL(“UPDATE DB_TABLE SET YOUR_COLUMN=’?’ WHERE id=?”, new String[]{name, id});

 

While SQL database brings developers many advantages (easily accessing, manipulating data), developers should always take actions to prevent insecurity implication. In this post, we have discussed the SQL injection attack specifically in Android’s SQLite. We have seen that SQL injection attack is not only popular in desktop or web applications but also possible in Android’s.

 

———————————————-

*  It also depends on the database settings.

** Alternatively, you can build your query/command by using the following APIs:

  • db.query(table_name, columns, selection, selectionArgs, groupBy, having, orderBy, limit)
  • db.delete(String table, String whereClause, String[] whereArgs)
  • db.update(String table, ContentValues values, String whereClause, String[] whereArgs)
  • db.insert(String table, String nullColumnHack, ContentValues values)

These are also secure against SQL Injection attack. However, they are quite different from traditional SQL query/command, that is the reason why I prefer raw query/execSQL with ? (question mark) as place holders which is similar to PreparedStatement of traditional SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *