In Android, there are five different types of data storage. They include shared preferences, internal storage, external storage, network connection, and SQLite databases. This hands-on lab practice will create a simple SQL injection application, based on the the SQLite databases of Android.
This hands on lab practice provides learning materials that allow students to be use SpotBugs to develop a security in database and how SQL injections can take place.
After programming job, we can make a static code analysis through Find Security Bugs plugin.
File->New-> New Project->Select Basic Activity->Click Next
Create a new Android Studio project and name it “SQLInjection0717” with the company domain of "sqlinjection.sqliexample" and click on Next.
Right click on sqlinjection.sqliexample.sqlinjection0717 in the project side-bar on the left and select New->Java Class.
Name your new java class “DatabaseHelper” and click “OK”.
Copy and paste the following code into “MainActivity.java” and "Other Pages".
//Copy and paste the following code into “MainActivity.java”
//MainActivity.java
package sqlinjection.sqliexample.sqlinjection0717;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import androidx.appcompat.app.AppCompatActivity;
public class MainActivity extends AppCompatActivity {
EditText input;
TextView showInput;
DatabaseHelper dbhelper;
SQLiteDatabase db;
public static final String TB_NAME = "usertable";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
input = (EditText) findViewById(R.id.editText);
showInput = (TextView) findViewById(R.id.textView2);
dbhelper = new DatabaseHelper(this, TB_NAME, null, 1);
db = dbhelper.getWritableDatabase();
}
public void onClick(View view) {
switch (view.getId()) {
case R.id.button_1:
showResult(input.getText().toString());
break;
}
}
public void showResult(String info) {
if (info == null || info.length() <= 0)
showInput.setText("Please input:");
else {
Cursor cursor;
cursor = db.rawQuery("SELECT * FROM usertable WHERE _id='" + info + "'", null);
cursor.moveToFirst();
String result = "";
while (!cursor.isAfterLast()) {
result += "id:" + cursor.getInt(0) + "\r\n" + "user:" + cursor.getString(1) + "\r\n" + "pass:" + cursor.getString(2) + "\r\n";
cursor.moveToNext();
}
showInput.setText(result);
cursor.close();
}
}
}
//Copy the following code into the newly created “DatabaseHelper.java”.
//DatabaseHelper.java
package sqlinjection.sqliexample.sqlinjection0717;
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";
public DatabaseHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS " + TB_NAME + " (" + ID + " INTEGER PRIMARY KEY," + USERNAME + " VARCHAR," + PASSWORD + " VARCHAR )");
db.execSQL("INSERT INTO " + TB_NAME + "(" + ID + "," + USERNAME + "," + PASSWORD + ") VALUES" + "('1','admin','admin888')");
db.execSQL("INSERT INTO " + TB_NAME + "(" + ID + "," + USERNAME + "," + PASSWORD + ") VALUES" + "('2','root','root123')");
db.execSQL("INSERT INTO " + TB_NAME + "(" + ID + "," + USERNAME + "," + PASSWORD + ") VALUES" + "('3','wanqing','wanqing')");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
@Override
public void onOpen(SQLiteDatabase db) {
// TODO Auto-generated method stub
super.onOpen(db);
}
}
//Copy the following code into “activity_main.xml”. Make sure to click on the “Text” tab at the bottom left of the “activity_main.xml” window
// activity_main.xml
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"
android:layout_height="match_parent" tools:context=".MainActivity">
<TextView
android:id="@+id/textView"
android:text="Please input:"
android:layout_width="wrap_content"
android:layout_height="wrap_content"/>
<EditText
android:layout_width="300dp"
android:layout_height="wrap_content"
android:id="@+id/editText"
android:layout_below="@+id/textView"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<Button
style="?android:attr/buttonStyleSmall"
android:layout_width="92dp"
android:layout_height="wrap_content"
android:text="Search"
android:id="@+id/button_1"
android:layout_below="@+id/editText"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:onClick="onClick"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:id="@+id/textView2"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_marginLeft="30dp"
android:layout_marginTop="158dp"
/>
</RelativeLayout>
If we run the code the first interfaces will look like this. Meaning that the code is working. However, we need to find the bug!
Click on "Spotbug"
Then click on Analyze ProjectFiles Including Test Sources"