PHP MySQL PDO syntax error in Bindparam

Question

Hi I'm trying to select some random rows from table using MySQL PDO using PHP

$query = $conn->prepare("SELECT * FROM `products` WHERE `cat` = :cat ORDER BY RAND() LIMIT :limit_to");

$query->bindParam(':limit_to', $limit, PDO::PARAM_INT);
$query->bindParam(':cat', $cat, PDO::PARAM_INT);
$stmt = $query->execute();

However this throws a mysql syntax error like this,

"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 ''2'' at line 1"

What's causing this error and why? I don't see anything wrong in the

Does $limit have quotes? Seems like it does from the double single quotes around the 2 in the error message. Also not sure that the '2' from limit, is that the case? — chris85
– chris85, Commented Apr 10, 2015 at 12:57
No $limit doesn't have quotes, $limit comes from a $_REQUEST['limit'] which is an integer and also I've used PDO::PARAM_INT to make sure it's integer so why am I getting the error? I also noticed the double quotes too — rksh
– rksh, Commented Apr 10, 2015 at 13:14

Alexander R. · Accepted Answer · 2015-04-10 15:20:28Z

The problem is that when you do $limit = $_REQUEST['limit'];, variable $limit has type string.

For example (both contains number, but variable type is different):

$varInt = 2;
$varString = "2";

var_dump ($varInt);
var_dump ($varString);

prints:
    int 2
    string '2' (length=1)

So your prepared statement becomes to be bound with:

$limit = $_REQUEST['limit']; // $_REQUEST['limit'] = 2
$cat = 3;

SELECT * FROM `products` WHERE `cat` = :cat ORDER BY RAND() LIMIT :limit_to

Bound with :limit_to='2', :cat='3'

The problem is in LIMIT syntax. Symbols '' breaks the syntax. You must be sure that you bind integer variable to $limit.

// do it in first place you have access to $_REQUEST['limit'] or other global arrays , for example $_GET, $_POST

$limit = intval($_REQUEST['limit']); // $_REQUEST['limit'] = 2
 $cat = intval($blablabla); // or from any other source

$query = $conn->prepare("SELECT * FROM products WHERE 
                         cat = :cat ORDER BY RAND() 
                                    LIMIT :limit_to");

$query->bindValue(':limit_to', $limit, PDO::PARAM_INT);
$query->bindValue(':cat', $cat, PDO::PARAM_INT);
$stmt = $query->execute();

p.s Use bindValue() , it's better in 99% cases.

Collectives™ on Stack Overflow

PHP MySQL PDO syntax error in Bindparam

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related