Stored Queries

[00:00:00]
>> Steve Kinney: The architecture of your code base can also be security, right? Having SQL statements littered all over your code base, makes it really hard to refactor this if you realize you have a problem, right? Having, even if you don't have an ORM or something making something like, it doesn't matter what it what you call like cause get, user from session, ID, right?

[00:00:23]
Get products all the things, especially for the, important parts of your app, right? Sure, let's go with whatever this is, then using this now means, if you made boo boo, and you didn't escape this or somebody like a junior engineer team would maybe just didn't know, right? Honest mistakes happen all the time, most security vulnerabilities aren't people being willfully negligent.

[00:00:45]
They're either people who didn't know or people who are, again responding to an incident in the middle of the night, that is where these things happen, right? And they're people are busy, I had an issue two days ago where it's, we had to put a banner up for maintenance.

[00:00:57]
It took four of us cuz we were all doing other things, and that includes code reviewers, right? For the simplest things, we're all busy and we're all just, trying to get this thing up real quick in between other things. Those are when your security issues, slide in, so giving yourselves abstractions that let you just change one function.

[00:01:14]
That's used throughout your entire application, is, weirdly a security feature at the end of the day. Yeah, awesome, so, yeah there are those things you can do there as well, but the strategy for this is the same, and I would say, if possible use an ORM, right? Do something where you're not kind of doing it yourself, right?

[00:01:31]
Because like in this case yes, pulling out passwords bad, right? Ideally, they were encrypted, all right? Ideally, you need to be PCI compliant if you're storing credit card information. We just got a whole bunch of things about, breaking the credit cards up between multiple segments and storing them differently, encrypting them.

[00:01:47]
Otherwise you can't hold onto a credit card at all, honestly just use stripe or something. But the idea is having it in place because, if you don't, these things can happen, right? And that is an entire, class of issues that you need to solve but ideally you can, right?

[00:02:06]
But again, having the logging around any of those things that come in so you can detect somebody even poking around, no one's gonna get it right on the first try, right? They are going to, look I got back a weird different response, we won't cover this, but, one of the more interesting, ways of researching and attempting attack.

[00:02:23]
Is, what's called a timing attack, which is, we talked before about how signing stuff and encrypting stuff takes time, right? So, a way to do research is how you're not supposed to say whether or not the username is taken, and they have the wrong password or whether they exist?

[00:02:38]
One attack is, hey if I type in a complete, ridiculous username, it's lightning fast, and if I type in probably a real username, it takes, 20 more milliseconds. That one must exist, because they were trying to de-hash the password to see if the password was right, right? It's those little things that will give this stuff away, so having the logs and stuff along those lines, is incredibly important.

[00:03:07]
Because yeah there's all sorts of fun ways that you can do this, there's a bunch of other fun ones for this fake data, right? You can watch me get fake data in six different ways if you really want to, it's in the readme on this example and I will put a link in a second.

[00:03:20]
You can try out some other ones as well, it's a good time but no one needs to watch me do it repeatedly. The one thing that I that could be even worse, maybe not worse, losing people's data is pretty bad, is there's all sorts of other harmful SQL injection, right?

[00:03:38]
So this is I joked about before, you legally have to show this cartoon, if you are teaching web security. This is Bobby Tables, which is, yes she has named her child Robert colon, drop table students, semicolon, dash, dash right? Which, when they ran it on sanitized query, dropped the entire student database, there's the joke, I've now explained a joke to you, yay, that is obviously bad, right?

[00:04:08]
And this is kind of one of those where you learn that, this process is important, but yeah, it exists. It's, we'll see we'll look at some case studies like I said later, I keep teasing you, but, it is a thing. I could not, do a drop table in here because, the SQL library has this idea of get all run.

[00:04:30]
There are protections in this library that stopped me and, as much as I am here to build purposely broken ass for you, writing my own, native binary. Sequel driver just so I could drop a table in front of you all, when I could have showed you a cartoon was not interesting to me.

[00:04:48]
So I personally, couldn't drop the tables in this app, I will be honest I didn't try that hard, right? But the there were some abstractions around the get and the all where I wasn't able to do it. But I will also leave that as an exercise to the reader if you can manage to drop one of these tables in here as well.

[00:05:04]
I couldn't, maybe you can succeed where I failed, awesome. So yeah we said before the fix to this one is the same, there's also the other thing you can do. And again, I don't wanna to over-fix it on this too much cuz, if you're using express and SQLite, what I'm about to show you works.

[00:05:20]
Nobody's using SQLite in production version, right? There's a version of this for postgres, there is a different thing for like I said, flask in Python, or whatever next js, what have you. The other thing you could do, and I have, is to have this idea called stored queries, so you could do something like, you could basically.

[00:05:41]
This is kind of the abstraction I had before, something like user's query, and I could say that this is actually await db.prepare. And this is what's called a prepared statement, and we'll say that, yeah, sure, right? So now this is, a function, that then will take that missing argument, it and, will escape everything, so on and so forth.

[00:06:06]
This works, it's just tricky if there is a lot of other stuff you need to do, you end up with a lot of these. And one time, I took on a consulting gig, and I saw somebody who took prepared statements in my sequel to its natural conclusion, and I since, personally have hated them.

[00:06:21]
But, they're good and everyone else likes them I just have, one bad experience that made me hate them for too long. But this will work as well, but this will give you a statement cuz, if you look at this, there's not, this statement is actually stored in the database, right?

[00:06:34]
Again, it depends on the database, so it's, it limits your surface area for these attacks. Now, again, we've seen flaws in the processors that intel makes, right? At the end of the day you are standing on the shoulder of giants and those shoulders might have security holes in them, right?

[00:06:50]
And you're not always, and this is why, staying on top of critical vulnerabilities, not keeping your dependents up to date, is a thing, but generally speaking, right? The more you can kind of, abstract yourself away to battle-tested tools, you're likely better off in a lot of cases. Unless that battle-tested tool is exploited and then you are, at least it's like if AWS goes down, or DNS goes down, it's a bad day for you, and half of the continent.

[00:07:21]
[LAUGH] So at least there's that.