在Android中具有准备好的语句的查询? [英] Queries with prepared statements in Android?

问题描述

在Android中，android.database.sqlite.SQLiteStatement允许我在SQLite中使用准备好的语句来避免注入攻击.它的execute方法适用于创建/更新/删除操作，但似乎没有任何方法可用于返回游标等的查询.

In Android, android.database.sqlite.SQLiteStatement allows me to use prepared statements in SQLite to avoid injection attacks. Its execute method is suitable for create/update/delete operations, but there does not seem to be any method for queries that returns a cursor or the like.

现在在iOS中，我可以创建类型为sqlite3_stmt*的准备好的语句并将其用于查询，因此我知道这不是SQLite的限制.如何在Android中使用已准备好的语句执行查询?

Now in iOS I can create prepared statements of type sqlite3_stmt* and use them for queries, so I know this is not a limitation of SQLite. How can I perform queries with prepared statements in Android?

推荐答案

准备好的语句可以让您做两件事

a prepared statement allows you to do two things

• 由于数据库不需要每次都解析语句，因此可以提高性能
• 绑定&在语句中转义参数，这样您就可以避免注入攻击

我不知道Android的SQLite实现实际在何处/何时真正使用sqlite3_prepare(不是sqlite3_prepare_v2的最早版本-请参见已编译的SQL语句缓存错误达到的MAX大小.

I don't know exactly where/when Androids SQLite implementation actually uses sqlite3_prepare (afiak not sqlite3_prepare_v2 - see here) but it does use it otherwise you could not get Reached MAX size for compiled-sql statement cache errors.

因此，如果您要查询数据库，则必须依赖于实现，而我不知道使用SQLiteStatement可以做到这一点.

So if you want to query the database you have to rely on the implementation there is no way I know of to do it with SQLiteStatement.

关于注入安全性，每个数据库查询，插入等方法都有(有时是替代的)版本，允许您绑定参数.

Regarding the injection safety, every database query, insert, etc method has (sometimes alternative) versions that allow you to bind arguments.

例如如果您想从

SELECT * FROM table WHERE column1='value1' OR column2='value2'

---

Cursor SQLiteDatabase#rawQuery(

• String sql ，:完整的SELECT陈述，可以在任何地方包含?
• String[] selectionArgs:按出现顺序替换?的值的列表

• String sql, : full SELECT statment which can include ? everywhere
• String[] selectionArgs : list of values that replace ?, in order they appear

)

Cursor c1 = db.rawQuery(
    "SELECT * FROM table WHERE column1=? OR column2=?",
    new String[] {"value1", "value2"}
);

---

Cursor SQLiteDatabase#query (

• String table ，:表名，可以包含JOIN等
• String[] columns ，:所需列的列表，null = *
• String selection，:不带WHERE的WHERE子句可以/应该包含?
• String[] selectionArgs ，:按顺序显示的替换?的值列表
• String groupBy，:没有GROUP BY
的GROUP BY子句
• String having，:没有HAVING
的HAVING子句
• String orderBy:没有ORDER BY
的ORDER BY子句

• String table, : table name, can include JOIN etc
• String[] columns, : list of the columns required, null = *
• String selection, : WHERE clause withouth WHERE can / should include ?
• String[] selectionArgs, : list of values that replace ?, in order they appear
• String groupBy, : GROUP BY clause w/o GROUP BY
• String having, : HAVING clause w/o HAVING
• String orderBy : ORDER BY clause w/o ORDER BY

)

Cursor c2 = db.query("table", null, 
     "column1=? OR column2=?", 
      new String[] {"value1", "value2"},
      null, null, null);

---

通过ContentProviders-这种情况略有不同，因为您是与抽象提供程序(而非数据库)进行交互的.绝对不能保证有一个支持ContentProvider的sqlite数据库.因此，除非您知道有哪些列/提供程序在内部如何工作，否则应遵循文档中所述的内容.

---

Via ContentProviders - that case is slightly different since you interact with an abstract provider, not a database. There is acutally no guarantee that there is a sqlite database backing the ContentProvider. So unless you know what columns there are / how the provider works internally you should stick to what the documentation says.

Cursor ContentResolver#query(

• Uri uri，:表示数据源的URI(内部转换为表)
• String[] projection ，:所需列的列表，null = *
• String selection，:不带WHERE的WHERE子句可以/应该包含?
• String[] selectionArgs，:替换出现顺序的值?的列表
• String sortOrder:没有ORDER BY
的ORDER BY子句

• Uri uri, : an URI representing the data source (internally translated to a table)
• String[] projection, : list of the columns required, null = *
• String selection, : WHERE clause withouth WHERE can / should include ?
• String[] selectionArgs, : list of values that replace ?, in order they appear
• String sortOrder : ORDER BY clause w/o ORDER BY

)

Cursor c3 = getContentResolver().query(
     Uri.parse("content://provider/table"), null,
     "column=? OR column2=?", 
      new String[] {"value1", "value2"},
      null);

提示:如果要在此处LIMIT，可以将其添加到ORDER BY子句中:

Hint: if you want to LIMIT here you can add it to the ORDER BY clause:

String sortOrder = "somecolumn LIMIT 5";

或根据ContentProvider的实现，将其作为参数添加到Uri:

or depending on the implementation of the ContentProvider add it as a parameter to the Uri:

Uri.parse("content://provider/table?limit=5");
// or better via buildUpon()
Uri audio = MediaStore.Audio.Media.EXTERNAL_CONTENT_URI;
audio.buildUpon().appendQueryParameter("limit", "5");

---

在所有情况下，?都将由您在bind参数中转义的版本代替.

---

In all cases ? will be replaced by the escaped version of what you put in the bind argument.

? + "hack'me" = 'hack''me'

这篇关于在Android中具有准备好的语句的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！