The Secure Way to Connect HTML Forms to a MySQL Database Using PHP PDO

使用 PHP PDO 将 HTML 表单安全连接到 MySQL 数据库

This tutorial walks you through how to safely handle form submissions from an HTML page into a MySQL database. We will use PDO (PHP Data Objects) because it gives you clean, secure, and consistent code that forms the foundation of modern backend development. 本教程将引导您了解如何安全地处理从 HTML 页面到 MySQL 数据库的表单提交。我们将使用 PDO（PHP 数据对象），因为它能为您提供简洁、安全且一致的代码，这也是现代后端开发的基础。

Why Use PDO Instead of Standard MySQLi?

为什么要使用 PDO 而不是标准的 MySQLi？

PDO is the modern, preferred way to talk to databases in PHP for three major reasons: PDO 是目前 PHP 中与数据库交互的首选现代方式，主要有三个原因：

Portability: You can switch database types (MySQL, PostgreSQL, SQLite, etc.) with almost no changes to your application code. 可移植性： 您几乎无需更改应用程序代码即可切换数据库类型（MySQL、PostgreSQL、SQLite 等）。
Consistency: It uses the same object-oriented methods and patterns regardless of the database driver. 一致性： 无论使用哪种数据库驱动，它都使用相同的面向对象方法和模式。
Security: It makes prepared statements—the key defense against SQL injection—incredibly clean to implement. 安全性： 它使得预处理语句（防御 SQL 注入的关键）的实现变得非常简洁。

SQL Injection in Plain English

通俗易懂地解释 SQL 注入

If you build a database query by directly gluing text together from user input like this: $sql = "INSERT INTO users VALUES ('" . $_POST['username'] . "')"; A malicious user can type '); DROP TABLE users; -- into your form and instantly destroy your data. 如果您通过直接拼接用户输入来构建数据库查询，例如：$sql = "INSERT INTO users VALUES ('" . $_POST['username'] . "')";，恶意用户可以在表单中输入 '); DROP TABLE users; --，从而瞬间摧毁您的数据。

Prepared statements completely fix this by separating the SQL structure from the actual data. PDO ensures that the database treats user inputs strictly as parameters, never as executable code. While the older mysqli extension supports prepared statements too, PDO is more straightforward and offers much better error handling. 预处理语句通过将 SQL 结构与实际数据分离，彻底解决了这个问题。PDO 确保数据库将用户输入严格视为参数，绝不会将其视为可执行代码。虽然较旧的 mysqli 扩展也支持预处理语句，但 PDO 更直观，并提供了更好的错误处理机制。

Setting Up the Secure Connection Array

设置安全连接配置

First, let’s establish a secure gateway to your database. Create a file named submit.php and add the following connection configuration: 首先，让我们为您的数据库建立一个安全网关。创建一个名为 submit.php 的文件，并添加以下连接配置：

<?php
// 1. Database Configuration
$host = 'localhost';
$db = 'my_portfolio_db';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    // 2. Establish the Connection
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    // In development, this shows the error. In production, log this instead!
    die("Database connection failed: " . $e->getMessage());
}

Why This Matters: 为什么这很重要：

The DSN (Data Source Name): This string defines your driver, host, database name, and character set (utf8mb4 ensures proper support for all text, including emojis). DSN（数据源名称）： 该字符串定义了您的驱动程序、主机、数据库名称和字符集（utf8mb4 确保对所有文本（包括表情符号）的正确支持）。
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION: This forces PDO to throw explicit exceptions when something breaks, letting you catch bugs immediately rather than dealing with silent failures. PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION： 这强制 PDO 在出错时抛出明确的异常，让您可以立即捕获错误，而不是处理静默失败。
PDO::ATTR_EMULATE_PREPARES => false: This disables emulated prepared statements, forcing the application to use real, native prepared statements at the database level for maximum protection. PDO::ATTR_EMULATE_PREPARES => false： 这会禁用模拟预处理语句，强制应用程序在数据库层面使用真正的原生预处理语句，以实现最大程度的保护。
The Try/Catch Block: Wrapping your initialization inside a try/catch block prevents the script from crashing catastrophically and leaking sensitive server configurations or passwords to the public user interface. Try/Catch 块： 将初始化代码包裹在 try/catch 块中，可以防止脚本发生灾难性崩溃，并避免将敏感的服务器配置或密码泄露给公共用户界面。

Capturing and Validating Form Inputs

捕获并验证表单输入

Never trust data coming directly from a browser. Before sending data anywhere near your database, you must sanitize and validate it. Add this processing logic below your connection code inside submit.php: 永远不要信任直接来自浏览器的数据。在将数据发送到数据库之前，必须对其进行清理和验证。在 submit.php 的连接代码下方添加以下处理逻辑：

// 3. Process Form Submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    // Collect and sanitize basic inputs
    $username = trim($_POST['username'] ?? '');
    $email = filter_var($_POST['email'] ?? '', FILTER_VALIDATE_EMAIL);

    if ($username && $email) {
        // Proceed to secure data insertion...
    } else {
        echo "Invalid input data. Please check your username and email.";
    }
}

Why This Matters: 为什么这很重要：

trim(): Removes accidental or malicious leading and trailing whitespace characters. trim()： 删除意外或恶意的首尾空格字符。
filter_var() with FILTER_VALIDATE_EMAIL: Explicitly checks if the provided string follows a real email address structure. If it fails validation, it evaluates to false. 带有 FILTER_VALIDATE_EMAIL 的 filter_var()： 明确检查提供的字符串是否符合真实的电子邮件地址结构。如果验证失败，则返回 false。
The Null Coalescing Operator (?? ”): Prevents annoying PHP notices like “undefined index” if a user submits a manipulated payload where a form field is completely missing. 空合并运算符 (?? ”)： 如果用户提交了被篡改的载荷导致表单字段完全缺失，它可以防止出现类似“未定义索引”的烦人 PHP 通知。

Executing the Data Transfer with Prepared Statements

使用预处理语句执行数据传输

Once the data passes validation, we can safely write it to the database table using named placeholders: 一旦数据通过验证，我们就可以使用命名占位符将其安全地写入数据库表：

// 4. Secure SQL Execution using Prepared Statements
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'username' => $username,
    'email' => $email
]);

echo "Success! Data saved securely.";

How This Works: 它是如何工作的：

:username and :email: These act as secure placeholders inside your raw SQL string. :username 和 :email： 它们在原始 SQL 字符串中充当安全占位符。
prepare(): Sends the SQL blueprint to the MySQL server ahead of time. The database optimization engine compiles the structural query layout without any user values attached. prepare()： 提前将 SQL 蓝图发送到 MySQL 服务器。数据库优化引擎会编译查询结构布局，而不附加任何用户值。
execute(): Passes the actual raw text variables separately. Because the SQL structure was already compiled in the preparation step, the database engine treats these variables purely as text strings, completely neutralizing any SQL injection tricks. execute()： 分别传递实际的原始文本变量。由于 SQL 结构已在预处理步骤中编译完成，数据库引擎会将这些变量纯粹视为文本字符串，从而彻底抵消任何 SQL 注入手段。

Building the Front-End Form

构建前端表单

To test this backend script, create an index.html file in the same directory. This provides the complete, valid HTML skeleton required to pass inputs cleanly to your PHP controller: 为了测试此后端脚本，请在同一目录下创建一个 index.html 文件。它提供了将输入干净地传递给 PHP 控制器所需的完整、有效的 HTML 骨架：

<!DOCTYPE html>
<html>
<body>
    <h2>Create An Account</h2>
    <form method="POST" action="submit.php">
        <div>
            <label for="username">Username:</label>
            <input type="text" id="username" name="username" required>
        </div>
        <br>
        <div>
            <label for="email">Email Address:</label>
            <input type="email" id="email" name="email" required>
        </div>
        <br>
        <button type="submit">Submit Data Securely</button>
    </form>
</body>
</html>

Production Checklist

生产环境检查清单

While this guide creates a secure connection, remember these mandatory security rules before pushing full-stack code live to a public production server: 虽然本指南创建了一个安全的连接，但在将全栈代码推送到公共生产服务器之前，请记住以下强制性的安全规则：

Enforce HTTPS: Secure connection strings protect data traveling between the user and your server. 强制使用 HTTPS： 安全的连接字符串可以保护用户与服务器之间传输的数据。
Hash Passwords: If you ever handle user registration credentials, always use PHP’s native password_hash() mechanism—never store plain text keys. 哈希密码： 如果您处理用户注册凭据，请务必使用 PHP 原生的 password_hash() 机制——永远不要存储明文密钥。
Add CSRF Tokens: Implement unique Cross-Site Request Forgery tokens to ensure that form submissions are coming from your own website. 添加 CSRF 令牌： 实现唯一的跨站请求伪造（CSRF）令牌，以确保表单提交确实来自您自己的网站。