I was doing some troubleshooting recently trying to fix some issues reported in the Captcha of a registration system. The Captcha wasn’t always properly displaying and wouldn’t recognize valid code entries intermittently. After tracing some of the PHP to find just how the Captcha’s were being generated and managed, I found my way to the MySQL database and the table storing the values.
It took only a glance at the last and only row to determine what the problem was – The primary key for the Captcha table was defined as an INT and had reached the value 65535. With the key unable to increment, any non-valid attempt (be it a bot or someone who couldn’t read the Captcha properly), would cause the system to lock up until the 1 in 100,000 chance that the same value was re-generated and someone correctly matched the code. The failure resulting from this oversight was essentially a Denial of Service on the registration system. To temporarily solve the issue, I did a simple reset of the primary key[1], which should buy a few years of development time to resolve the core issue.
This situation possesses an interesting design consideration when building long-running applications, even when you aren’t planning on storing a large amount of data for a long time. Tables that are frequently populated with new data should have measures in place to either handle recycling over-used values or should be built in a way that avoids these sort of conditions. In the case of the Captcha above, the primary key was hardly used and could have been factored out entirely without the application running differently.
Looks like it’s time to get this database a subscription to AARP and some Depends….
References: