SQL injection

OWASP category: MASVS-CODE: Code Quality

Overview

SQL injection exploits vulnerable applications by inserting code into SQL statements to access underlying databases beyond their intentionally-exposed interfaces. The attack can expose private data, corrupt database contents, and even compromising of backend infrastructure.

SQL can be vulnerable to injection via queries that are created dynamically by concatenating user input before execution. Targeting web, mobile and any SQL database application, SQL injection usually features in the OWASP Top Ten of web vulnerabilities. Attackers used the technique in several high-profile breaches.

In this basic example, an unescaped input by a user into an order number box can be inserted into the SQL string and interpreted as the following query:

SELECT * FROM users WHERE email = 'example@example.com' AND order_number = '251542'' LIMIT 1

Such code would generate a database syntax error in a web console, which shows the application may be vulnerable to SQL injection. Replacing the order number with 'OR 1=1– means authentication can be achieved since the database evaluates the statement to True, as one always equals one.

Similarly, this query returns all rows from a table:

SELECT * FROM purchases WHERE email='admin@app.com' OR 1=1;

Content providers

Content providers offer a structured storage mechanism that can be limited to an application or exported for sharing with other apps. Permissions should be set based on the principle of least privilege; an exported ContentProvider can have a single specified permission for reading and writing.

It is worth noting that not all SQL injections lead to exploitation. Some content providers already grant readers complete access to the SQLite database; being able to execute arbitrary queries yields little advantage. Patterns that can represent a security issue include:

• Multiple content providers sharing a single SQLite database file.
  • In this case, each table may be intended for a unique content provider. A successful SQL injection in one content provider would grant access to any other tables.
• A content provider has multiple permissions for content within the same database.
  • SQL injection in a single content provider that grants access with different permission levels could lead to local bypass of security or privacy settings.

Impact

SQL injection can expose sensitive user or application data, overcome authentication and authorization restrictions, and leave databases vulnerable to corruption or deletion. Impacts can include dangerous and lasting implications for users whose personal data has been exposed. Providers of apps and services risk losing intellectual property or user trust.

Mitigations

Replaceable parameters

Using ? as a replaceable parameter in selection clauses and a separate array of selection arguments binds the user input directly to the query rather than interpreting it as part of a SQL statement.

// Constructs a selection clause with a replaceable parameter.
val selectionClause = "var = ?"

// Sets up an array of arguments.
val selectionArgs: Array<String> = arrayOf("")

// Adds values to the selection arguments array.
selectionArgs[0] = userInput

// Constructs a selection clause with a replaceable parameter.
String selectionClause =  "var = ?";

// Sets up an array of arguments.
String[] selectionArgs = {""};

// Adds values to the selection arguments array.
selectionArgs[0] = userInput;

The user input is bound directly to the query rather than being treated as SQL, preventing code injection.

Here’s a more elaborate example showing a shopping app’s query to retrieve purchase details with replaceable parameters:

fun validateOrderDetails(email: String, orderNumber: String): Boolean {
    val cursor = db.rawQuery(
        "select * from purchases where EMAIL = ? and ORDER_NUMBER = ?",
        arrayOf(email, orderNumber)
    )

    val bool = cursor?.moveToFirst() ?: false
    cursor?.close()

    return bool
}

public boolean validateOrderDetails(String email, String orderNumber) {
    boolean bool = false;
    Cursor cursor = db.rawQuery(
      "select * from purchases where EMAIL = ? and ORDER_NUMBER = ?", 
      new String[]{email, orderNumber});
    if (cursor != null) {
        if (cursor.moveToFirst()) {
            bool = true;
        }
        cursor.close();
    }
    return bool;
}

Use PreparedStatement objects

The PreparedStatement interface precompiles SQL statements as an object which can then be executed efficiently multiple times. PreparedStatement uses ? as a placeholder for parameters, which would make the following compiled injection attempt ineffective:

WHERE id=295094 OR 1=1;

In this case, 295094 OR 1=1 statement is read as the value for ID, likely yielding no results, whereas a raw query would interpret the OR 1=1 statement as another part of the WHERE clause. The example below shows a parametrized query:

val pstmt: PreparedStatement = con.prepareStatement(
        "UPDATE EMPLOYEES SET ROLE = ? WHERE ID = ?").apply {
    setString(1, "Barista")
    setInt(2, 295094)
}

PreparedStatement pstmt = con.prepareStatement(
                                "UPDATE EMPLOYEES SET ROLE = ? WHERE ID = ?");
pstmt.setString(1, "Barista")   
pstmt.setInt(2, 295094)

Use query methods

In this longer example, the selection and selectionArgs of the query() method are combined to make a WHERE clause. Since the arguments are provided separately, they are escaped before their combination, preventing SQL injection.

val db: SQLiteDatabase = dbHelper.getReadableDatabase()
// Defines a projection that specifies which columns from the database
// should be selected.
val projection = arrayOf(
    BaseColumns._ID,
    FeedEntry.COLUMN_NAME_TITLE,
    FeedEntry.COLUMN_NAME_SUBTITLE
)

// Filters results WHERE "title" = 'My Title'.
val selection: String = FeedEntry.COLUMN_NAME_TITLE.toString() + " = ?"
val selectionArgs = arrayOf("My Title")

// Specifies how to sort the results in the returned Cursor object.
val sortOrder: String = FeedEntry.COLUMN_NAME_SUBTITLE.toString() + " DESC"

val cursor = db.query(
    FeedEntry.TABLE_NAME,  // The table to query
    projection,            // The array of columns to return
                           //   (pass null to get all)
    selection,             // The columns for the WHERE clause
    selectionArgs,         // The values for the WHERE clause
    null,                  // Don't group the rows
    null,                  // Don't filter by row groups
    sortOrder              // The sort order
).use {
    // Perform operations on the query result here.
    it.moveToFirst()
}

SQLiteDatabase db = dbHelper.getReadableDatabase();
// Defines a projection that specifies which columns from the database
// should be selected.
String[] projection = {
    BaseColumns._ID,
    FeedEntry.COLUMN_NAME_TITLE,
    FeedEntry.COLUMN_NAME_SUBTITLE
};

// Filters results WHERE "title" = 'My Title'.
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };

// Specifies how to sort the results in the returned Cursor object.
String sortOrder =
    FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";

Cursor cursor = db.query(
    FeedEntry.TABLE_NAME,   // The table to query
    projection,             // The array of columns to return (pass null to get all)
    selection,              // The columns for the WHERE clause
    selectionArgs,          // The values for the WHERE clause
    null,                   // don't group the rows
    null,                   // don't filter by row groups
    sortOrder               // The sort order
    );

Use properly configured SQLiteQueryBuilder

Developers can further protect applications by using SQLiteQueryBuilder, a class that helps build queries to be sent to SQLiteDatabase objects. Recommended configurations include:

• setStrict() mode for query validation.
• setStrictColumns() to validate that columns are allow-listed in the setProjectionMap.
• setStrictGrammar() to limit subqueries.

Use Room library

The android.database.sqlite package provides APIs necessary for using databases on Android. However, this approach requires writing low-level code and lacks compile-time verification of raw SQL queries. As data graphs change, affected SQL queries need to be updated manually – a time-consuming and error-prone process.

A high-level solution is to use the Room Persistence Library as an abstraction layer for SQLite databases. Room’s features comprise:

• A database class which serves as the main access point for connecting to the app’s persisted data.
• Data entities representing the database’s tables.
• Data access objects (DAOs), which provide methods the app can use to query, update, insert, and delete data.

Room’s benefits include:

• Compile-time verification of SQL queries.
• Reduction of error-prone boilerplate code.
• Streamlined database migration.

Best practices

SQL injection is a potent attack against which it can be difficult to be entirely resilient, particularly with large and complex applications. Additional security considerations should be in place to limit the severity of potential flaws in data interfaces, including:

• Robust, one-way and salted hashes to encrypt passwords:
  • 256-bit AES for commercial applications.
  • 224- or 256-bit public key sizes for elliptic curve cryptography.
• Limiting permissions.
• Precisely structuring data formats and verifying that the data conforms to the expected format.
• Avoiding storing personal or sensitive user data where possible (for example, implementing application logic by hashing rather than transmitting or storing data).
• Minimising APIs and third-party applications that access sensitive data.

Resources

• Save data using SQLite

• Content provider basics

• Saving data in local database using Room

• Security tips

• Kotlin SQL injection prevention guide