PDO Madness

TenaciousC

I have recently started using PDO as I understand it's a 1000 times better for security, etc. Imagine my surprise when I find out that the majority of my queries are not working. After much teeth gnashing, I find that the problem lies in the fact that PDO doesn't seem to like parameters anywhere but in the WHERE clause. Take this example.

// ... blah blah, connection
$params = array(
      ':gallery_id'      =>      1,
      'page_offset'      =>    0,
      'page_limit'      =>      25
);

$sql = "SELECT * FROM gallery WHERE gallery_id = :gallery_id LIMIT :page_offset, :page_limit";

// ... do query stuff here with PDO

No dice. If I do the following, it works:

$sql = "SELECT * FROM gallery WHERE gallery_id = :gallery_id LIMIT 0, 25";

I also noticed that PDO doesn't seem to like taking field names for ORDER BY clauses.

So am I stuck with having to use mysql_real_escape_string() for variables that need to be put in anywhere other than the where clause? This seems a bit counter productive.

johanafm

I havn't used PDO, but I did notice a syntax discrepancy

':gallery_id'
'page_offset'

Notice the missing ':'?

laserlight

Your example does not appear to actually use the PDO extension.

But I presume that you are doing something like this:

$params = array(
    ':gallery_id' =>  1,
    'page_offset' =>  0,
    'page_limit'  => 25
);

$sql = "SELECT * FROM gallery WHERE gallery_id = :gallery_id LIMIT :page_offset, :page_limit";
$stmt = $db->prepare($sql); // assume $db is the PDO connection object
foreach ($params as $placeholder => $param)
{
    $stmt->bindValue($placeholder, $param, PDO::PARAM_INT);
}
$stmt->execute();
// ...

If so, a possible problem is that you used 'page_offset' and 'page_limit' instead of [noparse]':page_offset'[/noparse] and [noparse]':page_limit'[/noparse].

TenaciousC

Sorry about the bad code. I rewrote it for this post, didn't see that I failed to put the :page_limit and :page_offset parameters correctly. My code actually does look correct, and I am indeed using PDO. I just left out the PDO stuff for brevity.

My code does look like that, laserlight, however the parameters are correctly written.

Doing more research, I did find an article (that I failed to bookmark, sadly) that talks about how you aren't able to use field names as parameters (say, in an ORDER BY clause). However, it would stand to reason you should be able to use them in LIMITs.

Roger_Ramjet

Not being able to use vars for fields names is a limitations of SQL, not PDO. So you just have to use string concatenation before you submit the sql statement for preparation.

As to the original problem; it must be an error in your syntax. That being the case, post the code you are using not some other code: we are not psychic.