- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Not the frist time a software developer knows shit about databases.
Admin
And finding one who also understands languages/localisation narrows the field even further.
Admin
I think the developer didn't go far enough. Imagine how fast the application can be if all data can be stored in columns in a single row. No need for indexes, query optimization, etc
Admin
Some people are deep thinkers. Others are "wide" thinkers. The wide thinkers should not be software devs. The author of this plugin is a very wide and very not-deep thinker.
The captcha asked .e to identify bicycles. Of which there were two. But it wouldn't let me post until I'd checked the box on what was very clearly a "chopper" customized motorcycle. Yaay for AIs; soon they'll make all our decisions for us!!??
Admin
Food for thought:
Since the column names aren't known at compile time (they're dependent on the language of the current user)... What are the odds that the queries are built using string concatenation? Secondly, what are the odds that they are vulnerable to SQL Injection?
I'd give the first one nearly 100% and the second has to be really high.
Admin
I've seen a few "Model Driven" systems. Primary key columns is varchar 255. The first 64 are almost always the same string or a few variants where starting parts are the same. On top of that these, "Prefixes" are denormalized into two other columns, plus one other column for the unique part of the primary key.
These are all enterprise software systems
Admin
I once had an application that took four criteria (city (one of 8), age bracket (5) and gender). One day one of the other devs came up to me with pride in his eyes because now all that data was in one row! No more need for parameters!
Admin
CISCO telephony (CUIC) database schema works the same damn way. And they're one the largest most successful telephony systems on the market.
Admin
Yeah, that was a great design FORTY YEARS AGO when disk space was absurdly more scarce and expensive. (Most of the first 15 years of my career involved customizing a system that was designed that way - all text fields as substrings of one or two big fields - along with filenames that fit within the old '8 characters + 3 for extension' limit. They've since revamped most of the modules with properly named files and fields, first one was released around the same time as Vista.)
Admin
Four criteria - city, age bracket, gender
I think I've spotted the problem.
Admin
Remember, there are 3 kinds of people: those who can count, and those who can't.
Admin
As always, there are 10 kinds of people. Those who can figure this out and those who can't.
Admin
I'm pretty sure the plural in "prefaeces" in cases like that.
Admin
Building queries by string concatenation isn't inherently bad, but it's certainly not needed in more everyday cases like localization. For queries that really do need to be dynamic in a certain way, you can still build safely via string concatenation if you properly "untaint" the table-related variables you're building with (via strictly controlled allow-lists), and of course use proper SQL variable placeholders for all other externally-supplied input. Sometimes the only alternative to this would be to more or less do the same thing from a stored procedure, which is likely to be more painful than in your language of choice.
Admin
Yes... hence the 100% probability of concatenation and a lower probability of SQLi vulnerability. It's highly unlikely that someone who thinks this database schema is a good idea will "do the needful".
Also, stored procedures won't help, they'll simply shift the place where the SQL Injection happens. The sp will have to use dynamic SQL and string concatenation. However, there are no readily available database-side libraries to help.
Admin
You are, of course, entirely correct, but I've a hunch if you did lift the hood on this abomination Jamie will win the bet. In the meantime, I'm going to brush up on how to speak ";DROP TABLES sys.tables"
Admin
I get how this is a design issue, but why is too many tables a technical problem?
Admin
Well that shouldn't be, but my guess is that design pattern tracks back to a temporary table somewhere that has a column for each language and got blown apart by this guy's need for eight of them.
Which, to be fair, is an entirely different WTF to the creation of massive numbers of unnecessary tables.
Two different flavours of normalisation failure.
Admin
It isn't. The problem was the width of the single table:
If they'd adopted an even slightly less insane table design (like one column for each text item that had to be localised, and one row per language) it wouldn't have mattered how many languages you installed. But having one column per text item per language just made too many columns.