CodeSOD: Lunatic Schema-tic

One day, James's boss asked him to take a look at a ticket regarding the "Cash Card Lookup" package, which had an issue. James had no idea what that was, so he asked.

"I don't know," his boss replied. "I just know the call center uses it. You'll need to talk to them."

James picked up the ticket and called the customer.

"Oh, yes," the customer replied. "We need this to get customer details based on their cash-card number. I think Timmy made it."

"Timmy? Who's Timmy?"

"He's our tech guy. He sets up our computers, helps us when we have issues, that stuff. Let me transfer you to him""

Timmy had indeed made it, because he "did a little programming". There was also the issue of internal billing- like many large companies, each business unit needed to charge other business units for their time. The software development team billed at $95/hr, but Timmy was already on salary to the customer service department.

He had grabbed a spare box, slapped Linux and MySQL on it, then whipped up a simple Perl script that served up a web page for doing the lookup.

Data entry, on the other hand, was a different problem all together. Knowing Remy's Law of Requirements Gathering, Timmy gave them an Excel spreadsheet with a VBA macro that could connect to the MySQL database to do bulk uploads of data. "

When James pulled up the code, he saw every horror he expected from Perl and VBA. When he saw the database, it got even worse. The data itself had a number of problems, the first one being that Timmy never set up a test environment, and instead, tested in production. And didn't clean up the test records. Even worse, though, the VBA macro tried to sanitize the inputs, and handle escaping characters like the single quote, but it did it wrong, leading to records like:

last_name	first_name
O	Reilly Kevin

As you might imagine, the database only had one table, and it was this code that really got James's attention.

CREATE TABLE `b2c` ( `masterorder` varchar(16) default NULL, `ordering_store_number` varchar(10) default NULL, `order_date` varchar(10) default NULL, `last_name` varchar(20) default NULL, `first_name` varchar(10) default NULL, `middle_initial` varchar(1) default NULL, `company_name` varchar(32) default NULL, `address1` varchar(32) default NULL, `address2` varchar(32) default NULL, `city` varchar(20) default NULL, `state_province` varchar(2) default NULL, `postal_code` varchar(9) default NULL, `country` varchar(15) default NULL, `phone` varchar(10) default NULL, `sequence` varchar(2) default NULL, `sku` varchar(10) default NULL, `card_value` varchar(11) default NULL, `shipping_method` varchar(3) default NULL, `insert_id` varchar(5) default NULL, `customer_number` varchar(70) default NULL, `last_4_cus_number` varchar(70) default NULL, `card_value2` varchar(70) default NULL, `prepared_by` varchar(70) default NULL, `witnessed_by` varchar(70) default NULL, `card_number` varchar(19) default NULL, `shipping_date` varchar(10) default NULL, `invoiced` text, `ship_method_code` varchar(3) default NULL, `valuation_date` varchar(10) default NULL, `comments` text, `num_of_days_from_ship_to_valuation_date` text, `ship_date` varchar(20) default NULL, `activation_date` varchar(20) default NULL, `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`)) ;

It's not just the VARCHAR's everywhere. It's things like card_value2 and card_value, which both hold the same data, but have wildly differing lengths. Date fields might be 10 or 20 characters long, the num_of_days_from_ship_to_valuation_date is a text type, but only holds, well" a number, and usually one less than 15. The field invoiced, also text, only holds "True" or "False" (or "Yes", "y", "Y", "N", "???", NULL).

But the real special absurdity, the real line that made James scratch his head and ask WTF, was this one:

 `last_4_cus_number` varchar(70) default NULL

[Advertisement] Manage IT infrastructure as code across all environments with Puppet. Puppet Enterprise now offers more control and insight, with role-based access control, activity logging and all-new Puppet Apps. Start your free trial today!