Optional LIMIT for a function grabbing all post with PDO

Question

I'm trying to create a function that will grab all posts within a table. I also wanted to add a optional LIMIT parameter. Here is an example:

function get_all_posts($conn, $limit = 0) {
if ($limit > 0) {
    $stmt = $conn->prepare("SELECT * FROM posts LIMIT :limit");
    $stmt->execute(array(
        ':limit' => $limit
    ));
    $results = $stmt->fetchAll();
    return $results ? $results : false ;
} else {
    $stmt = $conn->prepare("SELECT * FROM posts");
    $stmt->execute();
    $results = $stmt->fetchAll();
    return $results ? $results : false ;
}
}

If I call the function without using the limit parameter it works and displays all the posts. But if I call the function like this: get_all_posts($conn, "1"); Then I get this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1'' at line 1' in /Applications/MAMP/htdocs/sandbox/blog2/functions.php:19 Stack trace: #0 /Applications/MAMP/htdocs/sandbox/blog2/functions.php(19): PDOStatement->execute(Array) #1 /Applications/MAMP/htdocs/sandbox/blog2/index.php(12): get_all_posts(Object(PDO), '1') #2 {main} thrown in /Applications/MAMP/htdocs/sandbox/blog2/functions.php on line 19

Can anyone show me where I've gone wrong?

score 3 · Answer 1 · answered Nov 06 '12 at 23:00

3

1 is not a string, so don't put quotes around it here: get_all_posts($conn, 1);

answered Nov 06 '12 at 23:00

Sammitch

30,782
7
50
77

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

1

By default PDO execute() treats parameters as a string. As such it is quoting "1". You will need to use bindParam().

Although MySQL may handle this, you should bind this parameter accordingly (as an INT). See this related question for more detail.

edited May 23 '17 at 12:27

Community

1
1

answered Nov 06 '12 at 23:02

Jason McCreary

71,546
23
135
174

You're a life saver, Thank you! You say that MySQL may handle this, but in my case it didn't does this mean that in some cases it would work and some it wouldn't? – Ben Nov 06 '12 at 23:23
MySQL sometimes is lenient on string to integer comparison depending on our configuration. Either way, I think it's best to bind `limit` as an integer in this case. Looks like **hakre** has since provided a code example. – Jason McCreary Nov 07 '12 at 02:46

score 0 · Answer 3 · answered Nov 06 '12 at 23:03

0

Like Sammitch said, its because its a string, not an integer. Use this to fix:

if (is_numeric($limit)) {
    $limit = (int)$limit;
    ...

to clear any variable type issues

answered Nov 06 '12 at 23:03

topherg

4,203
4
37
72

There is actually no reason to check if it's numeric – zerkms Nov 06 '12 at 23:03
@zerkms I always ensure what I convert to an integer is a number beforehand in the event that something other than a number (even in a string) is entered to stop erroneous operation, but for this, `(int)'alskdfj'` would still equal 0 – topherg Nov 06 '12 at 23:06
@cgoddard: argument for `LIMIT` should **ALWAYS** be a number. So there is no reason to check it at all. "but for this, (int)'alskdfj' would still equal 0" --- so what? – zerkms Nov 06 '12 at 23:08
@user1801541 just use `(int)$limit` then – topherg Nov 06 '12 at 23:09
I tried: if ($limit > 0 && is_numeric($limit)) { and I removed the quote marks around the function call but I'm still getting the same error. – Ben Nov 06 '12 at 23:09
@zerkms yup, but in cases when its not just for that, just what i was taught for good practice – topherg Nov 06 '12 at 23:09

score 0 · Answer 4 · answered Nov 06 '12 at 23:22

In the LIMIT clause you need an integer parameter.

In your code you pass the :limit' parameter's value viaexecute` which are all strings.

A string is not an integer. This mismatch creates your issue.

Instead bin the parameter as integer and you are fine.

$stmt = $conn->prepare("SELECT * FROM posts LIMIT :limit");
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$success = $stmt->execute();
$results = $stmt->fetchAll();

As you accept the $limit variable via a function parameter, you should sanitize it's value as well:

$limit = (int) $limit;

That will ensure you are using a variable of type integer when binding it as an integer parameter.

Optional LIMIT for a function grabbing all post with PDO

4 Answers4