Little Bobby Tables

Every now and again I explain to people what “SQL Injection” is. I generally do this by writing a bit of an SQL string for them, using a string which can be manipulated (one which is vulnerable to this particular exploit, down at the database level of the application). And I then show them this XKCD comic:

So, for example, I’d define the following as an example of a vulnerable stored procedure:

create procedure SaveStudent
	@StudentName	nvarchar(256)
as

declare @sql nvarchar(max)

set @sql = 'insert into Students ( StudentName ) select '
set @sql = @sql + '''' + @StudentName + ''''
exec(@sql)

go

I would then put in the example name from the XKCD comic above to demonstrate just what ends up happening. Let’s say you were to call that stored procedure, passing in Little Bobby Tables’ name as the variable:

exec SaveStudent 'Robert''); DROP TABLE Students;--'

The stored procedure would then execute the following commands:

insert into Students ( StudentName ) select 'Robert'); DROP TABLE Students;--'

So … why is this a problem? One salient point is that the apostrophe character is used to enclose strings in quotation marks. The way Bobby’s name is constructed allows for a malicious command to be sent to the database (the Students table is erased by the command DROP TABLE Students;), and no error being necessarily returned, because Bobby’s name fits in perfectly with how SQL works – he’s got a proper semicolon, terminating the command that comes before, so he was added to the table … but then the table was dropped, using a valid command, and everything after that is commented out (the double-dash is a comment marker), so there wouldn’t necessarily be any errors at all coming out of this – it’s perfectly valid, it’s running in a privileged context (it has been “blessed” by being turned into a stored procedure, so it’s trusted to run).

All of this is the lead up to the punchline, which is this company, apparently registered in the UK (or, something used for testing, I suspect, as this is the beta for UK Government’s Companies House): ; DROP TABLE “COMPANIES”;– LTD. This, passed into the above sample procedure, will yield the following SQL string:

insert into Students ( StudentName ) select '; DROP TABLE "COMPANIES";-- LTD'

Now, this one’s going to throw an error, because it’s actually improper syntax no matter which database you pass it to (well – any of those I have used, anyway, which is … way too many). However, if this is indeed used for debugging or as a demonstration, it will 1) throw an error if you feed it to anything that’s vulnerable to this exploit, 2) hopefully not throw an error anywhere, because the UI is supposed to be sanitizing these inputs, so it should be properly formatted (“escaped”) so as not to cause this problem. I can see this value being used both to test the UI (put it in & see if some database code which is intentionally vulnerable to this exploit throws an error) and also to test the database code, for scenarios which do not use a user interface such as loading in data from another application or a programmatic interface.

This technique is not just dangerous because it allows things to be broken; this technique is routinely used to exfiltrate data such as usernames and passwords, credit cards, or whatever other juicy details are in the database. If an adversary can figure out just which poor programming technique was used, and can figure out how errors are presented to the webpage, then they can intentionally cause errors which return data which should remain secret, or they can simply replace the query that’s supposed to do something legitimate (pull back a list of toasters, for example) with a query that returns that sensitive data right onto the webpage.

In any event, once you understand this humor as a programmer, your programming fundamentally changes, as there are only a handful of bad programming techniques which allow for this kind of vulnerability – so, you quickly eradicate those techniques from your practice (and hopefully go back and clean things up in older code). The fact that a huge number of websites are vulnerable to this tells you something (bad) about the competency of people who write and test code. I will not rant here about the many programmers who think about databases as being simply dumping grounds for data, rather than fully-functional programming environments.

-D

Some Jobs are Better than Others

Every now and again I remember a horrible job I’m happy to have left. It’s far rarer that I reflect upon a job I’m happy to have turned down. Let’s do so now.

Hanford 1

The picture above was taken from a parking lot on the Hanford Nuclear Reservation. I was there to basically have a tour of the site, finish up the paperwork, and meet the programming team I was to lead. Some of the contract terms didn’t line up as promised, so I backed out. As news about Hanford has trickled out over the past year or so I’ve found myself very happy it fell through. This is particularly true reading this article about plutonium contamination drifting over the region.

“…site was ringed by 8-foot-tall piles of radioactive debris with little to prevent dust from blowing off.”

-D

Knowing When NOT to Work Hard

Last night I dreamed that I was called upon to give a lecture and I was unprepared. Apparently, though, I have some lectures that I give often enough that I’m able to pull one up on the fly, and my dreaming mind knows that I can always give the standard one I give to junior developers at some point: Be Intelligently Lazy.

Twenty-three years ago, I was working a full-time job doing data entry for a payroll processing company. This involved opening mailed-in timecards, “coding” them (sorting them into piles, basically), and entering their data into a Peoplesoft payroll system, with data entry done on Windows 3.1. After a couple months of doing this, I realized that I was typing the same thing over and over (each district manager had a markup rate, a name, a zip code, a phone number … and the fields probably went right in that order, actually), and it was incredibly boring and stupid, particularly when there was Windows Macro Recorder.

I set about assigning the 12 function keys to my 12 area managers’ information, typing in the information and tabbing through the entry fields as quickly as I could. When I was done and I’d written down which manager was on which key, I could type in the person’s name and their hours worked and then press F5, say, to key in the rest and press “submit” to send the timecard into the database.

My manager noticed this (well, I was probably excited and told her about it) and asked me to do the same thing for the other entry clerks in the group. A few days after completing that, the database administrator came to visit us, to ask what on earth we were doing, because the data used to trickle in all day long and now only came in before lunch (yes: I had cut the data entry time from 8 hours down to 3, for the entire group). When my manager told him what I had done, that was the end of my days as a data entry clerk: I was assigned to work in IT, working on macros in Excel or something.

So, yes: I became a programmer because I was intelligently lazy. That is part of what makes a good programmer: knowing when something can be automated. To be a truly effective programmer, though, takes a whole lot more experience in figuring out automation and figuring out when to automate and – more importantly – when not to automate. This is actually the real point of the lecture.

Your average junior to mid-level programmer will spend a ton of time automating things which could be done via brute-force. This is the classic trap of spending more time building automation than it would have taken to do it by hand. Building the skills to be able to estimate this takes a whole career, honestly. Knowing that you need to do this, though, is something that you can give to one another: simply print out the comic below and show people how to read it.

For example, I read this chart and know that every time I visit someone with multiple monitors, I need to tell them how to use the Window+Shift+Arrow shortcut. Why? Because your average person with multiple monitors will generally spend about 5 seconds 1) restoring an application window, 2) dragging it over to the other monitor, 3) maximizing the window … and they’ll do that at least 5 times per day. When you read those values into the comic above (5/day on the top axis, 5 seconds on the left axis) you get 12 hours spent in that activity over 5 years. In other words: every person I teach this trick will save 12 hours of labor over 5 years. That starts to be some real savings for the whole company, it takes me nearly no time at all, and it makes people really happy to learn as well!

This can also be useful for deciding whether to do projects, and help in deciding the importance of those projects (for example, if one person in your organization spends a day a month doing something that could be automated, that’s 8 weeks of savings over 5 years if you can automate it). That’s a whole other discussion, though it’s the same skill: so, maybe learn that skill, because making those kinds of decisions are important in moving into decision-making roles.

The other thing I tell junior developers in this talk is usually to get over the idea that there’s a perfect tool for things; the perfect tool is the one that gets the job done, and it may be that it’s clunky and kludgy but that doesn’t matter. I give the example here of how I use Excel to load data (I use Excel formulas to build SQL strings), or how I use Excel formulas to write C# code (I wrote some VBA code behind Excel & wrote some SQL code to feed data into Excel, which I’ve used to generate literally hundreds of thousands of lines of repetitive C# code), or how I use Excel formulas to write repetitive HTML (like, oh, a bunch of emails with different names & hyperlinks to their downloads). Even more importantly is the example of figuring out when to brute-force something (“Only 200 photos to crop? We’ll just use a photo editor rather than programming it. 40,000 photos to crop? Let’s write some Python and use CV2 to find the faces for us, so we can crop automatically.”)

There are plenty of examples to give, and plenty of tools that I use on a regular basis… but it basically comes down to using the same old set of tools because I know how to use them and because I’m efficient in using them. Yes, it would likely be faster for me to learn how to use RegEx … that is, if I knew RegEx way better than I do, I could be even more efficient than I am in Excel … but we come back to that chart and decide that it wouldn’t save me more than it would cost me. Likewise, if it’s only a few things & they can be hammered out using something simple, just spend the 20 minutes doing that rather than wasting more than that on building a tool for it.

Applying this kind of thinking to your own coding practice and knowledge acquisition is when you reach mastery: when you can look at a tool or language, determine how long it would take to master, determine how much time it would save you in the tasks you regularly perform, and decide whether or not to invest in learning it. That calculation is something which truly proficient programmers make on an intuitive basis, and which contributes to them sticking to the same things that have worked previously. If you’re conscious and intentional about it, though, it will lead you to some better decisions than, “oh, this is showing up on Hacker News, I should learn it!” That’s not to say that you shouldn’t learn new things (I’m dabbling in Python and convolutional neural networks these days), but that you should be aware that there’s a trade-off and it’s perfectly logical to decide not to learn the cool new tool / language.

So. That’s my usual talk on how to be intelligently lazy.

-D

Critical Thinking

I happened across a great passage about critical thinking and thought I’d share. It’s in a Christopher Anvil short, about aliens bearing gifts.

“I seldom watch television. I get my news from the papers, where I can take it in at my own pace, and pick out the bones, instead of swallowing it all whole. No, I don’t trust the Shaloux. What’s their motive? Why do they offer us this ‘life-serum’? What do they get out of it?”

“Open-mind! Everybody’s supposed to have an ‘open mind’. Humbug! Open it far enough, and who knows what will come in? The whole thing’s a trap. Leave the door open, to prove you trust everyone. Then the thieves can strip the house and put a knife in you while you sleep.”

Cautiously, he began to read the paper, conscious of the article’s bias, opening his mind just a little slit at a time to bash the unwelcome ideas over the head as they entered: Washington — Miliram Diastat, the benevolent (How the devil do they know he’s benevolent?) plenipotentiary (Hogwash. He may be just a messenger boy.) of the Shaloux Interstellar (They could come from Mars, for all we know.) Federation, met with the President today, and in solemn rapport (What’s “rapport” really mean? Maybe it’s hypnotism.) concluded the mind-exchange (Or brainwashing) which is a precondition for entry into (defeat by) the Federation. Mr. Diastat (Why call him “Mr.”? The damned things are neuter.) assured reporters afterward that all had gone well (For the Shalouxs, that is.) in the mind-exchange (brain-washing). He said (It said), raising his (its) hands (extremities of upper tentacles) to heaven (over its head–that is, over the end of the thing with teeth in it.), that our peoples will be joined as one (eaten up) with (by) theirs, in a final ceremony next year. At that time, travel throughout the vast extent (They claim it’s vast.) of the Federation will be free to all (Economically impossible.), and Earth’s excess-population problem will be solved (Everybody will be killed.), while at the same time (never) personal immortality will have been granted by universal (Humbug. There must be some people with sense enough to keep out.) inoculation with the serum (slow poison).

This passage seems to me to really exemplify the kind of thinking which should be applied to most things, to be honest.

-D

Stirling 132

European Travel

Way back in 1999 (before good photography) we visited friends who moved to The Netherlands for work, so we have fond memories of that, our first overseas trip (N & K have since moved back to California, visited us when we were in Scotland, and have then moved on to Portland, where we visited them a few years back). Being new at the whole international travel thing, we (ridiculously) also scheduled an additional visit with a friend in Germany … all this, in the course of a single week. That meant that we really didn’t get to see much (although N did indeed try to show us all of the things). While we know we enjoyed ourselves, some of that trip is… really, a blur.

Rijksmuseum, Amsterdam

Well, 18 years and a whole lot of travel later, we’re thinking that it is past time to revisit The Netherlands and actually take some time with it, rather than trying to cram it all into 4 days plus jetlag. We had planned to return to Scotland (of course), but our timing isn’t great, as most of our closest Scottish friends are going to be out of the country! There are also OTHER PLACES to see in Europe, and fortuitously, we have friends who, a year ago, moved to Southern Holland after a brief stint in Iceland, so it’ll be fun to see how they’re settling in. Our hope now is that, maybe if we ask nicely, we can entice a few other friends of ours in Europe to meet us “in the middle” as it were (we’re looking at you, Pille). Of those we’ve asked so far, several indicated willingness! So, now to find a place to rent for a few weeks in and around Amsterdam. Hurray!

Hayford Mills 343 Hayford Mills 337

So, while it may not be as comfy as having friends over for a Spring/Easter feast in a flat of our own, we are looking forward to hanging out with faraway friends, and a whole new neighborhood. It’ll still be something to celebrate.

-D & T

Reappearances, Disappearances

Occasionally we’ll go through our blog links and check on all of the people we’ve maybe not heard from in awhile. Some of them have disappeared in favor of The Face Hook (sorry you’ve succumbed, Ms. Nancy), some have simply dropped out and don’t have any presence any longer (we miss you Chef Paz). Some have returned to blogging, though (Haalo is back, after a 4 year hiatus!), and others have started taking pictures again.

Portland 010

Welcome back, to those returning, and we hope those who have left will stop by to tell us where they’ve gone.

-D & T

Choir Concerts

Fremont 73Rehearsal for December 2 and 3 performances.

It’s holiday performance season, so last weekend we had a concert on both Saturday and Sunday (which you can watch here and here). We had a brief performance yesterday, in Livermore, with the whole choir as a warm-up to the Nutcracker and the women as chorus during part of the ballet itself. We have a performance next Saturday with the Fremont Symphony and then D. has been roped into singing with a madrigal group on Sunday at the retirement home associated with the San Jose Mission. And then concert season is over until February!

Mexico City 020

We are really looking forward towards the week of Christmas, just to do nothing. We’re tempted, of course, to go somewhere exotic … but then we remember that everybody really wants the week off, and services are really wonky during the holiday, and that we keep telling ourselves that it’s a bad idea to go anywhere over Christmas. But we’re tempted nonetheless.

D’s work has been rather chaotic, with multiple changes in management and with the company announcing all sorts of news. We need the break to just relax and do nothing, maybe ride the bicycles through the wilderness trail system, take some pictures.

And, of course, we need today to make more fruitcake, as most of the last batch has already been eaten!

-D & T

Non-Photogenic Food and Bicycles

Lasagna

Sometimes, food just isn’t that attractive. It’s a sad reality, and probably the reason why our entire photostream – 34,214 pictures as of today – contains just two pictures of lasagna. The one above suffers from being an unattractive specimen (although quite tasty, thank you). The one below is obviously from a restaurant (at The Blue Lagoon, in Iceland) but we feel it’s cheating just a bit because it’s not really lasagna, it’s layers of eggplant pretending really hard that they’re lasagna. But, it’s vegan – our first experience with vegan lasagna many years ago… And now you can get it practically everywhere on this end of the state.

Blue Lagoon Excursion 06

As the week begins, D. will be cycling to work for the first time. Well – for the second time, as we made the trek there and back yesterday, to see how it would go. This is the first time in maybe ten years we’ve had bicycles, and we went for something much more rideable than our previous cycles. They’re low enough to the ground that even the shortest among us can cope, they have lots of gears for hill-climbing, yet still let us sit up straight, and cruise.

Roll Bikes 1

The ride in to work takes about 10 minutes, just having a leisurely cycle. These are designed so the rider sits upright rather than hunching over the handlebars. They’re also designed to really keep on rolling, so you don’t have to pedal constantly to keep going anywhere. They’re rather like the bicycles of childhood, and we’ll be enjoying taking them out on the weekends as well.

It’s time to go bake cranberry orange bread/cake to take in to work tomorrow. We’ll save aside a few slices for ourselves, in celebration of our 23rd anniversary.

-D & T