title: Issues with IPN and data entry in a MySQL database
body: <p>When I test my IPN handler, if I use the IPN Simulator from:
  <a href="https://developer.paypal.com/developer/ipnSimulator/" rel="nofollow noreferrer">https://developer.paypal.com/developer/ipnSimulator/</a> everything works fine.</p>
  
  <p>Although, when I try to use the sandbox environment on my website: <a href="https://ipnpb.sandbox.paypal.com/cgi-bin/webscr" rel="nofollow noreferrer">https://ipnpb.sandbox.paypal.com/cgi-bin/webscr</a> nothing is inserted in the database.</p>
  
  <p>I do see the entry processed successfully with the HTTP response code 200 from: <a href="https://www.sandbox.paypal.com/webscr?cmd=_display-ipns-history" rel="nofollow noreferrer">https://www.sandbox.paypal.com/webscr?cmd=_display-ipns-history</a></p>
  
  <p>I suppose that both must work differently, am I missing something?</p>
  
  <p>When I use the sandbox environment, I never see an INSERT command in the MySQL logs either.</p>
  
  <p>Here is the code for the listener:</p>
  
  <pre><code>&lt;?php
  
  class PaypalIPN
  {
  
  /**
   * @var bool $use_sandbox     Indicates if the sandbox endpoint is used.
   */
  private $use_sandbox = false;
  /**
   * @var bool $use_local_certs Indicates if the local certificates are used.
   */
  private $use_local_certs = true;
  
  /** Production Postback URL */
  const VERIFY_URI = 'https://ipnpb.paypal.com/cgi-bin/webscr';
  /** Sandbox Postback URL */
  const SANDBOX_VERIFY_URI = 'https://ipnpb.sandbox.paypal.com/cgi-bin/webscr';
  
  
  /** Response from PayPal indicating validation was successful */
  const VALID = 'VERIFIED';
  /** Response from PayPal indicating validation failed */
  const INVALID = 'INVALID';
  
  
  /**
   * Sets the IPN verification to sandbox mode (for use when testing,
   * should not be enabled in production).
   * @return void
   */
  public function useSandbox()
  {
      $this-&gt;use_sandbox = true;
  }
  
  /**
   * Sets curl to use php curl's built in certs (may be required in some
   * environments).
   * @return void
   */
  public function usePHPCerts()
  {
      $this-&gt;use_local_certs = false;
  }
  
  
  /**
   * Determine endpoint to post the verification data to.
   * @return string
   */
  public function getPaypalUri()
  {
      if ($this-&gt;use_sandbox) {
          return self::SANDBOX_VERIFY_URI;
      } else {
          return self::VERIFY_URI;
      }
  }
  
  
  /**
   * Verification Function
   * Sends the incoming post data back to PayPal using the cURL library.
   *
   * @return bool
   * @throws Exception
   */
  public function verifyIPN()
  {
      if ( ! count($_POST)) {
          throw new Exception("Missing POST Data");
      }
  
      $raw_post_data = file_get_contents('php://input');
      $raw_post_array = explode('&amp;', $raw_post_data);
      $myPost = array();
      foreach ($raw_post_array as $keyval) {
          $keyval = explode('=', $keyval);
          if (count($keyval) == 2) {
              // Since we do not want the plus in the datetime string to be encoded to a space, we manually encode it.
              if ($keyval[0] === 'payment_date') {
                  if (substr_count($keyval[1], '+') === 1) {
                      $keyval[1] = str_replace('+', '%2B', $keyval[1]);
                  }
              }
              $myPost[$keyval[0]] = urldecode($keyval[1]);
          }
      }
  
      // Build the body of the verification post request, adding the _notify-validate command.
      $req = 'cmd=_notify-validate';
      $get_magic_quotes_exists = false;
      if (function_exists('get_magic_quotes_gpc')) {
          $get_magic_quotes_exists = true;
      }
      foreach ($myPost as $key =&gt; $value) {
          if ($get_magic_quotes_exists == true &amp;&amp; get_magic_quotes_gpc() == 1) {
              $value = urlencode(stripslashes($value));
          } else {
              $value = urlencode($value);
          }
          $req .= "&amp;$key=$value";
      }
  
      // Post the data back to PayPal, using curl. Throw exceptions if errors occur.
      $ch = curl_init($this-&gt;getPaypalUri());
      curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
      curl_setopt($ch, CURLOPT_POST, 1);
      curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
      curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
      curl_setopt($ch, CURLOPT_SSLVERSION, 6);
      curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 1);
      curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
  
      // This is often required if the server is missing a global cert bundle, or is using an outdated one.
      if ($this-&gt;use_local_certs) {
          curl_setopt($ch, CURLOPT_CAINFO, __DIR__ . "\\assets\\cert\\cacert.pem");
      }
      curl_setopt($ch, CURLOPT_FORBID_REUSE, 1);
      curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
      curl_setopt($ch, CURLOPT_HTTPHEADER, array('Connection: Close'));
      $res = curl_exec($ch);
      if ( ! ($res)) {
          $errno = curl_errno($ch);
          $errstr = curl_error($ch);
          curl_close($ch);
          throw new Exception("cURL error: [$errno] $errstr");
      }
  
      $info = curl_getinfo($ch);
      $http_code = $info['http_code'];
      if ($http_code != 200) {
          throw new Exception("PayPal responded with http code $http_code");
      }
  
      curl_close($ch);
  
      // Check if PayPal verifies the IPN data, and if so, return true.
      if ($res == self::VALID) {
          return true;
      } else {
          return false;
      }
  }
  }
  </code></pre>
  
  <p>Here is the code for the handler (I am using more variables than needed for testing purposes right now):</p>
  
  <pre><code>&lt;?php require('PaypalIPN.php');
  use PaypalIPN;
  ipn = new PaypalIPN();
  // Use the sandbox endpoint during testing.
  $ipn-&gt;useSandbox();
  $verified = $ipn-&gt;verifyIPN();
  if ($verified) {
  /*
   * Process IPN
   * A list of variables is available here:
   * https://developer.paypal.com/webapps/developer/docs/classic/ipn/integration-guide/IPNandPDTVariables/
   */
  
   /*
  
   ADD DATABASE COMMANDS HERE
  
   */
  
  $payment_type = "";
  $payment_date = "";
  $payment_status = "";
  $pending_reason = "";
  $address_status = "";
  $payer_status = "";
  $first_name = "";
  $last_name = "";
  $payer_email = "";
  $payer_id = "";
  $address_name = "";
  $address_country = "";
  $address_country_code = "";
  $address_zip = "";
  $address_state = "";
  $address_city = "";
  $address_street = "";
  $business = "";
  $receiver_email = "";
  $receiver_id = "";
  $residence_country = "";
  $item_name = "";
  $item_name1 = "";
  $item_number = "";
  $item_number1 = "";
  $quantity = "";
  $shipping = "";
  $tax = "";
  $mc_currency = "";
  $mc_fee = "";
  $mc_gross = "";
  $mc_gross_1 = "";
  $mc_handling = "";
  $mc_handling1 = "";
  $mc_shipping = "";
  $mc_shipping1 = "";
  $txn_type = "";
  $txn_id = "";
  $notify_version = "";
  $parent_txn_id = "";
  $reason_code = "";
  $receipt_ID = "";
  $auction_buyer_id = "";
  $auction_closing_date = "";
  $for_auction = "";
  $custom = "";
  $invoice = "";
  $protection_eligibility = "";
  $charset = "";
  $option_selection1 = "";
  $subscr_id = "";
  $verify_sign = "";
  $option_name1 = "";
  $btn_id = "";
  $payment_fee = "";
  $test_ipn = "";
  $transaction_subject = "";
  $payment_gross = "";
  $ipn_track_id = "";
  
  if (isset($_POST['payment_type']))
  {
      $payment_type = $_POST['payment_type'];
  }
  
      if (isset($_POST['payment_date']))
  {
      $payment_date = $_POST['payment_date'];
  }
  
      if (isset($_POST['payment_status']))
  {
      $payment_status = $_POST['payment_status'];
  }
  
      if (isset($_POST['pending_reason']))
  {
      $pending_reason = $_POST['pending_reason'];
  }
  
      if (isset($_POST['address_status']))
  {
      $address_status = $_POST['address_status'];
  }
  
      if (isset($_POST['payer_status']))
  {
      $payer_status = $_POST['payer_status'];
  }
  
      if (isset($_POST['first_name']))
  {
      $first_name = $_POST['first_name'];
  }
  
      if (isset($_POST['last_name']))
  {
      $last_name = $_POST['last_name'];
  }
  
      if (isset($_POST['payer_email']))
  {
      $payer_email = $_POST['payer_email'];
  }
  
      if (isset($_POST['payer_id']))
  {
      $payer_id = $_POST['payer_id'];
  }
  
      if (isset($_POST['address_name']))
  {
      $address_name = $_POST['address_name'];
  }
  
      if (isset($_POST['address_country']))
  {
      $address_country = $_POST['address_country'];
  }
  
      if (isset($_POST['address_country_code']))
  {
      $address_country_code = $_POST['address_country_code'];
  }
  
      if (isset($_POST['address_zip']))
  {
      $address_zip = $_POST['address_zip'];
  }
  
      if (isset($_POST['address_state']))
  {
      $address_state = $_POST['address_state'];
  }
  
      if (isset($_POST['address_city']))
  {
      $address_city = $_POST['address_city'];
  }
  
      if (isset($_POST['address_street']))
  {
      $address_street = $_POST['address_street'];
  }
  
      if (isset($_POST['business']))
  {
      $business = $_POST['business'];
  }
  
      if (isset($_POST['receiver_email']))
  {
      $receiver_email = $_POST['receiver_email'];
  }
  
      if (isset($_POST['receiver_id']))
  {
      $receiver_id = $_POST['receiver_id'];
  }
      if (isset($_POST['residence_country']))
  {
      $residence_country = $_POST['residence_country'];
  }
      if (isset($_POST['item_name']))
  {
      $item_name = $_POST['item_name'];
  }
      if (isset($_POST['item_name1']))
  {
      $item_name1 = $_POST['item_name1'];
  }
      if (isset($_POST['item_number']))
      {
      $item_number = $_POST['item_number'];
  }
      if (isset($_POST['item_number1']))
  {
      $item_number1 = $_POST['item_number1'];
  }
      if (isset($_POST['quantity']))
  {
      $quantity = $_POST['quantity'];
  }
      if (isset($_POST['shipping']))
  {
      $shipping = $_POST['shipping'];
  }
      if (isset($_POST['tax']))
  {
      $tax = $_POST['tax'];
  }
      if (isset($_POST['mc_currency']))
  {
      $mc_currency = $_POST['mc_currency'];
  }
      if (isset($_POST['mc_fee']))
  {
      $mc_fee = $_POST['mc_fee'];
  }
      if (isset($_POST['mc_gross']))
  {
      $mc_gross = $_POST['mc_gross'];
  }
      if (isset($_POST['mc_gross_1']))
  {
      $mc_gross_1 = $_POST['mc_gross_1'];
  }
      if (isset($_POST['mc_handling']))
  {
      $mc_handling = $_POST['mc_handling'];
  }
      if (isset($_POST['mc_handling1']))
  {
      $mc_handling1 = $_POST['mc_handling1'];
  }
      if (isset($_POST['mc_shipping']))
  {
      $mc_shipping = $_POST['mc_shipping'];
  }
      if (isset($_POST['mc_shipping1']))
  {
      $mc_shipping1 = $_POST['mc_shipping1'];
  }
      if (isset($_POST['txn_type']))
  {
      $txn_type = $_POST['txn_type'];
  }
      if (isset($_POST['txn_id']))
  {
      $txn_id = $_POST['txn_id'];
  }
      if (isset($_POST['notify_version']))
  {
      $notify_version = $_POST['notify_version'];
  }
      if (isset($_POST['parent_txn_id']))
  {
      $parent_txn_id = $_POST['parent_txn_id'];
  }
      if (isset($_POST['reason_code']))
  {
      $reason_code = $_POST['reason_code'];
  }
      if (isset($_POST['receipt_ID']))
  {
      $receipt_ID = $_POST['receipt_ID'];
  }
      if (isset($_POST['auction_buyer_id']))
  {
      $auction_buyer_id = $_POST['auction_buyer_id'];
  }
      if (isset($_POST['auction_closing_date']))
  {
      $auction_closing_date = $_POST['auction_closing_date'];
  }
      if (isset($_POST['for_auction']))
  {
      $for_auction = $_POST['for_auction'];
  }
      if (isset($_POST['custom']))
  {
      $custom = $_POST['custom'];
  }
      if (isset($_POST['invoice']))
  {
      $invoice = $_POST['invoice'];
  }
      if (isset($_POST['protection_eligibility']))
  {
      $protection_eligibility = $_POST['protection_eligibility'];
  }
      if (isset($_POST['charset']))
  {
      $charset = $_POST['charset'];
  }
      if (isset($_POST['option_selection1']))
  {
      $option_selection1 = $_POST['option_selection1'];
  }
      if (isset($_POST['subscr_id']))
  {
      $subscr_id = $_POST['subscr_id'];
  }
      if (isset($_POST['verify_sign']))
  {
      $verify_sign = $_POST['verify_sign'];
  }
      if (isset($_POST['option_name1']))
  {
      $option_name1 = $_POST['option_name1'];
  }
      if (isset($_POST['btn_id']))
  {
      $btn_id = $_POST['btn_id'];
  }
      if (isset($_POST['payment_fee']))
  {
      $payment_fee = $_POST['payment_fee'];
  }
      if (isset($_POST['test_ipn']))
  {
      $test_ipn = $_POST['test_ipn'];
  }
      if (isset($_POST['transaction_subject']))
  {
      $transaction_subject = $_POST['transaction_subject'];
  }
      if (isset($_POST['payment_gross']))
  {
      $payment_gross = $_POST['payment_gross'];
  }
      if (isset($_POST['ipn_track_id']))
  {
      $ipn_track_id = $_POST['ipn_track_id'];
  }
  
  $servername = "REDACTED";
  $username = "REDACTED";
  $password = "REDACTED";
  $dbname = "REDACTED";
  
  // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);
  // Check connection
  if ($conn-&gt;connect_error) {
      die("Connection failed: " . $conn-&gt;connect_error);
  }
  
  $sql = 
  "INSERT INTO paypal (payment_type,payment_date,payment_status,pending_reason,address_status,payer_status,first_name,last_name,payer_email,payer_id,address_name,address_country,address_country_code,address_zip,address_state,address_city,address_street,business,receiver_email,receiver_id,residence_country,item_name,item_name1,item_number,item_number1,quantity,shipping,tax,mc_currency,mc_fee,mc_gross,mc_gross_1,mc_handling,mc_handling1,mc_shipping,mc_shipping1,txn_type,txn_id,notify_version,parent_txn_id,reason_code,receipt_ID,auction_buyer_id,auction_closing_date,for_auction,custom,invoice,protection_eligibility,charset,option_selection1,subscr_id,verify_sign,option_name1,btn_id,payment_fee,test_ipn,transaction_subject,payment_gross,ipn_track_id)
  VALUES
  (
  '" . $payment_type . "',
  '" . $payment_date . "',
  '" . $payment_status . "',
  '" . $pending_reason . "',
  '" . $address_status . "',
  '" . $payer_status . "',
  '" . $first_name . "',
  '" . $last_name . "',
  '" . $payer_email . "',
  '" . $payer_id . "',
  '" . $address_name . "',
  '" . $address_country . "',
  '" . $address_country_code . "',
  '" . $address_zip . "',
  '" . $address_state . "',
  '" . $address_city . "',
  '" . $address_street . "',
  '" . $business . "',
  '" . $receiver_email . "',
  '" . $receiver_id . "',
  '" . $residence_country . "',
  '" . $item_name . "',
  '" . $item_name1 . "',
  '" . $item_number . "',
  '" . $item_number1 . "',
  '" . $quantity . "',
  '" . $shipping . "',
  '" . $tax . "',
  '" . $mc_currency . "',
  '" . $mc_fee . "',
  '" . $mc_gross . "',
  '" . $mc_gross_1 . "',
  '" . $mc_handling . "',
  '" . $mc_handling1 . "',
  '" . $mc_shipping . "',
  '" . $mc_shipping1 . "',
  '" . $txn_type . "',
  '" . $txn_id . "',
  '" . $notify_version . "',
  '" . $parent_txn_id . "',
  '" . $reason_code . "',
  '" . $receipt_ID . "',
  '" . $auction_buyer_id . "',
  '" . $auction_closing_date . "',
  '" . $for_auction . "',
  '" . $custom . "',
  '" . $invoice . "',
  '" . $protection_eligibility . "',
  '" . $charset . "',
  '" . $option_selection1 . "',
  '" . $subscr_id . "',
  '" . $verify_sign . "',
  '" . $option_name1 . "',
  '" . $btn_id . "',
  '" . $payment_fee . "',
  '" . $test_ipn . "',
  '" . $transaction_subject . "',
  '" . $payment_gross . "',
  '" . $ipn_track_id . "'
  )";
  
  if ($conn-&gt;query($sql) === TRUE) {
      echo "New record created successfully";
  } else {
      echo "Error: " . $sql . "&lt;br&gt;" . $conn-&gt;error;
  }
  
  $conn-&gt;close();
  }
  // Reply with an empty 200 response to indicate to paypal the IPN was received correctly.
  header("HTTP/1.1 200 OK");?&gt;
  </code></pre>
  
  <p>Reference: This is the source of the code I used to set up my IPN: <a href="https://github.com/paypal/ipn-code-samples" rel="nofollow noreferrer">https://github.com/paypal/ipn-code-samples</a></p>
  
  <p>Thank you for your time and help, it is greatly appreciated.</p>