Android Studio登录注册表单与SQLite联动问题及代码修正求助

• 登录功能异常：输入注册时的正确邮箱和密码，却提示数据无效，怀疑问题出在if(res.getCount()==1)逻辑或SQL查询部分
• 注册功能完善：需添加邮箱已注册的校验逻辑，若邮箱已存在则提示并跳转至登录页

loginActivity2代码 #

package com.example.cs50;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class loginActivity2 extends AppCompatActivity {
    TextView dont_have_account;
    EditText email,password;
    Button signin;
    DBapp my_DB;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_login2);

        email=findViewById(R.id.editEmail);
        password=findViewById(R.id.editPassword);
        signin=findViewById(R.id.buttonLogin);

        my_DB = new DBapp(this);

        signin.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                attemp_login();
            }
        });

        //make listener for text dont have account
        dont_have_account=findViewById(R.id.textview_dont_have_account);
        dont_have_account.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Intent intent=new Intent(loginActivity2.this,signupActivity2.class);
                startActivity(intent);
            }
        });
    }

    private void attemp_login() {
        String Email=email.getText().toString();
        String pass=password.getText().toString();

        if(!isEmailValid(Email)){
            Toast.makeText( this, "Email Invalid", Toast.LENGTH_SHORT).show();
        }
        if(!isPasswordValid(pass)){
            Toast.makeText(  this, "Password Invalid", Toast.LENGTH_SHORT).show();
        }
        Cursor res = my_DB.login_user(Email,pass);
        if (res.getCount()== 1){
            final Intent intent = new Intent(loginActivity2.this,Home.class);
            startActivity(intent);
        }
        else {
            Toast.makeText(  this, "Invalid Account", Toast.LENGTH_SHORT).show();
        }

    }

    private boolean isEmailValid(String email){
        return email.contains("@");
    }

    private boolean isPasswordValid(String password){
        return password.length()>6;
    }

}

signupActivity2代码 #

package com.example.cs50;

import androidx.annotation.Nullable;
import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import cn.pedant.SweetAlert.SweetAlertDialog ;

public class signupActivity2 extends AppCompatActivity {
    TextView already_signin;
    EditText username,email,password;
    Button signup;
    DBapp my_DB;
    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_signup2);

        username=findViewById(R.id.editName);
        email = findViewById(R.id.editEmail);
        password=findViewById(R.id.editPassword);
        signup=findViewById(R.id.buttonSignup);
        my_DB = new DBapp(this);

        Register_user();

        //make listener for text already sign in
        already_signin =findViewById(R.id.textView_alreadysign);
        already_signin.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Intent intent=new Intent(signupActivity2.this,loginActivity2.class);
                startActivity(intent);
            }
        });
    }

    private void Register_user() {

        signup.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {


                String Email=email.getText().toString();
                String pass=password.getText().toString();
                String user=username.getText().toString();

                 if(!isEmailValid(Email)){
                    Toast.makeText(signupActivity2.this, "Is Not Valid Email try Again", Toast.LENGTH_SHORT).show();
                }
                else if(!isPasswordvalid(pass)){
                    Toast.makeText( signupActivity2.this, "Too Small password length !!!",Toast.LENGTH_SHORT) .show();
                }
                else if(Email.isEmpty()){
                    Toast.makeText(signupActivity2.this, "Mail field Required !!", Toast.LENGTH_SHORT).show();
                }
                else if(pass.isEmpty()) {
                     Toast.makeText(signupActivity2.this, "Password Field Required !!", Toast.LENGTH_SHORT).show();
                 }
                else {
                     boolean isInserted= my_DB.insertData(Email,pass,user);
                     new SweetAlertDialog( signupActivity2.this, SweetAlertDialog.SUCCESS_TYPE)
                             .setTitleText("Message")
                             .setContentText("You are Registered")
                             .setConfirmText("OK")
                             .setConfirmClickListener(new SweetAlertDialog.OnSweetClickListener(){
                                 @Override
                                 public void onClick(SweetAlertDialog sweetAlertDialog) {
                                         Intent i =new Intent( signupActivity2.this, loginActivity2.class);
                                         startActivity(i);
                             }
                             })
                     .show();
                 }



            }
        });
    }

    private boolean isEmailValid(String email){
        return email.contains("@");
    }

    private boolean isPasswordvalid(String password){
        return password.length()>6;
    }
}

DBapp数据库代码 #

package com.example.cs50;


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

import androidx.annotation.Nullable;

public class DBapp extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "DB";
    public static final String TABLE_NAME = "user";
    public static final String COL_2 = "EMAIL";
    public static final String COL_3 = "USERNAME";
    public static final String COL_4 = "PASSWORD";


    public DBapp(@Nullable 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, EMAIL TEXT , USERNAME TEXT,PASSWORD TEXT )");

    }

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

    }


    //registration

    public boolean insertData(String email, String password, String username) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues ContentValues = new ContentValues();
        ContentValues.put(COL_2, email);
        ContentValues.put(COL_3, username);
        ContentValues.put(COL_4, password);
        long result = db.insert(TABLE_NAME, null, ContentValues);
        if (result == -1)
            return false;
        else
            return true;
    }


    //LOGIN

    public Cursor login_user(String email, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery(" Select * from " + TABLE_NAME + " where EMAIL= '" + email + " 'and PASSWORD= '" + password + "'", null);
        return res;
    }
}

问题修复方案 #

1. 登录功能异常修复 #

问题根源

DBapp中login_user方法的SQL语句存在两个问题：

• EMAIL参数后多了一个空格：EMAIL= '" + email + " '，导致查询时匹配不到数据库中存储的邮箱
• 使用字符串拼接SQL存在SQL注入风险，且未使用只读数据库优化性能

修改代码

DBapp类的login_user方法：

public Cursor login_user(String email, String password) {
    SQLiteDatabase db = this.getReadableDatabase(); // 登录操作使用只读数据库更合理
    // 使用参数化查询避免SQL注入，同时修复空格问题
    Cursor res = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE EMAIL=? AND PASSWORD=?", 
                            new String[]{email, password});
    return res;
}

loginActivity2的attemp_login方法（优化验证逻辑，添加空值判断并及时终止流程）：

private void attemp_login() {
    String Email=email.getText().toString().trim(); // 移除输入内容首尾空格
    String pass=password.getText().toString().trim();

    // 先判断空值
    if(TextUtils.isEmpty(Email)){
        Toast.makeText(this, "邮箱不能为空", Toast.LENGTH_SHORT).show();
        return;
    }
    if(TextUtils.isEmpty(pass)){
        Toast.makeText(this, "密码不能为空", Toast.LENGTH_SHORT).show();
        return;
    }
    if(!isEmailValid(Email)){
        Toast.makeText(this, "邮箱格式无效", Toast.LENGTH_SHORT).show();
        return;
    }
    if(!isPasswordValid(pass)){
        Toast.makeText(this, "密码长度需大于6位", Toast.LENGTH_SHORT).show();
        return;
    }

    Cursor res = my_DB.login_user(Email,pass);
    // 使用moveToFirst判断是否存在数据，比count更可靠，同时关闭Cursor释放资源
    if (res.moveToFirst()){
        Intent intent = new Intent(loginActivity2.this,Home.class);
        startActivity(intent);
        finish(); // 关闭登录页，避免返回
        res.close();
    } else {
        Toast.makeText(this, "账号或密码错误", Toast.LENGTH_SHORT).show();
        res.close();
    }
}

2. 注册功能添加账号存在校验 #

步骤1：DBapp添加邮箱检查方法

// 检查邮箱是否已注册
public boolean isEmailExists(String email) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE EMAIL=?", 
                            new String[]{email});
    boolean exists = res.moveToFirst();
    res.close(); // 及时关闭Cursor
    return exists;
}

步骤2：修改signupActivity2的注册逻辑

private void Register_user() {
    signup.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            String Email=email.getText().toString().trim();
            String pass=password.getText().toString().trim();
            String user=username.getText().toString().trim();

            // 调整验证顺序，先检查空值
            if(TextUtils.isEmpty(user)){
                Toast.makeText(signupActivity2.this, "用户名不能为空", Toast.LENGTH_SHORT).show();
                return;
            }
            if(TextUtils.isEmpty(Email)){
                Toast.makeText(signupActivity2.this, "邮箱不能为空", Toast.LENGTH_SHORT).show();
                return;
            }
            if(TextUtils.isEmpty(pass)) {
                Toast.makeText(signupActivity2.this, "密码不能为空", Toast.LENGTH_SHORT).show();
                return;
            }
            if(!isEmailValid(Email)){
                Toast.makeText(signupActivity2.this, "邮箱格式无效", Toast.LENGTH_SHORT).show();
                return;
            }
            if(!isPasswordvalid(pass)){
                Toast.makeText(signupActivity2.this, "密码长度需大于6位", Toast.LENGTH_SHORT).show();
                return;
            }

            // 新增邮箱存在校验
            if(my_DB.isEmailExists(Email)){
                Toast.makeText(signupActivity2.this, "该邮箱已注册，请登录", Toast.LENGTH_SHORT).show();
                Intent intent=new Intent(signupActivity2.this,loginActivity2.class);
                startActivity(intent);
                finish();
                return;
            }

            boolean isInserted= my_DB.insertData(Email,pass,user);
            // 确认插入成功再弹出提示
            if(isInserted){
                new SweetAlertDialog(signupActivity2.this, SweetAlertDialog.SUCCESS_TYPE)
                        .setTitleText("注册成功")
                        .setContentText("您已完成注册")
                        .setConfirmText("前往登录")
                        .setConfirmClickListener(new SweetAlertDialog.OnSweetClickListener(){
                            @Override
                            public void onClick(SweetAlertDialog sweetAlertDialog) {
                                sweetAlertDialog.dismiss();
                                Intent i =new Intent(signupActivity2.this,loginActivity2.class);
                                startActivity(i);
                                finish();
                            }
                        })
                        .show();
            } else {
                Toast.makeText(signupActivity2.this, "注册失败，请重试", Toast.LENGTH_SHORT).show();
            }
        }
    });
}

内容的提问来源于stack exchange，提问作者mezine aomar