PHPMailer邮件打开追踪实现及现有脚本数据库不更新修复求助

以下是针对你的邮件追踪脚本问题的详细修复步骤和修正后代码：

核心问题分析 #

1. 图片URL文件名不匹配：主脚本中嵌入的是email_track.php，但实际文件名为track.php
2. HTML实体转义错误：邮件内容中使用了<和"等转义字符，导致邮件客户端无法识别图片标签
3. PHPMailer配置错误：缺少From地址、加密设置不正确
4. SQL注入风险：track.php直接拼接用户输入到SQL查询中
5. 无有效图片输出：track.php执行数据库更新后未返回图片内容，导致邮件客户端可能终止请求
6. 数据库初始化缺失：插入数据时未设置初始状态值

修正后主脚本代码 #

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;
 
// 加载Composer自动加载器
require 'vendor/autoload.php';

$connect = new PDO("mysql:host=localhost;dbname=mailtest", "root", "");
$base_url = 'http://localhost/howtrack/'; // 修正为track.php所在路径

$message = '';

if(isset($_POST["send"]))
{
    try {
        // 启用异常处理便于调试
        $mail = new PHPMailer(true);

        $mail->IsSMTP();
        $mail->Host = 'smtp.gmail.com';
        $mail->Port = 587;
        $mail->SMTPAuth = true;
        $mail->Username = 'firstmikebension@gmail.com';
        $mail->Password = 'oncb wvge upya gbyg'; // Gmail应用专用密码
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS; // 正确的加密方式

        // 设置发件人（必须配置，否则可能被邮件服务器拦截）
        $mail->From = 'firstmikebension@gmail.com';
        $mail->FromName = '邮件追踪测试';

        $mail->AddAddress($_POST["receiver_email"]);
        $mail->WordWrap = 50;
        $mail->IsHTML(true);
        $mail->Subject = $_POST['email_subject'];

        // 生成更唯一的追踪码
        $track_code = md5(uniqid(rand(), true));

        $message_body = $_POST['email_body'];

        // 嵌入追踪图片（使用原始HTML而非转义字符）
        $message_body .= '<img src="' . $base_url . 'track.php?code=' . $track_code . '" width="1" height="1" style="display:block; border:0;" alt="" />';
        $mail->Body = $message_body;

        $mail->Send();

        // 插入数据时设置初始状态
        $data = array(
            ':email_subject' => $_POST["email_subject"],
            ':email_body' => $_POST["email_body"],
            ':email_address' => $_POST["receiver_email"],
            ':email_track_code' => $track_code,
            ':email_status' => 'no',
            ':email_open_datetime' => null
        );
        $query = "
        INSERT INTO email_data 
        (email_subject, email_body, email_address, email_track_code, email_status, email_open_datetime) 
        VALUES 
        (:email_subject, :email_body, :email_address, :email_track_code, :email_status, :email_open_datetime)
        ";

        $statement = $connect->prepare($query);
        if($statement->execute($data))
        {
            $message = '<label class="text-success">邮件发送成功</label>';
        }
    } catch (Exception $e) {
        $message = '<label class="text-danger">邮件发送失败: ' . $mail->ErrorInfo . '</label>';
    }
}

function fetch_email_track_data($connect)
{
    $query = "SELECT * FROM email_data ORDER BY email_id";
    $statement = $connect->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();
    $total_row = $statement->rowCount();
    $output = '
    <div class="table-responsive">
        <table class="table table-bordered table-striped">
            <tr>
                <th width="25%">收件邮箱</th>
                <th width="45%">邮件主题</th>
                <th width="10%">状态</th>
                <th width="20%">打开时间</th>
            </tr>
    ';
    if($total_row > 0)
    {
        foreach($result as $row)
        {
            $status = '';
            $open_time = 'N/A';
            if($row['email_status'] == 'yes')
            {
                $status = '<span class="label label-success">已打开</span>';
                $open_time = $row["email_open_datetime"] ?? 'N/A';
            }
            else
            {
                $status = '<span class="label label-danger">未打开</span>';
            }
            $output .= '
                <tr>
                    <td>' . htmlspecialchars($row["email_address"]) . '</td>
                    <td>' . htmlspecialchars($row["email_subject"]) . '</td>
                    <td>' . $status . '</td>
                    <td>' . $open_time . '</td>
                </tr>
            ';
        }
    }
    else
    {
        $output .= '
        <tr>
            <td colspan="4" align="center">暂无邮件发送记录</td>
        </tr>
        ';
    }
    $output .= '</table>';
    return $output;
}

修正后track.php代码 #

<?php
$connect = new PDO("mysql:host=localhost;dbname=mailtest", "root", "");

// 设置响应为图片类型
header('Content-Type: image/gif');

// 输出1x1透明GIF图片
$gif = base64_decode('R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==');
echo $gif;

if(isset($_GET["code"]))
{
    $track_code = $_GET["code"];
    $current_datetime = date("Y-m-d H:i:s");

    // 使用预编译语句防止SQL注入
    $query = "
    UPDATE email_data 
    SET email_status = 'yes', email_open_datetime = :datetime 
    WHERE email_track_code = :code 
    AND email_status = 'no'
    ";
    $statement = $connect->prepare($query);
    $statement->bindParam(':code', $track_code, PDO::PARAM_STR);
    $statement->bindParam(':datetime', $current_datetime, PDO::PARAM_STR);
    
    $statement->execute();
}
?>

额外注意事项 #

1. 数据库表结构：确保email_data表包含以下字段：
   • email_id (INT, 自增主键)
   • email_subject (TEXT)
   • email_body (TEXT)
   • email_address (VARCHAR(255))
   • email_track_code (VARCHAR(32))
   • email_status (VARCHAR(3), 默认值'no')
   • email_open_datetime (DATETIME, 允许为空)
2. Gmail配置：确保已启用2FA并使用应用专用密码，普通密码无法用于SMTP登录
3. 邮件客户端限制：部分邮件客户端默认阻止远程图片，用户需要手动启用图片才能触发追踪
4. 测试方法：可以直接访问track.php?code=xxx来测试数据库更新是否正常

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