在Android中具有准备好的语句的查询?

编程入门行业动态更新时间:2024-10-24 14:22:53

本文介绍了在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
String having，:没有HAVING
String orderBy:没有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

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.