How to make searching more sensitive in Codeigniter

Question

I need the searching in the database more sensitive and recognize the difference between the upper and lower case like passwords.

Model

function checker(){
    $this->db->select('*');
    $this->db->from('gift_cards');
    
    $this->db->join('gift_partners', 'gift_partners.gp_id = gift_cards.gift_part_id');
    if(isset($_POST["redeem"])){
        $redeemvalue=$this->input->post("redeem");
        $this->db->where('gift_id',$redeemvalue);
    }else{
    $this->db->where('gift_code',$this->input->post('gift_code'));
    $this->db->where('gift_part_id',$this->input->post('partner'));
    }
    
    $data=$this->db->get();
    if ($data->num_rows() > 0) {
        
        foreach ($data->result_array() as $row) {
                $dt[] = $row;
                
            }
        return $dt;
    }else{
        
        return false;
    }
}

I need the gift code more sensitive, I used WHERE condition but in searching back the same result if I searched with upper or lower case.

$this->db->where('gift_code',$this->input->post('gift_code'));

which database are you using? what is the type of the `gift_code` column? — Vickel, Nov 12 '20 at 20:33
also the collation is important, as by: https://stackoverflow.com/a/5629129/2275490 — Vickel, Nov 12 '20 at 20:46
"Like passwords" sounds strange - do you store them unencrypted? — Nico Haase, Nov 12 '20 at 20:55
@Vickel i chnaged thecolumn collection to utf8_bin and the where condtion to be like `$this->db->where('BINARY gift_code = '.$this->input->post('gift_code'));` but still not working — Mohamed Syam, Nov 12 '20 at 21:37
try `$this->db->where('gift_code', $this->input->post('gift_code'));`,as you are now using utf8_bin, it should be case sensitive — Vickel, Nov 12 '20 at 21:41
I just checked on myAdmin with a column gift_code set to: varchar(128), collation: latin1_bin: a WHERE query is absolutely case sensitive! — Vickel, Nov 12 '20 at 21:51

score 0 · Answer 1 · answered Nov 13 '20 at 08:49

MySQL is not case-sensitive while comparing characters but it can be made case-sensitive with use of key "BINARY".

$this->db->where('BINARY gift_code',$this->input->post('gift_code'));

The above code will not work. You need to use SQL to achive your required result.

function checker(){
    $rawSql = "SELECT * FROM gift_cards JOIN ON gift_partners.gp_id = gift_cards.gift_part_id";

    if(isset($_POST["redeem"])){
        $redeemvalue=$this->input->post("redeem");
    
        $rawSql.=" WHERE gift_id=?";    
        $query = $this->db->query($rawSql, [$redeemvalue]);
        
    }else{
        $redeemvalue=$this->input->post("redeem");
    
        // Case Sensitive Query
        $rawSql.=" WHERE BINARY gift_code=? AND gift_part_id=?";    
        $query = $this->db->query($rawSql, [
                                        $this->input->post('gift_code'),
                                        $this->input->post('partner'))
                                        ]);
    
    }
    
    if ($query->num_rows() > 0) {
        
        foreach ($query->result_array() as $row) {
                $dt[] = $row;
                
            }
        return $dt;
    }else{
        
        return false;
    }
}

How to make searching more sensitive in Codeigniter

1 Answers1