Why Does My MySQL Prepared Statement with LIMIT Fail When Using String Parameters?

LIMIT Keyword on MySQL with Prepared Statement

Problem:

When using prepared statements with the LIMIT clause in MySQL, it fails when the parameters are passed as strings.

SELECT id, content, date
FROM comment
WHERE post = ?
ORDER BY date DESC
LIMIT ?, ?

$comments = $db->prepare($query);
$comments->execute(array($post, $min, $max));

Answer:

The issue arises because the PDOStatement::execute() method treats all parameters as strings, leading to an invalid SQL statement. This is compounded by the fact that MySQL will not cast string parameters to numbers, resulting in a parse error.

Solutions:

• Bind Parameters Individually:

$comments->bindParam(1, $post, PDO::PARAM_STR);
$comments->bindParam(2, $min, PDO::PARAM_INT);
$comments->bindParam(3, $min, PDO::PARAM_INT);

• Use sprintf() to Hardcode Numeric Parameters:

$query = sprintf('SELECT id, content, date
    FROM comment
    WHERE post = ?
    ORDER BY date DESC
    LIMIT %d, %d', $min, $max);

• Disable Emulated Prepares:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

The above is the detailed content of Why Does My MySQL Prepared Statement with LIMIT Fail When Using String Parameters?. For more information, please follow other related articles on the PHP Chinese website!