1

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

5
  • Can you reproduce 1-digit insert with PHP's PDO which can use odbc drivers? Commented Sep 18, 2018 at 17:35
  • Unfortunately I cannot test this at this time. We have pdo_odbc.dll disabled, and as this is on our production server I cannot just go switching things on & off. Commented Sep 18, 2018 at 18:29
  • Understood. From what I find, PHP's PDO tends to be a more robust database API than PHP odbc and even designated in the industry I believe as the premier DB-API for PHP where you can interface different drivers like sqlsrv and odbc. Commented Sep 18, 2018 at 19:32
  • What type is attribute field pink in your EventPayments table? Commented Sep 19, 2018 at 14:16
  • @tukan It is VARBINARY(80) Commented Sep 19, 2018 at 14:23

1 Answer 1

1
+50

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.

Sign up to request clarification or add additional context in comments.

2 Comments

I'm going to accept this, as this does seem to be the correct answer. It is not, though, determinant on the use of the $GLOBALS variable. Even when putting the passphrase directly into the statement, i.e. ...BYPASSPHRASE('PASSWORD', ?) the output is still only the first char of whatever is passed in. This outcome is only produce when using binding parameters.
@GeoffreyH. Thank you. I'll then edit the answer. If I should find any information I'll update the answer. Hmm maybe the ENCRYPTBYPASSPHRASE itself somehow. You could try to use different SQL Server functionality like adding linked server (sp_addlinkedsrvlogin) and try if the expansion works there. One more question what type is the '".$GLOBALS["pinkySwear"]."' you are sending? You could try it out with different types as ENCRYPTBYPASSPHRASE as it supports nvarchar, char, varchar, binary,varbinary, or` nchar` as a passphrase.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.