0

I have a mysql database that contain a table with 5 columns

a column has a name db_aya contain Shaped letters like this  

إِنَّ ٱلَّذِينَ كَفَرُواْ سَوَآءٌ عَلَيۡهِمۡ ءَأَنذَرۡتَهُمۡ أَمۡ لَمۡ تُنذِرۡهُمۡ لَا يُؤۡمِنُونَ

The user want to make a search on this column

I have an input field where the user can type the one or more words to make a search but the user is writting unshaped letters like this

إن الذين كفروا سواء

and in this situation the user get 0 result

How to solve this problem when the user type unshaped letters to get the result if exist in the database Note that in the above example the both are similar but one is shaped and the other is not

I tired this code:

<?php
include_once "../includes/connect.php";
$header = "content-type: application/json; charset=utf-8";
$search = mysqli_real_escape_string($conn, $_GET['search']) ;

$response = array();
// Check connection
if ($conn) {
    $sql = mysqli_query($conn, "select db_id, db_aya from tbl_khire where db_aya LIKE '%$search%' order by db_id asc") or die(mysqli_error($conn));
    if ($sql) {
        header($header);
        $i = 0;
        while ($row = mysqli_fetch_array($sql)) {
            $response[$i]['aya'] = $row['db_aya'];
            echo $row['db_aya'];
            $i++;
        }
        $json = json_encode($response, JSON_UNESCAPED_UNICODE);
        echo $json;
    }
} else {
    echo "database connection failed";
}
?>
Mohamad
  • 602
  • 2
  • 5
  • 18
  • 2
    **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unparameterised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. mysqli_real_escape_string is obsolete and doesn't guard against everything. – ADyson Jul 21 '23 at 08:39
  • https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use that resource again. – ADyson Jul 21 '23 at 08:39
  • And please bring your error handling into the 21st century. Add `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` before your `mysqli_connect()` (or `new mysqli()`) command, and this will ensure that errors with your SQL queries are reported correctly to PHP automatically. That way you don't need to clutter your script with repetitive code to keep checking errors after every mysqli command. And you should never be echoing error data deliberately - it can easily reveal sensitive info to attackers by accident. – ADyson Jul 21 '23 at 08:39
  • P.S. I tagged this "arabic" hoping that's correct, but please [edit] it, if it's a different language. Remember that this is an English-speaking site with users from everywhere in the world, so most of us are unlikely to understand the non-English text you have posted. Thanks. – ADyson Jul 21 '23 at 08:44
  • This behaviour is controlled by database collation (the set of rules to sort and compare characters in different languages), PHP does not really play any role here. MySQL allows to set collation at many levels: database, table, column and even specific query clauses. – Álvaro González Jul 21 '23 at 09:12
  • @ÁlvaroGonzález what rules should i put to have the result of the search – Mohamad Jul 21 '23 at 09:14
  • For a start, which one are you currently using? [Documentation](https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html) does not get into a lot of detail and I can't speak any language with non-Latin scripts so I'm pretty much clueless. – Álvaro González Jul 21 '23 at 09:20
  • File a feature request at bugs.mysql.com -- requesting a better Collation for Arabic. – Rick James Jul 23 '23 at 06:52

1 Answers1

-2

After a lot of trying i solve the problem using this code

First i remove all diacritics from the input

Then i remove them using an sql query using REPLACE

Check the code below:

<?php
include_once "../includes/connect.php";
$header = "content-type: application/json; charset=utf-8";
$response = array();
$searchValue = mysqli_real_escape_string($conn, $_GET['search']);
// List of characters to remove (diacritics and other symbols)
$charactersToRemove = array('َ', 'ُ', 'ِ', 'ً', 'ٌ', 'ٍ', 'ّ', 'ۖ', 'ۗ', 'ۘ', 'ۚ', 'ۛ', '۠', 'ۡ', 'ۢ', 'ۦ', 'ۧ', 'ۨ', '۪', '۫', '۬', 'ء');

// Remove characters from user input
$userInputWithoutDiacritics = str_replace($charactersToRemove, '', $searchValue);

// Check connection
if ($conn) {
    $sql = mysqli_query($conn, "SELECT * FROM tbl_khire WHERE
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(db_aya, 'َ', ''), 'ُ', ''), 'ِ', ''), 'ً', ''), 'ٌ', ''), 'ٍ', ''), 'ّ', ''), 'ۖ', ''), 'ۗ', ''), 'ۘ', ''), 'ۚ', ''), 'ۛ', ''), '۠', ''), 'ۡ', ''), 'ۢ', ''), 'ۦ', ''), 'ۧ', ''), 'ۨ', ''), '۪', ''), '۫', ''), '۬', ''),'ء','')
    LIKE CONCAT('%', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    '$userInputWithoutDiacritics', 'َ', ''), 'ُ', ''), 'ِ', ''), 'ً', ''), 'ٌ', ''), 'ٍ', ''), 'ّ', ''), 'ۖ', ''), 'ۗ', ''), 'ۘ', ''), 'ۚ', ''), 'ۛ', ''), '۠', ''), 'ۡ', ''), 'ۢ', ''), 'ۦ', ''), 'ۧ', ''), 'ۨ', ''), '۪', ''), '۫', ''), '۬', ''),'ء',''), '%')") or die(mysqli_error($conn));

    if (mysqli_num_rows($sql) > 0) {
        header($header);
        $i = 0;
        while ($row = mysqli_fetch_array($sql)) {
            $response[$i]['aya'] = $row['db_aya'];
            $i++;
        }
        $json = json_encode($response, JSON_UNESCAPED_UNICODE);
        echo $json;
    }
} else {
    echo "database connection failed";
}

This code help me to make a search with arabic letter that contain diacritics from user or in the database

Mohamad
  • 602
  • 2
  • 5
  • 18