CODEWITHSUNDEEP
phpmysql
Damian Curos
Damian Curos

Reputation: 51

How to prevent PHP from rounding a string (a big decimal)?

I have the following scenario:

    $amount = '1000000000.00000001';
    //echo $amount;

    $query = $mysqli->prepare("UPDATE `wallets` SET `dollars` = `dollars` + ?   WHERE `userId` = ?");        
    $query->bind_param('si', $amount, $id);
    $query->execute();

The column dollars in the table is DECIMAL(18,8).

Now the problem is that if I execute this query and the current value of dollars is 0, then the new value in the table will be 1000000000.00000000. As you can see, the .00000001 got lost in the ether and my question is why?

I am specifically preventing this from happening by binding the $amount as a String and not as an Integer, yet it still gets converted to float (i assume) somewhere and cut off.

If I do this instead:

    $query = $mysqli->prepare("UPDATE `wallets` SET `dollars` = `dollars` + 1000000000.00000001   WHERE `userId` = ?");        
    $query->bind_param('i', $id);
    $query->execute();

Then it works perfectly fine and the table will contain the expected value 1000000000.00000001

So why is the $amount treated as a number, even though I am treating it as a string to make sure PHP won't screw with it?

How can I fix it?

Upvotes: 2

Views: 409

Answers (3)

A. Denis
A. Denis

Reputation: 562

You can make mysql to calculate for you instead of php:

$amount = 1000000000.00000001;
$amountModifed = $amount * 100000000;

$query = $mysqli->prepare("UPDATE `wallets` SET `dollars` = `dollars` + ? WHERE `userId` = ?");        
$query->bind_param('ii', $amountModifed, $id);
$query->execute();

Upvotes: 0

symcbean
symcbean

Reputation: 48357

yet it still gets converted to float (i assume) somewhere and cut off

Like any bug, you start by isolating where the bug is occurring then either amend that code or work around the issue. From what you presented in your question you have already isolated where the problem is arising - its when you bind the parameter.

Now we've gone of the reservation somewhat, as MySQL happily handles numbers with different precision (and representation) than PHP. However you have tried to address this. It does look like the bind operation is at some point recognizing that the parameter is a number - and that would be a bug in PHP.

It is trivial to verify this: just enable query logging on the DBMS (if its not already setup) and check what SQL statement was actually submitted to the DBMS after binding.

I think it is more likely that the issue resides in MySQL. I expect you'll see something like this in your logs:

UPDATE `wallets` 
 SET `dollars` = `dollars` + '1000000000.00000001'   
 WHERE `userId` = 1234

Here MySQL has to convert a string value into something suitable to be added to wallets.dollars. And my money is on this implicit type casting being where you are losing the ....1.

In fairness to MySQL, it would be very difficult to implement this kind of dynamic casting losslessly across all the possible datatypes, so while I would consider this a bug, I don't think its likely to be fixed any time soon.

....but even if they do start working on a fix, it would be much, MUCH quicker for you to...

 $amount = '1000000000.00000001';

if (preg_match('/^([0-9.]*)$/', $amount)) {
  $query = $mysqli->prepare("UPDATE `wallets` SET `dollars` = `dollars` + "
     . mysqli->real_escape_string($amount)
     . "  WHERE `userId` = ?");        
  $query->bind_param('i', $id);
}

Normally its not necessary to do an additional validation on data when using bind params or mysql_real_escape_string, however as I said before we're off the reservation here and splicing an unquoted string into a SQL statement. Having said that, the regex check is ensuring that there is nothing in the string which should need escaped; the escaping is therefore redundant. But I get nauseous looking at SQL statements with un-escaped, spliced strings :).

Upvotes: 1

Solarflare
Solarflare

Reputation: 11106

If you use a string in the place where MySQL expects a number (because you add it to a number), it will autocast it to a float (or integer).

It has nothing to do with the parsing in php, since you want to parse it as a string. Parsing as a string means that php will add ' around it. So the explicit query you should compare the behaviour with is

 UPDATE `wallets` SET `dollars` = `dollars` + '1000000000.00000001'   
 WHERE `userId` = ?

and this will behave the same way as the parsed one (because, well, it is exactly what the parsing will produce).

So tell MySQL explicitely to cast the string as a decimal to prevent MySQL from doing autocasting:

UPDATE wallets SET dollars = dollars + cast((?) as decimal(18,8)) 
WHERE userId = ?

Another way would be to paste it directly (... SET dollars = dollars + $amount where ...), but that is generally a bad idea.

Upvotes: 2

Related Questions