How not to SQL #2

Previously, we established SQL is not a modular language. Brent Ozar would like to point out it’s not a programming or scripting language either.

In a programming language like C# or Java, you tell the computer what to do, in order… SQL, on the other hand, is a declarative language where you declare the shape of your result set… You’re declaring the output that you want, not the methods the database server uses to build it. Oh sure, you CAN use SQL to declare the shape of your query plan, but generally that leads to heartbreak and despair…”

Click through to read all the reasons why, and when you should and should not force things.

Rule 9 – Even in space, temporary==forever

An entertaining conversation with a colleague gave me reason to revisit the source code for the Apollo 11 flight computer…the one that landed the first humans on the moon. The comments are all too relatable.

Image stolen from MIT/NASA

An ABC News article runs through the best Easter eggs and comments. The full source code is available on GitHub, natch. If there is a lesson to be learned for modern software developers, maybe it’s this: you may not think much of your “temporary” solution, but often the first thing that works is good enough. This was good enough to put 12 people on the moon and bring them home. What’s the most enduring thing your temporary fix accomplished?

You might be in management now if…

…you know the Jira schema better than the project your team is building. And that’s ok. Karl Hughes explains why, and why the transition from contributor to manager is so hard for developers in What you give up when moving into engineering management.

As a leader, you’re no longer expected to write the most code, solve the hardest technical problems, or fix the trickiest bugs. Instead, you’re responsible for ensuring that your team can do these things. It’s hard for great engineers to move into management because they like being deeply focused on challenging technical problems, not hopping in and out of a dozen meetings every day.

Your job is to develop the developers.

Things you should never do, even if you are a $FAMOUS_COMPANY using $HYPED_TECHNOLOGY

I like to joke that 90% of my job is just telling developers “No you can’t rewrite that.” Joel Spolsky explains why, and why why you’re not reading this in a Netscape browser, in his seminal article Things You Should Never Do, Part I

When you throw away code and start from scratch, you are throwing away all that knowledge. All those collected bug fixes. Years of programming work.

You are throwing away your market leadership. You are giving a gift of two or three years to your competitors, and believe me, that is a long time in software years.

You are putting yourself in an extremely dangerous position where you will be shipping an old version of the code for several years, completely unable to make any strategic changes or react to new features that the market demands, because you don’t have shippable code. You might as well just close for business for the duration.

You are wasting an outlandish amount of money writing code that already exists.

Which is why this parody template of a press release had me in stitches – Why we at $FAMOUS_COMPANY Switched to $HYPED_TECHNOLOGY

When $FAMOUS_COMPANY launched in 2010, it ran on a single server in $TECHBRO_FOUNDER’s garage…Our existing technology stack has served us well for all these years, but as we seek to grow further it’s clear that a complete rewrite of our application is something which will somehow prevent us from losing two billion dollars a year on customer acquisition.

…the $FAMOUS_COMPANY backend has historically been developed in $UNREMARKABLE_LANGUAGE and architected on top of $PRACTICAL_OPEN_SOURCE_FRAMEWORK. To suit our unique needs, we designed and open-sourced $AN_ENGINEER_TOOK_A_MYTHOLOGY_CLASS, a highly-available, just-in-time compiler for $UNREMARKABLE_LANGUAGE.

Read the whole thing, as they say.

Legacy == Proven. It’s as true today as it was when Netscape was on top and threw it all away.

Go Home SSMS, You’re Drunk

I don’t even know where to start with this abomination. I was enjoying my new Logitech K480 Wireless Multi-Device Keyboard I got for Christmas, writing a query as I tend to do. I’ve been too lazy to flip the bit to make the function keys do function things, so when I hit F5 to run the query it executed whatever SSMS thinks the magnifying glass should do.

What fresh Hell is this?

It thinks it should launch Bing, in a browser, inside SSMS. What the hell is the use case for this? Perhaps this is an unholy mutation of Zawinski’s Law:

Every program attempts to expand until it can read mail browse the web. Those programs which cannot so expand are replaced by ones which can.

The ruined ship, trapped in the ice, is apt.

Rule 6 – shell expansion injection attack edition

Say it with me everyone, cleverness is the mother of regret. Rachelbythebay with yet another tale of turn-by-turn navigation down the road to ruin in Fix the unit test and open a giant hole everywhere

Like most dumpster fires, it starts with a mundane business problem:

Our program was going down a road where it might need to create a series of paths. This is where you have “/my/path” and you need a couple of directories nested underneath it, so you end up with “/my/path/project/staging” and “/my/path/project/prod” and things like that.

…that someone solves in a clever way

What I found was… disturbing. I found a function that claimed to create directories, but it wasn’t just a simple passthrough to the usual C library mkdir() function…it was kicking off a subprocess to run the mkdir program

void create_a_directory(path) {
  system("mkdir -p " + path);

Unfortunately, in doing this, it created an enormous security hole, too. The C system() call runs subcommands *through a shell*, and whatever you pass to the shell is subject to all kinds of fun shell expansion rules…These shell expansion rules include a semicolon to split commands. This means if you can get a semicolon and another command in there, it will happily run it right after the “mkdir -p /whatever”…It’s trivial to change it to do something far nastier at that point, like opening a shell to somewhere else, exfiltrating data, or whatever. Your program is now wide open to this kind of attack and you’ve changed nothing.

And now for the best part, why it happened at all

So finally, you’re probably wondering why this happened, and why someone would change a function that called a C library function into something that ran a $(*^&$( subprocess. Ah, well, that’s easy. Someone had a unit test that called that directory creation function with a complicated path that had several elements. Sometimes, not all of the intermediate directories existed, and then it would fail. They knew that “mkdir -p” is what they’d do by hand, but they needed the program to do it for them. They changed the common library, checked it in, reran their unit test, and now it started passing, so they were done.

To paraphrase Homer Simpson: To unit tests! The cause of, and solution to, all of dev’s problems