允许Android用户访问数据库是否合理？用户注册登录鉴权正确实现方案咨询

Alright, let's tackle this properly. Opening up MySQL to all IPs is a huge red flag—so first things first, we'll lock that down, then build your user registration/login system securely with your existing Apache+PHP+MySQL stack. Here's a step-by-step breakdown:

First, we need to restrict MySQL to only accept connections from trusted sources (your local Apache server, ideally):

• Update MySQL's bind address:
  Edit your MySQL config file (usually my.cnf on Linux, my.ini on Windows) and set:

  bind-address = 127.0.0.1
  

  This ensures MySQL only listens for local connections (since Apache is running on the same machine). If you ever need remote access later, replace this with a specific trusted IP instead of %.

• Restrict database user permissions:
  Revoke the overly broad permissions you granted earlier, and give your PHP app only the access it needs. Run these SQL commands in your MySQL shell:

  -- Remove the open-access user
  REVOKE ALL PRIVILEGES ON *.* FROM 'your_app_user'@'%';
  DROP USER IF EXISTS 'your_app_user'@'%';
  
  -- Create a user with local-only access, limited to your auth database
  CREATE USER 'your_app_user'@'localhost' IDENTIFIED BY 'strong_password_here';
  GRANT SELECT, INSERT, UPDATE ON your_auth_database.* TO 'your_app_user'@'localhost';
  FLUSH PRIVILEGES;
  

  Stick to the principle of least privilege—never grant ALL PRIVILEGES on *.* unless absolutely necessary.

Now, let's fix the auth code to avoid common vulnerabilities:

• Use Prepared Statements to Prevent SQL Injection:
  Never concatenate user input directly into SQL queries. Use PDO or mysqli's prepared statements instead. Here's a PDO example:

  // Initialize PDO connection (store credentials in a non-web-accessible config file!)
  $pdo = new PDO(
      'mysql:host=localhost;dbname=your_auth_database;charset=utf8mb4',
      'your_app_user',
      'strong_password_here',
      [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
  );
  
  // User Registration
  if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['register'])) {
      // Validate input first
      $username = trim($_POST['username']);
      $email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
      $password = $_POST['password'];
  
      if (!$email || strlen($username) < 3 || strlen($password) < 8) {
          die("Invalid input. Please check your details.");
      }
  
      // Hash password NEVER store plain text!
      $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
  
      // Prepared statement for safe insertion
      $stmt = $pdo->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)");
      $stmt->execute([$username, $email, $hashedPassword]);
  
      echo "Registration successful!";
  }
  
  // User Login
  if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['login'])) {
      $email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
      $password = $_POST['password'];
  
      if (!$email) {
          die("Invalid email format.");
      }
  
      // Fetch only the hashed password for the user
      $stmt = $pdo->prepare("SELECT id, password_hash FROM users WHERE email = ?");
      $stmt->execute([$email]);
      $user = $stmt->fetch(PDO::FETCH_ASSOC);
  
      // Verify password (never compare hashes manually!)
      if ($user && password_verify($password, $user['password_hash'])) {
          // Start a secure session
          session_set_cookie_params([
              'lifetime' => 3600,
              'path' => '/',
              'domain' => $_SERVER['HTTP_HOST'],
              'secure' => true,
              'httponly' => true,
              'samesite' => 'Strict'
          ]);
          session_start();
          $_SESSION['user_id'] = $user['id'];
  
          // Redirect to dashboard
          header("Location: dashboard.php");
          exit;
      } else {
          die("Invalid email or password.");
      }
  }
  

• Secure Session Management:
  The session settings included above block common attacks like session hijacking and CSRF—always enable these for auth-related sessions.

• Enable HTTPS:
  Use a free Let's Encrypt certificate to encrypt all user data in transit. For Apache, you can use Certbot to automate this process—it will configure SSL for your site automatically, including redirecting HTTP traffic to HTTPS.

• Apache Security Tweaks:

  • Disable directory indexing by adding Options -Indexes to your .htaccess or Apache config.
  • Restrict access to sensitive files (like config files) with .htaccess:

    <Files "config.php">
        Order Allow,Deny
        Deny from all
    </Files>
    

  • Consider installing mod_security (a web application firewall) to block malicious requests.

• Monitor for Attacks:
  Use tools like Fail2ban to scan Apache/MySQL logs and automatically IP-ban users who attempt brute-force login attacks.

• Database Management: Use Adminer (lightweight, more secure than phpMyAdmin) or phpMyAdmin (lock it down with IP restrictions and strong passwords) for local MySQL management.
• Code Auditing: PHP_CodeSniffer can scan your code for security issues like missing prepared statements or plain-text password storage.
• SSL Certificates: Certbot for easy Let's Encrypt certificate setup and renewal.
• Attack Prevention: Fail2ban to automate IP blocking for brute-force attempts.

内容的提问来源于stack exchange，提问作者No N