在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中具有准备好的语句的查询?
发布评论