Hello,

Expert response requied:

-----------------------------------------------------------------------------------

Take a look at the attached/below file, and based on that, design and develop a new add on lab/file with different SQL injection attack vectors and protection solution from the given lab.

***Basically, provide other form of SQL injection vulnerability and prevent it from injection. Please just modify the given program in the lab and test. provide clear Steps and results, screenshots, codes please.

You should have unsecured and secured results and files.

-----------------------------------------------------------------------------------------

Please refer to the below:

----------------------------------------------------------------------------------------

This hands-on lab practice will create a simple SQL injection application, based on the the SQLite databases of Android.

Objective: develop a better understanding of security vulnerabilities in databases and how SQL injections can take place.

Create a new Android Studio project and name it SQLInjection0717 with company domain of "sqliexample.sqlinjection" and click on Next.

Choose the form factor that your app will run on. You can leave it on the default settings, and click on Next.

Select the Empty Activity and click on Next.

Leave the options for your new file on the default settings and click on Finish.

You should see something similar to the following image.

Copy and paste the following code into MainActivity.java.

MainActivity.java

package sqlinjection.sqliexample.sqlinjection0717;

import android.support.v7.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) + " " + "user:" + cursor.getString(1) + " " + "pass:" + cursor.getString(2) + " ";

cursor.moveToNext();

}

showInput.setText(result);

cursor.close();

}

}

}

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 the following code into the newly created DatabaseHelper.java.

DatabaseHelper.java

package sqlinjection.sqliexample.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 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:text="Please input:" android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:id="@+id/textView" />

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

Save your project and run it on the AVD.

The data stored in the database should be the following:

id:1

user: admin

pass: admin888

id:2

user: root

pass: root123

id:3

user: wanqing

pass: wanqing

When the user inputs 1 or 2 or 3 into the input field and press any of the search buttons the application will display the respective information for the id provided. However, if the user was to use an invalid input, such as 1 or 1 = 1 or " 1' or username not null -- " and press the search button, then the invalid tautology input will result in the whole database returning, as the invalid input always returns true, resulting into a successful SQL injection attack.

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.

Parameterized queries do proper substitution of arguments prior to running the SQL query. It completely removes the possibility of "dirty" input changing the meaning of your query. That is, if the input contains SQL, it can't become part of what is executed becase the SQL is never injected into the resulting statement.

Imagine a dynamic SQL query

sqlQuery='SELECT * FROM custTable WHERE User=' + Username + ' AND

Pass=' + password

so a simple sql injection would be just to put the Username in as ' OR

1=1-- This would effectively make the sql query:

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

+ password

This says select all customers where they're username is blank ('') or 1=1, which is a boolean, equating to true. Then it uses -- to comment out the rest of the query. So this will just print out all the customer table, or do whatever you want with it, if logging in, it will log in with the first user's privileges, which can often be the administrator.

Now parameterized queries do it differently, with code like:

sqlQuery='SELECT * FROM custTable WHERE User=? AND Pass=?'

parameters.add("User", username) parameters.add("Pass", password)

where username and password are variables pointing to the associated inputted username and password

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.