SQL Injection

[00:00:00]
>> Steve Kinney: There are a whole bunch of fun ways to do this. Let's open up another fun example. And so we'll do npm start again here. And this time we're gonna go into our quaint little store. Let's go take a look at this wonderful little store.
>> Steve Kinney: If, let's see, I'm pretty sure, yeah, there are no cookies because the port is different.

[00:00:27]
See, same origin policy, kind of. And so we have our quaint little store. It's got a login now yes, the play. Placeholders are the username and password, because I got tired of looking in over a file to remember, and I got smarter by the second example. But we've got this home where we can log in.

[00:00:46]
We've got some products that you can buy at the store. You can go ahead and look at a product and you can see the privacy policy, which there's nothing to see there, cuz there ain't a lot of privacy on this site. So we could theoretically log in as bobby@tables.com.

[00:01:05]
With a very strong password, password123, right? And then we go ahead, we got the site profile, where we can change our credit card number and the expiration date, all sorts of stuff that store in the database, completely unencrypted, ain't bothering nobody. And I could do that. I could also choose to be somebody else I don't remember the emails at the top of my head.

[00:01:32]
Yeah, let's see what else, at least is an email. I won't even look at the password, right? Let's go into our quaint little store, and we'll go into the data, see our users, and okay, I don't even know well, we don't need siri in this case. I don't even, let's pretend I didn't see that password, admin@example.com.

[00:01:59]
>> Steve Kinney: Let's see what we got. I've made a typo, which actually what I'm gonna do real quick is also I'm gonna do the -- SQL, so you can actually see the queries as well, because that's gonna be part of the big reveal. Cool, cool, cool, let's go ahead and log in.

[00:02:21]
Nope, all right and so we can do that admin@example.com. And I'm just gonna do this, and I'll show you what I'm doing in a second when we see the query. Maybe I messed up something in the database, but let's look at the query. I didn't type in this, the person's password.

[00:02:41]
I just typed in quote space OR 1=1--, right? And this goes back to that escaping of the inputs that we were talking about before. You can kinda see it right here.
>> Steve Kinney: So we have that quote where a password would start. And I was you know what? Well, the quote will end, that these two dashes mean end of the SQL statement, which is where the closing quote was.

[00:03:07]
I was find me a user where the email equals admin@example.com and the password equals something, whatever it doesn't matter OR 1 = 1, right? And that becomes the logical statement OR equals true, right? Which is cool, come on in, right? You're gonna pass that database query and we're going to get you back the user, right?

[00:03:28]
And this is because we didn't escape our output. So this is a entire class of attacks. They can be in SQL they can be in bash, they can be also, depending if somebody, especially if they can figure out what technology stack you're using, right? And there are clues a lot of times that you can accidentally give away in this case, right?

[00:03:49]
If they can get a sense of what technology you're using, it becomes easier to figure out an exploit, right? In this case, I happen to know I'm using SQLite, right? So I happen to know the phrase is a SQL statement. I have a simulated version if they were using Mongo or something that where it would be a different attack to do something very similar, and part of that is figuring out what they're using now.

[00:04:10]
So if you just have error pages that spill out a stack trace, guess what you're accidentally doing? You're just giving away free information, right? Maybe you don't have the best security on your app in the world, but because they don't actually even know what you're using, it can be at least a little bit harder to figure out.

[00:04:28]
But if you leak stack traces clearly becomes a little bit easier. And then sometimes, if you don't just configure things correctly, right, you can also give away a bunch of information as well. And so, for instance, if we go into this request, right, we go through the headers, express tries to be a hero and tell you, powered by express, right?

[00:04:53]
And this opens you up for what's called sometimes a zero-day vulnerability, which is news of this thing just dropped. And a lot of times okay, the latest version of express or express something or Rails 6 point whatever has a security vulnerability. There's people who just have scanners. We're gonna powered by Rails 4.2?

[00:05:13]
Neat I know how to take advantage of that, right? And it's yeah, it's not even you did anything wrong. There was something wrong in your framework, but you're just kind of exposing hack me please, right? And so you can either there are settings for any framework. Doesn't really matter this express in this point you can, if check your response headers, if you are visibly saying who you are.

[00:05:39]
Go, get rid of that, right? Go turn it off. Framework dependent figure it out, turn it on, even if it says nginx, whatever or and I have never done this, but I've always thought real hard about it lie, right? You can make that header anything you want it to be, right?

[00:05:53]
You can, I can say that I'm Rails 3.1 now I don't necessarily wanna that just feels I'm just inviting attention, right? I would just hide it versus anything I don't need to welcome that kind of stuff, but I've thought up really hard about it. Let's go ahead and so that and this will there's always, unless you are literally working on a PHP app from 1998 you're probably using some kind of library.

[00:06:21]
To do your data fetching manipulation, right? And I would say if you have some kind of ORM, right, object-relational mapper, right? You're not writing SQL directly, that's probably good, cuz then you are less likely to make a boo-boo and again is using a library, does it make your code inherently more safe?

[00:06:43]
It does not, right? But the idea is if many people are using it, hopefully somebody else has found the air, if it is just your code, hopefully you found the air. I like the idea of many eyes on it, right? And so it all kind of depends on what library you happen to be using.

[00:07:06]
But in this case, I'm just using the SQLite library, in this case, nothing particularly special. And it's got this middleware here, which is just setting some generic view layer stuff. And also go ahead and fetching that session as well. So, let's actually look at the one we just exploited, which was the login.

[00:07:30]
As you can see, we're just string interpolating in there. So, when they escape something, we make a string that is problematic. Again, no matter what type technology you're using there is some version of this where you can kind of just say question mark and then pass it in as email and password.

[00:07:51]
If you don't use a comma then like JavaScript gets very angry with you, now the library will do all the safety mechanisms to escape, to make sure you don't have some quote that's gonna get you out of there. It's got all the necessary library code that you could write yourself.

[00:08:13]
You shouldn't, but you could. We'll go ahead and make it so that now if they do try to put one equals one in there, it's not gonna let them in, so on and so forth, right? And that's good, because this isn't the worst of your problems, right? There are lots of other bad things that you could try to figure out in here, right?

[00:08:37]
For instance, if there's an API for this app. So we can do API/users. So we've got this list of users, why you would expose CII and an API that I don't know, so we could also do products, right? Here's API of all the different products, right? And it's got if we look at the code there's some clever things you can do cuz we write good APIs where I can like search for a given product or I could do a limit of 10 right?

[00:09:11]
And I can pass those things in and that seems good except again I didn't escape my inputs right and so there's all sorts of dark stuff that you could theoretically do, right? For instance, and there's no way that I'm gonna stand and talk and live code this. So we will do a quick moment of copy paste, which is we'll do a version of that same SQL injection trick here.

[00:09:39]
This is a or search or do this union select where then also I don't know see if there's a table called sessions and get all the session IDs and user IDs that they're attached to right? Or maybe I just do the user one I intended to instead of worrying about fake PII but making decisions on the fly not always the best choice.

[00:10:00]
All right, so we'll grab that one, and maybe we'll grab something this, and I think this will do the trick for us, and we'll say search.
>> Steve Kinney: Look at that I've also pulled out their passwords, right? Again, cuz it's basically the name of the game for either remote, a lot of command injection we'll see slides in a second.

[00:10:25]
Command injection is what you call when you slid something into Bash. Remote code execution is when you slid something into JavaScript or Ruby or what have you. And then SQL injection, we slid into a SQL statement. There are various different names for the same idea here. You can do that theoretically, I'm sure I could also, it's a little harder once it's query params, but there's credit cards in this database, ideally, the hacker would be playing around and experimenting.

[00:10:55]
It's usually not a one time thing, but this leads us to a point that I will hopefully make again later if I do my job well, but I will make now just in case. Which is this is why a underrated security mechanism is having logs, right? Because one if they are not successful, it at least lets you see that somebody was trying and what they were up to, and if they are successful, at least you know what happened and how to deal with it, right?

[00:11:25]
So having logs is not just a, we should totally have the nicest thing, let's shift to production and we'll do a fast follow, cuz we all know fast follow the things we're never gonna do, right, the things that product won't let us do. We'll do a fast follow with the that later, and then we'll go ahead and add logging and alerting.

[00:11:41]
No, you do the logging and alerting early on, because then if you see that someone's even trying stuff like credit card or password, maybe that's an alert, right? And you get notified it beforehand, instead of waiting for an attack to happen silently that you don't know what's happening.

[00:11:56]
The nice part here is that, again the same basic strategy works. I will leave it as an exercise for you to refactor any of the code that you see. Some future examples I will actually do this. Some I just decided to leave open because I thought it was more fun.

[00:12:13]
But the strategy is the same every time. It is something like you throw a question mark in there and then you just move it out.