Small change breaks the mysql query

Question

Possible Duplicate:
PHP PDO bindValue in LIMIT

I have this code that works quite well.

if (array_key_exists('cat', $_GET) === TRUE) {
    $category = $_GET['cat'];
} else {
    $category = '.*';
}


$conn = new PDO('mysql:host=localhost;dbname=news', 'root', '');
$stmt = $conn->prepare('SELECT * FROM stories 
                        WHERE category RLIKE :cat
                        ORDER BY score DESC LIMIT 0, 25');
$stmt -> execute(array(
    'cat' => $category,
    ));

$result = $stmt->fetchAll();

As you can see, it gets a category from the get request, and searches the database for everything in that category.

I'm also trying to add a bit so that there can be a page defined in the get request, and the query will start 25 rows later for each increase by one in page.

Here's what I wrote:

if (array_key_exists('cat', $_GET) === TRUE) {
    $category = $_GET['cat'];
} else {
    $category = '.*';
}

if (array_key_exists('page', $_GET) === TRUE) {
    $page = intval($_GET['page'])*25;
} else {
    $page = 0;
}


$conn = new PDO('mysql:host=localhost;dbname=news', 'root', '');
$stmt = $conn->prepare('SELECT * FROM stories 
                        WHERE category RLIKE :cat
                        ORDER BY score DESC LIMIT :page, 25');
$stmt -> execute(array(
    'cat' => $category,
    'page' => $page
    ));

$result = $stmt->fetchAll();

But now, the query is returning nothing, no matter what page is, or if there is a category.

Maybe I'm not dealing with integers right. Any idea why I'm getting this result?

Can you pass the values to PDO like that if it's not a string? (Un-Experienced with PDO) — John V., Oct 29 '12 at 18:04
Maybe not. I'm unexperienced as well. I'm hoping there's some way to get that int into the query though. — user1624005, Oct 29 '12 at 18:07
Also http://stackoverflow.com/questions/5508993/pdo-limit-and-offset and some others. Please try to use the search function in the top right corner before asking. — kapa, Oct 29 '12 at 18:11

score 0 · Accepted Answer · edited May 23 '17 at 12:27

0

As in the referred example on how to apply values to LIMIT conditions:

$stmt -> execute(array(
  'cat' => $category,
  'page' => (int) $page
));

edited May 23 '17 at 12:27

Community

1
1

answered Oct 29 '12 at 18:32

tadman

208,517
23
234
262

score -2 · Answer 2 · answered Oct 29 '12 at 18:14

-2

Ray already gave you the answer: cast the user-submitted page variable to a integer (be sure to do that or you'll be vulnerable to SQL injection) and insert it into the query string directly without using a placeholder.

answered Oct 29 '12 at 18:14

Xano

62
5

PDO will properly sanitize all user input when using placeholders as in the example. The problem here is that the parameter is a string and is being correctly escaped, but the `LIMIT` clause does not work with string arguments so casting is required. – tadman Oct 29 '12 at 18:28

Small change breaks the mysql query

2 Answers2