SQL Injection

[00:00:00]
>> What could be more exciting than right now talking about SQL injection, I can fathom nothing. So that's what we're going to do. So, I want to talk about why we do this, this part right here. Well think about this for just a second. We're taking an req.query.search.

[00:00:18]
And this, I need to rearrange my windows here. Here we go. This is coming from the user, right? Imagine this is just some thing available to your message board user. And they're coming in here and they're typing, you know, 45 and search and getting information back from the server.

[00:00:36]
Okay? Everything that comes from the user you have to assume as hostile right some not so nice actor is going to come in and put things garbage in there that's going to be destructive to what you're doing. Okay? If I put rec query dot search directly into here, in fact I'm let's be irresponsible and do this.

[00:01:01]
So I'm going to put this instead. And instead of that I'm going to put this. Okay, so now I'm putting directly into this query here. Whatever the user gives me. Why is this dangerous? Why is this terrible, horrible, no good idea? Because the user could just give you a number, which is what they should do.

[00:01:30]
They can put 45 or they can start giving you SQL, right? So what happens if the user comes in and says something like- One semicolon, DROP TABLE users, semi colon dash dash. I'm not going to do it because it literally wrecked my database right now. Let's just replace it here.

[00:02:01]
What happens if the user does like what happens if I put that into my query? That's gonna drop my users table, right? It's gonna delete everything that I have in that particular database. That's a huge problem. So if I put a semi-colon there, that means I can start writing more queries.

[00:02:25]
Like Postgres is not going to know. It was like, I assume this is what you want to do. This is the information that you sent me. I'm going to do exactly what you're doing. They put this dash dash at the end to comment out. If you have more query stuff after that, this is called SQL injection right?

[00:02:40]
You're interpreting user input directly as SQL. Hopefully this is striking like the fear of the hackers in your heart, right? That's why we don't do this this way. This is why you do parameterized queries because what happens here. Is now the driver, PG goes and escapes all of this and says nothing.

[00:03:04]
SQL related can exist in search, req. Query.search. So they escape out all the characters. They don't allow comments. There's a bunch of stuff that you cannot do in here. The driver will protect you from shooting yourself in the foot. So let's just kind of do something for fun here, so let's go back to the way that I had this, all right, so we do it this way and then I would change this to just be returned common rez, okay now what happens if I come in?

[00:03:42]
So right now I can just do 45 search. I got to restart my server to search, okay, this all works fine. The user is doing everything that we expect them to. But what happens if I do one colon. What do I have here? Yeah, SELECT * FROM user; -- Did I break it?

[00:04:15]
I might have.yeah, that's actually okay. Hold on. So this one, I'm just going to replace this with 39 just for a second because normally not running two queries at once based on the same user input. All right, one more time. Try this again. So, here's what happened here.

[00:04:41]
I got the board bags, I got the post back, but here's the issue. Now, what happened is it ran a second query for me here with a Select rowCount 1000. I just leaked all my user information. Right you can see here that here's all the emails usernames last log on.

[00:05:03]
This is like a catastrophe. This is how like, whenever you see like data leaks like have I been poned calm this is very frequently how this happens, some uninformed developer or developer that's just working too fast accidentally just leaves themself up into SQL injection. And someone less nice can come through and do some SQL injection here, right?

[00:05:31]
Now this is like relatively, you know, leaking emails is not good, right? But like leaking passwords, or leaking a bunch of other stuff can be absolutely catastrophic. And if someone discovers that they can do this, what they can actually do is they can go using Postgres set themselves up as an administrative user, give themself a password.

[00:05:54]
Figure out your IP and then directly connect to your database and then they can get everything right. They can exfiltrate all of your data and you would never know that there another user was in there and stole all of your data. That's exactly what happens to again a lot of big companies, if someone gets in there steals all their data, wipes, whatever record of themselves being there, and no one ever knows that they were ever there, right and they stole all of that data.

[00:06:17]
They only know you know, three years later when they see that their data is for sale. So all of this to say, SQL injection, really big deal. Make sure you're doing parameterized queries. This is like, if I teach, if you learn nothing else from this course, it's probably the most important thing is it's really important that you never do this, but I just showed you how to do don't do that.