MySQL query WHERE case sensitive

Question

I have the following method: (Codeigniter model)

/**
 * Get an Ad based on its hash
 *
 * @param $hash
 * @return bool
 */
public function getAdbyHash($hash)
{
    $this->db->select('category.id as category_id, category.subcat as category_name, provinces.id as provinces_id, provinces.prov_name, users.id as users_id, users.*, ads.*');
    $this->db->where('ads.hash', $hash);

    $this->db->join('category', 'category.id = ads.subcat_id');
    $this->db->join('provinces', 'provinces.id = ads.province_id');
    $this->db->join('users', 'users.id = ads.user_id', 'left');

    $r = $this->db->get('ads');

    //echo $this->db->last_query();

    if ($r->num_rows() == 1) {
        $ad = $r->result()[0];
        return $ad;
    }
    return FALSE;
}

And it's works fine. but today I try to hack myself introducing a similar hash in the URL, and its work too, but that's not the desired behavior.
So this hash: edit/zIpM41NkS8igFXaC must NO be the same as edit/zIpM41NkS8igFXac

Note the last char (C/c) How do I query with case sensitive?

My approach was to use a specific method of Codeigniter and not using a direct SQL sentence. But works anyway.

Change column encoding to case sensititve? Also take a look at this [post](https://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql) — Rulisp, Oct 09 '17 at 23:25
Possible duplicate of [mysql, case sensitive compare through codeigniter](https://stackoverflow.com/questions/2538114/mysql-case-sensitive-compare-through-codeigniter) — Vixed, Oct 09 '17 at 23:27
@ErichGarcía do you have an access to your DB? Through phpmyadmin or mysql console — Rulisp, Oct 09 '17 at 23:30

Vixed · Accepted Answer · 2017-10-10T13:44:34.960

2

Try with:

$this->db->where('ads.hash like binary "'.$hash.'"', NULL, FALSE)

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. CodeIgniter Doc. Where

To clean the data, you can try something like:

public function getAdbyHash($hash)
{
    $db = get_instance()->db->conn_id;
    $hash= mysqli_real_escape_string($db, $hash);
    $this->db->select('category.id as.....'
    $this->db->where('ads.hash like binary "'.$hash.'"', NULL, FALSE);
    $this->db->join('cate......'

edited Oct 10 '17 at 13:44

answered Oct 09 '17 at 23:30

Vixed

3,429
5
37
68

OK, this works! But, what means the second and third parameter? – Erich García Oct 10 '17 at 00:12
1

String concatenation is very dangerous advice. – Michael - sqlbot Oct 10 '17 at 02:07
OK, now understand. The hash is obtained from a URL GET parameter. And I´m not protecting the field name. So, is this a risk for MySQL injection or XSS? – Erich García Oct 10 '17 at 13:31
Yes, my suggestion is to clean $hash in the function, before the $this->db->select. I'm quite busy now, but I think I can provide an example later. – Vixed Oct 10 '17 at 13:38
Hahaha, works perfectly, but, leave something for me, hahaha. – Erich García Oct 10 '17 at 14:28

Rulisp · Answer 2 · 2017-10-10T01:21:15.917

2

As @Vixed suggested, you can modify your where condition.

But also, you can modify your ads.hash column to be case sensitive

ALTER TABLE `ads` MODIFY `hash` VARCHAR(255) CHARACTER SET utf8 COLLATE `utf8_bin`;

Note that, for Unicode, you can use only utf8_bin for case sensitive comparison. This is because MySQL doesn't have case-sensitive Unicode collations(see this post)

But if your collation is latin, just change it to latin_general_cs.

Also, if you have some specific data in your column, you can take a look at this SO post - How to change Column Collation without losing or changing data?

UPD. Performance notes

Well, like binary looks like a very expensive operation (as mentioned here). Also, I think that you'll want to see this posts: SQL 'like' vs '=' performance and What effects does using a binary collation have?

Anyway, it requires testing

edited Oct 10 '17 at 01:21

answered Oct 10 '17 at 00:56

Rulisp

1,586
1
18
30

uhmmm, great tip. But, the one million question: Which solution is more optimal? I'm a newbie in MySQL, but I know that every millisecond count. Thanks! – Erich García Oct 10 '17 at 01:07
@ErichGarcía answer updated. Testing is really required, because if you'll loose couple ms per query, it's not critical at some level. But if difference will be in dozens of ms or even greater, you should optimize your search. Once I used additional column with CRC32 hash of string for search optimization – Rulisp Oct 10 '17 at 01:21
2

@ErichGarcía there is no need to compare. This is the correct solution as well as the most optimal. Casting, coercing, and conversions in the `WHERE` clause are almost always best avoided. And using string concatenation and interpolation as shown in the other answer is an unacceptable and dangerous practice. – Michael - sqlbot Oct 10 '17 at 02:07

MySQL query WHERE case sensitive

2 Answers2