1. Home
  2. Training Library
  3. SQL Crash Course

Advanced Queries

Contents

keyboard_tab
Crash Course in SQL
1
Introduction
PREVIEW58s
2
What is SQL?
PREVIEW4m 47s
5
Filtering
7m 23s
7

The course is part of this learning path

Start course
Overview
Difficulty
Intermediate
Duration
41m
Students
4
Description

This course focuses on SQL vulnerabilities which are some of the most common and dangerous vulnerabilities that you will come across when you carry out web pentesting or bug bounty hunting. We'll start by covering the fundamentals of SQL and how a database is created using it. You'll also learn about SQL comments to insert values, get their values back or change them, delete them, or edit them.

Transcript

Hi. Within this lecture, we're going to cover some advanced query techniques and this will be very helpful when it comes to web pen testing and bug bounty hunting. So, this is one of the most important things that you should understand in order to continue searching for vulnerabilities and exploiting them. So, there is a thing called UNION SELECT and it allows us to do multiple queries at once,  like in a one single comment. And we can do this from the same table or with kind of different tables as well, but it has some specifications. So, let me show you what I mean. So, if I run this, SELECT * FROM test, I can see the records over here.

So, I can see the id, name, and age. Here, we also have a demo, here as well. So, let me run that and let's see what happens. So, we can see the records over here as well. So, they have this id, name, and hint thing. So, they all have three different columns as you might see. So, even though columns are different, so we have id, name, and age here,  and the other table we have id, name, and hint.

So, they are completely different in a way but they have the same count. So, even though they're not connected at all, they do not have any kind of relation between demo and test tables over here, they have the same count. So, let me show you how this works. If we write something like the SELECT * FROM demo and UNION SELECT * FROM test for example. Now within a single comment, we can get the results in only one table  but it'll be a lit bit complicated. So, it shows the demo records over here but it will be a little bit complicated. So, it shows the demo records over here, but also it shows the test records over here, within the same page. So, as you can see these are for the test, so the Atil, James, and Lars, and Hetfield. So, we have some multiple ids over here,  we have names and hints. But in the test table we didn't have anything called hint, but we see some age values over here under the hint column. So, why does this happen? Because we have specified demo to be the first table to be queried in this case. So, it actually brought us the demo over here. So, if I just changed the ordering of these two tables, if I run, I will see the age over here, I will see the age values but I can see the hint values as well. So, even though I see the hint values, the column name is age. So, we can get the data from both tables but we cannot see the column names at once. So, this is a good thing in a sense that we can get all the records in only one query, but also it's a little bit confusing because we don't see the column names over here and we don't have to see that.

If we know the column names, then it will definitely know that 33 is age, but for data science, the SQL online thing in the second row is not age, it's a hint. So, we will come across this situation a lot when we do SQL pen testing. So, we're going to see if we have an opportunity to do a query and we will try to combine it with UNION SELECT to see the table, our values that we are not authorized to see. So, this will bring us the values but we won't get the exact column name, exact value name over here. Then we're going to have to guess it or we're going to have to try and see the column name in a different query.

But this might get confusing in the other sections and other lectures in the advanced SQL injection site. So, let's do a little bit more example. I am going to create a new table over here called test2, you already know how to do it. So, it's going to have an id as well, this time as well. So, I am going to call this id, and this will be INTEGER PRIMARY KEY, and let's say name here like Varchar, and age here like an INTEGER, but this time we're going to have a fourth column. We already had this, so let me change this to name, surname, and this will be Varchar for example, but we also have age here which is an INTEGER, and that's it. So, this time we have four columns: id, name, surname, and age. And it will make things much more complicated in this case. Let's see why. We already have this test2. Now what I am going to do, of course I am going to insert something over here. So, what I am going to insert is of course the values. So, I am going to say test2, INSERT INTO test2, and I will have to specify the columns that I want to INSERT INTO. So, name, surname, age, and values will be within single quotation marks, 'Atil'. So, let's give the surname. So 'Samancioglu' my surname in full, and the age is 33. Now if I insert this, it will be edit and displayed to us but I am going to just copy this a couple of more times, so we can have more than one record over here actually. So, let me change this to 'James', 'Hetfield', and then age is 50, and go for 'Lars', 'Ulrich'. So, these are from Metallica. If you're a fan like me, then you would know.

So, here you go. We have James and Lars over here and I believe that's it. We can work with right, because we will have three values. So, let me delete all of this stuff and let me just call SELECT * FROM test2 over here to see the results. And let's see. Here we go. Now, If I say 'Run', here we go, now we see all the things and as you can see now we have four columns like id, name, surname, and age. So, I am going to delete all of this stuff and now what I am going to do I am going to try and do a UNION SELECT with the test2 and the other tables. So, this time it will get complicated, because why? Because they do not have the same column count. So, let's see what happens. If I say 'Run', as you can see it says that left and right of the UNION do not have the number of the result columns, do not have the exact same number of result columns. So, this is one of the specifications and requirements that we should meet if we want to do a UNION SELECT. So, if we do a UNION SELECT, left side of the UNION and right side of the UNION should have the same column number. So, in this case, we don't have the same column number. So, this is again a problem that we're going to face a lot during web pen testing and let's see how to solve this problem within the next lecture.

About the Author
Students
420
Courses
55
Learning Paths
3

Atil is an instructor at Bogazici University, where he graduated back in 2010. He is also co-founder of Academy Club, which provides training, and Pera Games, which operates in the mobile gaming industry.