PHP PDO PostgreSQL issue

Question

Having problems passing a parameter to a sql query if the parameter has spaces in it. The code is

$statement="select  di_timestamp, di_item_value
from data_item
where
fk_fc_id=(select fc_id 
        from field_column
        where 
        fc_description ilike :sensor
        and
        fk_mds_id=( select mds_id 
                    from monitored_data_set
                    where fk_pa_id=(select pa_id 
                        from pilot_ambient 
                        where   
                        pa_ambient_name ilike :room
                        and 
                        fk_sp_id=(
                            select sp_id 
                            from School_Pilot 
                            where sp_description ilike :name
                            )
                        )
                    )
                )";
$query = $databaseConn->prepare($statement);
$query->execute(array(':sensor'=>$sensor,':room'=>$room,':name' => '%'.$school.'%'));

However if sensor contains any spaces the query fails. How can I resolve this? I've tried putting quotes in the parameter, quotes in the query, but nothing works.

PDO prepared statements parameters should never need quotes -- the parameters are not replaced "in place" the query is "prepared" by the RDBMS (kind of like creating a procedure) and the it is called using the parameters (as parameters passed to the procedure). — Mihai Stancu
– Mihai Stancu, Commented Nov 1, 2013 at 11:39
However, it's producing it, it's producing it wrongly, quotes or no quotes. Does anyone know how to resolve this? — James
– James, Commented Nov 1, 2013 at 14:19
query fails means rejected with an error message (which one?), or that results are not what you expect? — Daniel Vérité
– Daniel Vérité, Commented Nov 2, 2013 at 14:45

Daniel Vérité · Accepted Answer · 2013-11-02 14:44:45Z

1

You may pass space characters in prepared statements parameters without quotes or any special care.

Here's a demo:

<?
$pdo = new PDO('pgsql:dbname=test');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = "SELECT 1 WHERE 'abc de' ilike :pattern";
try {
  $stmt = $pdo->prepare($query);
  $stmt->execute(array(":pattern"=>'ABc %'));
  var_dump($stmt->fetchAll());
}
catch(PDOException $e){
   echo "Error".  $e->getMessage();
}
?>

It yields no error and displays the proper result:

array(1) {
  [0]=>
  array(2) {
    ["?column?"]=>
    int(1)
    [0]=>
    int(1)
  }
}

If the datatype of the column to match is of type CHAR(n) as opposed to VARCHAR(n) or TEXT, be aware that trailing spaces are treated as non significant.

answered Nov 2, 2013 at 14:44

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

Subtle error (to me anyway!) I was using single quotes to construct the array for execute, you were using double quotes. Once I'd changed to double quotes it worked fine. Thanks for helping out, appreciated.

Collectives™ on Stack Overflow

PHP PDO PostgreSQL issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related