Problem accessing database with PDO

Linux Database Administration
1

Hello,

I’m facing the following issue that I can’t resolve. Can you help me?
I’m using PHP 8.2, Apache2, and MariaDB on the Debian 12 system (6.1.69-1 2023-12-30 x86_64 GNU/Linux).

I want to verify the non-existence of the value “Roze” in the “Texte” field of the “objet” table in my “objet” database. The value “Rozé” exists but not “Roze”… yet my PDO query finds a result! Attached is the PHP sequence causing issues:

if (isset($_POST['modifier']) AND isset($_POST['texte']) AND $_POST['texte'] != NULL ) {    
 $trash = array_pop($_POST);            // eliminate the post of valid
 $trash = array_pop($_POST);            // eliminate the post of message
 
print_r($_POST);
 try {                  // start try processing
  $bdd->beginTransaction();         // start a transaction
  $idpere = $_POST['pere'];
  $idobjet = $_POST['objet'];       // Old object Id
  $libelle = protegeQuotes($_POST['texte']);
// check if the pair (texte,type object) is unique
  $options = selectObjet($bdd,$idpere,$libelle,1);

The function selectObjet

function selectObjet($bdd,$idpere,$texte,$visible) {
 $data = array();
 $options = array();
 if ($idpere == "") {
  $response = $bdd->query("SELECT Id,Texte FROM objet WHERE Texte ='".$texte."' AND visible = ".$visible.";") 
  or die('selectIdPere:'.$id.' '.print_r($bdd->errorInfo()));
 }
 else {
  $response = $bdd->query("SELECT Id,Texte from objet WHERE IdPere = '".$idpere."' 
  AND Texte LIKE '".$texte."' AND visible = ".$visible.";") 
  or die('selectIdPere:'.$id.' '.print_r($bdd->errorInfo()));
 }
 $i=0;
 while ($data = $response->fetch()) {
  $options[$i] = $data;
  $i++;
 }    
 $response->closeCursor();
 return $options;
}

Database connection function:

function openBase() {
 // returns the $bdd object instance of the PDO class
 $host = "localhost";       // server
 $param2 = "christian";  // database user
 $param3 = "blabla";     // database password
 $options = " --opt --single-transaction --skip-lock-tables ";
 $base = "objet";        // database name
 try {
  $param1="mysql:host=".$host.";dbname=".$base;
  $bdd = new PDO($param1,$param2,$param3); 
  $bdd->exec('SET NAMES utf8');
// set PDO attribute
  $bdd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // default error handling in php8
  $bdd->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);  // ineffective
 return $bdd;
 }
 catch (Exception $e) {
  die('Fatal error:'.$e->getMessage()); }
}

In $libelle, I loaded the value “Roze” (without accent).
The function selectObjet returns the array $options in which I have
Array ( [0] => Array ( [Id] => 30 [0] => 30 [Texte] => Rozé [1] => Rozé ) )

It seems as if searching for “Roze” (or “roze” for that matter) finds “Rozé”! However, the value “Roze” does not exist in the “objet” table and should not be found.

Under phpMyAdmin, the search for “Roze” does not yield any results, which is expected.

I tried changing some PDO attributes without success, such as ATTR_CASE, SET NAMES utf8
Nothing works!
The print_r of $options shows that by default, PDO performs a Fetch-both.

The “Texte” field of the “objet” table is in utf8mb4_general_ci like all the textual fields of the database.

Thanks for your help.

2

Actually, it doesn’t work with phpMyAdmin either!
utf8mb4_general_ci is a case-insensitive encoding!
One problem remains: is there a case-sensitive utf8 encoding type under MariaDB? It doesn’t appear in phpMyAdmin. Except for latin1_general_cs but that’s not utf8!
Any ideas?

3

Hi Olibiobus,

What you can try is the utf8mb4_bin collation.

So you can either alter your table. eg.:

> ALTER TABLE objet MODIFY Texte VARCHAR(255) COLLATE utf8mb4_bin;

or your query eg. :
FROM:
$stmt = $pdo->prepare("SELECT Id, Texte FROM objet WHERE Texte = ?");
TO:
$stmt = $pdo->prepare("SELECT Id, Texte FROM objet WHERE Texte = BINARY ?");

I have tested this solution locally and it seems to be working for me:

image
image877×928 70.6 KB

Now the solution with BINARY select query:

image
image940×845 74.1 KB

utf8mb4_bin collation is generally a good choice for case and accent sensitivity.

Hope this helps
Lubos

4

Thanks Lubos.
Effectively with collation utf8mb4_bin no prpblem ! my code runs fine !