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:
- Any idea why the second example (using UPPER with parameter binding) is not working and how to resolve it?
- Is the query from the third example (search string directly in query) safe from SQL injection? If not then what needs to be done?