Wednesday, 4 April 2018

android - How to handle/avoid SQL injection in Sqlite db




I write a class EmployeeDao and all functionalities work fine but i got issue from client SQL injection attack, i read about SQL injection and prepared statement but i can't understand how to handle/avoid SQL injection attack using prepared statements it in my code i'm very new in it please help me.



EmployeeDao.java class



  public class EmployeeDAO {

// Database fields
private SQLiteDatabase database;
private EmployeeDatabaseHelper dbHelper;


private String[] allColumns = {
// all column name
};

public EmployeeDAO(Context context) {
dbHelper = new EmployeeDatabaseHelper(context);
}

public void open() throws SQLException {

database = dbHelper.getWritableDatabase();
}

public void close() {
dbHelper.close();
}

public void saveEmployeeDetails(Employee employee) {

try {

truncateEmployeeDetails();
ContentValues values = new ContentValues();

values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_AD_ID, CryptoHelper.encrypt(employee.getAdId()));
values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_CODE, CryptoHelper.encrypt(employee.getCode()));
values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_EMAIL, CryptoHelper.encrypt(employee.getEmail()));
values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_FIRST_NAME, CryptoHelper.encrypt(employee.getFirstName()));
values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_GENDER, CryptoHelper.encrypt(employee.getGender()));
values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_PK_ID, CryptoHelper.encrypt(String.valueOf(employee.getId())));
values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_IMEI, CryptoHelper.encrypt(employee.getImei()));

values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_LAST_NAME, CryptoHelper.encrypt(employee.getLastName()));
values.put(EmployeeContract.EmployeeEntry.COLUMN_NAME_PHONE, CryptoHelper.encrypt(employee.getPhone()));

database.insert(EmployeeContract.EmployeeEntry.TABLE_NAME, null, values);
} catch (Exception e) {
e.printStackTrace();
}
}

public void truncateEmployeeDetails() {

database.execSQL("delete from " + EmployeeContract.EmployeeEntry.TABLE_NAME);
}

public Employee getEmployeeDetails() {
Employee employee = null;
Cursor cursor = null;
try {
cursor = database.query(EmployeeContract.EmployeeEntry.TABLE_NAME,
allColumns, null, null, null, null, null);


cursor.moveToFirst();
employee = cursorToEmployee(cursor);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null)
cursor.close();
}
return employee;
}


private Employee cursorToEmployee(Cursor cursor) {
Employee employee = new Employee();
try {
employee.setAdId(CryptoHelper.decrypt(cursor.getString(1)));
employee.setCode(CryptoHelper.decrypt(cursor.getString(2)));
employee.setEmail(CryptoHelper.decrypt(cursor.getString(3)));
employee.setFirstName(CryptoHelper.decrypt(cursor.getString(4)));
employee.setGender(CryptoHelper.decrypt(cursor.getString(5)));
employee.setId(Long.parseLong(CryptoHelper.decrypt(cursor.getString(6))));

employee.setImei(CryptoHelper.decrypt(cursor.getString(7)));
employee.setLastName(CryptoHelper.decrypt(cursor.getString(8)));
employee.setPhone(CryptoHelper.decrypt(cursor.getString(9)));
} catch (Exception e) {
e.printStackTrace();
}

return employee;
}}


Answer



i solved it by using prepared statement



SQLiteStatement sqLiteStatement = database.compileStatement("" +
"INSERT INTO " +
EmployeeContract.EmployeeEntry.TABLE_NAME +
" ( " +
EmployeeContract.EmployeeEntry.COLUMN_NAME_AD_ID +
"," +
EmployeeContract.EmployeeEntry.COLUMN_NAME_CODE +

"," +
EmployeeContract.EmployeeEntry.COLUMN_NAME_EMAIL +
"," +
EmployeeContract.EmployeeEntry.COLUMN_NAME_FIRST_NAME +
"," +
EmployeeContract.EmployeeEntry.COLUMN_NAME_GENDER +
"," +
EmployeeContract.EmployeeEntry.COLUMN_NAME_PK_ID +
"," +
EmployeeContract.EmployeeEntry.COLUMN_NAME_IMEI +

"," +
EmployeeContract.EmployeeEntry.COLUMN_NAME_LAST_NAME +
","+
EmployeeContract.EmployeeEntry.COLUMN_NAME_PHONE +
" ) " +
" VALUES ('" + adId + "','" + code + "', '" + email + "','" + firstName + "','" + gender + "','" + pkId + "','"+ imei+"','"+ lastName +"','"+ phone +"') ");

sqLiteStatement.executeInsert();
sqLiteStatement.close();


No comments:

Post a Comment

casting - Why wasn't Tobey Maguire in The Amazing Spider-Man? - Movies & TV

In the Spider-Man franchise, Tobey Maguire is an outstanding performer as a Spider-Man and also reprised his role in the sequels Spider-Man...