How to prevent $wpdb->prepare stripping a leading zero in variable value?

Try this:

$order_id = 012345;
$sql = $wpdb->prepare("SELECT payStatus FROM test_table WHERE ref = %s",$order_id);//<-- this doesn't work
var_dump($sql);
echo '<br/>';

$sql = $wpdb->prepare("SELECT payStatus FROM test_table WHERE ref = %d",$order_id);//<-- this doesn't work
var_dump($sql);
echo '<br/>';        

$sql = $wpdb->prepare("SELECT payStatus FROM test_table WHERE ref = %f",$order_id);//<-- this doesn't work
var_dump($sql);
echo '<br/>'; 

$sql = $wpdb->prepare("SELECT payStatus FROM test_table WHERE ref = %s",(string)$order_id);//<-- this doesn't work
var_dump($sql);
echo '<br/>';

$order_id = '012345';
$sql = $wpdb->prepare("SELECT payStatus FROM test_table WHERE ref = %s",$order_id);//<-- this doesn't work
var_dump($sql);
echo '<br/>';

You will notice that all but the last convert to 5349. This is because a leading zero in PHP indicates an octal number. Your zero isn’t being stripped, exactly. It is being converted into a decimal.

In effect, you are passing in an octal number or you have somewhere converted a string with a leading zero into an integer. I can’t tell exactly where that happens but you need to prevent it.

Really, it isn’t prepare that is doing this. It is more PHP core.

Related Posts: