CODEWITHSUNDEEP
phpsql-serverodbc
Bee H.
Bee H.

Reputation: 283

ODBC binding parameters not sending full parameter

The company I work for uses PHP and SQL Server for our main, customer facing website. For communication between the site and the database, we use PHP's native ODBC functions. Using this, whenever using variables sent in from the web, I always use binding parameters, and they have always worked for me. The problem I encountered was that when storing credit card data using binding parameters, only the first digit was passed to the database. Because the numbers were encrypted, the problem was not immediately noticed. I understand where the problem happened, but cannot figure out why.

Background: Last week I set up a web page for one of our biggest events of the year, and used a slightly different call to the database than I usually do when storing the credit card number. I tested it, saw that information was getting stored, and pushed it out for production. Fast forward to Friday, when I went to check some of the numbers, for the sake of this event's payment processing page, and I learned that we did not have any of the numbers on file. We only had the first digit. On the registration page, I followed the input all the way to the database call, and even after the call, and found it to be whole the entire process.

Here are the relevant functions I was using

<?php 
// ...
$registerID = placeRegistration($eventInfo["ID"], $attendeeFirst, $attendeeLast, $attendeePhone, $attendeeEmail, $attendeeClub);
if($registerID === NULL){
    $errStr .= "Unable to place registration. Please try again later.<br>";
}else{
    if(placePayment($registerID, _POST("bigInt"), _POST("bigIntMon"), _POST("bigIntYear"), _POST("totalCost")) === false){
    $errStr .= "Unable to place payment for this registration. Please try again.<br>";
}
// ...
?>
<!-- ... -->
<?php
/**
* Places a registration into EventsRegister.
* Returns the ID of the added entry if successful, else returns false.
*/
function placeRegistration($eventID, $firstName, $lastName, $phone, $email, $clubCard){
    $placeRegister = odbc_prepare($GLOBALS["dbh_WEB"], "
        INSERT INTO EventRegisters(EventID, FirstName, LastName, Phone, Email, ClubCard)
        OUTPUT INSERTED.ID
        VALUES (?, ?, ?, ?, ?, ?)
    ");

    if(!odbc_execute($placeRegister, array($eventID, $firstName, $lastName, $phone, $email, $clubCard))){
        $registerID = false;
        print_error(9, array($eventID, $firstName, $lastName, $phone, $email, $clubCard), __FILE__, __LINE__);
    }else{
        $temp = odbc_fetch_array($placeRegister);
        $registerID = $temp["ID"];
    }

    return $registerID;
}

/**
* Adds the payment for the corresponding registration ID.
* Returns true if successful, else false
*/
function placePayment($registerID, $pink, $expMo, $expYr, $total){
    $placePayment = odbc_prepare($GLOBALS["dbh_WEB"], "
        INSERT INTO EventPayments(RegisterID, Pink, ExpMo, ExpYr, Total)
        VALUES (?, ENCRYPTBYPASSPHRASE('".$GLOBALS["pinkySwear"]."', ?), ?, ?, ?)
    ");

    if(!odbc_execute($placePayment, array($registerID, $pink, $expMo, $expYr, $total))){
        return false;
    }else{
        return true;
    }
}

The $GLOBALS mentioned in there are: $pinkySwear the encryption password, $dbh_WEB the database handle

$dbh_WEB = odbc_connect("Driver={SQl Server Native Client 11.0};Server=$server;Database=website", $server_user, $server_password)
             or die('Something went wrong while connecting to MSSQL');

The problem statement is this one,

$placePayment = odbc_prepare($GLOBALS["dbh_WEB"], "
    INSERT INTO EventPayments(RegisterID, Pink, ExpMo, ExpYr, Total)
    VALUES (?, ENCRYPTBYPASSPHRASE('".$GLOBALS["pinkySwear"]."', ?), ?, ?, ?)
");
if(!odbc_execute($placePayment, array($registerID, $pink, $expMo, $expYr, $total))){
    return false;
}else{
    return true;
}

This statement, for some reason, only enters the first digit of $pink. If I were to use the following statement, which I was under the impression functions the same way, it works perfectly fine.

$placePayment = odbc_prepare($GLOBALS["dbh_WEB"], "
    INSERT INTO EventPayments(RegisterID, Pink, ExpMo, ExpYr, Total)
    VALUES (?, ENCRYPTBYPASSPHRASE('".$GLOBALS["pinkySwear"]."', '$pink'), ?, ?, ?)
");
if(!odbc_execute($placePayment, array($registerID, $expMo, $expYr, $total))){
    return false;
}else{
    return true;
}

What is the reason that the binding parameter would truncate a 12-19 digit number to just the first digit?

Our systems are using PHP 7.1.19, ODBC Driver "SQL Server native Client 11.0", SQL Server 12.0.5579.0

Upvotes: 1

Views: 443

Answers (1)

tukan
tukan

Reputation: 17345

A bug.

My guess is that it has to do with the variable expansion. Hard to tell without debugger and more information like seeing what is really being sent (wireshark).

I tried to search in the php odbc source code, but could not find anything fishy.

EDIT based on comment

The expansion is somehow hindered when used in function ENCRYPTBYPASSPHRASE in conjunction with odbc_prepare/odbc_execute (currently unknown how).

If you add the $pink directly you will get the value as that is represented by that variable there is no processing via the odbc_prepare/odbc_execute.

Upvotes: 1

Related Questions