PHP PostgreSQL PDO can't bind parameter with LIKE

Question

Using PHP PDO to attempt a simple search for term in database. The error return implies that PDO can't determine the param type. I'm explicitly specifying it as STR when binding it and also casting it to varchar in the query string. The query works fine in MySQL without the CAST.

// Get course retry limit from database
$query = $this->db_connection->prepare("
SELECT column FROM datbase.table
WHERE
column LIKE CAST(CONCAT('%', :search_term, '%') as varchar)
GROUP BY column"
);
$query->bindValue(':search_term', $search_term, PDO::PARAM_STR);
$query->execute();

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: :search_term 

If I explicitly escape the string in the query with single quotes as I would do in a direct query call, I get another error:

Fatal error: Uncaught PDOException: SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $1

Answer 1

I solved this by moving the CAST inside directly to the parameter. Not sure why it's needed since the PHP variable is a string which has been encoded to uppercase.

Here is the correct query:

$query = $this->db_connection->prepare("
SELECT column FROM datbase.table
WHERE
column LIKE CONCAT('%', CAST(:search_term as VARCHAR), ' %')
GROUP BY column"
);
$query->bindValue(':search_term', $search_term, PDO::PARAM_STR);
$query->execute();