how best to make a case_sensitive query

Question

SELECT * FROM `table_users` WHERE BINARY nome = $nome AND senha = $senha

Is that correct, or would it be bad practice?

Answer 1

It’s a mistake to use BINARY for this case, if you are storing passwords. By the way, passwords should not be stored in DB.

The BINARY completely ignores Unicode and character equivalence, so a Á compound will always be treated differently from a Á simple-character.

In short: it can happen that, depending on the OS and the application, a user type the same character, and it is sent in different ways, invalidating a legitimate access, and the user will not even know what happened.

It is right to store hashes, on the other hand. Hashes are naturally "binary" in the sense that they can use any character of 0x00 to 0xff. I’ve seen a lot of people storing hashes in hexadecimal, but by storing directly in binary you spend half the space.

In the names, even, would not have reason to be CS, except some very specific restriction of the application. Having two different users called "José" and "josé" is a good confounding principle.

Collations

The solution in these cases is to use the collations correct in each column of the seat, as required.

You can use it natively by specifying when creating the table (or changing it), or you can use the verb COLLATE DB at query time.

For example, in Unicode, you have the utf8_bin, which considers the character code in Unicode, and not the bytes individual. Note that when displaying on the screen, it may be interesting to force a ORDER BY column COLLATE utf8_general_ci to maintain the expected alphabetic order, if you are really going to use this in the names.

In latin, you have the endings _ci for _case_insensitive_ and _cs for sensitive.

Better understand here:

What is the difference between charset and collation in a database?

Answer 2

Short answer: it is correct and it is not, it depends on the need. In your case, dealing with user and password, it would be better to validate the data entry with a specified pattern, user names are generally considered valid only with lowercase numbers characters and etc, following this context if you always validate the input of the data so that such value is in such a way (always lowercase for example), you will not need to apply specific things when making queries for the database for example.. But if you want a username to have uppercase characters, then this implementation would make sense. I hope it helps!

Answer 3

It is not incorrect but has consequences.

The BINARY keyword casts the string for a binary string, forcing comparison byte to byte and not character to character, making comparison case sensitive and making whitespace at the beginning of the string meaningful.

If you really need the comparison for this field to be case sensitive, it is more appropriate that you make the entire BINARY column, because using the BINARY keyword in the query cancels the use of the index if that column is indexed. This is bad, and in your case, this column should be indexed.

In the example below note that the xname index will not be used when using BINARY:

    mysql> explain select * from tb where xnome = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb
   partitions: NULL
         type: ref
possible_keys: xnome
          key: xnome
      key_len: 153
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0,00 sec)

mysql> explain select * from tb where BINARY xnome = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0,00 sec)

This does not occur when you create the column as BINARY, the index is also already created in an appropriate way.

You can change the column type:

ALTER TABLE nome_tabela CHANGE nome_coluna nome_coluna VARCHAR(XX) BINARY NOT NULL;