Do Not Use Case Insensitive String Primary Keys in Ruby on Rails
Using case insensitive primary keys of type string/varchar/char doesn’t work very well in Rails. It works well as far as case is the same. But in my scenario, the primary key was unix username and as user logged in to EAP-TTLS on Android - the first letter is capital by default. The user was able to log in as SELECT John will find john, but I got weird exceptions in rails_admin as it couldn’t find association from RADIUS auth record. In the end I just disabled the association as it wasn’t worth change freeradius config (if possible) or change database and tables encoding.
Schema, notice the username field:
CREATE TABLE `radpostauth` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`pass` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`reply` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`authdate` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3909873 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`gid` bigint(20) NOT NULL,
`gecos` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`homedir` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`shell` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '/usr/bin/rssh',
`password` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'x',
`lstchg` bigint(20) NOT NULL DEFAULT 1,
`min` bigint(20) NOT NULL DEFAULT 0,
`max` bigint(20) NOT NULL DEFAULT 9999,
`warn` bigint(20) NOT NULL DEFAULT 30,
`inact` bigint(20) NOT NULL DEFAULT 0,
`expire` bigint(20) NOT NULL DEFAULT -1,
`flag` tinyint(4) NOT NULL DEFAULT 0,
`quota_mass` bigint(20) NOT NULL DEFAULT 52428800,
`quota_inodes` bigint(20) NOT NULL DEFAULT 15000,
`active` tinyint(1) NOT NULL DEFAULT 1,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`ntlm_password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unqusername` (`username`),
KEY `index_users_on_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=8896 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
ActiveRecord query with where works well, query takes username and searches original string + lowercased version:
irb(main):021:0> Radpostauth.where(username: 'Keanu').includes(:user)
Radpostauth Load (14.3ms) SELECT `radpostauth`.* FROM `radpostauth` WHERE `radpostauth`.`username` = 'Keanu' /* loading for inspect */ LIMIT 11
User Load (0.6ms) SELECT `users`.* FROM `users` WHERE `users`.`username` IN ('Keanu', 'Keanu')
=> #<ActiveRecord::Relation [#<Radpostauth id: 3577348, username: "Keanu", pass: "*****", reply: "Access-Accept", authdate: "2020-01-01 10:57:25.000000000 +0000">, ...]>
This query just shows first three usernames from authentication requests in the database, one is capitalized.
irb(main):025:0> Radpostauth.all.limit(3).each {|u| puts u.username.inspect}
Radpostauth Load (0.3ms) SELECT `radpostauth`.* FROM `radpostauth` LIMIT 3
"novakova"
"anonymous"
"Keanu"
Trying to get associated user is successful for the first two users:
irb(main):026:0> Radpostauth.all.includes(:user).limit(2).each {|u| puts u.username.inspect}
Radpostauth Load (0.3ms) SELECT `radpostauth`.* FROM `radpostauth` LIMIT 2
User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`username` IN ('novakova', 'anonymous')
"novakova"
"anonymous"
However fails for the third with capitalized username - username on user is lowercased, there is no first record, because IN (Keanu, ...) won’t return the record, but Rails counts everything is there and fails:
irb(main):027:0> Radpostauth.all.includes(:user).limit(3).each {|u| puts u.username.inspect}
Radpostauth Load (0.3ms) SELECT `radpostauth`.* FROM `radpostauth` LIMIT 3
User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`username` IN ('novakova', 'anonymous', 'Keanu')
Traceback (most recent call last):
1: from (irb):27
NoMethodError (undefined method `first' for nil:NilClass)
Beware of this pitfall.
Add Comment