A PHP/MySQL Bug Most People Have But Don’t Realize
I’ve seen this over and over in my career and thought I should save others from the horror. Part of me feels like I blogged about this years ago, but I couldn’t find a post referencing it (EDIT: found it!). The bug is simple:
- Create a database table with a decimal value, such as order_total
- Write some code that retrieves the row
- Do an implicit boolean check on order_total to see if it has a value
Here’s some actual code:
$results = mysql_query("SELECT * FROM orders");
while($row = mysql_fetch_assoc($results)) {
if($row['order_total']) {
echo "Order total is clearly not zero!";
}
else {
log_bad_order($row['id']);
}
}
This code has a serious bug in it. The problem is the line pertaining to checking if the order_total has been set. Pop quiz:
What is the value of the following:
(bool) “0.00″
The answer is TRUE! 0.00 may evaluate to zero, but “0.00″ is not the same thing! As soon as PHP sees more than just a single “0″ in a string, it assumes it’s a regular string and treats it as a non-zero string. A more obvious way to ask the same question:
What is the value of the following:
(bool) “0.0000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000″
Or what about:
What is the value of the following: (bool) “0.”
The point is that as soon as you go beyond a single zero, PHP just assumes the rest is real data and will not discard it. Thus:
if(0.00) {
echo "THIS NEVER EXECUTES";
}
if("0.00") {
echo "THIS ALWAYS EVALUATES TO TRUE";
}
SO going back to the original problem, the way to solve is is by fixing the code to either explicitly type cast the variable or use a “greater than” check:
$results = mysql_query("SELECT * FROM orders");
while($row = mysql_fetch_assoc($results)) {
if((float) $row['order_total'] > 0) {
echo "Order total is clearly not zero!";
}
else {
log_bad_order($row['id']);
}
}
If you don’t do either of these things, I’d suggest you go and double check some of your code.