时间戳

首页 > 解决方案 > 登录时用户出错并且他不在数据库中

java - 登录时用户出错并且他不在数据库中

问题描述

当我尝试使用错误的凭据登录时出现错误，我预计会收到 toast 错误，错误的凭据输入错误但它崩溃了..

我认为我的数据库助手在找不到用户 email_phone 和密码时会停止。并且不会在我的错误登录活动中发送任何拒绝功能..

什么是否认功能或我错过了什么

这是我的日志猫

03-09 03:01:09.047 16124-16124/edu.angelo.parentsportal E/SQLiteLog: (1) no such column: parent
03-09 03:01:09.048 16124-16124/edu.angelo.parentsportal E/AndroidRuntime: FATAL EXCEPTION: main
Process: edu.angelo.parentsportal, PID: 16124
android.database.sqlite.SQLiteException: no such column: parent (code 1): , while compiling: select * from Parents_Table where (EMAIL_ADDRESS = parent OR PHONE_NUMBER = parent) AND PASSWORD = 123456
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
    at edu.angelo.parentsportal.DatabaseHelper.userExistance(DatabaseHelper.java:120)
    at edu.angelo.parentsportal.Login.userLogin(Login.java:86)
    at edu.angelo.parentsportal.Login.onClick(Login.java:50)
    at android.view.View.performClick(View.java:4780)
    at android.view.View$PerformClick.run(View.java:19866)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:135)
    at android.app.ActivityThread.main(ActivityThread.java:5254)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

我的数据库助手

package edu.angelo.parentsportal;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;

public class DatabaseHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "Parents_Portal.db";
public static final String TABLE_NAME = "Parents_Table";
public static final String COL_0 = "ID";
public static final String COL_1 = "NAME";
public static final String COL_2 = "SURNAME";
public static final String COL_3 = "EMAIL_ADDRESS";
public static final String COL_4 = "PHONE_NUMBER";
public static final String COL_5 = "PASSWORD";

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);

}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table " + TABLE_NAME +"(ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, SURNAME TEXT, EMAIL_ADDRESS TEXT, PHONE_NUMBER TEXT, PASSWORD TEXT)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
onCreate(db);
}

public boolean insertData(String name, String surname, String email_address, String phone_number, String password){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_1,name);
    contentValues.put(COL_2,surname);
    contentValues.put(COL_3,email_address);
    contentValues.put(COL_4,phone_number);
    contentValues.put(COL_5,password);
    long result = db.insert(TABLE_NAME, null , contentValues);
    if (result == -1) {
        return false;
    }
    else {
        return true;
    }
}

public ArrayList<ParentModel> getAllParentsData(){
    ArrayList<ParentModel> list = new ArrayList<>();
    String sql = "select * from " + TABLE_NAME;
    SQLiteDatabase mydb = this.getWritableDatabase();
    Cursor cursor = mydb.rawQuery(sql, null);
    if (cursor.moveToFirst()) {
        do {
            ParentModel parentModel = new ParentModel();
            parentModel.setID(cursor.getString(0));
            parentModel.setName(cursor.getString(1));
            parentModel.setSurname(cursor.getString(2));
            parentModel.setEmail(cursor.getString(3));
            parentModel.setPhone_number(cursor.getString(4));
            parentModel.setPassword(cursor.getString(5));
            list.add(parentModel);
        }
        while (cursor.moveToNext());
    }
    return list;
}

public void updateData(int id, String name , String surname , String email , String phone_number , String password){
   ContentValues contentValues = new ContentValues();
    contentValues.put(COL_1, name);
    contentValues.put(COL_2, surname);
    contentValues.put(COL_3, email);
    contentValues.put(COL_4, phone_number);
    contentValues.put(COL_5, password);

    SQLiteDatabase mydb = this.getWritableDatabase();
    mydb.update(TABLE_NAME, contentValues, COL_0 + "=" + id, null);
    mydb.close();
}

public void deleteParent(int id){
    SQLiteDatabase mydb = this.getWritableDatabase();
    mydb.delete(TABLE_NAME, COL_0 + "=" + id, null);
    mydb.close();
}


public ArrayList<ParentModel> getParentLoginData(String emailOrPhone,String password){
    ArrayList<ParentModel> list = new ArrayList<>();
    String sql = "SELECT * FROM " + TABLE_NAME+" WHERE ("+COL_3+"= "+emailOrPhone+" OR "+COL_4 +" = "+emailOrPhone+") AND "+COL_5 +" = "+ password;
    SQLiteDatabase mydb = this.getWritableDatabase();
    Cursor cursor = mydb.rawQuery(sql, null);
    if (cursor.getCount() > 0) {
        do {
            ParentModel parentModel = new ParentModel();
            parentModel.setID(cursor.getString(0));
            parentModel.setName(cursor.getString(1));
            parentModel.setSurname(cursor.getString(2));
            parentModel.setEmail(cursor.getString(3));
            parentModel.setPhone_number(cursor.getString(4));
            parentModel.setPassword(cursor.getString(5));
            list.add(parentModel);
        }
        while (cursor.moveToNext());
    }
    return list;
}

public boolean userExistance(String emailOrPhone, String pwd) {
    String sql = "select * from " + TABLE_NAME + " where (" + COL_3 + " = " + emailOrPhone + " OR " + COL_4 + " = " + emailOrPhone + ") AND " + COL_5 + " = " + pwd;
    SQLiteDatabase mydb = this.getWritableDatabase();
    Cursor cursor = mydb.rawQuery(sql, null);

    if (cursor.getCount() > 0) {
        do {
            ArrayList<ParentModel> list = new ArrayList<>();
            ParentModel parentModel = new ParentModel();
            parentModel.setID(cursor.getString(0));
            parentModel.setName(cursor.getString(1));
            parentModel.setSurname(cursor.getString(2));
            parentModel.setEmail(cursor.getString(3));
            parentModel.setPhone_number(cursor.getString(4));
            parentModel.setPassword(cursor.getString(5));
            list.add(parentModel);
        }
        while (cursor.moveToNext());
        return true;
    }

    else{
        return false;
    }

}
}

我的登录活动

public class Login extends AppCompatActivity implements View.OnClickListener {

private EditText editTextEmailPhone;
private EditText editTextPassword;
private Button Login;
private ProgressDialog progressDialog;
DatabaseHelper mydb;
SQLiteDatabase sqLiteDatabase;
ParentModel parentModel;



@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_login);


    editTextEmailPhone = findViewById(R.id.input_username);
    editTextPassword = findViewById(R.id.input_password);
    findViewById(R.id.btn_register).setOnClickListener(Login.this);

    progressDialog = new ProgressDialog(this);

    mydb = new DatabaseHelper(this);
    sqLiteDatabase = mydb.getReadableDatabase();
}

@Override
public void onClick(View view) {
    switch (view.getId()){
        case R.id.btn_register: {
            userLogin();
            break;
        }

    }


}


private void userLogin() {
    String email = editTextEmailPhone.getText().toString().trim();
    String password = editTextPassword.getText().toString().trim();

    if (email.isEmpty()) {
        editTextEmailPhone.setError("Email or Phone Number is required");
        editTextEmailPhone.requestFocus();
        return;
    }

    if (password.isEmpty()) {
        editTextPassword.setError("Password is required");
        editTextPassword.requestFocus();
        return;
    }
    if (password.length()<6 ){
        editTextPassword.setError("Minimum of length of password should be 6");
        editTextPassword.requestFocus();
        return;
    }
    //if the email&pass is not empty
    else{
        progressDialog.setMessage("Please Wait...");
        progressDialog.show();

        boolean exists = mydb.userExistance(email, password);
        if(true)
        {
            progressDialog.dismiss();
            SharedPrefs.saveSharedSetting(this, "NoAccount", "false");
            Intent intent = new Intent(Login.this, Parent_Home.class);

            String parentID;
            String parentName;
            String parentSurname;
            parentID = parentModel.getID();
            parentName = parentModel.getName();
            parentSurname = parentModel.getSurname();

            //Change to prefs
            //intent.putExtra("Ik_CurrentParentID",parentModel.getID());
           // intent.putExtra("Ik_CurrentParentName",parentModel.getName());
           // intent.putExtra("Ik_CurrentParentSurname",parentModel.getSurname());
           // Toast.makeText(this, "Welcome" + parentName, Toast.LENGTH_SHORT).show();
            startActivity(intent);
            finish();
        }
        else {
            Toast.makeText(getApplicationContext(), "Login error", Toast.LENGTH_SHORT).show();
            progressDialog.dismiss();
            return;
        }
    }
}

}

标签： javaandroiddatabasesqlite

解决方案

原因是因为parent没有用单引号括起来，所以它被视为一个标识符（在这种情况下是一个列名，因为您可以通过它的名称与列进行比较，因此是消息）。

1. 快速但不太好的修复。

看来您有这样三个实例，因此您可以更改：-

String sql = "select * from " + TABLE_NAME + " where (" + COL_3 + " = " + emailOrPhone + " OR " + COL_4 + " = " + emailOrPhone + ") AND " + COL_5 + " = " + pwd;

成为

String sql = "select * from " + TABLE_NAME + " where (" + COL_3 + " = '" + emailOrPhone + "' OR " + COL_4 + " = '" + emailOrPhone + "') AND " + COL_5 + " = '" + pwd + "'";

2. 更好的修复，但仍然存在一些潜在问题（请参阅下一个修复）

但是，要比较的值是否应该通过用户输入。这使应用程序对 SQL 注入开放（通过输入输入破坏性命令），因此建议您使用 rawQuewry 方法的第二个参数来传递一个字符串数组，这些值将逐个替换？询问。

因此，使用以下方法将被认为是一种更安全的做法：-

String sql = "select * from " + TABLE_NAME + " where (" + COL_3 + " = ? OR " + COL_4 + " =?) AND " + COL_5 + " =? ";
String[] args = new String[]{emailOrPhone,emailOrPhone,pwd};
SQLiteDatabase mydb = this.getWritableDatabase();
Cursor cursor = mydb.rawQuery(sql,args);

3. 更好的修复。

但是，有一些方便的方法，例如查询（你使用了更新方便的方法），它们被认为比在可能的情况下使用 rawQuery 更好。

便捷方法构建底层 SQL。因此，推荐的方法是使用：-

public boolean userExistance(String emailOrPhone, String pwd) {

    SQLiteDatabase mydb = this.getWritableDatabase();
    String whereclause = "(" + COL_3 + "=? OR " + COL_4 + "=?) AND " + COL_5 + "=?";
    String[] whereargs = new String[]{emailOrPhone,emailOrPhone,pwd}    
    Cursor cursor = mydb.query(TABLE_NAME,null,whereclause,whereargs,null,null,null);
    boolean rv = cusror.getCount() > 0;
    cursor.close();
    return rv;
}
  • 请注意，这也已被更改以消除不必要的和潜在危险的代码。
  • 游标已关闭，如果打开的游标过多，应用程序将崩溃，因此在完成游标后不要关闭游标是不好的做法。
  • 无需遍历游标并提取数据，因为您只想知道是否已返回任何行。所以使用计数来设置返回值是真还是假，游标关闭，返回布尔值。
  • 您不妨看看SQLiteDatabase - 查询

请注意，以上是原则代码，尚未经过测试或运行，因此可能包含一些错误。

您还应该注意，您将遇到与 getParentLoginData 类似的问题，因为 SELECT 子句非常相似。

推荐阅读