Charlie Smith Charlie Smith - 7 months ago 50
SQL Question

PDO Prepared Statements with % and concat

I am converting some MySQL queries in PHP into PDO prepared statement queries.

Most queries are simple enough, but I have come across a couple of queries and I Am not sure how they should look

in the regular queries I am converting:

where field ='$type'


into

where field =:type


and changing

$results->execute();


into

$results->execute(array('type'=>$type));

Scenario 1

usually when converting them the variable becomes :type in the query, but with the % there does it become :type% or :type'%'

where field like '$type%'


Scenario 2

When concat in used

concat(uuid(),'-$id')


how is this supposed to be handled? how do I handle this when there is a - in front of the variable?

Any help would be appreciated.

Answer

The thing you need to understand is that a placeholder have to represent a complete data literal only.

And thus your code will be:

Scenario 1

Create a search string with wildcard characters in PHP,

$type = "$type%";

and then you can bind it the usual way.

Scenario 2

Just add one more argument for CONCAT():

concat(uuid(),'-', :id)