Hands-on Lab Practice

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 SQLite databases of Android. This hands-on lab practice is intended to help students develop a better understanding of security vulnerabilities in databases and how SQL injections can take place.

Create a new Android Studio project and select "Empty Activity" and click Next

Then name it “SQLInjection0717” with company domain of com.ksu and click on Finish.

//Copy the following code into the newly created “DatabaseHelper.java”.

//DatabaseHelper.java

package com.ksu.sqlinjection0717;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

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 and paste the following code into “MainActivity.java”.

//MainActivity.java

package com.ksu.sqlinjection0717;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.view.View;

import android.widget.EditText;

import android.widget.TextView;

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 “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>

Output Interfaces

Now at this point, you may be thinking, this doesn't change anything at all. Surely you could still just put into the username field something like Nobody OR 1=1'--, effectively making the query:

sqlQuery='SELECT * FROM custTable WHERE User=Nobody OR 1=1'-- AND

Pass=?'

And this would seem like a valid argument. But, you would be wrong.

The way parameterized queries work, is that the sqlQuery is sent as a query, and the database knows exactly what this query will do, and only then will it insert the username and passwords merely as values. This means they cannot effect the query, because the database already knows what the query will do. So in this case it would look for a username of "Nobody OR 1=1'--" and a blank password, which should come up false.

To prevent this SQL injection attack, we can utilize parameterized queries. We can do this by replacing one line of code in the following original susceptible code with 2 lines of code just below the former. This line of code can be located in the showResult method in "MainActivity.java".

Susceptible code

cursor = db.rawQuery("SELECT * FROM usertable WHERE _id='" + info + "'", null);

Parameterized query

String m_argv[] = {input.getText().toString()};

cursor = db.rawQuery("SELECT * FROM usertable WHERE _id= ? ", m_argv);

After replacing the code, saving your program and re-run it on the AVD. This will result in the program to utilize parameterized query to prevent the previously successful SQL injection attack.

Download Link for SQLInjection file

Initial File Modified File