Using BINARY function in MySQL for case sensitive search

Using BINARY function in MySQL for case sensitive search

Generally when we search for a string stored in MySQL database we resort to using “Like” or “=” operators but it returns a case-insensitive search result only. One of my recents projects required me to conduct a case-sensitive search. There are two ways to deal with it. 1. Changing the collation from ci to cs            – In MySQL the cs refers to case-sensitive string and ci refers to case-insensitive one. The COLLATE operator can be used to make the search case-sensitive but it may not be possible in all cases. In some cases the database might have already been desgined or there needs to be some consistency maintained for collation. Or 2) Binary Function :           -This is the function that is provided by MySQL to compare columns byte-by-byte and can be used to perform case-sensitive search.  For ex:  SELECT * FROM TABLE _NAME WHERE FIELD=BINARY(‘VALUE TO BE COMPARED’);

Drawback: Though this function is more efficient in fetching us the required result,it has some serious drawbacks too as the server generates a SQL injection Alert.  As per my understanding this function converts the provided string to binary value and compares it with other value. As it passes  binary value, there is a scope of SQL injection. So to be on the safer side we should escape it  before passing the value to binary function. We can usemysql_real_escape_string for this. SELECT * FROM `TABLE` t WHERE t.`name` = BINARY(mysql_real_escape_string(‘mindfire’));