I have a search function written by me to search a MYSQL database from a website form which calls a php script

website is LAMP Centos 7 with WHM

the database is MYSQL 5.7.36 fields are utf8_general_ci

Database Collation latin1_swedish_ci

Database Connection Collation utf8mb4_general_ci

perl 5 (revision 5 version 16 subversion 3)

the database connection is done like this:

sub connect
{
my $dsn = "DBI:mysql:host=$host_name";
my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1);

  $dsn .= ";database=$db_name" if defined $db_name;
  $dsn .= ";mysql_socket=$socket_file" if defined $socket_file;
  $dsn .= ";port=$port_num" if defined $port_num;

  return (DBI->connect ($dsn, $user_name, $password, \%conn_attrs ));
}
[download]

I have inserted a LOG_FILE into the script to check what is happening.

the code is intended to search for a surname (which is always uppercase in the database) in four fields of a table named 'g2_Item' checking that the public has access to this file (g2_accessMap.g_permission).

The function works fine as long as no accented characters are involved but as soon as an accented character is included (èéêëöüçÈÉËÊÖÜ) the function returns no rows from the database table = 'nothing found'

There seem to be 2 problems:

1. The accented characters are not being upcased - but even if the accented character is inclued already upcased, no rows are found - equally the MySQL database is case insensitive.

2. the term submitted for searching in the mysql select is not being found

the search term surname appears as it should in the log_file, eg: 'HÖRR' (for which there is an entry in the database).

If I copy and use that term from the log_file and use it in an sql query on PHPmysqlAdmin I get around 100 rows including all HORR and HÖRR entries in the database.

But when that term is passed by the perl code to mysql the result is 0 rows found.

Also I copied a php form and function (https://www.elithecomputerguy.com/2019/12/mysql-search-form-with-html-and-php/) and adapted it for my database - it works fine with accented characters, returning all the rows containing 'HÖRR' or 'HORR'.

this is the perl code:

use Cookbook_grave; # the connection as above
use CGI;
use CGI::Carp qw(fatalsToBrowser);

my $OK_CHARS='-a-zA-Z0-9\'\- èéêëöüçÈÉËÊÖÜ'; # A restrictive list, whi
+ch
                # should be modified to match
                # as appropriate 

$match_surname = $q->param("what_surname");
$match_surname = uc($match_surname);
#$match_surname =~ s/[^$OK_CHARS]/_/go;    

print LOG_FILE "surname: $match_surname\n";
    
# fetch all rows that match the surname 
# where title LIKE %$surname% 
   
$search_phrase = "%$match_surname%";

$dbh = $dbh_name;
$link_ref = $name_ref;

    my $sth = $dbh->prepare ("SELECT title, id, description, keywords,
+ summary
    FROM Item, AccessSubscriberMap, AccessMap
    WHERE (Item.title LIKE ?  
    OR Item.description LIKE ? 
    OR Item.keywords LIKE ?
    OR Item.summary LIKE ? )
    AND AccessSubscriberMap.itemId = Item.id
    AND AccessMap.accessListId = AccessSubscriberMap.accessListId
    AND AccessMap.userOrGroupId =4
    AND AccessMap.permission != 4096

    ORDER BY title");
    
    $sth->execute ("$search_phrase","$search_phrase","$search_phrase",
+"$search_phrase");

print LOG_FILE "search phrase: $search_phrase\n";    
    
my $lol_ref = $sth->fetchall_arrayref();

my $nrows = (defined ($lol_ref) ? @{$lol_ref} : 0);

print LOG_FILE "rows found: $nrows\n";

print LOG_FILE "search term 1: $search_term1\n";
print LOG_FILE "rows found: $nrows\n\n";
[download]

The log file records the $searchphrase as '%HöRR%'

the number of rows found is invariably 0.