CakePHP is converting MySQL integers into strings... messing up rand() function

Question

I want to grab a random sample of data out of my database using CakePHP. Here's my function:

function categories_list() 
{   
    $this->paginate['limit'] = 6;
    $this->paginate['order'] = '';
    $this->paginate['conditions'] = '';

    // Sort Randomly Start 
    if ($this->Session->check('Category.randomSeed')) 
    { 
        $seed = $this->Session->read('Category.randomSeed'); 
    } else { 
        $seed = mt_rand(); 
        $this->Session->write('Category.randomSeed', $seed); 
    } 
    $this->paginate['order'] = sprintf('RAND(%d)', $seed); 
    // Sort Randomly End 

    $this->set('cat_ajax_items', $this->paginate('Category')); 
}

The problem is, the query that Cake sends to the DB always does this to the RAND() portion, sending MySQL into a hissy fit:

ORDER BY RAND(`1235123412341`)

Testing on a manual query, it works just fine, and returns a sample when it's formatted like this:

ORDER BY RAND(1235123412341)

Is there any way to get Cake to back off of the autoformatting? Anything I put into that RAND() function gets dumped into string quotes.

Mark Byers · Accepted Answer · 2011-01-29T08:27:55.630

4

Anything I put into that RAND() function gets dumped into string quotes.

No, this isn't correct. If it used string quotes then it would work fine, however backticks aren't string quotes. The problem is that CakePHP is quoting the number as if it were a column name. Try quoting the value using single quotes instead:

"RAND('%d')"

This should result in the following SQL being produced:

ORDER BY RAND('1235123412341')

This gives the same result as when you don't include the quotes.

edited Jan 29 '11 at 08:27

answered Jan 29 '11 at 08:21

Mark Byers

811,555
193
1,581
1,452

Worked like a charm. I'm admittedly a bit of a MySQL noob, so the whole backtick/quote distinction totally threw me. Thanks much, and here's your check ;) – dclowd9901 Jan 29 '11 at 15:47
But, how about when there are no quotes, like here: http://stackoverflow.com/questions/22622062/how-to-pass-an-integer-to-a-query-in-cakephp – Francisco Corrales Morales Mar 25 '14 at 20:01

score 0 · Answer 2 · answered Jan 29 '11 at 10:14

many applications and frameworks try to use a so called smart determination of the type of variable before they insert them into a database

however, many of these also fail with integers and strings :)

because of PHP's automatic typecasting, you can do a following check: is_int('01234') and that would return TRUE - but that actually is not true - the "number" is actually a string, starting with 0 - and so it should be handled (unless manually converted into an integet before, if that's what it should be)

you will need to adjust CakePHP's database class where it checks for data types

I'm not familiar with CakePHP, but CodeIgniter did use a following check in its escape() function:

if (is_string($str))

... which I've changed to:

if (is_string($str) && (mb_strlen((int) $str) != strlen($str)))

... and now it all works :)

P.S.: I've tried using (int) $str === $str, however that always yielded incorrect result

CakePHP is converting MySQL integers into strings... messing up rand() function

2 Answers2

Linked