PHP int being converted to different string by MySQL

Question

I've got this piece of code in a Magento script:

$order = $observer->getEvent()->getOrder();
$customer_id = $order->getBillingAddress()->getCustomerId();

$idArray = array('LN123456789XZY'); // this is just arbitray sample data, the array can be any length

$prequery = "UPDATE $detailedTable SET ordered=1 WHERE customer_id=$customer_id AND image_id IN (";
$qPart = array_fill(0, count($idArray), "?");
$prequery .= implode(",", $qPart) . ")";
$query = $dbLink->prepare($prequery);
$i = 1;
foreach($idArray as $elem){
        $query->bindValue($i++, (string) $elem, PDO::PARAM_STR);                
}
$query->execute();

And what I'm seeing is the value for $customer_id is being converted into a string with a completely different value. The data types in my table for those two columns are:

customer_id => VARCHAR(64)
image_id => TEXT

So I realize that my first problem is that I'm not surrounding $customer_id with single quotes. But what’s happening because of it is an int value like 1011 in PHP is getting converted to something like 'g-608311' when it gets written to MySQL. And its happenening intermittently.

So as I'm pondering how to clean things up in my table, I'm curious how MySQL is coming up with that 'g-608311' string from the int 1011 when there's the data type mismatch?

EDIT: I updated the code snippet to show where the $customer_id value actually comes from. Also, I thought of this earlier today while pondering this issue, but when I initially ran this code, I was getting an exception with the message:

message SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'g-608311'

The surrounding single quotes are not mandatory. And there must be something else (in PHP!) causing $customer_id to be overwritten. Please show more code. (Don't give pseudocode or do the thinking for us, by already filling in values that actually come from other places/functions) — nl-x
– nl-x, Commented May 6, 2014 at 22:20
Not sure if it matters, but the value that I assign to $customer_id in my code snippet is what Xdebug is showing me is the value when I get to this block of code. But I'll post more of the code when I get to my work computer in the morning. — Mike Levy
– Mike Levy, Commented May 7, 2014 at 4:50

Niet the Dark Absol · Accepted Answer · 2014-05-06 22:17:57Z

3

Although I can't tell you exactly why you get different values due to your unquoted string input, but I can point out how ridiculous it is to use prepared statements... and then embed variables directly in the query anyway.

Your query should be:

$prequery = "UPDATE `literal_table_name_here` SET `ordered`=1
   WHERE `customer_id` = ?
   AND `image_id` IN (".str_repeat("?,",count($idArray)-1)."?)";
$query = $dbLink->prepare($prequery);
$query->bindValue(1,$customer_id);
foreach($idArray as $i=>$elem) {
   $query->bindValue($i+2, $elem, PDO_PARAM_STR);
}
$query->execute();

answered May 6, 2014 at 22:17

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

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

4 Comments

nl-x Over a year ago

Though what you say is true, it is not an answer to the question. Nor a solution to the problem.

Mike Levy Over a year ago

So I can learn/understand a little better to become a better programmer, but why is using a variable in a string and then passing the result into a prepared statement frowned upon? I'm envisioning as a example where I'd want to do that is something like I've got a generic class for talking to a database and I'm passing in the table name as a variable such that I can't just hard-code it in. Is there like a bad coding practice/security problem/something else that makes it not good?

Niet the Dark Absol Over a year ago

@MikeLevy Passing in a table name defined in a constant is just about okay, however with values like the customer_id you're kind of defeating the purpose of prepared statements by just injecting it in, and as you found out doing so can lead to problems!

Mike Levy Over a year ago

@NiettheDarkAbsol I tested out your solution and it looks to have taken care of the issue. I'm still not 100% sure of the 'why' part, but now I know the 'where' so I'm happy with that. Preciate the insight!

Collectives™ on Stack Overflow

PHP int being converted to different string by MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related