如何改造产品码验证脚本，使其验证通过后无法重复使用

Alright, let's fix up your script so product codes can't be reused once they're verified. Here's a step-by-step breakdown:

1. First, Update Your Database Table #

You need a way to track if a code has been used. Add a new column to your code1 table to act as a "used" flag:

ALTER TABLE code1 ADD COLUMN is_used TINYINT(1) NOT NULL DEFAULT 0;

This column will be 0 (unused) by default, and we'll set it to 1 once the code is verified successfully.

2. Modify the Verification Logic #

Your current script only checks if the code exists—now we need to also check that it hasn't been used yet. Then, after verification passes, we'll mark it as used.

Modified Original Script (MySQL Extension) #

Note: The mysql_* functions are deprecated in PHP, so I'll also include a safer mysqli version below. But here's the fix for your existing code first:

<?php
require_once('captcha.php');
$error = $verified = false;

if($_POST){
    mysql_connect('localhost', 'root', 'root');
    mysql_select_db('code');

    if(!PhpCaptcha::Validate($_POST['captcha'])){
        $error = 'Anti-bot code is not valid!';
    }else{
        // Check for existing UNUSED code
        $query = "SELECT product_title FROM code1 WHERE code_plain='".mysql_real_escape_string($_POST['code'])."' AND is_used = 0";
        $result = mysql_query($query) or die(mysql_error());
        $res = mysql_fetch_assoc($result);

        if(mysql_num_rows($result)>0){
            $verified = $res['product_title'];
            // Mark the code as used
            $updateQuery = "UPDATE code1 SET is_used = 1 WHERE code_plain='".mysql_real_escape_string($_POST['code'])."'";
            mysql_query($updateQuery) or die(mysql_error());
        }else{
            $error = '<b>This product code is not valid, doesn\'t exist, or has already been used!</b>';
        }
    }
}
?>

Safer Version (Using MySQLi Extension) #

Since mysql_* is outdated and insecure, here's a better implementation with mysqli (prepared statements to prevent SQL injection):

<?php
require_once('captcha.php');
$error = $verified = false;

if($_POST){
    // Create mysqli connection
    $conn = mysqli_connect('localhost', 'root', 'root', 'code');
    if(!$conn){
        die("Connection failed: ".mysqli_connect_error());
    }

    if(!PhpCaptcha::Validate($_POST['captcha'])){
        $error = 'Anti-bot code is not valid!';
    }else{
        // Use prepared statement to avoid SQL injection
        $stmt = mysqli_prepare($conn, "SELECT product_title FROM code1 WHERE code_plain = ? AND is_used = 0");
        mysqli_stmt_bind_param($stmt, "s", $_POST['code']);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        $res = mysqli_fetch_assoc($result);

        if(mysqli_num_rows($result)>0){
            $verified = $res['product_title'];
            // Mark code as used with prepared statement
            $updateStmt = mysqli_prepare($conn, "UPDATE code1 SET is_used = 1 WHERE code_plain = ?");
            mysqli_stmt_bind_param($updateStmt, "s", $_POST['code']);
            mysqli_stmt_execute($updateStmt);
        }else{
            $error = '<b>This product code is not valid, doesn\'t exist, or has already been used!</b>';
        }

        // Cleanup
        mysqli_stmt_close($stmt);
        if(isset($updateStmt)) mysqli_stmt_close($updateStmt);
        mysqli_close($conn);
    }
}
?>

Key Changes Explained #

• Added is_used column: Tracks whether the code has been redeemed.
• Updated SELECT query: Now filters for codes where is_used = 0, so only unused codes pass validation.
• Added UPDATE query: Immediately marks the code as used (is_used = 1) after successful verification, so it can't be reused.
• Prepared statements (in mysqli version): Eliminates SQL injection risks, which is critical for security.

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