MySQL NULL gotcha

Never try to find the authenticated user in a Rails app running on MySQL with something like:

@authenticated = Person.find_by_id session[:authenticated_id]

Only use find_by_id if session[:authenticated_id] is not nil:

unless session[:authenticated_id].nil?
  @authenticated = Person.find_by_id session[:authenticated_id]
end

When you do a find_by nil the following query is executed:

SELECT * FROM people WHERE (people.`id` IS NULL) LIMIT 1;

Most of the time this returns an empty set, but if the previous query was an insert into the people table, MySQL will return the row for the last insert. This is because NULL somehow returns the generated id:

mysql> SELECT id FROM people WHERE id IS NULL;
Empty set (0.00 sec)

mysql> INSERT people SET username='bob';
Query OK, 1 row affected (0.08 sec)

mysql> SELECT id FROM people WHERE id IS NULL;
+----+
| id |
+----+
| 11 |
+----+
1 row in set (0.00 sec)

mysql> SELECT id FROM people WHERE id IS NULL;
Empty set (0.00 sec)

It would take some time and determination to exploit this, but it’s certainly not impossible.

Update: This was fixed in Rails just after the release of 1.2.1.