Android sqlite database injection vulnerability and repair solution

Brief description:
 
Android uses sqlite as a database. For database queries, if the developer constructs an SQL statement using string connection, SQL injection is generated.
 
 
Detailed description:
 
 
Android implements an sqlite operation class SQLiteOpenHelper. We inherit this class and then reload methods such as onCreate and onUpgrade.
DatabaseHelper. java is as follows:
 
 
 
 
Package com. xiaod. sqlinj;
 
Import android. content. Context;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteDatabase. CursorFactory;
Import android. database. sqlite. SQLiteOpenHelper;
 
Public class DatabaseHelper extends SQLiteOpenHelper {
Public static final String TB_NAME = "usertable ";
Public static final String ID = "_ id ";
Public static final String USERNAME = "username ";
Public static final String PASSWORD = "password ";
DatabaseHelper (Context context, String name, CursorFactory cursorFactory, int version ){
Super (context, name, cursorFactory, version );
}
 
@ Override
Public void onCreate (SQLiteDatabase db ){
Db.exe cSQL ("create table if not exists" +
TB_NAME + "(" + ID + "integer primary key," +
USERNAME + "VARCHAR," + PASSWORD + "VARCHAR )");
Db.exe cSQL ("insert into" + TB_NAME + "(" + USERNAME + "," + PASSWORD + ") VALUES" + "('admin', 'admin888 ')");
Db.exe cSQL ("insert into" + TB_NAME + "(" + USERNAME + "," + PASSWORD + ") VALUES" + "('root', 'root123 ')");
Db.exe cSQL ("insert into" + TB_NAME + "(" + USERNAME + "," + PASSWORD + ") VALUES" + "('xiaod ', 'xiaodwin ')");
}
 
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
 
}
 
@ Override
Public void onOpen (SQLiteDatabase db ){
Super. onOpen (db );
 
}
}
 
 
Add a query box and a query button to display the query result in textview.
SqlinjActivity. java is as follows:
 
Package com. xiaod. sqlinj;
 
Import android. app. Activity;
Import android. database. Cursor;
Import android. database. sqlite. SQLiteDatabase;
Import android. OS. Bundle;
Import android. view. View;
Import android. view. View. OnClickListener;
Import android. widget. Button;
Import android. widget. EditText;
Import android. widget. TextView;
 
Public class SqlinjActivity extends Activity {
Public static final String DB_NAME = "sqlinj. db ";
Public static final int VERSION = 1;
Private String result = "";
Private TextView m_ TV;
Private EditText m_et;
DatabaseHelper m_dbhelper;
SQLiteDatabase m_db;
Button m_btnselect;
/** Called when the activity is first created .*/
@ Override
Public void onCreate (Bundle savedInstanceState ){
Super. onCreate (savedInstanceState );
SetContentView (R. layout. main );
 
M_ TV = (TextView) findViewById (R. id. TV _view );
M_btnselect = (Button) findViewById (R. id. btn_select );
M_et = (EditText) findViewById (R. id. et_id );
 
M_btnselect.setOnClickListener (new OnClickListener (){
 
@ Override
Public void onClick (View arg0 ){
// TODO Auto-generated method stub
String m_id = m_et.getText (). toString ();
M_ TV .setText (ShowData (m_id ));
}
 
});
 
M_dbhelper = new DatabaseHelper (this, DB_NAME, null, VERSION );
M_db = m_dbhelper.getWritableDatabase ();
 
}
 
Private String ShowData (String m_id)
{
Result = "";
Cursor m_cursor;
String m_argv [] = {m_id };
M_cursor = m_db.rawQuery ("SELECT * FROM usertable WHERE _ id = '" + m_id + "'", null );
M_cursor.moveToFirst ();
While (! M_cursor.isAfterLast ()){
Result + = "id:" + m_cursor.getInt (0) + "\ n" +
"User:" + m_cursor.getString (1) + "\ n" +
"Pass:" + m_cursor.getString (2) + "\ n ";
M_cursor.moveToNext ();
}
M_cursor.close ();
Return result;
}
}
 
 
Proof of vulnerability:
 
 
In the ShowData function, records are queried by passing the id number. In the 54-row statement, SELECT * FROM usertable WHERE _ id = uses the string connection method to construct an SQL statement. We run the program to test whether the program can be injected.
After running, submit 1 and return to normal
 
 
Submitting 2' and '1' = '1 returns normal
 
 
 
Submitting 2' and '1' = '2 cannot query data
 
 
 
Enter 2' or _ id <> 'to return all data.
 
 


Solution:
 
The SQL injection protection method is consistent with the conventional approach, using parameterized Query
Add an array storage query parameter
 
 
Author: Leng SEN @ wooyun