SecretNotes App: SQL injection with the Room Persistence Library

During Google I/O 2017 the Room Persistence Library was announced. Room is a new library built into Android that provides an abstraction on top of SQLite to allow easier database access without losing SQLite's functionality. You can learn more about Room Persistence Library on: https://developer.android.com/training/data-storage/room.

On the other hand, SQL injection attacks are well documented on the web, indeed, OWASP rank injection as their number 1 security concern in 2017. SQLite built into Android is also susceptible to client-side SQL injection attacks, putting any data you store locally at risk. It would be interesting to look at Room to see if it is possible to perform SQL injection attacks when using it.

User Interface (UI) and Feature of Secret Notes

User Interface (UI) design link:
https://www.figma.com/design/6ZlyeTgwAIpGikScmToGjO/SecretNotesApp?node-id=0-1&m=dev&t=yLPFY6iwxsKoRwOo-1

SQL Injection Implementation Code

MainActivity.kt

binding?.etQuery?.setOnEditorActionListener { textView, i, _ ->
    return@setOnEditorActionListener when (i) {
        EditorInfo.IME_ACTION_SEARCH -> {
            val query = textView.text.toString()
            when (isValidSearch(query)) {
                true -> {
                    val searchQuery = "%$query%"
                    mainViewModel.searchNote(searchQuery).observe(this) {
                        adapter.setListNotes(it)
                    }
                    binding?.coordinatorLayout?.visibility = View.VISIBLE
                    isNoteEmpty(false)
                    hideKeyboard()
                }
                false -> {
                    val searchQuery = "%$query%"
                    val cursor: Cursor = noteDatabase.query(
                        "SELECT * FROM note WHERE title LIKE '$searchQuery'",
                        null
                    )
                    cursor.moveToFirst()
                    var result: String? = ""
                    while (!cursor.isAfterLast) {
                        result += """
                                    id: ${cursor.getInt(0)}
                                    pin: ${cursor.getString(1)}
                                    title: ${cursor.getString(2)}
                                    content: ${cursor.getString(3)}
                                    date: ${cursor.getString(4)}
                                    """.trimIndent()
                        cursor.moveToNext()
                    }
                    cursor.close()
                    binding?.coordinatorLayout?.visibility = View.GONE
                    binding?.scrollView?.visibility = View.VISIBLE
                    binding?.tvResult?.text = result
                    isNoteEmpty(false)
                    hideKeyboard()
                }
            }
            true
        }
        else -> false
    }
}

How SQL injection Works on This App

First, we have to create a New Note with Add Note Button.

Then, Create a note and PIN numbers.

After that, type ' or 'x' = 'x then click Search Button on the keyboard bar.

Finally, you can see content of the note without entering PIN numbers.

This project is based on Medium.com article, written by Matthew Dolan:
https://appmattus.medium.com/android-security-sql-injection-with-the-room-persistence-library-69f4e286960f