CODEWITHSUNDEEP
phpmysqlcollationcase-insensitive
Tomaure
Tomaure

Reputation: 45

Case Insensitive MySQL Database

On my MySQL database, my collation is set to utf8_general_ci. I also checked the collation of table in my database. This should allow for case insensitive queries. However all queries are still are still case sensitive.

For example, this query will not work if I have the username with the first letter capitalized. 

  SELECT * 
  FROM   users 
  WHERE  username = 'test' 
  AND    password = 'test'

I am using MySQL 5.5.27 Community edition. Thanks.

Upvotes: 2

Views: 1124

Answers (3)

Saleem
Saleem

Reputation: 1049

Follow the @mauris answer. Otherwise you use strtolower($username) in php and 

SELECT * FROM users WHERE LOWER(username) = 'test' AND password = 'test'

Upvotes: 0

Izodn
Izodn

Reputation: 152

I don't know too much about databases set to be case insensitive, but you can use Lower()

eg: 

SELECT * FROM users WHERE Lower(username) = 'test' AND lower(password) = 'test'

Upvotes: 0

mauris
mauris

Reputation: 43619

Your collation set to ci (case-insensitive) only affects the way you search in data, not columns. IIRC, column names are case sensitive and table names aren't.

Follow a standard naming convention for your database.

Upvotes: 1

Related Questions