使用Coinbase Commerce API时PHP及Webhook订单更新异常问题

使用Coinbase Commerce API完成加密货币支付后，支付流程正常，页面能跳转至payment_complete页面，但无法更新checkout表的订单信息。尝试添加日志排查，但无法生成debug.log文件，且页面跳转后无法通过控制台监控，无法定位脚本失败点，疑似webhook脚本未执行checkout表更新操作。

<?php
$ch = curl_init();

try {
// Query to get the items from the checkout table for the current user
$stmt = $dbh->prepare("SELECT product FROM checkout WHERE userID = :userID");
$stmt->execute(['userID' => $userID]);

$items = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Check if there are items in the cart
if (count($items) > 1) {
    // For multiple items, join the titles into a single string separated by commas
    $description = implode(', ', array_column($items, 'product'));
} elseif (count($items) === 1) {
    // If there's only one item, use its title directly
    $description = $items[0]['product'];
} else {
    // If no items are found, set a default description
    $description = "No items found";
}

} catch (PDOException $e) {
// Handle any errors in the connection or query
echo "Error: " . $e->getMessage();
exit();
}

// Set up the cURL request to the Coinbase API
// Set up the cURL request to the Coinbase API
curl_setopt($ch, CURLOPT_URL, 'https://api.commerce.coinbase.com/charges');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode(
array (
    'name' => 'TL',  // The name of your business or checkout
    'description' => $description,  // Dynamically generated description of the items
    'local_price' => 
    array (
        'amount' => $totalprice['totPrice'],  // Total price of all items (use your original total price variable)
        'currency' => 'GBP',  // Currency code
    ),
    'pricing_type' => 'fixed_price',  // Pricing model (fixed in this case)
    'metadata' => 
    array (
        'customer_id' => $userID,  // Pass the userID as requested
        'customer_name' => $useremail,  // Customer's email or identifier (from session or checkout)
    ),
    'redirect_url' => 'https://www.my-site.co.uk/Checkout/payment_complete.php',  // URL to redirect after successful payment
    'cancel_url' => 'https://www.my-site.co.uk/Checkout/payment_cancelled.php',  // URL to redirect if payment is canceled
    'webhook_url' => 'https://www.my-site.co.uk/Checkout/PHP/coinbase_webhook.php'  // Webhook URL for payment confirmation
)
));

// Set headers for the Coinbase API request
$headers = array();
$headers[] = 'Content-Type: application/json';
$headers[] = 'X-Cc-Api-Key: MY-API-KEY';  // Your Coinbase API key
$headers[] = 'X-Cc-Version: 2018-03-22';  // Coinbase API version
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

// Execute the cURL request and get the response
$result = curl_exec($ch);

// Close the cURL session
curl_close($ch);

// Decode the JSON response from Coinbase
$response = json_decode($result, true);

// Extract the hosted URL from the response to redirect the user
$letssee = $response['data']['hosted_url'];

// Output the payment button/link to the user
echo "<a id='byee' class='leggo2' href='$letssee'><b>Pay with Crypto</b></a>";
?>

<?php
session_start();
// DB connect //
require_once '/var/www/vhosts/my-site.co.uk/httpdocs/include/PHP/main.php';

// Set error handling
ini_set('display_errors', 0); // Disable error output to the client
ini_set('log_errors', 1); // Enable error logging
ini_set('error_log', '/var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log');
error_reporting(E_ALL); // Report all errors

// Log script start
file_put_contents('/var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log', "Script started at " . date('Y-m-d H:i:s') . "\n", FILE_APPEND);

// Coinbase Webhook Listener to handle crypto payment confirmation

// Read the incoming webhook data
$payload = file_get_contents('php://input');
$signature = $_SERVER['HTTP_X_CC_WEBHOOK_SIGNATURE'];

// Verify the Coinbase signature to ensure it's a legit request
$shared_secret = 'MY-API-KEY'; // Get this from Coinbase Dashboard
$computed_signature = hash_hmac('sha256', $payload, $shared_secret);

if (hash_equals($signature, $computed_signature)) {
$event = json_decode($payload, true);
error_log("Received webhook event: " . print_r($event, true), 3, "/var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log");

// Check if the event is 'charge:confirmed'
if ($event['event']['type'] == 'charge:confirmed') {
    $paymentDetails = $event['event']['data'];

    // Extract necessary data
    $orderID = 'TL-' . bin2hex(random_bytes(5)); // Generate unique order ID
    $userID = $paymentDetails['metadata']['customer_id']; // Retrieve userID from metadata
    $amount = $paymentDetails['pricing']['local']['amount'];
    $currency = $paymentDetails['pricing']['local']['currency'];

    // Connect to the database using PDO (ensure you have the $dbh variable defined)
    try {
        $userID = $_SESSION['sessionID'];
        // Update the checkout records
        $paytype = "Crypto";
        $confirmationStatus = "Confirmed"; // This is what you want to store in the confirmation column
        $ordprogress = "75"; // Update the order progress as needed
        
        $updateStmt = $dbh->prepare("UPDATE checkout SET paytype = :paytype, confirmation = :confirmation, ordprogress = :ordprogress WHERE userID = :userID");
        $updateStmt->bindParam(':paytype', $paytype, PDO::PARAM_STR);
        $updateStmt->bindParam(':confirmation', $confirmationStatus, PDO::PARAM_STR);
        $updateStmt->bindParam(':ordprogress', $ordprogress, PDO::PARAM_STR);
        $updateStmt->bindParam(':userID', $userID, PDO::PARAM_STR);
        
        // Attempt to execute the update statement
        if (!$updateStmt->execute()) {
            // Log the SQL error if the update fails
            error_log("Failed to update checkout table. Error: " . implode(", ", $updateStmt->errorInfo()) . "\n", 3, "/var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log");
            throw new Exception("Failed to update checkout table.");
        }
        error_log("Checkout updated for userID: $userID\n", 3, "/var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log");

        http_response_code(200); // Respond with 200 OK
    } catch (Exception $e) {
        error_log("Error occurred: " . $e->getMessage() . "\n", 3, "/var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log");
        http_response_code(500); // Internal server error
    }
}
} else {
// Invalid signature, possible attack
error_log("Invalid signature received\n", 3, "/var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log");
http_response_code(400); // Bad request
}
?>

1. 日志无法生成的解决 #

• 权限修正：确保日志目录和文件归PHP运行用户（通常是www-data）所有，执行以下命令：

  chown www-data:www-data /var/www/vhosts/my-site.co.uk/httpdocs/Checkout/
  chmod 755 /var/www/vhosts/my-site.co.uk/httpdocs/Checkout/
  touch /var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log
  chown www-data:www-data /var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log
  chmod 644 /var/www/vhosts/my-site.co.uk/httpdocs/Checkout/debug.log
  

• 路径验证：确认日志文件的绝对路径无拼写错误，避免因路径错误导致无法写入。

2. Webhook脚本的核心错误修复 #

（1）Session覆盖用户ID问题 #

Webhook请求由Coinbase服务器发起，不携带用户浏览器的Session，$_SESSION['sessionID']为无效值，且覆盖了从支付元数据中正确获取的$userID，直接导致UPDATE语句找不到对应记录。
修复：删除$userID = $_SESSION['sessionID'];这一行代码，保留从元数据获取的$userID。

（2）签名验证密钥错误 #

Coinbase Webhook的验证密钥不是API Key，而是在Coinbase Commerce后台Webhook设置页面单独生成的Webhook Secret。当前用API Key作为shared_secret会导致签名验证失败，直接跳过后续逻辑。
修复：登录Coinbase Commerce后台，找到对应Webhook的Secret值，替换$shared_secret = 'MY-API-KEY';中的内容。

（3）PDO错误模式开启 #

确保数据库连接文件main.php中开启了PDO异常模式，便于捕获数据库错误：

// 在main.php的PDO初始化代码中添加
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

3. 调试辅助建议 #

• 临时将日志写入系统临时目录，验证脚本是否执行：

  file_put_contents('/tmp/coinbase_webhook_debug.log', "Script started at " . date('Y-m-d H:i:s') . "\n", FILE_APPEND);
  

• 使用Coinbase Commerce后台的Webhook测试工具，发送模拟charge:confirmed事件到你的webhook地址，直接查看返回状态和日志输出。

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