1

I have a simple database_read function and per accident I noticed order_id's starting with a leading zero would not give me the expected results.

It turns out using $wpdb->prepare strips out leading zero's. How can i prevent this and still use the prepare method?

Here's the function:

function database_read($order_id)
{
    // $order_id = intval($order_id);//this will also strip leading zero
    // echo $order_id;
    //  exit();

    global $wpdb;

    // This doesn't work: 
    // $sql = $wpdb->prepare("SELECT payStatus FROM test_table WHERE ref = %s",$order_id);

    // This works:
    $sql = "SELECT payStatus FROM test_table WHERE ref = {$order_id}";

    $res = $wpdb->get_results($sql, ARRAY_A);                     
    return $res;
}
3

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.

  • Wow, didn't see that one. In my case the leading zero is part of a random barcode (...should have excluded a leading zero :(). Each barcode is stored in mysql varchar field. I am going to look into my php code and check where i have converted it to a decimal – alex Nov 23 '15 at 17:02

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.