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

$GLOBALS

$pinkySwear

$dbh_WEB

$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.

$pink

$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

pdo_odbc.dll

pink

EventPayments

VARBINARY(80)

1 Answer
1

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.

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

ENCRYPTBYPASSPHRASE

odbc_prepare

odbc_execute

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.

$pink

odbc_prepare

odbc_execute

$GLOBALS

...BYPASSPHRASE('PASSWORD', ?)

ENCRYPTBYPASSPHRASE

sp_addlinkedsrvlogin

ENCRYPTBYPASSPHRASE

nvarchar

char

varchar

binary

varbinary

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy