Android应用中如何通过SQLite实现登录用户记录校验

Hey there! Let's tackle your login validation requirement and fix some critical issues in your code to make it secure, functional, and follow Android best practices.

Key Issues in Your Current Code #

• The login button skips all validation and directly navigates to another screen
• SQL injection vulnerability from concatenating user input into raw SQL strings
• Uninitialized database references leading to potential crashes
• Hardcoded SD card path (not recommended for Android storage)
• Duplicate variable declarations (e.g., btnSignUp is defined twice)
• Toast messages show success before verifying the operation actually succeeded
• Unclosed Cursor instances which can cause memory leaks

Step-by-Step Fixes & Implementation #

1. Add Login Validation Logic

Replace your current login button click listener with code that queries the database for matching credentials:

btnLogin.setOnClickListener(new OnClickListener(){
    public void onClick (View V){
        String username = etUsername.getText().toString().trim();
        String password = etPassword.getText().toString().trim();

        // Validate empty inputs first
        if(username.isEmpty() || password.isEmpty()){
            Toast.makeText(getApplicationContext(), "Please fill all fields!", Toast.LENGTH_LONG).show();
            return;
        }

        // Use parameterized query to avoid SQL injection
        String[] projection = {"Username", "Password"};
        String selection = "Username = ? AND Password = ?";
        String[] selectionArgs = {username, password};

        Cursor cursor = db.query(
            "Registration",  // Target table
            projection,      // Columns to retrieve
            selection,       // WHERE clause template
            selectionArgs,   // Values for WHERE clause
            null,            // GROUP BY (unused)
            null,            // HAVING (unused)
            null             // ORDER BY (unused)
        );

        if(cursor.moveToFirst()){
            // Credentials match - allow login
            Toast.makeText(getApplicationContext(), "Log In Success!", Toast.LENGTH_LONG).show();
            Intent msg1 = new Intent(Login.this, Userreg.class);
            startActivity(msg1);
            // Clear input fields after successful login
            etUsername.setText("");
            etPassword.setText("");
        } else {
            // No match - reject login
            Toast.makeText(getApplicationContext(), "Invalid username or password!", Toast.LENGTH_LONG).show();
        }

        // Always close the cursor to avoid memory leaks
        cursor.close();
    }
});

2. Fix SQL Injection in Registration

Update your signup logic to use parameterized inserts instead of concatenating strings:

btnSignUp.setOnClickListener(new OnClickListener (){
    public void onClick (View V) {
        String username= etUsername.getText().toString().trim();
        String password= etPassword.getText().toString().trim();

        if(username.isEmpty() || password.isEmpty()){
            Toast.makeText(getApplicationContext(), "Please fill all fields!", Toast.LENGTH_LONG).show();
            return;
        }

        ContentValues values = new ContentValues();
        values.put("Username", username);
        values.put("Password", password);

        // Insert into database and check result
        long insertResult = db.insert("Registration", null, values);
        if(insertResult != -1){
            Toast.makeText(getApplicationContext(), "Sign Up Success!", Toast.LENGTH_LONG).show();
            etUsername.setText("");
            etPassword.setText("");
        } else {
            Toast.makeText(getApplicationContext(), "Sign Up Failed - Username may already exist!", Toast.LENGTH_LONG).show();
        }
    }});

3. Fix Database Initialization & Storage

Avoid hardcoding the SD card path - use Android's recommended internal storage:

String createDatabase(String sql, String dbName) {
    try{
        // Use app-specific internal storage (no SD card permissions needed)
        db = openOrCreateDatabase(dbName, MODE_PRIVATE, null);
        db.beginTransaction();
        db.execSQL(sql);
        db.setTransactionSuccessful();
        db.endTransaction();
    } catch (Exception e){
        Toast.makeText(this, "Database error: " + e.getMessage(), Toast.LENGTH_LONG).show();
        Log.e("LoginDB", e.toString());
        return "error open DB";
    }
    return "";
}

4. Clean Up Variable Declarations

Remove the local Button btnSignUp declaration in onCreate() to use the class-level variable:

// Change this:
// Button btnSignUp = (Button) findViewById (R.id.btnSignUp);
// To this:
btnSignUp = findViewById(R.id.btnSignUp);

5. Handle Database Closure

Add an onDestroy() method to close the database when the activity is destroyed:

@Override
protected void onDestroy() {
    super.onDestroy();
    if(db != null && db.isOpen()){
        db.close();
    }
}

Full Modified Code #

Here's the complete updated code with all fixes applied:

package mdad.project;
import com.example.manandhowproject.R;
import android.app.Activity;
import android.content.Intent;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class Login extends Activity {
    SQLiteDatabase db;
    Button btnLogin, btnSignUp;
    EditText etUsername, etPassword;

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

        etUsername = findViewById(R.id.etUsername);
        etPassword = findViewById(R.id.etPassword);
        btnLogin = findViewById(R.id.btnLogin);
        btnSignUp = findViewById(R.id.btnSignUp);

        // Initialize database table
        String sql="create table if not exists Registration (recld integer PRIMARY KEY autoincrement, Username text, Password text)";
        String result = createDatabase(sql, "Reg.db");

        // Login button logic
        btnLogin.setOnClickListener(new OnClickListener(){
            public void onClick (View V){
                String username = etUsername.getText().toString().trim();
                String password = etPassword.getText().toString().trim();

                if(username.isEmpty() || password.isEmpty()){
                    Toast.makeText(getApplicationContext(), "Please fill all fields!", Toast.LENGTH_LONG).show();
                    return;
                }

                String[] projection = {"Username", "Password"};
                String selection = "Username = ? AND Password = ?";
                String[] selectionArgs = {username, password};

                Cursor cursor = db.query(
                    "Registration",
                    projection,
                    selection,
                    selectionArgs,
                    null,
                    null,
                    null
                );

                if(cursor.moveToFirst()){
                    Toast.makeText(getApplicationContext(), "Log In Success!", Toast.LENGTH_LONG).show();
                    Intent msg1 = new Intent(Login.this, Userreg.class);
                    startActivity(msg1);
                    etUsername.setText("");
                    etPassword.setText("");
                } else {
                    Toast.makeText(getApplicationContext(), "Invalid username or password!", Toast.LENGTH_LONG).show();
                }

                cursor.close();
            }
        });

        // Signup button logic
        btnSignUp.setOnClickListener(new OnClickListener (){
            public void onClick (View V) {
                String username= etUsername.getText().toString().trim();
                String password= etPassword.getText().toString().trim();

                if(username.isEmpty() || password.isEmpty()){
                    Toast.makeText(getApplicationContext(), "Please fill all fields!", Toast.LENGTH_LONG).show();
                    return;
                }

                ContentValues values = new ContentValues();
                values.put("Username", username);
                values.put("Password", password);

                long insertResult = db.insert("Registration", null, values);
                if(insertResult != -1){
                    Toast.makeText(getApplicationContext(), "Sign Up Success!", Toast.LENGTH_LONG).show();
                    etUsername.setText("");
                    etPassword.setText("");
                } else {
                    Toast.makeText(getApplicationContext(), "Sign Up Failed - Username may already exist!", Toast.LENGTH_LONG).show();
                }
            }});
    }

    String createDatabase(String sql, String dbName) {
        try{
            db = openOrCreateDatabase(dbName, MODE_PRIVATE, null);
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
            db.endTransaction();
        } catch (Exception e){
            Toast.makeText(this, "Database error: " + e.getMessage(), Toast.LENGTH_LONG).show();
            Log.e("LoginDB", e.toString());
            return "error open DB";
        }
        return "";
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        int id = item.getItemId();
        if (id == R.id.action_settings) {
            return true;
        }
        return super.onOptionsItemSelected(item);
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        if(db != null && db.isOpen()){
            db.close();
        }
    }
}

Additional Recommendations #

• Store hashed passwords: Never store plain text passwords! Use a hashing algorithm like SHA-256 or bcrypt to hash passwords before storing them.
• Use SQLiteOpenHelper: For better database management (schema upgrades, cleaner initialization), consider implementing SQLiteOpenHelper instead of direct database opening.
• Enhanced input validation: Add rules like minimum length for username/password and allowed character checks.

内容的提问来源于stack exchange，提问作者Kelvin How