Mysql database query case in-sensitive...

somiksomik OG
in Technical

So I just found out that mysql database queries are case insensitive. So how do we query case sensitive contents?

My usecase is generating those short URLs for file uploads/link redirection, etc. I used to generate a random string of alphanumeric chars and save it as the identifier. When the same string (short url) is provided, i query the database and retrieve the appropriate result. However I noticed that it doesn't matter whether I used all upper case or all lower case or mixed case, the mysql query ignores case when I query with the short url.

So all of these queries returns the same row:

SELECT * FROM MyTable WHERE short_url = 'h6Sp1A';
SELECT * FROM MyTable WHERE short_url = 'H6sP1a';
SELECT * FROM MyTable WHERE short_url = 'H6SP1A';
SELECT * FROM MyTable WHERE short_url = 'h6sp1a';

I do not want to use links that just contain either upper only or lower only chars mixed with numbers as in this case, the short url will be a bit long in this case...

So what to do about this?

As per my understanding, using BINARY to query will cause issues if both the input string and database are not in same character encoding so that's out.

One option I have is to hash the short url (md5, sha128) and search for the hash instead of the url itself, like we do for passwords.

Do you guys/gals have any other ideas better idea?

Artificial intelligence is no match for our natural stupidity.

Time flies like an arrow; fruit flies like a banana.

Comments

  • yoursunnyyoursunny OG

    @somik said:
    As per my understanding, using BINARY to query will cause issues if both the input string and database are not in same character encoding so that's out.

    Convert string to UTF8 prior to insertion/query.

    Thanked by (1)somik

    ServerFactory aff best VPS; HostBrr aff best storage.

  • tetechtetech OG

    Define the column with a collation type ending in _bin

    Thanked by (1)somik
  • ehabehab

    are you able

    select * from MyTable where short_url collate latin1_swedish_ci = "H6SP1A";

    choose the correct encoding first?

    Thanked by (1)somik
  • somiksomik OG

    @yoursunny said:

    @somik said:
    As per my understanding, using BINARY to query will cause issues if both the input string and database are not in same character encoding so that's out.

    Convert string to UTF8 prior to insertion/query.

    Yes, that is certainly an option but wont I need to ensure that the database encoding is also in utf8?

    @tetech said:
    Define the column with a collation type ending in _bin

    Sorry, could you please explain a bit about this? Do you mean what @ehab is recommending?

    @ehab said:
    are you able

    select * from MyTable where short_url collate latin1_swedish_ci = "H6SP1A";

    choose the correct encoding first?

    Oh... this might actually work with collate type utf8... Need to try it with MySQL/MariaDB.

    Artificial intelligence is no match for our natural stupidity.

    Time flies like an arrow; fruit flies like a banana.

  • tetechtetech OG

    @somik said: Sorry, could you please explain a bit about this? Do you mean what @ehab is recommending?

    CREATE TABLE MyTable (short_url CHAR(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, ...); or do ALTER on an existing column.

    It functionally ends up doing the same, but putting it in the schema means you shouldn't have to adjust every query.

    Thanked by (3)ehab yoursunny somik
  • somiksomik OG

    @tetech said:

    @somik said: Sorry, could you please explain a bit about this? Do you mean what @ehab is recommending?

    CREATE TABLE MyTable (short_url CHAR(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, ...); or do ALTER on an existing column.

    It functionally ends up doing the same, but putting it in the schema means you shouldn't have to adjust every query.

    Thank you very much for the SQL code. I was planning to modify my tables anyway as the existing tables are not working so having actual code really helps!

    Artificial intelligence is no match for our natural stupidity.

    Time flies like an arrow; fruit flies like a banana.

Sign In or Register to comment.