Keeping It Simple

Category: antipattern

  • Speaking on SQL Injection at MySQL Conference

    Speaking on SQL Injection at MySQL Conference

    O'Reilly MySQL Conference & Expo 2010

    I’m speaking this year at the MySQL Conference & Expo 2010 in Santa Clara. Be sure to get your early registration discount by Feb 22! If you miss that deadline, get 25% off with this discount code: mys10fsp

    I’m presenting a talk on SQL Injection Myths and Fallacies. This may seem like a topic that’s been done to death, but it’s important for all developers to understand it. This reminds me of a story:

    My mother volunteers with the League of Women Voters. One of their activities is helping college students register to vote. So every year they set up a table on campus and help young people fill out the forms.

    One day one of the women expressed frustration: “We’ve been doing this for ten years! When are these students going to learn how to register to vote for themselves?!”

    The rest of the group looked at her blankly. Finally someone said calmly, “You realize that every year a new class of students becomes eligible to vote, right?

    The woman who complained felt suitably embarrassed.

    I’m going to cover the basics about SQL Injection, but I’ll also show how much of the advice about SQL Injection (even advice from noted security experts) misses the whole picture. I’ll also give some new techniques for remedies, that I seldom see in books or blogs. Come on by!

  • EAV FAIL

    The photo above illustrates (by counter-example) an important characteristic of a normalized database: each logical “type” of attribute belongs in a separate column.

    Just because three values happen to be numeric doesn’t mean it makes sense to SUM() them together. But if dissimilar attributes are stored in the same column, it’s tempting to treat them as compatible in this way.

    This also shows a fallacy of the Entity-Attribute-Value antipattern. In this design, all attribute values are stored in a single column.

    CREATE TABLE EntityAttributeValue (
entity        VARCHAR(20) NOT NULL,
attribute     VARCHAR(20) NOT NULL,
value         VARCHAR(1000) NOT NULL,
PRIMARY KEY (entity, attribute)
);

INSERT INTO EntityAttributeValue (entity, attribute, value)
VALUES
('New Cuyama', 'Population',          '562'),
('New Cuyama', 'Ft. above sea level', '2150'),
('New Cuyama', 'Established',         '1951'),

SELECT SUM(value) FROM EntityAttributeValue
WHERE entity = 'New Cuyama';

    The Entity-Attribute-Value design does not support or conform to rules of database normalization.

    To be clear, the proper way to design a database is to put different attributes in different columns. Use column names, not strings, to identify the attributes.

    CREATE TABLE Cities (
 city_id          SERIAL PRIMARY KEY,
 city_name        VARCHAR(100) NOT NULL,
 population       INT UNSIGNED NOT NULL,
 feet_altitude    SMALLINT UNSIGNED NOT NULL,
 year_established SMALLINT UNSIGNED NOT NULL
);