Hit send and we get a 200 OK. That's a good indication that our exploit works. I love it that you didn't choose a simple password.
I mean, this lab isn't a simple password. It's great to see like a complex password being broken like this. Where we logged in as the administrator user.
Cookies are a great way for you to potentially cause a lot of harm to the application. You could actually use a SQL injection vulnerability to try and brute force the password by the SQL injection vulnerability because a cookie will never have some kind of lockout. And here we go.
We get the account page and it says, congratulations, you solved the lab. Hey everyone, it's David Bombal back with Rana. Rana, welcome.
Hi everyone. Thanks David for having me. So for everyone who hasn't seen our previous videos, I've put links below where Rana did some demonstrations, spoke about the best certifications that you can get in cybersecurity, but I've got a really exciting announcement. Rana is going to be collaborating with me by putting courses on Udemy. Rana is so excited about this.
Your first course is going to be SQL injection. Is that right? That's correct. So I'm very excited for the collaboration.
We're going to start off with the SQL injection course, which essentially kind of talks about the technical details. It goes into a deep dive of the technical details of SQL injection vulnerabilities. And then it talks about the different types of SQL injection vulnerabilities we have.
Over 17 labs for SQL injection, where you get hands-on experience, not only exploiting them manually, but also scripting the exploits in Python. And then we also talk about different prevention and mitigation techniques to prevent SQL injection vulnerabilities. So it's a really exciting course that is over nine hours long.
That's amazing, but I need to tell everyone because a lot of people complain about, you know, courses on Udemy and so forth. Rani, brief, what's the differences between those three platforms? Because you've got YouTube, you've got your Academy, and you've got Udemy.
I'm assuming like YouTube's free, but you get ads and stuff like that, right? Yeah, absolutely. So YouTube, you can watch it for free.
However, you end up with having to watch sponsor ads and also YouTube ads. And there's, for some of the videos, there's no auto transcription, which a lot of people ask for. The second option is to buy it on Udemy. So over there, there's no ads, there's no sponsor messages, and there are onto transcription features. So there is an auto transcription feature there, which is really useful for other people.
And I believe there's also a Discord channel. So David Bumble's Discord channel, where you gain access to his team and the support of his team. And I'll also pop into that Discord channel every once in a while to help out as well.
Now, the third option is My Academy. That's where you gain access to... The entire Web Security Academy series content, not just the SQL injection content, it's a little bit more expensive than Udemy. But over there, again, you gain access to a ton of vulnerabilities instead of having to purchase individual modules on their own on Udemy.
And then you gain access to my own private Discord channel where you can ask me questions. Yeah, Ronna, I think what's fantastic about that is, you know, it's different options for different people. If you're struggling financially, go and watch the content for free on YouTube. But I know a lot of people prefer paying $10 or $20, whatever the course price is on Udemy, whether it's discounted or not. And then they can watch it sequentially in a platform that's built for courses with no ads.
And I will say this, as Rana mentioned, my team are providing support. Udemy has its own support system. So a lot of support is done that way.
But the best place to go is my Discord, where there's a whole bunch of people that can help you if you're struggling. And obviously you don't necessarily get that on YouTube. The ultimate option is to buy the course from Rana's Academy where you get access to her, where she provides the support and you get access to a whole bunch of other content.
So different options for different people. Hopefully one of these options is great for you who's watching. But Rana, let's give people a taster. You have got a demo, I believe, with SQL injection. But perhaps you can take us down this road.
What is SQL injection? Why do we care? And how does it actually work practically? Yeah, absolutely. So.
Like David said, today we're going to be covering SQL injection vulnerabilities. So SQL injection for the longest time fell under the number one most critical security risk facing web applications today. It only changed to number three about a few years ago.
So today, we're going to get a taste of that. Of course, we can't learn everything. It's a nine hour plus course.
So today we're going to go over the technical details behind SQL injection vulnerabilities. We're going to learn about the different types at a high level overview of SQL injection vulnerabilities. And then we're going to do some hands-on experience. So we're going to gain some experience manually exploiting SQL injection vulnerabilities, but we're also going to automate one of the exploits in Python.
And then we'll learn different mitigation techniques for SQL injection vulnerabilities. All right, so we'll start off with talking about what is a SQL injection vulnerability. So SQL injection is a vulnerability that consists of an attacker interfering with the SQL queries that an application makes to the backend database.
Let's take an example to better understand this. Imagine you have an attacker, a web server, and a database. Now the application is similar to pretty much every application that you interact with. It has some kind of authentication mechanism. And in this case, it's a username and password.
So form-based authentication. It asks you for your username and password. If you enter the correct username and password, it makes a query to the backend database with that username and password. If they exist in the backend database, it logs you into your account. If they don't exist in the backend database, that means you're not a user of the system or you entered them incorrectly.
And so it'll give you an error saying you have entered either an incorrect username or an incorrect password. That's essentially how kind of web applications work when it comes to form-based authentication. Now, the issue over here is that if this client supplied input, so the username and password that you as a client put in, if this goes directly into the query and it's not validated in any way, in the backend database, then you could potentially inject SQL code that interferes with the SQL database. And when you do that, there could be catastrophic results.
It could be authentication bypass. It could gain full-on remote code execution. You could be able to read certain content from the database and so on. And we'll see many examples of that.
But as an attacker right now, what I want to do is gain... access to the administrator's account. However, I only know that the admin user has the username admin, and I don't know the user's password. So what am I going to do in this case?
I'm going to try to see if the application, which uses a SQL server, so a SQL database in the backend, I'm going to see if it's vulnerable to SQL injection. And the way I do that is, again, I inject SQL code that could potentially interfere with the backend database. Over here, let's say the query that the application makes to the database is select star.
Select everything from the user's table where username is equal to the username that you put in here, and password is equal to the password that you put in here. Now, once again, you put in the username and password, it checks. Is this a valid username and password? If it is, it returns the user's profile. Otherwise, it gives you an authentication error.
Now, in this case, what happened is that we added, as an attacker, we added admin and then a single quote and then dash dash over here. And so if this query is not parameterized or not validated in any way in the backend, any character that is also a SQL character, will be interpreted as SQL code. So over here, the single quote is a SQL character. What ends up happening is this single quote over here closes off the single quote for this string over here. So it closes off the admin string.
And then the dash dash represents a comment character in SQL. And so it says, comment out the rest of the query. So what ends up happening is the entire query becomes something like this.
So select. all the entries from the users table where username is admin. Now the query is no longer asking for a password. And so the database checks is admin a username in the database, the database response?
Yes, it is. And so it returns the admin profile, and the user gets logged in as the admin user. And it's as simple as that.
And Rana, what I really love about, you know, what you do in the real world is you, you are working with app developers, and you're doing this stuff in the real world, right? Yeah, absolutely. So I started off in my career as a pure pen tester. So I used to find these vulnerabilities all the time. However, I realized that when I do find these vulnerabilities, depending on how critical the vulnerability is, and the timelines that the developers have, they usually didn't fix the vulnerability just because as for a pen test, we do it at the latest stage of the software development lifecycle.
So I was kind of tired of the developers and the team deciding not to fix vulnerabilities and accepting the risk just because they don't have the time to push it in production in time. And so what I ended up doing is I actually switched into application security. So now not only do I pen test applications, but I also work with developers at earlier stages of the software development lifecycle to make sure that they find these vulnerabilities earlier and so they have time to fix them. So, I mean, on YouTube, I always get these comments.
David, this is dumb. It's too simple. So, this example that you've given us, is this actually real world that you've found in your experience?
Absolutely. So, I found this exact vulnerability in a previous application. Now, frameworks have built-in defenses right now, and everyone is using parameterized queries. And so, it's less likely that you find this specific vulnerability where you could just bypass authentication.
However… I still continue to this day find different types of SQL injection vulnerabilities. They're just a little bit more advanced. And what we're going to do in the presentation today is have this exact example. So gain some hands-on experience, and that would be the simplest one.
And then with each lab, it increases in difficulty. Of course, we're going to do just three labs just because of the limited time that we have. However, in the course, we have, again, about 17 or over 17 labs where each one goes from... practitioner levels.
So the easiest and simple cases of SQL injection, which you could still definitely find today, but each one increases in level up until you get to expert level, which are definitely ones that I find in applications that have been developed in the past couple of years. I love it. I love that you're sharing, you know, with the community, your experience and that it's real world. But Ronna, I don't want to take you too much on a tangent.
You've got more demos. Is that right? Yes, yes, I do.
So if we move on, at the beginning of that presentation, I mentioned that for the longest time, this vulnerability category fell under the number one most critical security risk facing web applications today. So the OWASP top 10 is essentially just the list of top 10 most critical security risks facing web applications, and it's compiled every year. Now, 2010 is not included on the slide. However, in 2010, in 2013, and in 2017, injection was considered to be the most critical security risk facing web applications today. And SQL injection falls under that category.
And again, the reason is because injection vulnerabilities usually allow you to perform a variety of things. And specifically, SQL injection allows you to bypass authentication. It allows you to read.
the content of the database, which usually leads to bypassing authentication. And then depending on the privileges that your database is running with, it could even potentially allow you to gain remote code execution. And so it's a major vulnerability that you should definitely test for if you're a pen tester. And if you're a developer, you should definitely be on the lookout for it to make sure that you're using proper secure coding practices to make sure that you don't introduce that vulnerability into your code.
All right, so this is the first lab. I've got the link to the lab on the slide and we'll also include it in the description of the video. So over here you've got a regular shopping application that allows you to shop online and essentially buy items. However, in order to purchase an item, you need to log into your account.
Now me, as an attacker, I don't have any funds to buy any items and so my main goal is to gain access to the administrator's account which can buy items. any item that they want. Now, the assumption that I'm making over here is I have the administrator's username.
We're going to assume that through, let's say, awesome techniques or open source intelligence, I figured out that the username of the administrator user is administrator, but I wasn't able to gain his password through password dumps, and now I'm stuck trying to find a vulnerability in the application. that will allow me to bypass authentication and gain access to the administrator's account without knowing the password of the administrator. So the first thing I'm going to do is try and figure out if the application could potentially leak a SQL query, just so that I don't have to guess it on my own. So to do that, you just enter SQL characters, which could potentially break the backend query, and then generate an error. So I'm just going to enter a bunch of characters that will be viewed as SQL code, hit login and see if it generates an error.
Now right away you see that the error is suppressed and it just gives you an error saying that it's an invalid username or password. And if I look at it through my proxy right over here you could see that the error is also, so we could send this to repeater to see it a little bit better, but you could see over here that the error is also suppressed at the at the client level as well, so in the proxy. And so I'm out of luck, it's not going to tell me the SQL query, so I'm stuck kind of guessing it. So based on previous experience, my guess is the SQL query looks something like this. So let's say select star.
from the users table where username is equal to whatever username you give it and password is equal to whatever password that you give it. And so just like with the example, now I need to figure out a way to change up the query. So inject SQL code to change up the query a little bit so that it's no longer asking for my password.
And to do that, I'm just going to take the username that I've enumerated. put it in here, and then now I need to close off this single quote. So I'm going to close it off and then comment out the rest of the query.
If you don't comment out the rest of the query, you're going to have a quote over here that is essentially just like a lost quote that doesn't close off anything and so it'll end up generating an error. And then also you have the clause of what's the password as well. And so by commenting it all out, you essentially remove this entire string right over here.
And so I'm going to copy my payload and hopefully that's going to allow me to log in as the administrator user. So I'll just add any random characters for the password. It doesn't matter because it'll get inputted over here and this entire part is commented out. So we hit log in and here we go.
We were able to log in as the admin user and you could see it says congratulations, you solved the lab. It's as simple as that. This was a practical demonstration of what you just showed in the slide.
And Ronna, just because this question people always ask when they take courses on Udemy or other platforms, you've got these kind of labs in your course, right? Yes. So these are labs that are provided by Portswigger, so the same organization that created the Burp Suite tool, which everyone uses, and the same authors of the Web Application Hackers Handbook, which is considered to be the Bible of. web security.
Personally, in my opinion, I think this is the best resource in order to teach web security. And so what I do is I use the labs in my course. And in fact, my videos, my YouTube videos are integrated also in the Portswigger Academy itself under each lab. You know, that's what I love about the way you teach. And I'll just say for anyone who hasn't watched Rana's videos, go and have a look at them on YouTube.
You have this amazing ability to take complex topics and explain them very, very simply, but also give us a whole bunch of practical examples of that. So this is fantastic. All right. Let's move on to talking about the different types of SQL injection vulnerabilities. Okay.
So there are overall three different types of SQL injection vulnerabilities. The first one is called embed or classic SQL injection. That's where the...
Communication channel where you send the payload is the same channel where you receive the response. So in the previous example, we sent our payload administrator a single quote dash dash in the application and we were able to automatically receive the response in the browser itself where we logged in as the administrator user. That's a type of inbound SQL injection.
Now, inferential SQL injection or also blind SQL injection. is a little bit different. So when you send your payload, you don't receive the exact response in the same communication channel. So you're kind of stuck asking the question, true and false questions.
Instead of asking the application to give you the result of your payload, you would have to ask the application, hey, is so and so true? And if the application responds in a certain way, then you can infer that it's true. And if it responds in a different way, then you can infer that it's false. So it's a little bit more difficult to exploit than in-band SQL injection.
The last type is out-of-band SQL injection. That's where you definitely don't receive the response in the same communications channel. You would structure your payload in such a way where you send your payload and then...
the response of your payload will be sent to you in an attacker-controlled server. So you would have a server that is out of band and set up that you control, and the response of the payload that you send in the application gets sent to your server itself. Again, this one is also a little bit harder to exploit than in-band SQL injection.
Now, for today, we're not going to be able to cover all the different... types and subtypes of SQL injection vulnerabilities. And so we're just going to cover two more examples. The first one is union-based SQL injection.
And then the second one is going to be Boolean-based blind SQL injection. And we'll move on to the second exercise, which is SQL injection using the union attack to retrieve data from other tables. Rana, we obviously don't have the time to go through them here, but in your course, you cover a whole bunch more, right?
Yeah, that's correct. So So In today's demo, we're just going to cover three labs of varying difficulty levels. However, we're going to end up having to make a bunch of assumptions because, for example, the third lab on its own takes about 40 minutes to kind of be able to explain how to exploit that SQL injection vulnerability, just because it's more on the expert level where you need to know certain pieces of information in order to be able to do it. However, in the course, what we do is we have a video.
for each lab. And we have, again, I believe over 17 labs in the SQL injection module, where we go in depth and in detail on each lab. We'll start off with the practitioner labs, which are considered to be easy. And then we'll move on to intermediate labs.
And then we'll reach the expert labs, where we'll exploit kind of really intricate SQL injection vulnerabilities that can be found by automated tools, because you need to keep going back and forth between the application. until you figure out what the SQL query is, and then keep going back and forth with the application until you actually exploit the SQL injection vulnerability. All right, so this is the second lab. Again, it's a shopping application. I've got the link on the screen, but we'll also include the link in the description of the video.
So in this case, the SQL injection vulnerability is not in the login page. So we're not going to try and attempt to bypass authentication by exploiting the login page of the app. application. Instead, we're going to try and see if the application has any other SQL injection vulnerabilities in other parameters that talk to the backend.
So in a real world application, the first thing that you would do is figure out all the different parameters in the application that potentially talk to the backend. So let's say over here, we're going to say corporate gifts. So let's click on that and see the request that is being made by the backend. And we'll make that a little bit bigger. and send this one to repeater.
Now over here you could see it takes in a parameter called category and then it takes in the category that you selected and then based on that it displays the different products in that specific category. Now for sure I know that this parameter over here is talking to the back end because it needs to tell the back end that I selected this specific category in order for it to show me these specific items under this category. And so the first thing I'm going to do is test this for SQL injection vulnerability.
So I'm going to click send right over here and see if it generates an error. So it does give me an internal server error. However, it looks like it's suppressing the error. And so again, I'm out of luck in this lab and I can't output the query itself, so I'm stuck guessing it.
So based on my previous experience, my guess is that is the query is going to look something like this. So let's say select star from let's say category table, it'll be slightly different, but the idea is the same. So select star from category table, where category is equal to the category that we give it right over here. So in this case, it was corporate gifts.
So let's add that right over here. And then once the SQL database receives that query, it queries the database, and then it'll output all the items that are under the corporate gifts category. So right now we have a different type of SQL injection and this one is specifically called union-based in-band SQL injection.
And the reason it's called union-based is because it uses the union operator. So the union operator is spelled like this and essentially what it does is allows you to combine two queries together. So my aim right now as an attacker is not only to get it to run this query over here, but I want to use the union operator in order to get it to perform a query of my liking. And the query that I'm interested in is the password of the administrator user. And so what I'm going to do is make sure that this is a valid query.
And the first thing I'm going to do is add a single quote because I need to close off this single quote over here. and then I'm going to add my dash dash to comment out the rest of the query. And then from there, I'm going to use the union operator in order to add my query. So we're making a few assumptions in this lab. Again, in the course, what we'll do is we'll actually teach you how to enumerate the fact that there is a users table in the backend and the fact that the username and the password columns exists in the users table.
But right now we're going to assume that we already know all of that. And so in order to exploit the SQL injection vulnerability, we're going to say union. And we want to say, select the username and the password from the users table.
However, before we do that, we need to actually follow the rules of the union operator. And there are two rules in the union operator. The first one is that the two queries, so this one right over here, and whatever query you write, have to have the exact number of columns. So we'll write that down, we need to figure out the number of columns. And two, the columns have to have the same type.
And so if you don't follow these rules, you'll end up erroring out. And as we saw over here, you'll just get an internal server error and you won't know. why you ended up erroring out.
And so the first thing we're going to do before actually writing up our query is we're going to try and figure out the number of columns. And the way we're going to do that is using the order by clause. What that does is when you order by a column, if you don't get an error, that means the column exists.
But if you try to order by a column that does not exist, you end up getting an error. And so you can infer that the column does not exist. So we'll start off.
by ordering by the first column, which we know for sure exists because if you just look at the application right over here, you could see that there's at least a column for the name of the item and then there's also at least a column for the description of the item. Now there might be a third column like an ID column that doesn't show but is used by the database just to keep track of the different items, but we don't know that for sure so we need to kind of figure that out on our own and again we'll do that using the order by clause. So control U to URL encoded, hit send, and we get a 200 OK.
So that means it ordered by the column. So we definitely have one column. We start off by ordering by two.
We still get a 200 OK. And so we definitely have two columns. We order by three, hit send, and we get an internal server error.
So that means there's no third column. So we've deduced over here that there is two columns. in the database. Now the next thing that we need to do is figure out the data type of each column because again if we output something of a different data type in this query that is different from this query we're going to error out and our SQL injection is not going to work. So the way to do that is to use the union operator and say select and then put the data type over here.
So my guess is they're both strings so sets of characters just because you could see over here. It's a character and over here it's alphabets at least. It could potentially be alphanumeric with certain characters as well. So I'm just going to say a and a and see if that works. If it doesn't work that means one of them is incorrect.
So let's copy this, put it in here and url encode it, hit send. And we get an internal server error, meaning that something must be wrong. So if we go back to the query right over here, I can immediately tell what's wrong. It's because I added this for some reason, and I don't know why I added that.
It's nice to see you make a mistake and troubleshoot it. So that's great, actually. Yeah.
So in the course, you'll see a lot of that. And the reason I've decided to keep that in the videos is because I've had a lot of comments saying that people learn that way. And so I've decided to every time I make an error, I actually include it in the course just so that people learn how to debug their code as well. So hit send.
And now we get a 200 OK, meaning that both columns accept strings. So now I know that I can output my information in those columns. So I'm going to say select username and password. from the users table.
Again, we're making the assumption that we know that there's a user stable. In the course, we'll start off in the first few labs making that assumption just because we want it to be simple for the user to kind of get the hang of exploiting SQL injection vulnerabilities before we get into more technical details and more difficult labs. But we will teach you in the course how to identify that it is using a user stable and there is a username column and a password column in the users table.
So let's copy this right over here, put it in here. And then again, we need to URL encode it, or the request won't be valid. Hit send, and we get a 200. Okay, that's a good, that's a good indication that our exploit worked. So now what's going to happen is because we use the union operator, it's going to run this query over here, which will output this entire content over here, but it'll also combine that query with this query over here. So it should output the content of this query over here, which is all the users in the database.
So if we go down right over here, we should see users and here we go. So you could see right away that we've got an administrator user, and this is the password of the administrator user. And if we go down, here's an Yeah, and if we go down, here's another user, here's another user, and these are all their passwords. Now, in a real world scenario, most applications right now will hash their passwords in the back end.
They won't save them in clear text, in which case what you would do is extract the hash. That's still a pretty useful piece of information to have. So you extract the hash, and then from there, you try to perform a dictionary attack on the hash.
and retrieve the plain text password. But again, for simplicity over here, it's just in plain text. So we're going to copy that, go to my account, go to administrator and then paste the password over here and hit log in.
And here we go. We logged in as the administrator user. What I love about what you're doing though, and I think it's something that everyone who's watching can... can see is that you give us the, this is the way to learn, because when you learn, you have to keep it simple. And then you give us like, okay, here's the real world stuff that you need to be aware of.
And I love that, that you're giving us both, because the problem if you know, I always say this, it's a curse of knowledge. Some people who have too much knowledge, forget that people who start don't have enough knowledge to, you know, go to the full production level. You've got to start slowly, you know, you've got to crawl before you can run.
So crawl, walk, run. And I love that you're doing that, Rana, taking us from, you know, here's some simple examples to at the end of the course, like here's the expert level. Yeah, absolutely.
So that's how I learned myself. And I always try to teach it in the way that I would have wanted to learn it if I was just getting started right now. Rana, that was a brilliant demo. And what I love, again, with what you're doing here is you're taking us from a very simple to a more complex one.
And I believe you've got a third one that's even more complex, right? Yes, I do. So here's the third lab over here. Again, I've got the link. on the screen and we'll add the link in the description of the video as well.
So it's the same shopping application that we've been dealing with, with the exception right now that both the login page is no longer vulnerable and also the search field for the category is no longer vulnerable anymore. And so now we need to look for even more parameters that talk to the backend that could potentially be vulnerable. So this is one of those slabs which is more on the difficulty level. because we're no longer dealing with in-band SQL injection, where you receive the response in the same channel as the one that you inserted your payload on. Now you have to kind of infer the response, and that's why it's called inferential or blind SQL injection, by just asking true and false questions.
So first of all, if we look right over here, you could see you've got the homepage, you've got the My Account page. And now we're going to visit a page in the application and view the request just to determine how the application functions. So let's make this a little bit smaller. And let's say we go to accessories.
All of a sudden, I see that there's a welcome back message. So my guess over here, there's some kind of cookie or there's some kind of tracking mechanism that is being used to determine that this is not my first time on the application. Because when I first visited it, I didn't see the welcome back. But when I visited another page. I see the welcome back message.
So if we look at the request right over here, let's go let's make this bigger, you could see and actually we'll send this to repeater and hit send. And if we search over here, you'll see there's a welcome back message. So if we look at the request, again, this is just the category field that we exploited in the previous lab.
And in this lab, it's no longer vulnerable. The other parameters that could potentially be talking to the back end are the cookies. So most people will not test cookies for injection vulnerabilities just because they assume that they can be vulnerable.
However, cookies are a great way, especially custom cookies are a great way. for you to potentially cause a lot of harm to the application. And it's usually missed by novice pen testers or pen testers that don't have much experience.
And so today for this lab, what we're going to do is we're going to exploit the tracking ID cookie to essentially bypass authentication in the application. So right now, I see that we have two cookies. There's the session cookie, which is generated by the framework.
So it's quite... unlikely that there is backend code that extracts this cookie and then uses it somewhere else. So the session cookie, which is generated by the framework, usually does not have any injection vulnerabilities.
However, tracking ID is definitely a custom cookie. So my guess is that it is definitely used in some kind of custom code. And if there's no proper validation, it could be vulnerable to a variety of injection vulnerabilities and SQL injection is just one. them.
So again, what I'm going to do is just insert a single quote over here, see if I could break the application. It's a 200 okay, so it does not tell me that it's actually vulnerable. So we're going to have to kind of guess what the query looks like in the backend. And then based on that, and based on the response of the application, figure out if that query is actually what it looks like, and then how to exploit that query.
But before we do that, there's one thing that we need to notice over here. And that's the fact that no matter what you change this to, it's not like you could output the content of your SQL injection in the response. And that's why it's called a blind SQL injection, because you can't just use a union-based injection. Because like we said, with union-based SQL injection, the query has to have the same number of columns.
as the original query. However, this one doesn't actually output any content in the page. And so this is definitely going to be a blind SQL injection vulnerability if it's vulnerable. And so we need to figure out a way of how to tell if when we ask it a true and false question, if it's responding with true or it's responding with false. And the way we're going to do that is essentially we're going to see a variation of the response depending on whether it's true or false.
So if we hit send right over here, this is a tracking ID that we know exists in the backend database. And you see over here, we get the welcome back message. However, if we add another character, we know that this tracking ID no longer exists because we've changed it up and we hit send and we no longer get the welcome back message. And so that's what we're going to use in order to ask the application true and false questions.
If it's true, the app application will respond with a welcome back message. If it's false, the application will not have a welcome back message in the response of the request. And this is why I said these ones are a little bit harder to exploit.
So a few assumptions. Again, we know that the database has a user stable, and the user stable contains a username and a password column. And the username that we want to enumerate the password for is the administrator user. So um, we're gonna try to kind of figure out what the query is and then try to exploit it. So let's say the query is select star from let's say tracking ID table where tracking ID is equal to this tracking ID right over here and we'll remove the x because we added that so now we should see the welcome back message let's copy that and paste it in here.
So this is the regular query now I need a way to make the query output a true statement and see if it still gives me the welcome back message, and then make the query output a false statement, and then see if I don't get the welcome back message. And if that's how it works, that means this is definitely vulnerable to SQL injection. So over here, I'm going to say single quote to close off this string right over here.
And then I'm going to say and one is equal to one and comment out the rest of the query. So over here what I'm asking it is, does this tracking ID exist in the tracking ID table? which I know it definitely exists because the application gave it to me.
So this will report as true. And then I'm asking it, and is one equal to one? And one will always be equal to one. And so true and true will evaluate to true.
So this is where math, the very few cases that math is actually useful in life. So let's copy this. Paste it in here.
and see if we still get the welcome back message. If we do, then this is definitely vulnerable to a blind-based SQL injection. Okay, hit send, and here we go. So you can see we do have a match on the welcome back message.
So the application was actually able to evaluate the end operator and then evaluate that one is equal to one and then respond with the true case. So now let's test it out for the false case. and see if the application does not respond with a welcome back message. So we're going to say one is equal to zero. So again, we know that this will evaluate to true because this tracking ID exists in the database, but one will never be equal to zero.
So this should evaluate to false. So true and false should be equal to false. So we shouldn't get the welcome back message.
So let's copy that and then control U to URL encoded. Hit send. And here we go. Okay, so now it's done correctly.
So zero matches over here, meaning that when we force the application in a true use case, it responded with a welcome back message. And when we force the application in a false use case, it responded back with a with a lack of a welcome back message. So now that means we can ask the application true and false questions. So For someone who doesn't have experience in this, the first thing that they'll maybe suggest is, why don't we ask the application if the administrator's password is, let's say, password1!
So it would look something like this. We have to put that in brackets. Select. Password from users table where the username is equal to administrator.
And we have to put that in single quotes. And the password, oh, actually. We don't ask for the password over here because it's a true and false question.
So we're going to say and select the password from the users table where username is equal to administrator and we're going to ask the application is the password let's say password one exclamation mark. So now we're asking the application again a true and false question. We're asking it I want you to extract the password from the users table where the username is equal to administrator and I want you to tell me if it's equal to password one.
If it is equal to password one, it'll respond with a welcome back message. But if it's not equal to password one, it won't respond to a welcome back message. So intuitively, this might be like the kind of thing that you would suggest that you should do.
However, it's not the smart thing to do. And the reason is because why go through all the trouble of building a SQL query and actually exploiting the SQL injection when you could just simply do it by going to the My Account page? and literally brute forcing the password over here, right? So you would just put password one over here and if it logs in that means you're the correct user and if you're not and if it doesn't log you in that means you put in the incorrect password.
So this is not very smart, we're gonna say not smart. The smart way to do this is to actually be able to enumerate the password with a finite number of characters. So over here It's infinite number of possibilities. The password could be anything.
But we were going to structure our query in such a way that with a finite number of operations, we're going to be able to enumerate the password. And the way we're going to do that, and it might seem like it's an infinite thing, but it's actually something that we could easily automate, is we're going to ask the application, is the first letter of the password A? And if the application responds with...
the welcome back message that means yes the first letter is A and we could move on to the second letter but if it responds with no welcome back message that means we're going to ask the application is the first letter of the password B. and then we're going to do that for C and so on until it responds with a welcome back message. And that's where it gets a little bit tedious and you no longer can do this using manual techniques and that's why I've actually written a Python code for that.
So if you look right over here, I'm just going to make this a little bit bigger. If you look right over here, this is a Python script that is a really good example of the Python scripts that you'll see in the course. And we actually write them from scratch in the course. So we don't just give you the script, we'll actually build it together. But the idea over here is that it just takes in a URL.
It also takes in the correct tracking ID and a valid session ID. And then from there, it asks the application true and false questions. And you can see over here, there's two for loops.
because it'll loop through each letter, actually each alphanumeric character, and until it gets the welcome back message, it'll move on to the next position in the password. So it'll move on to the second character and then the third character and so on. And depending on how big your password is, it could take some time. So the password over here is actually 20 characters in length.
So we're going to say terminal. in your terminal and we're going to run it but before we do that we just need to put in the correct tracking id so if we go to burp right over here and just copy the tracking id paste it in here and we'll also include the link to the python code in the description of the video all right so we put in the correct tracking id and the correct session id so now it's as simple as just saying python3 and then we're going to say example3 code dot and add in the URL of the vulnerable application. So let's copy that, paste it in here, and it's just the main URL.
And then we're just gonna hit enter right over here. And essentially what it's trying to do right now is to retrieve the admin password. And you could see over here, it's trying all the different alphanumeric characters, also a bunch of other characters until it enumerates it.
And the first one is actually the number five. And now it's trying to guess the second character of the password, which is B. And now the third character and so on.
And I love this because you said it's a 20-character password, right? Yes, that's correct. So it's essentially trying all the different letters and all the different numbers and all the different characters for each. position of the password, so for each character of the password.
And if we go to burp right over here, the way I write my scripts is by actually sending all my requests to my proxy just in case I need to debug a script if it doesn't work. So you could actually see all the requests being performed right over here in the tracking ID with the character that it's trying. I love it that you didn't choose a simple password. I mean, this lab isn't a simple password because that's also, you know, something people always complain about on YouTube is like, oh, these passwords are too simple. It's great to see like a complex password being broken like this.
Oh, yeah. And the way the Portswigger labs are is that each individual will have a randomized password. And so even if you copy the password that is on the screen, you won't be able to solve the lab.
You actually have to enumerate it yourself. because it's going to be a different password for your instance of the lab. So one thing I wanted to ask you, you said previously, you could enter the password into the UI and try and guess what the password is.
And the same thing here, isn't there like a password lockout? So is that just a weakness in the way that the website was written or something? Or is it just like this is a vulnerability in SQL?
That's actually a really good point. So over here, I said you could just log into the account or attempt to brute force the password on the login functionality. Now, this would not be possible if there is some kind of soft lockout to some kind of firewall or rate limiting that doesn't allow you to actually brute force the password. And that's where you could actually use a SQL injection vulnerability to try and brute force the password and not go through.
you know, the trouble that we did by the SQL injection vulnerability, because a cookie will never have some kind of lockout on it. And that's, that's actually an interview question that I had back when I started out in my interviews, if you were to brute force, let's say the login page versus a cookie that contains the password, which it should not contain. But in this case, it does contain the password, where would you run your brute force mechanism? Would it be on the login page?
or the cookie? And the answer is always the cookie because usually there's no rate limiting or lockout on the cookie, whereas there is rate limiting and lockout on the login page. Yeah, cookies just seem to be a nightmare.
I mean, this whole thing where YouTubers, including me, that where someone's cookie was stolen and they were able to log in to their session and do stuff on the YouTube channel. It sounds like cookies are just an absolute nightmare sometimes. Yeah. So while we're actually on that topic, a cookie is essentially just your short. lived password.
It's a randomly generated, depending on the application, but it's usually a randomly generated number or set of characters that represents your password. It's a short lived password. So it's not your password itself, but it represents your password.
And so what you need to do is ensure that the cookie has the HTTP only flag set on it. If it has the HTTP only flag set on it. That means that JavaScript, including the JavaScript of the application itself, can't access the cookie. So it makes it really difficult for it to be stolen unless the user actually compromises your computer itself.
Now, that's not your responsibility. That's the responsibility of the developers. They should include that flag on the cookie to make sure that it can't be stolen, at least remotely, by an attacker. Oh, I see.
It cracked it already. That's great. Yes. Yeah. So it already cracked it.
So let's copy that and then try to log in with the administrator user. Hit login. And here we go. We get the account page and it says, congratulations, you solved the lab. So Rana, what you've done in previous videos is you've told us or shown us the vulnerabilities and how to exploit them, but then you showed us how to prevent it.
Do you have that kind of information here as well? Yeah, absolutely. So when it comes to SQL injection vulnerabilities, the primary defense is the use of prepared or also called parameterized queries.
And we'll go more into that into depth on that as well. But there's also additional defenses that you could do to make it extremely hard for me as an attacker to exploit the SQL injection vulnerability. And that's through enforcing least privilege and having a white list input validation or an unallowed list input validation as a secondary defense. But like I said, the primary defense is the use of prepared statements.
So if we look at this code right over here, you could see that the state that the query itself takes user supplied input directly from the request. So you could see over here, it says request dot get parameter and takes in the customer name, and then it automatically injects it in the query. And so in this case, you could apply all the different methods that we learned about today in order to exploit this SQL injection to perform malicious actions.
However, if you were to use prepared or parameterized queries, what essentially happens is that you separate the client-supplied input or data from the structure of the query itself. So essentially, by the backend database, it's... performed in two steps. The first one is that the application specifies the query structure with placeholders for each user input. So the structure is kind of set in stone, and it has placeholders for the user's input.
And then your input is put in that placeholder. Now, no matter what your input is, whether it has a single quote or a dash dash or the union operator or whatever, now it's only viewed as a string. It can no longer interact with the SQL query itself because the structure of the SQL query is already set in stone and defined.
And that's essentially how prepared statements work. They make it impossible for SQL injection vulnerabilities to exist if you were to use that secure coding technique. And this is an example of how you would prevent SQL injection in the example that we saw in the previous slide.
So over here, the query is defined, and then it sets a placeholder for the username. And then you use prepared statements or parameterized queries in order to define the structure of the query and then add the user-supplied input, which is the customer name to the query itself. So this is the primary defense for a SQL injection.
It'll always be the go-to defense. It's definitely the one that you should use. However, there are additional defenses that would make my life harder as an attacker. And the first one is the use of least privilege. So making sure that the application uses the lowest possible level of privileges when accessing the database.
And the reason this is important is because if you do... for whatever reason, have a SQL injection vulnerability, the impact of the SQL injection vulnerability will largely depend on the privileges that you use in order to access the database. So certain functions that give you remote code execution on the database are only available for privileges like sysadmin privileges.
And if I have sysadmin privileges, My SQL injection vulnerability is going to have sysadmin privileges. And so I'm going to be able to gain remote code execution on the server. So make sure you run it with the lowest possible level of privilege. The second one is any unnecessary functionality in the database should be removed or disabled.
So the more functionality I have, the more my attack vector is going to be. And the third one is ensure CIS benchmark for the database in use is. applied. So the CIS benchmarks are essentially just sets of configuration that you could use in order to ensure that you're either secure by default, or you add a layer of security to your system or your database or your server and so on. And there is one for each different database type that you could use in order to ensure that again, it makes my life as an attacker a little bit harder if I were to find the SQL injection vulnerability. And then the third one is pretty obvious.
So all vendor issued security patches should be applied in a timely fashion. Now, the second additional defense that you should use, which I really hesitate putting on my slides, but because I see it everywhere, I have to. It's an allow list of input validation. So have an allow list or also known as a whitelist of characters that are allowed for this specific field. Now, the reason I hate to put this on the slide is because Let's say a single quote.
You would say that's not allowed. And so you wouldn't put it in the allow list. However, if it's a name field and my name is, let's say, O'Reilly, I need that single quote in my name.
Yeah. And so what happens is that usually customers complain and the developers go like, oh, OK, that's fine. We'll just add it to the allow list or the white list.
And then this will happen for essentially every character and the and the alphabets and every. every character that there is because people's names are different. And depending on the field, it's different and you might need characters. And so even if you weren't vulnerable to SQL injection in time, you will be vulnerable to SQL injection because you need to accommodate user experience.
And so I definitely don't recommend this. It's just an additional defense to the use of parameterized queries. And that's essentially it when it comes to how to defend or mitigate against SQL injection vulnerabilities.
I love it. I love that you show, you know, the problem and then you show us how to solve it. And I mean, people watching this video might be interested more on the red team side. Some people may be interested in like the secure coding side. So I really appreciate that you show both.
And in the course, you do both as well, right? Yeah, absolutely. So in the course, we have what we call a theory video where we actually go over the technical details behind the vulnerability, the different types of SQL injection vulnerabilities. And then the prevention techniques also explain them in more detail. And then we have all the lab videos which give you a hands-on experience in exploiting SQL injection vulnerabilities.
So, Rana, in your experience, are developers making a lot of these basic mistakes? Or is it getting better? It's definitely getting better. So SQL injection vulnerabilities, you used to find them in pretty much every application now.
And it would be on the login page and it would be as simple as the first example that we saw. So with admin, single quote, dash, dash, you immediately find, you immediately bypass authentication. However, right now they're getting a little bit harder.
So it would be more to the difficulty level of this lab than the difficulty level of... lab number one. So they definitely still exist. It just, as a pen tester, you just need to get better at finding them in kind of those odd places like the cookie and so on. So definitely still see them in real world.
But in your course, I mean, that's what I love what you've done. You've taken, you're taking people from the very basics so they can understand. And then you're taking them to this crazy expert level, which is more real world, right?
Correct. So if I were to kind of go over this code right away from the beginning, most people will find this very daunting and would think, oh, hey, hacking is for only like the smartest of the smartest people and so on. However, when you kind of do things like in order and like from really simple up until to expert level, you realize it's just a matter of understanding the concepts. And once you understand the concepts, this becomes really simple. And just for everyone watching, Rana, I really appreciate you sharing your knowledge by making it freely available on YouTube.
So for those of you who can't afford to buy the course on Udemy or can't afford to buy it on Rana's Academy, just use the link below and go and watch the videos for free on YouTube. But for those of you who want a structured course with some support, go and buy it on Udemy to support Rana. And me, really appreciate you for buying the courses there to support both of us to create more content.
And if you really want to support support Ronna, go and buy it from her academy where she gets the full funds. Lots of options here. So Ronna, again, thanks so much for sharing on this video. Thanks so much for creating all of this content, making it freely available on YouTube. But I'm really excited once again to collaborate with you on Udemy and share your content with more people.
Thanks so much, Ronna. No problem. Happy to help.
And I'm also very excited about this collaboration. So please check it out if you're interested in the courses.