SQL injection content_copy arrow_drop_down

OWASP category: MASVS-CODE: Code Quality ([Link]

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
([Link] 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@[Link]' AND order_number = '2515

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@[Link]' 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.
KotlinJava (#java)
(#kotlin)

// 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

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:

KotlinJava (#java)
(#kotlin)

fun validateOrderDetails(email: String, orderNumber: String): Boolean {

val cursor = [Link](
"select * from purchases where EMAIL = ? and ORDER_NUMBER = ?",
arrayOf(email, orderNumber)
)

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

cursor?.close()

return bool
}

Use PreparedStatement objects

The PreparedStatement (/reference/java/sql/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:

KotlinJava (#java)
(#kotlin)

val pstmt: PreparedStatement = [Link](

"UPDATE EMPLOYEES SET ROLE = ? WHERE ID = ?").apply {
setString(1, "Barista")
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.

KotlinJava (#java)
(#kotlin)

val db: SQLiteDatabase = [Link]()

// 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 = [Link](

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.
[Link]()
}

Use properly configured SQLiteQueryBuilder

Developers can further protect applications by using SQLiteQueryBuilder

(/reference/android/database/sqlite/SQLiteQueryBuilder), a class that helps build queries to be sent
to SQLiteDatabase objects. Recommended configurations include:

setStrict() (/reference/android/database/sqlite/SQLiteQueryBuilder#setStrict(boolean)) mode

for query validation.

setStrictColumns()
(/reference/android/database/sqlite/SQLiteQueryBuilder#setStrictColumns(boolean)) to validate
that columns are allow-listed in the setProjectionMap.

setStrictGrammar()
(/reference/android/database/sqlite/SQLiteQueryBuilder#setStrictGrammar(boolean)) to limit
subqueries.

Use Room library

The [Link] 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 (/training/basics/data-storage/room)

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 (/training/data-storage/sqlite)

Content provider basics (/guide/topics/providers/content-provider-basics)

Saving data in local database using Room (/training/data-storage/room)

Security tips (/training/articles/security-tips)

Kotlin SQL injection prevention guide

([Link]

Content and code samples on this page are subject to the licenses described in the Content License (/license).
Java and OpenJDK are trademarks or registered trademarks of Oracle and/or its affiliates.

Last updated 2024-09-24 UTC.