Saving the World from Code
-
It’s over 55 million lines of code. And one of the things that I found out in this study is more than 98 percent of it is completely irrelevant.
I think even @TimeBandit, if pushed, would admit that more than 2% of Microsoft code is relevant.
-
@masonwheeler said in Saving the World from Code:
If it's supposed to be (not) matching against the data in a column, you're fine. That's how joins normally work. It's only when you're matching against a literal that this becomes an issue.
It's not an issue at all without your sabotage.
@masonwheeler said in Saving the World from Code:
Why?
It's overkill.
-
@boomzilla said in Saving the World from Code:
sabotage
No, that's what SQL Injection is. My idea is security. Security frequently requires a bit of work to get right, but the beauty of this idea is that it's foolproof. Set this up, and it's literally impossible to have a SQL injection error in your code without going out of your way to deliberately open up a security hole.
-
@masonwheeler said in Saving the World from Code:
Set this up, and it's literally impossible to have a SQL injection error in your code without going out of your way to deliberately open up a security hole.
I like your optimism in thinking that would in any way reduce the number of injections
-
@masonwheeler said in Saving the World from Code:
@boomzilla said in Saving the World from Code:
sabotage
No, that's what SQL Injection is. My idea is security. Security frequently requires a bit of work to get right, but the beauty of this idea is that it's foolproof. Set this up, and it's literally impossible to have a SQL injection error in your code without going out of your way to deliberately open up a security hole.
I know what you think about it. I don't want it. I will fight it. I will ridicule it. It's easy to think up things that make stuff more secure but that really suck. Your idea is one of them.
-
@jaloopa said in Saving the World from Code:
I like your optimism in thinking that would in any way reduce the number of injections
What? You think that the natural reaction would be to simply turn it off, every time?
IME most developers want to get things right. They screw up things like this because they don't know about parameters and how important they are. This would essentially force them to learn about them.
-
@boomzilla said in Saving the World from Code:
It's easy to think up things that make stuff more secure but that really suck. Your idea is one of them.
And yet you can't provide a single example when asked.
-
@masonwheeler Note that thisome basically leads to one of three places:
- The spec notation is exactly as expressive as the implementation language, in which case converting the spec to the programming language is pointless - write a compiler for that notation and use that;
- the spec language is less expressive, in which case, you would be better off skipping the spec;
- it is more expressive than the programming language, in which case the language is bad and you shouldn't have been using it in the first place - again, compile the spec notation instead.
Now, in practice, the purpose of a spec is to allow people who are not programmers themselves explain enough about what they need to the programmers that the programmers can fill in the details rather than the other stakeholders becoming programmers. It also can serve as a documentation of the program - code docs, that is, not user or administrator docs - but that, too means that it shouldn't be comprehensive.
Anyway, all of this is ignoring something known to be a major problem with specifications: they change. There may be unforeseen issues. The needs may change. The planned solution maybe not address everything, or even be the wrong solution entirely. The whole point of 'Agile methods' is addressing these cases, but unfortunately they are so badly misunderstood by most of those using them that in many cases they make things worse - and most of the times when projects using them succeed, the success was more due to basic competence of the devs than because they followed some particular methodology. Agile is a good set of rules, when applied appropriately and judiciously, but it makes for a lousy religion.
-
@masonwheeler said in Saving the World from Code:
@boomzilla said in Saving the World from Code:
It's easy to think up things that make stuff more secure but that really suck. Your idea is one of them.
And yet you can't provide a single example when asked.
Literally: What the fuck are you talking about? You never asked this. But I'll oblige:
We could make cars only ever go 5 mph. Now we're all safe from head on collisions!
-
@boomzilla said in Saving the World from Code:
Literally: What the fuck are you talking about? You never asked this. But I'll oblige:
You said this would be problematic for joins, which apparently use literals in join conditions all the time. I asked for an example. You ignored it.
-
@masonwheeler said in Saving the World from Code:
@boomzilla said in Saving the World from Code:
Literally: What the fuck are you talking about? You never asked this. But I'll oblige:
You said this would be problematic for joins, which apparently use literals in join conditions all the time. I asked for an example. You ignored it.
Oh, well, that's not what your post said. I thought it was obvious when I described what it was doing. But for instance, I might have a flag to indicate when something soft deleted and filter those rows out.
Something like:
left join foo on foo.id = bar.foo_id and foo.deleted = 0Also something that JDBC or Hibernate (I don't know which level did actually does it, but...) does is to not allow comments or semicolons, preventing the typical injection attacks that rely on those things to work.
-
@boomzilla said in Saving the World from Code:
Something like:
left join foo on foo.id = bar.foo_id and foo.deleted = 0Yeah. That second part really should be in the
whereclause, not thejoincriteria.
-
@masonwheeler said in Saving the World from Code:
@boomzilla said in Saving the World from Code:
Something like:
left join foo on foo.id = bar.foo_id and foo.deleted = 0Yeah. That second part really should be in the
whereclause, not thejoincriteria.Congratulations! You broke the query. To do that, you'd need to make a subquery. But why? For such a simple thing?
-
@boomzilla said in Saving the World from Code:
Congratulations! You broke the query. To do that, you'd need to make a subquery.
???
left join foo on foo.id = bar.foo_id where foo.deleted = 0How does this break the query?
-
@masonwheeler said in Saving the World from Code:
What? You think that the natural reaction would be to simply turn it off, every time?
50/50 chance. Depends whether the accepted answer to the stack overflow question that comes out at the top of the Google search for the error message is how to turn it off or how to use parameters
-
@masonwheeler said in Saving the World from Code:
@boomzilla said in Saving the World from Code:
Congratulations! You broke the query. To do that, you'd need to make a subquery.
???
left join foo on foo.id = bar.foo_id where foo.deleted = 0How does this break the query?
It potentially (
depending on the data, of course) doesn't return the same records. Consider the case where rows in foothat match havedeleted = 1. It seems like a subtle difference but it's very important when you're writing a query.
-
@masonwheeler said in Saving the World from Code:
How does this break the query?
It effectively turns a left join into an inner join
-
@boomzilla said in Saving the World from Code:
It potentially (
depending on the data, of course) doesn't return the same records. Consider the case where rows in foothat match havedeleted = 1. It seems like a subtle difference but it's very important when you're writing a query.
Ugh, you're right.So... yeah. That's ugly, but I guess they'd have to stay set up that way in that case.
Even so, putting that
0in a parameter is still advantageous. If you're soft-deleting things, it means you want to keep them around, which means you're going to want to query for them at some point. In that case, you can reuse the exact same query, but with a1in the parameter, and query plan caching means it will run faster.
-
@masonwheeler That depends. It may not make sense for that query. You're just complicating my life for very little return. As I said, I already have what's probably the 99% solution (ass pull, but how many injection attacks don't use a semicolon or a comment marker?).
-
@boomzilla said in Saving the World from Code:
As I said, I already have what's probably the 99% solution
In a specific client library--and you're not sure which one it is. That does very little to help code written with anything else.
-
@masonwheeler said in Saving the World from Code:
In a specific client library--and you're not sure which one it is.
Yes, so?
@masonwheeler said in Saving the World from Code:
That does very little to help code written with anything else.
Well, if you put your solution into a particular RDBMS then it wouldn't help the people using other RDBMSes either!
Yeah...neither one of those criticisms make any sense, do they? I was pointing out an alternative to what you were proposing. One that doesn't get in the developer's way but does a pretty solid job of accomplishing your stated goal.
-
@masonwheeler said in Saving the World from Code:
In that case, you can reuse the exact same query, but with a 1 in the parameter, and query plan caching means it will run faster.
Not necessarily, because then the query planner can't take account of and optimise for the literal value, the parameterized version of the query might be far less efficient.
-
@japonicus How would that work?
-
@masonwheeler said in Saving the World from Code:
@japonicus How would that work?
Black magic.
No, but seriously, you can't see how having a specific value could improve a query plan? Like, knowing that a lot of all of the table is required or none of it or whatever? Do you suppose that query optimizers work better with less information about what they're working on?
-
@masonwheeler if the constant values are known at planning time then the query planner can use estimates of the distribution of values in a column to assess whether to use indices and to rough-out how many results will be returned.
e.g. in @boomzilla's example, suppose 99.999% of rows have
deleted = 0The two versions of the query (looking for deleted = 1 or deleted = 0) are profoundly different. In one case using an index on 'deleted' might make sense whereas in the other it would be
pointlessharmful.If the query is entirely parameterized then the cached query plan has to be completely generic.
-
@boomzilla said in Saving the World from Code:
Black magic.
I don't believe in magic.
No, but seriously, you can't see how having a specific value could improve a query plan?
Not really. I see two possibilities: either the
deletedcolumn is indexed, in which case the query planner can optimize based on the index (regardless of whether it gets the value from a literal or a parameter) or it's not, in which case it can't.
-
@masonwheeler said in Saving the World from Code:
Not really. I see two possibilities: either the
deletedcolumn is indexed, in which case the query planner can optimize based on the index (regardless of whether it gets the value from a literal or a parameter) or it's not, in which case it can't.The index on
deletedis a candidate regardless. But when filtering ondeleted = 0, it is a very unselective index and a different index (or even a full table scan) will give better performance.
-
@masonwheeler said in Saving the World from Code:
@boomzilla said in Saving the World from Code:
Black magic.
I don't believe in magic.
Nor imagination (based on your comments below)?
No, but seriously, you can't see how having a specific value could improve a query plan?
Not really. I see two possibilities: either the
deletedcolumn is indexed, in which case the query planner can optimize based on the index (regardless of whether it gets the value from a literal or a parameter) or it's not, in which case it can't.But the index may actually be harmful to use in some cases. Or it might not be a binary variable, but something with multiple values. Suppose one of the cases only had a few values and some of them had a lot. There are most definitely cases where full table scans beat using an index. It might also change the join strategy based on the expected number of rows. Seriously, you're working on a much too simple mental model here.
I recommend studying explain plans for a while to see how many different ways a DB can pull your data. Also that you don't believe in magic tells me that you haven't watched what DBs do very much, either.
-
@boomzilla said in Saving the World from Code:
expected number of rows
That's a pretty index you've just had me build there. But my statistics say the table has zero rows, so a full table scan will be faster.
But I've just inserted several million rows before that! You've got to use the index!
Nope, statistics say the table is practically empty. Full table scan it is, your results will take about 0.1s.
There you go!
-
@pleegwat Yes, stupendously bad statistics will fuck you every time.
-
@boomzilla Actually, I'm just realizing you won't get results after an hour and a half. You'll get a vague ORA error about snapshot too old.
-
@the_quiet_one said in Saving the World from Code:
Yes. That's exactly what caused Malaysia Flight 370. See, they accidentally changed the autopilot system to an inventory system in an update to the Boeing 777 and the algorithm determined it had two engines in surplus, so it decided to detach them. True story.
Reminds me of the Y2K stories that were like "nuclear power plant computers, when they think it's 1900 due to Y2K, will shut off because there was no nuclear power in 1900!"
Right, because the computer researches historical events before every operation it commands, that makes perfect sense.
-
@pleegwat said in Saving the World from Code:
@boomzilla Actually, I'm just realizing you won't get results after an hour and a half. You'll get a vague ORA error about snapshot too old.
Or possibly that it ran out of temp space.
-
@masonwheeler said in Saving the World from Code:
If we want this to stop, we need to abandon C. Better alternatives exist. (Heck, better alternatives existed at the time of the Morris Worm!) Fixing that would fix a massive amount of security issues right away.
Just not using C isn't a solution. Unsafe code is necessary to run all those safe interpreted languages, or embedded systems.
-
@magnusmaster Who said anything about interpreted languages?
@shoulder-alien Please stop pestering @magnusmaster...
-
@scholrlea said in Saving the World from Code:
("Wait, did you say that the Tacoma Narrows regularly get 40MPH winds, I thought it was only 25MPH!")
The resonance phenomena that brought down the Narrows bridge was known by its designers (and since antiquity-- why soldiers break step while marching over a bridge), but they had no mathematical basis to calculate safety margins based on it. That research was done only after the collapse. (Much of it done by Engineering Professor Frederick B. Farquharson, who personally witnessed and filmed the bridge's collapse.)
In short, the designer wasn't at fault.
-
@boomzilla said in Saving the World from Code:
No, sometimes those kinds of things make sense. Especially when you're dealing with outer joins. But seriously, are all of the things in your where clauses always coming from dynamic sources / user input? It sounds like you have some very simple queries to me, if that's the case.
How about we get secure software, and you can make a Stored Procedure to do the weird-ass shit you like.
-
@magnusmaster said in Saving the World from Code:
Just not using C isn't a solution. Unsafe code is necessary to run all those safe interpreted languages, or embedded systems.
There are safe compiled languages (at least three I can think of off the top of my head, assuming in advance you'd already discounted C# and Java), and if the embedded systems are doing anything involving the network I'd argue: tough shit, spend a bit more $$$ on the chip and program in a safe language.
-
@blakeyrat True, but IIUC, it was also the case that they had incorrect information about how often the winds exceeded 30MPH. Even without exact figures, they would have probably used a higher margin of error had they known that, which might - not necessarily, but might - have made a difference in the outcome.
Even if this is the case, it still was not their fault, though, as it would mean that they were given bad data.
-
@masonwheeler said in Saving the World from Code:
First things first, fix the 2 largest causes of problems: buffer overflows and SQL injection
Don't forget XSS and CSRF!
-
@scholrlea said in Saving the World from Code:
The electromechanical interlockings that controlled train movements at railroad crossings, for instance, only had so many configurations; a few sheets of paper could describe the whole system, and you could run physical trains against each configuration to see how it would behave. Once you’d built and tested it, you knew exactly what you were dealing with.
Right. Because failure states in mechanical systems never involve things breaking in an unpredictable fashion, people doing stupid things that never occurred to the designers, incorrect values for load capacity or load requirements ("Wait, did you say that the Tacoma Narrows regularly get 40MPH winds, I thought it was only 25MPH!"), failures to make necessary engineering calculations ("I'm sure that the connection between these hanger rods whose layout we changed for aesthetic reasons, and the box girders we redesigned for aesthetic reasons, can hold the load; no reason to re-calculate that"), manufacturing defects or shoddy production processes, maintenance errors ("Ah, I don't need to look up the correct parts in the documentation, matching these replacement screws for this airliner's windshield by eye has always worked before"), undocumented engineering shortcuts ("Hey, this procedure of using a forklift when re-attaching the engine nacelles in a process requiring millimeter precision works great and saves a ton of time, let's tell everyone else about it, but don't bother writing it down or anything"), business cutbacks leading to incomplete maintenance ("Ah, what harm could there possibly be in going six months between times lubricating the tail section's elevator jack screws rather than the mandated six weeks?"), manager's making asinine decisions without looking at the consequences ("sure, you can just slide those 20 ton air conditioning units across the roof, no need for a crane or even rollers"), or anything else that can't be anticipated. Good to know.
I know the Tacoma Narrows one. What (if any) are the other references?
-
("I'm sure that the connection between these hanger rods whose layout we changed for aesthetic reasons, and the box girders we redesigned for aesthetic reasons, can hold the load; no reason to re-calculate that")
Although the changes weren't for aesthetic reasons, I'm assuming this is one of the reference.
-
@scholrlea The problem wasn't the speed, the problem was that the wind blew at a steady rate for a long period of time. The bridge was perfectly safe when there were gusting, random winds... it was when the wind didn't change direction or speed for a long period of time that it began to oscillate.
@dreikin said in Saving the World from Code:
I know the Tacoma Narrows one. What (if any) are the other references?
@dreikin said in Saving the World from Code:
"I'm sure that the connection between these hanger rods whose layout we changed for aesthetic reasons, and the box girders we redesigned for aesthetic reasons, can hold the load; no reason to re-calculate that"
This was a second store balcony collapsing at a shopping center. (Can't remember the name exactly.)
Also the box girders bit might refer to the emergency Citicorp Center repairs back in 1978, where an engineering student realized that a (supposedly) harmless change made during construction resulted in the building's wind resistance being far less than designed, and the architect had to repair it before a tropical storm landed in New York.
@dreikin said in Saving the World from Code:
"Hey, this procedure of using a forklift when re-attaching the engine nacelles in a process requiring millimeter precision works great and saves a ton of time, let's tell everyone else about it, but don't bother writing it down or anything"
I've read about that one but I can't remember the flight number.
EDIT: American Flight 191.
@dreikin said in Saving the World from Code:
"Ah, what harm could there possibly be in going six months between times lubricating the tail section's elevator jack screws rather than the mandated six weeks?"
Alaska Flight 261. The jackscrew wore-down faster than anticipated because the maintenance team: 1) didn't lubricate it enough, and 2) didn't open it up to inspect it often enough. Notable because the pilots tried everything to get that broken heap on the ground, including attempting to fly it upside-down when they realized there was no other way to get its nose up. They failed, but still a pretty amazing story.
@dreikin said in Saving the World from Code:
"sure, you can just slide those 20 ton air conditioning units across the roof, no need for a crane or even rollers"
That was a shopping center somewhere in Asia... China? Pretty self-explanatory, an incompetent crew was installing hugely heavy air conditioning units without checking that the building was rated for the weight, and without using the proper equipment to place it.
-
@blakeyrat said in Saving the World from Code:
I've read about that one but I can't remember the flight number.
American Airlines 191.
Edit: Goddammit
.
-
@blakeyrat said in Saving the World from Code:
https://www.youtube.com/watch?v=y8Yw9hill1k"sure, you can just slide those 20 ton air conditioning units across the roof, no need for a crane or even rollers"
That was a shopping center somewhere in Asia... China? Pretty self-explanatory, an incompetent crew was installing hugely heavy air conditioning units without checking that the building was rated for the weight, and without using the proper equipment to place it.
-
@mzh Yeah, I was wrong about that - in part. The changes in the hanger rod arrangement was due to the difficulty and cost of making a full-length hanger rod. The change in the box beams, however (according to Seconds from Disaster, at any rate) was because the hotel owners didn't like the way it looked with two outward-facing C-channels, so they put them together to form a box; the problem with this that it reduced the connection area between the beams and the hanger rods, further weakening the already understrength supports.
https://www.youtube.com/watch?v=CsI8_0H6pEA
Filed Under: Did I mention graphite-tipped control rods again in this thread yet? Because that was totally a thing.
-
@boomzilla said in Saving the World from Code:
If my business users tried to get into the business of dictating data types I would start some fights.
... just because Oracle doesn't let you distinguish between empty string and null.
-
@boomzilla said in Saving the World from Code:
Filed Under: Nobody shares flowcharts like this
Ah, that's why I couldn't make sense of it.
-
@masonwheeler said in Saving the World from Code:
@boomzilla said in Saving the World from Code:
No, sometimes those kinds of things make sense. Especially when you're dealing with outer joins.
...such as?
--Table: Patients -- P_ID - unique patient ID -- ... - other stuff --Table: DrugTypes -- DT_ID - unique ID for each kind of drug -- ... - other stuff --Table: Dispensing -- D_ID - unique ID for each dispensing event -- D_P_ID - FK to Patients -- D_Date - Date of dispensing --Table: DispensingRecords --DR_ID - unique ID for each dispensing record -- DR_D_ID - FK to Dispensing -- DR_DT_ID - FK to DrugTypes -- DR_Qty - amount of drug dispensed -- ... - other stuff --Query SELECT p.p_id, d.d_date, dr1.dr_qty, dr2.dr_qty, dr3.dr_qty FROM Patients p INNER JOIN Dispensing d ON d.d_p_id = p.p_id LEFT JOIN DispensingRecords dr1 ON dr1.dr_d_id = d.d_id and dr1.dr_dt_id = 'drug1' LEFT JOIN DispensingRecords dr2 ON dr2.dr_d_id = d.d_id and dr2.dr_dt_id = 'drug2' LEFT JOIN DispensingRecords dr3 ON dr3.dr_d_id = d.d_id and dr3.dr_dt_id = 'drug3' WHERE d.d_date between '2016-01-01' and '2017-01-01'Edit:
But seriously, are all of the things in your where clauses always coming from dynamic sources / user input?
No, but most of the literals do. And if this system makes your software more secure at the expense of having to add a couple more parameters, is that really a problem?
You're suggesting that hard-coded literals should be used like so?
DECLARE @param1 VARCHAR(50), @param2 int, @param3 datetime SET @param1 = 'lit_val' SET @param2 = 5 SET @param3 = '2017-09-28 05:05:31.354' SELECT @param1, colA, colB, ... FROM TableA ta INNER JOIN TableB tb ON tb.key = ta.key AND tb.id = @param2 WHERE colX = @param3
-
@masonwheeler said in Saving the World from Code:
What? You think that the natural reaction would be to simply turn it off, every time?
In essence, yes. More fully, it would go something like:
- hit strange error message
- google strange error message and
readskim the first half a dozen hits - three of them would say "that's a setting they changed in $VERSION, do this to turn it off" and one would say "you should change the way you do queries to use parameters, this is how" (the other two wouldn't be for the right problem)
- I'm in a hurry, let's turn that setting off so I can get my stuff working, this parameter stuff looks complicated, and I certainly don't have time to go and change every single query in the code
- document "turn setting off" workaround for future reference
- Blame $RDBMS_VENDOR for making such crappy software
@japonicus said in Saving the World from Code:
the query planner can't take account of and optimise for the literal value, the parameterized version of the query might be far less efficient.
Yeah, this was going to be my main objection. Skewed data sets very often benefit from literals in queries. As long as you're not getting them from the user, that's not a problem.
@boomzilla said in Saving the World from Code:
Also that you don't believe in magic tells me that you haven't watched what DBs do very much, either.
Hyatt Regency walkway collapse - Wikipedia
