PDO error "Truncated incorrect DOUBLE value" using placeholder-CodePudding

I have the following PHP code that works perfectly ($qry_str is actually generated in the PHP):

$qry_str = <<<'QRY'
FIND_IN_SET('6-47', attributes)
    AND FIND_IN_SET('4-176', attributes)
    AND FIND_IN_SET('9-218', attributes)
QRY;

$pdo->query('DROP TEMPORARY TABLE IF EXISTS `temp_attr`');

$temp_sql = <<<"TEMP"
CREATE TEMPORARY TABLE IF NOT EXISTS `temp_attr` (
    SELECT product_id, GROUP_CONCAT(CONCAT(group_id, '-', IF (custom_value != '', custom_value, value_id)) SEPARATOR ',') AS attributes
    FROM `products_attributes`
    GROUP BY `product_id`
    HAVING $qry_str
);
TEMP;

$pdo->query($temp_sql);

$sql = "SELECT
            m.recommended_price   AS msrp,
            m.purchase_price      AS cost,
            pp.USD                AS regular_price,
            pc.USD                AS special_price,
            pc.start_date         AS start_date,
            pc.end_date           AS end_date,
            pl.permalink          AS permalink,
            pi.name               AS description,
            m.sku                 AS sku,
            m.default_category_id AS cat,
            m.id                  AS prod_id

          FROM `products`                    AS m
          LEFT JOIN `products_prices`        AS pp  ON m.id = pp.product_id
          LEFT JOIN `products_campaigns`     AS pc  ON m.id = pc.product_id
          LEFT JOIN `permalinks`             AS pl  ON (m.id = pl.resource_id AND pl.resource = 'product')
          LEFT JOIN `products_info`          AS pi  ON (m.id = pi.product_id)
          LEFT JOIN `products_to_categories` AS ptc ON (m.id = ptc.product_id)
          INNER JOIN `temp_attr`                AS pa

          WHERE ptc.category_id = :cat
          AND m.status = 1
          AND m.id = pa.product_id
          LIMIT 55;
";

$data = $pdo->prepare($sql)
            ->bindValue('cat', 100)
            ->execute()
            ->fetchAll();

However, when I use a placeholder in the temp table code, i.e.

$temp_sql = <<<"TEMP"
CREATE TEMPORARY TABLE IF NOT EXISTS `temp_attr` (
    SELECT product_id, GROUP_CONCAT(CONCAT(group_id, '-', IF (custom_value != '', custom_value, value_id)) SEPARATOR ',') AS attributes
    FROM `products_attributes`
    GROUP BY `product_id`
    HAVING :qry_str
);
TEMP;

$sth = $pdo->prepare($temp_sql);
$sth->bindValue('qry_str', $qry_str, PDO::PARAM_STR);
$sth->execute();

I get the following error:

PHP Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'FIND_IN_SET('6-47', attributes) AND FIND_IN_SET('4-176', attributes) AND FIND_IN_SET('9-218', attributes) AND FIND_IN_SET(...'

There are no datetime columns in this table.

group_id and value_id are integer columns

Since the code works fine without the placeholder, I'm at a loss as to why the use of a placeholder breaks the code. The placeholder in the main SQL works properly.

PHP 8.0

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/prepare.html explains:

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

In your case, you're apparently trying to bind an expression with the FIND_IN_SET() function. You can't do that. All expressions and other SQL syntax must be fixed in the query at the time you prepare it. You can use a parameter only in a place where you would otherwise use a scalar literal value. That is, a quoted string or a numeric literal.