SQL = vs LIKE vs LIKE BINARY, not case sensitive

Question

I have encountered rather weird behavior with SQL LIKE, = and LIKE BINARY

Note : The first 3 characters of password is actually 3Vf and the rest of the query is syntactically correct too.

SUBSTRING(password,1, 3) = "3VF"      -> returns true
SUBSTRING(password,1, 3) = "3Vf"      -> returns true

SUBSTRING(password,1, 3) LIKE "3VF"   -> returns true
SUBSTRING(password,1, 3) LIKE "3Vf"   -> returns true

However if i use LIKE BINARY, i get case sensitive behavior

SUBSTRING(password,1, 3) LIKE BINARY "3VF"   -> returns false
SUBSTRING(password,1, 3) LIKE BINARY "3Vf"   -> returns true

I dont understand why the comparisions are case insensitive. Considering the password is a VARCHAR(64). In all the resources I've seen online it says that = and LIKE both are case sensitive.

Note: the full query I'm running is

SELECT * from users where username="natas16" AND SUBSTRING(password,1, 3) = XX

Also, This is NOT a real world application but a natas level. It is sort of a 'hacking' playground. They have different levels with vulnerabilities you are supposed to exploit. So this is not a real world example.

http://www.overthewire.org/wargames/natas/

Answer 1

Whether the LIKE and = act in a case sensitive manner will be determined by the collation of the field you are doing the comparison on. If your field has a non-case-sensitive collation (like I'm guessing yours does) then you get non-case-sensitive comparison results. If the field has a binary or case-sensitive collation or if you used the BINARY keyword on the comparison to force a binary comparison, you would get the case-sensitive comparison.