PayPal IPN监听脚本无法写入MySQLi数据库（已解决待更新）

⚠️ Pause Notice: After reviewing the latest PayPal documentation, I've found significant changes to PayPal's flow since this script was originally implemented years ago. I'm currently updating the script to align with these changes—please hold off on taking action for now. Thanks for your patience.

Hey there, let's break down how to fix your PayPal IPN script that started acting up after switching from old MySQL to MySQLi, while also accounting for PayPal's updated processes:

1. Troubleshoot Common MySQLi Migration Pitfalls #

The switch from mysql_* functions to MySQLi often trips people up with syntax and connection differences. Here are the key checks:

• Verify Database Connections: Unlike mysql_connect(), MySQLi requires you to explicitly pass the connection object to queries. Use this pattern to handle connections and errors:

  $dbHost = 'your_host';
  $dbUser = 'your_user';
  $dbPass = 'your_password';
  $dbName = 'your_db';
  
  $conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);
  if (!$conn) {
      error_log("MySQLi Connection Failed: " . mysqli_connect_error());
      exit;
  }
  

• Update Query Syntax: Replace mysql_query() with mysqli_query($conn, $sql)—don't forget the connection object! For better security and reliability, use prepared statements instead of raw SQL concatenation (this also avoids injection risks):

  // Example: Insert IPN data into logs
  $stmt = mysqli_prepare($conn, "INSERT INTO ipn_logs (txn_id, payment_status, payer_email) VALUES (?, ?, ?)");
  mysqli_stmt_bind_param($stmt, "sss", $txnId, $paymentStatus, $payerEmail);
  mysqli_stmt_execute($stmt);
  

• Adjust Result Handling: Functions like mysql_fetch_array() become mysqli_fetch_assoc($result) or mysqli_fetch_array($result)—make sure you pass the result set from mysqli_query():

  $result = mysqli_query($conn, "SELECT * FROM ipn_logs WHERE txn_id = '$txnId'");
  if (mysqli_num_rows($result) > 0) {
      $logEntry = mysqli_fetch_assoc($result);
  }
  

2. Adapt to PayPal's Updated IPN Flow #

PayPal has made critical changes to IPN over the years—here's what you need to update in your script:

• Force HTTPS for Validation: PayPal now requires all IPN validation requests to use HTTPS. Use these endpoints:
  • Production: https://ipnpb.paypal.com/cgi-bin/webscr
  • Sandbox: https://ipnpb.sandbox.paypal.com/cgi-bin/webscr
• Correctly Construct Validation Requests: You must echo back all received POST parameters plus cmd=_notify-validate, sent via POST. Use cURL with proper SSL settings:

  $req = 'cmd=_notify-validate';
  foreach ($_POST as $key => $value) {
      $value = urlencode(stripslashes($value));
      $req .= "&$key=$value";
  }
  
  $ch = curl_init('https://ipnpb.paypal.com/cgi-bin/webscr');
  curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
  curl_setopt($ch, CURLOPT_POST, true);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
  curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
  curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true);
  curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
  curl_setopt($ch, CURLOPT_FORBID_REUSE, true);
  curl_setopt($ch, CURLOPT_HTTPHEADER, array('Connection: Close'));
  
  $validationResponse = curl_exec($ch);
  if (curl_errno($ch)) {
      error_log("cURL Error During IPN Validation: " . curl_error($ch));
      exit;
  }
  curl_close($ch);
  

• Handle Validation Responses: PayPal still returns VERIFIED or INVALID (all uppercase), so match these exactly:

  if (strcmp($validationResponse, "VERIFIED") === 0) {
      // Process the valid IPN (update orders, send receipts, etc.)
  } else if (strcmp($validationResponse, "INVALID") === 0) {
      // Log invalid requests and ignore
      error_log("Invalid IPN Received: " . print_r($_POST, true));
  }
  

• Update Parameter Handling: PayPal has added new parameters and deprecated some old ones. Avoid relying on outdated fields (like num_cart_items in some cases) and make sure your script handles current values for payment_status (e.g., COMPLETED, REFUNDED).

3. Debugging Tips #

• Enable Detailed Logging: Log all IPN POST data, validation responses, and MySQLi errors to a file—this is the fastest way to spot issues:

  error_log("IPN POST Data: " . print_r($_POST, true));
  error_log("PayPal Validation Response: " . $validationResponse);
  

• Test with PayPal Sandbox: Use PayPal's Sandbox IPN Simulator to send test requests—this lets you verify your script works without real transactions.
• Check Server Environment: Ensure your server has the cURL extension enabled and valid SSL certificates (otherwise, HTTPS validation requests will fail).

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