PHP PDO parameter binding with UPPER and LOWER functions in Informix SQL WHERE condition does not work

Viewed 133

Works:

$pdo = new PDO();
$string = 'TEST';
$search_string = $string . '%';
$sql = 'SELECT * FROM companies WHERE name LIKE ? LIMIT 1';
$query = $pdo->prepare($sql);
$query->execute([$search_string]);
$result = $query->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

Doesn't work (difference being that UPPER(name) is used instead of name):

$pdo = new PDO();
$string = 'TEST';
$search_string = $string . '%';
$sql = 'SELECT * FROM companies WHERE UPPER(name) LIKE ? LIMIT 1';
$query = $pdo->prepare($sql);
$query->execute([$search_string]);
$result = $query->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

The error:

Uncaught PDOException: SQLSTATE[HY004]: Invalid SQL data type: -11064 [Informix][Informix ODBC Driver]SQL data type out of range.

Works (injecting search string directly into the query):

$pdo = new PDO();
$string = 'TEST';
$search_string = "'" . str_replace("'", "''", $string) . "%'";
$sql = "SELECT * FROM companies WHERE UPPER(name) LIKE $search_string LIMIT 1";
$query = $pdo->prepare($sql);
$query->execute();
$result = $query->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

Background information:

  • IBM Informix Dynamic Server Version 12.10.FC14WE
  • PHP version 7.2
  • Column "name" data type lvarchar (-1), size 1024

Questions:

  1. Any idea why the second example (using UPPER with parameter binding) is not working and how to resolve it?
  2. Is the query from the third example (search string directly in query) safe from SQL injection? If not then what needs to be done?
0 Answers
Related