PDO and Firebird prepared SELECT query with LIKE is not working

Question

I'm trying to execute a query with LIKE '%txt%' in a Firebird database, Im using PHP with PDO and the connection is OK, I'm just not getting results (the echo returns []) with this code:

header('Content-type: text/plain; charset=ISO-8859-1');

$host = 'firebird:dbname=192.x.x.x:/usr/interbase/test.gdb';
$username = 'test';
$password = 'test';

try{
  $db = new PDO($host, $username, $password);
  $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
  echo "Error: " . $e->getMessage();
}

$txt = "%Oll%";

$sql = "SELECT PRV_CODIGO id, PRV_RAZSOC text ";
$sql .= "FROM T_MYTABLE ";
$sql .= "WHERE PRV_RUT LIKE ':txt' OR PRV_RAZSOC LIKE ':txt'";

try{
  $select = $db->prepare($sql);
  $select->bindValue(":txt",$txt,PDO::PARAM_STR);
  $select->execute();
  $result = $select->fetchAll(PDO::FETCH_ASSOC);
  //var_dump($select);
  echo json_encode($result);
}catch (PDOException $e) {
  echo $e->getMessage();
}

I have tested this things:

If I uncomment that var_dump, it shows the following in the web browser:

object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(106) "SELECT PRV_CODIGO id, PRV_RAZSOC text FROM T_MYTABLE WHERE PRV_RUT LIKE ':txt' OR PRV_RAZSOC LIKE ':txt'"
}

And you can see that the string is keeping the ':txt' intact.

If I put the variable directly in the query, with no prepared statement, like this:

$sql = "SELECT PRV_CODIGO id, PRV_RAZSOC text ";
$sql .= "FROM T_MYTABLE ";
$sql .= "WHERE PRV_RUT LIKE '$txt' OR PRV_RAZSOC LIKE '$txt'";

It works well, it brings the results and that var_dump shows the following:

object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(108) "SELECT PRV_CODIGO id, PRV_RAZSOC text FROM T_MYTABLE WHERE PRV_RUT LIKE '%Oll%' OR PRV_RAZSOC LIKE '%Oll%'"
}

It works because the string is now getting the $txt value and not preserving the ':txt'. Of course I can't put that variable there because I'm trying to do this with prepared statement.

So I need to know what is happening, I have never had this kind of problems with MySQL.

Normally, bind variables don't have quotes round them - `LIKE :txt` — Nigel Ren, Apr 07 '21 at 20:46
@NigelRen That is right, but Firebird will throw error 804 if I remove that quotes. I never use those quotes when working with MySQL. — Roberto Sepúlveda, Apr 07 '21 at 21:07
I suspect your problem is that when you unquote the parameters (as you should, otherwise they are just treated as strings), you are running into the issue that the firebird driver doesn't support using the same parameter name more than once. Try `WHERE PRV_RUT LIKE :txt OR PRV_RAZSOC LIKE :txt2` and binding `$txt` to both `:txt` and `:txt2` — Nick, Apr 07 '21 at 23:38
Firebird doesn't have named parameters; I don't program in PHP, so I don't know if PDO translates these or not, but otherwise you'll have to use positional parameters, that is `?`. If you use `':txt'` like you're doing now, then you are simply using a string literal with the value `:txt`. — Mark Rotteveel, Apr 08 '21 at 08:05
Also, error 804 doesn't exist in Firebird, maybe you mean -804 which covers at least 7 different errors, so you'd better provide the actual error message. — Mark Rotteveel, Apr 08 '21 at 08:11
i think Nick is right here. PDO does introduce perameters naming - https://www.bobby-tables.com/php - but it probably has one-to-one between names and partameters. So when Roberto sets many parameters with the same name - he then only binds first of them, and other parameters with same name remain non-bound — Arioch 'The, Apr 08 '21 at 09:21
@RobertoSepúlveda if you `need to know what is happening` then take FBProfiler.sf.net or IBExpert or any other TraceAPI gui and see exactly what queries and data are coming into Firebird server. And skipping to copy exact error text you are setting a block against nayone trying to understand what is happening in between your program and Firebird. https://www.chiark.greenend.org.uk/~sgtatham/bugs.html — Arioch 'The, Apr 08 '21 at 09:26

score 1 · Answer 1 · answered Apr 08 '21 at 08:16

When you use ':txt', then you're not specifying a parameter, you're specifying a string literal with length 4 and the characters :, t, x, t as its value.

Firebird itself doesn't support named parameters, but a quick look at the PDO documentation suggests it performs the translation for you. The solution then would be to use :txt (no quotes!), or a positional parameter: ?.

So either:

SELECT PRV_CODIGO id, PRV_RAZSOC text 
FROM T_MYTABLE 
WHERE PRV_RUT LIKE :txt OR PRV_RAZSOC LIKE :txt

Or:

SELECT PRV_CODIGO id, PRV_RAZSOC text 
FROM T_MYTABLE 
WHERE PRV_RUT LIKE ? OR PRV_RAZSOC LIKE ?

In this last case, you will need to bind with 1-based indexes for each parameter:

$select->bindValue(1, $txt, PDO::PARAM_STR);
$select->bindValue(2, $txt, PDO::PARAM_STR);

PDO and Firebird prepared SELECT query with LIKE is not working

1 Answers1