How to Safely Parameterize the IN Clause in Android SQL Queries?

IN clause and placeholder conundrum in Android SQL queries

In Android, executing SQL queries often involves using parameterized placeholders to prevent SQL injection vulnerabilities. This occurs when an IN clause is used. Consider the following query:

<code>String names = "name1', 'name2";   // 动态生成
String query = "SELECT * FROM table WHERE name IN (?)";
Cursor cursor = mDb.rawQuery(query, new String[]{names});</code>

However, Android cannot replace the question mark with the provided value. Alternatively, you can choose:

<code>String query = "SELECT * FROM table WHERE name IN (" + names + ")";
Cursor cursor = mDb.rawQuery(query, null);</code>

While this approach eliminates the SQL injection threat, it fails to properly parameterize the query.

Parameterized IN clause

To achieve parameterization while avoiding the risk of injection, consider using strings with placeholder patterns:

<code>String query = "SELECT * FROM table WHERE name IN (" + makePlaceholders(names.length) + ")";
Cursor cursor = mDb.rawQuery(query, names);</code>

The

makePlaceholders(len) function generates a string containing the required number of question marks, separated by commas. This allows queries to be safely inserted without affecting parameterization.

Implementation of makePlaceholders(len):

<code>String makePlaceholders(int len) {
    if (len < 1) {
        throw new IllegalArgumentException("Length must be > 0");
    }
    StringBuilder sb = new StringBuilder(len * 2 - 1);
    sb.append("?");
    for (int i = 1; i < len; i++) {
        sb.append(",?");
    }
    return sb.toString();
}</code>

Please note that SQLite generally supports up to 999 host parameters, except in some specific Android versions.

The above is the detailed content of How to Safely Parameterize the IN Clause in Android SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!