How to debug variable not posting to MYSQL database

Question

I'm trying to figure out how to debug an issue with the posting of a variable to my database. Everything is being posted, but the username. Any tips on how to debug this would be much appreciated.

The username does run through a couple of functions first. I'm not getting any errors with the script itself, but I'm also not getting anything to post to the database.

<?php

// Check to see there are posted variables coming into the script
if ($_SERVER['REQUEST_METHOD'] != "POST") die ("No Post Variables");
// Initialize the $req variable and add CMD key value pair
$req = 'cmd=_notify-validate';
// Read the post from PayPal
foreach ($_POST as $key => $value) {
    $value = urlencode(stripslashes($value));
    $req .= "&$key=$value";
}
// Now Post all of that back to PayPal's server using curl, and validate everything with PayPal
// We will use CURL instead of PHP for this for a more universally operable script (fsockopen has issues on some environments)
//$url = "https://www.sandbox.paypal.com/cgi-bin/webscr";
$url = "https://www.paypal.com/cgi-bin/webscr";
$curl_result=$curl_err='';
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,$url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/x-www-form-urlencoded", "Content-Length: " . strlen($req)));
curl_setopt($ch, CURLOPT_HEADER , 0);   
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
$curl_result = @curl_exec($ch);
$curl_err = curl_error($ch);
curl_close($ch);

$req = str_replace("&", "\n", $req);  // Make it a nice list in case we want to email it to ourselves for reporting

// Check that the result verifies
if (strpos($curl_result, "VERIFIED") !== false) {
    $req .= "\n\nPaypal Verified OK";
} else {
    $req .= "\n\nData NOT verified from Paypal!";
    mail(".com", "IPN interaction not verified", "$req", "From: .com" );
    exit();
}

/* CHECK THESE 4 THINGS BEFORE PROCESSING THE TRANSACTION, HANDLE THEM AS YOU WISH
1. Make sure that business email returned is your business email
2. Make sure that the transaction’s payment status is “completed”
3. Make sure there are no duplicate txn_id
4. Make sure the payment amount matches what you charge for items. (Defeat Price-Jacking) */

// Check Number 1 ------------------------------------------------------------------------------------------------------------
$receiver_email = $_POST['receiver_email'];
if ($receiver_email != ".com") {
    $message = "Investigate why and how receiver email is wrong. Email = " . $_POST['receiver_email'] . "\n\n\n$req";
    mail(".com", "Receiver Email is incorrect", $message, "From: .com" );
    exit(); // exit script
}
// Check number 2 ------------------------------------------------------------------------------------------------------------
if ($_POST['payment_status'] != "Completed") {
    // Handle how you think you should if a payment is not complete yet, a few scenarios can cause a transaction to be incomplete
}
// Connect to database ------------------------------------------------------------------------------------------------------
require_once 'connect_to_mysql.php';
// Check number 3 ------------------------------------------------------------------------------------------------------------
$this_txn = $_POST['txn_id'];
$sql = mysql_query("SELECT id FROM transactions WHERE txn_id='$this_txn' LIMIT 1");
$numRows = mysql_num_rows($sql);
if ($numRows > 0) {
    $message = "Duplicate transaction ID occured so we killed the IPN script. \n\n\n$req";
    mail(".com", "Duplicate txn_id in the IPN system", $message, "From: .com" );
    exit(); // exit script
} 
// Check number 4 ------------------------------------------------------------------------------------------------------------
$product_id_string = $_POST['custom'];
$product_id_string = rtrim($product_id_string, ","); // remove last comma
// Explode the string, make it an array, then query all the prices out, add them up, and make sure they match the payment_gross amount
$id_str_array = explode(",", $product_id_string); // Uses Comma(,) as delimiter(break point)
$fullAmount = 0;
foreach ($id_str_array as $key => $value) {

    $id_quantity_pair = explode("-", $value); // Uses Hyphen(-) as delimiter to separate product ID from its quantity
    $product_id = $id_quantity_pair[0]; // Get the product ID
    $product_quantity = $id_quantity_pair[1]; // Get the quantity
    $sql = mysql_query("SELECT price FROM products WHERE id='$product_id' LIMIT 1");
    while($row = mysql_fetch_array($sql)){
        $product_price = $row["price"];
    }
    $product_price = $product_price * $product_quantity;
    $fullAmount = $fullAmount + $product_price;
}
$fullAmount = number_format($fullAmount, 2);
$grossAmount = $_POST['mc_gross']; 
if ($fullAmount != $grossAmount) {
        $message = "Possible Price Jack: " . $_POST['payment_gross'] . " != $fullAmount \n\n\n$req";
        mail(".com", "Price Jack or Bad Programming", $message, "From: .com" );
        exit(); // exit script
} 

//
//
require_once 'connect_to_mysql.php';

//now to always get unique username
$username = substr($payer_email, 0, strpos($payer_email, '@'));
if ( ! uniqueName($username))
{
    $username = makeUniqueName($username);
}


//function to check if is the existing username
function uniqueName($username)
{
    $sql = mysql_query("SELECT username FROM transactions WHERE username='$username'");
    $numRows = mysql_num_rows($sql);
    if ($numRows > 0)
    {
        return false;
    }

    return true;
}


//function to generate new unique username
function makeUniqueName($username)
{
    //serch username string for number at the end
    //regexp makes sure all preceeding zeroes go to first match group
    if (preg_match('/^(\S*?0*)?(\d+?)$/', $username, $match))
    {
        //we got digit from the end of string, just add 1 to the digit
        $username = $match[1] . ($match[2] + 1);
    }
    else
    {
        //no digit at the end of string, just add digit 1 at the end
        $username = $username . 1;
    }

if (uniqueName($username))
{
    return $username;
}

    return makeUniqueName($username);
}

// END ALL SECURITY CHECKS NOW IN THE DATABASE IT GOES ------------------------------------
////////////////////////////////////////////////////
// Homework - Examples of assigning local variables from the POST variables
$txn_id = $_POST['txn_id'];
$payer_email = $_POST['payer_email'];
$custom = $_POST['custom'];
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$payment_date = $_POST['payment_date'];
$mc_gross = $_POST['mc_gross'];
$payment_currency = $_POST['payment_currency'];
$txn_id = $_POST['txn_id'];
$receiver_email = $_POST['receiver_email'];
$payment_type = $_POST['payment_type'];
$payment_status = $_POST['payment_status'];
$txn_type = $_POST['txn_type'];
$payer_status = $_POST['payer_status'];
$address_street = $_POST['address_street'];
$address_city = $_POST['address_city'];
$address_state = $_POST['address_state'];
$address_zip = $_POST['address_zip'];
$address_country = $_POST['address_country'];
$address_status = $_POST['address_status'];
$notify_version = $_POST['notify_version'];
$verify_sign = $_POST['verify_sign'];
$payer_id = $_POST['payer_id'];
$mc_currency = $_POST['mc_currency'];
$mc_fee = $_POST['mc_fee'];
$password = mt_rand(1000, 9999); 
$p_hash = md5($password);
$username = $_POST['username'];
 //

// Place the transaction into the database
$sql = mysql_query("INSERT INTO transactions (product_id_array, payer_email, first_name, last_name, payment_date, mc_gross, payment_currency, txn_id, receiver_email, payment_type, payment_status, txn_type, payer_status, address_street, address_city, address_state, address_zip, address_country, address_status, notify_version, verify_sign, payer_id, mc_currency, mc_fee, password, ip, username) 
   VALUES('$custom','$payer_email','$first_name','$last_name','$payment_date','$mc_gross','$payment_currency','$txn_id','$receiver_email','$payment_type','$payment_status','$txn_type','$payer_status','$address_street','$address_city','$address_state','$address_zip','$address_country','$address_status','$notify_version','$verify_sign','$payer_id','$mc_currency','$mc_fee','$p_hash','$ip','$username')") or die ("unable to execute the query");
$to      = $payer_email;  
$subject = 'Learn  | Login Credentials';  
$message = ' 

Your officially all ready to go. To login use the information below.

Your account login information 
------------------------- 
Email: '.$payer_email.' 
Password: '.$password.' 
------------------------- 

You can now login at https://www..com/signin.php';  
$headers = 'From:noreply@.com' . "\r\n";  

mail($to, $subject, $message, $headers);  
mysql_close();
// Mail yourself the details
mail(".com", "NORMAL IPN RESULT YAY MONEY!", $req, "From: ");

?>

Answer 1

Debugging involves:

1. Determining what the value of the variables during the script execution should be.
2. Determining what the value of the variables during the script execution actually are.
3. Determining the reason for a mismatch between the two.

This informatation can then be use to change the code so that actual and expected values of the variables match.

For item 1, an understanding of what the code should and how it should do it is necessary. The code itself is of little help at this point, because the code, failing to be able to read the programmers mind, does not know what is expected of it.

For item 2, you can place print statements in the code (and remove them after debugging). A better way is often to use a debugger like XDebug, preferably in combination with an IDE like Eclipse PDT.

As an example, I assume after

$sql = mysql_query("INSERT INTO transactions (product_id_array, payer_email, first_name, last_name, payment_date, mc_gross, payment_currency, txn_id, receiver_email, payment_type, payment_status, txn_type, payer_status, address_street, address_city, address_state, address_zip, address_country, address_status, notify_version, verify_sign, payer_id, mc_currency, mc_fee, password, ip, username) 

the value of the $sql variable should be true. A print($sql) will confirm or refute this assumption. If it is false, a call to mysql_error() will shed some light on the reason why the query failed. If it is true and a record with the expected username is not inserted, The value of $username is probably not what you expected.

Answer 2

Just a guess: Your $username variable seems to be generated based on the email first and running through various functions.

//now to always get unique username
$username = substr($payer_email, 0, strpos($payer_email, '@'));
if ( ! uniqueName($username))
{
    $username = makeUniqueName($username);
}
[...]

but at the end you overwrite it with:

$username = $_POST['username'];

are you sure this field should be posted? maybe you just have to remove this line?