本文介绍了IN子句和占位符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我试图在Android的做下面的SQL查询:

I'm attempting to do the following SQL query within Android:

    String names = "'name1', 'name2";   // in the code this is dynamically generated

    String query = "SELECT * FROM table WHERE name IN (?)";
    Cursor cursor = mDb.rawQuery(query, new String[]{names});

不过,Android不正确的值替换问号。我可以做到以下几点,但是,这并没有对SQL注入保护:

However, Android does not replace the question mark with the correct values. I could do the following, however, this does not protect against SQL injection:

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

如何才能解决这个问题,可以使用IN子句?

How can I get around this issue and be able to use the IN clause?

推荐答案

一个字符串形式的?,?,?,?可以是动态创建的串并安全地投入的原始SQL查询(因为它是一个受限制的形式不包含外部数据),然后该占位符可作为正常

A string of the form "?, ?, ..., ?" can be a dynamically created string and safely put into the original SQL query (because it is a restricted form that does not contain external data) and then the placeholders can be used as normal.

考虑一个函数字符串makePlaceholders(INT LEN)返回 len个问题痕用逗号,然后分离:

Consider a function String makePlaceholders(int len) which returns len question-marks separated with commas, then:

String[] names = { "name1", "name2" }; // do whatever is needed first
String query = "SELECT * FROM table"
    + " WHERE name IN (" + makePlaceholders(names.length) + ")";
Cursor cursor = mDb.rawQuery(query, names);

只要确保准确地传递尽可能多的值的地方。主机的默认最大极限参数中的SQLite是999 - 至少在一个正常的版本,不知道安卓)

Just make sure to pass exactly as many values as places. The default maximum limit of host parameters in SQLite is 999 - at least in a normal build, not sure about Android :)

快乐编码。

下面是一个实现:

String makePlaceholders(int len) {
    if (len < 1) {
        // It will lead to an invalid query anyway ..
        throw new RuntimeException("No placeholders");
    } else {
        StringBuilder sb = new StringBuilder(len * 2 - 1);
        sb.append("?");
        for (int i = 1; i < len; i++) {
            sb.append(",?");
        }
        return sb.toString();
    }
}

这篇关于IN子句和占位符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-12 08:21