如何通过输入ID从MySQL数据库正确展示数据？

Hey there! Let's get your code working safely and reliably to display user data based on the ID entered in the form. Your current setup has a couple of key issues (like SQL injection risks and undefined variable errors) that we'll address step by step.

First, Let's List the Problems in Your Original Code #

• Critical SQL Injection Vulnerability: You're directly inserting $_POST['id'] into your SQL query, which lets attackers manipulate your database.
• Undefined Variable Error: When the page loads initially (before submitting the form), $_POST['id'] doesn't exist, causing a PHP warning.
• HTML Syntax Bug: Your submit button has a typo: value"enter" should be value="enter".

Here's the Corrected, Safe Version of Your Code #

<form action="" method="post">
    <input type="number" name="id" required>
    <input type="submit" value="enter">
</form>

<table>
    <thead>
        <tr>
            <td>id</td>
            <td>First Name</td>
            <td>Last Name</td>
        </tr>
    </thead>
    <tbody>
        <?php
        require_once('conect.php'); // Double-check this filename—maybe it's "connect.php"?

        // Only run the query if the form was submitted and ID is valid
        if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['id']) && is_numeric($_POST['id'])) {
            $id = (int)$_POST['id']; // Cast to integer to ensure it's a valid number

            // Use prepared statements to avoid SQL injection
            $stmt = $conn->prepare("SELECT * FROM users WHERE id = ? ORDER BY id DESC");
            $stmt->bind_param("i", $id); // "i" denotes the parameter is an integer
            $stmt->execute();
            $result = $stmt->get_result();
            $users = $result->fetch_all(MYSQLI_ASSOC);

            if (empty($users)) {
                echo '<tr><td colspan="3">No user found with that ID.</td></tr>';
            } else {
                foreach ($users as $row) {
        ?>
                    <tr>
                        <td><?php echo htmlspecialchars($row['id']); ?></td>
                        <td><?php echo htmlspecialchars($row['FirstName']); ?></td>
                        <td><?php echo htmlspecialchars($row['LastName']); ?></td>
                    </tr>
        <?php
                }
            }

            // Clean up database resources
            $stmt->close();
        }
        ?>
    </tbody>
</table>

Key Improvements Explained #

• SQL Injection Protection: We use prepared statements with bind_param() to safely pass the ID to the query. This ensures the input is treated as a literal value, not executable SQL.
• Request Validation: We check if the request is a POST, if the id parameter exists, and if it's a numeric value before running the query. This prevents undefined variable warnings and invalid inputs.
• XSS Prevention: htmlspecialchars() is used when echoing user data to stop cross-site scripting attacks.
• User Feedback: We added a message if no user matches the entered ID, so the user gets clear feedback instead of an empty table.
• Fixed HTML Typo: The submit button's value attribute now uses correct syntax.

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