website development: sql query returning only case-sensitive values when using LIKE to query from the database

Question

 <?php 
$searchErr="";

function test_input($data) {
            $data = trim($data); //whitespacess
            $data = stripslashes($data); //removes backslashes n clean data from database or form
            $data = htmlspecialchars($data); //converts predefined characters to html entities, encoding user input so that they cannot manipulate html codes
            return $data;
            }

if ($_SERVER["REQUEST_METHOD"]=="POST")
{

    if(isset($_POST["searchQuery"]))
    {
        if(empty($_POST["searchQuery"]))
        {
            $searchErr="Field cannot be empty!";
        }

        else //no error
        {




            $searchData=test_input($_POST["searchQuery"]);
            $searchData=preg_replace("#[^0-9a-z]#i","",$searchData);

            echo $searchData;
            require_once('includes/db_connect.php');


            $sQuery ="SELECT * FROM food WHERE Food_Name LIKE BINARY :searchData OR Food_Description LIKE BINARY :searchData";


            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $stmt=$conn->prepare($sQuery);

            $stmt->bindParam(':searchData',$searchData); //binding var to parameter
            $stmt->execute(); //executing prepared statement and returning

result to obj $numResults=$stmt->rowCount();

            if($numResults==0)
            {
                $msg="Your search did not match any of our available 
       foods!";
                echo $msg;
            } 

            else
            {
                while($row=$addResult->fetch(PDO::FETCH_ASSOC))
                {
                    echo $row['Food_Name'];
                    echo "<br/>";

                }
            }

        }

    }

   }



        ?>

        <?php

I am currently working on a website. In a section, I have a search bar which i use to search for text- match from my database. However, when I am getting the results, only texts which are case-sensitive are being returned. Please can u help me? I am using Apache and phpMyAdmin

Please provide sample data, expected results, as well as your current sql query in order to make your question answerable. — GMB, Nov 23 '19 at 15:39
While it is quite legal to declate a function inside 2 IF's, it is really not very logical — RiggsFolly, Nov 23 '19 at 15:50
Good code indentation would help us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Nov 23 '19 at 15:50
Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values — RiggsFolly, Nov 23 '19 at 15:51

GMB · Answer 1 · 2019-11-23T18:48:27.880

0

MySQL LIKE operator is case insensitive by design, unless the related column has a case-sensitive collation.

If you want a case-sensitive seach, you can use LIKE BINARY.

SELECT * 
FROM food 
WHERE 
    Food_Name LIKE BINARY :searchData 
    OR Food_Description LIKE BINARY :searchData

On the other hand, if your column has a case-sensitive collation and you want a case insensitive search, you can use lower() to turn both the column values and the search term to lower case before comparing:

SELECT * 
FROM food 
WHERE 
    LOWER(Food_Name) LIKE LOWER(:searchData)
    OR LOWER(Food_Description) LIKE LOWER(:searchData)

Please note that both approaches are not index-friendly. Bottom line, you want to use a column collation that does match your need. If you want a case-insensitive match, use a case-insensitive collation, else use a case-sensitive collation.

Important side note: your code is vulnerable to SQL injection. Do read about bind parameters and prepared statement, and consider fixing your code. I updated your query so it uses named bind variables.

edited Nov 23 '19 at 18:48

answered Nov 23 '19 at 15:47

GMB

216,147
25
84
135

String-matching operators such as `=` and `LIKE` in MySQL actually obey the collation in the column definition. That's good to know because collations are baked into indexes, so string matching can use them. If you override the collation as you did with `LIKE BINARY` the match can't use the index. – O. Jones Nov 23 '19 at 15:56
What i mean i should type the exact letter to get the result from the database. I need it to be case insensitive... – 0x12abc Nov 23 '19 at 16:21
@0x12abc: I added a solution for case-insensitive search on columns that have a case-sensitive collation. – GMB Nov 23 '19 at 18:49
@O.Jones: yes you are correct, this is an important point to mention. I updated my answer with more context. Side note: using `LIKE` with a wildcard on the left side is not index-friendly either... – GMB Nov 23 '19 at 18:50
Thanks.. i dont know how to use prepared statements to return multiple results tho – 0x12abc Nov 23 '19 at 19:02
@0x12abc: on how to use prepared statements, you have plenty of examples in the link that I put in my answer, and a quick google search will give you a lot more. – GMB Nov 23 '19 at 19:05
No problem still persists – 0x12abc Nov 23 '19 at 19:24
Can you write the proper formatting with prepared statements to echo all data please? – 0x12abc Nov 23 '19 at 19:36

website development: sql query returning only case-sensitive values when using LIKE to query from the database

1 Answers1