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

Filtering

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
10
Ratings
5/5
starstarstarstarstar
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 see how we can improve the queries by adding some filters. So, for example, we already have two records right now, but maybe we can just display them by age, or by name, or by anything that we can think of like by ID. So, we're going to have to learn how to do that. In order to do that, I'm just going to delete this and add some new data. For example, I'm going to add 'Kirk'. And I'm just going to copy this and paste it like four times. So, let's add some random things over here, like age '40', maybe '55', or something like '33' over here.

So, I'm going to change the names as well. So, I'm going to make this 'Rob'. I'm going to make this one 'Lars'. And finally, I'm going to make this one, maybe 'Hetfield'. So, here you go. If I run this, it will be added to my test table. And, let's 'Run' this actually. And here we go. Now, it's added. We are not actually seeing anything because we haven't run any query. So, let me run some query over here. Select everything from test and here you go. Now, we see all the data in this test table. So, what we can do? We can filter the results, right? Maybe, we have tens of thousands of data in here, but we only want to see them filtered by name, for example. We only want to see the name equals Atil, or name equals James, or name equals Kirk. Or maybe we want to see where age is 60, or ID is four, something like that. In order to see those things, we use something called VARCLUS or varFilter. So, the syntax goes like this. So, you have to write 'WHERE'. And then, you specify the condition actually. For example, 'WHERE id = 3'. So, that's it. That's all you have to do; 'WHERE id = 3'. So, it's pretty self explanatory as well. And if we run it, as you can see, we only see the thing with the ID 3. Of course, we can do this with one and we can see the first record in this database, in this table. So, that's great. And we can do this not only by ID, but by name as well. But, we have to specify the value between single quotation marks like this. So, as you can see, it brings Atil. If I turn this to into 'James', we see the James. And the other columns change as well. So, that's great, right? So, it's pretty easy over here. Of course, we can improve this or make it a little bit more complex. Maybe, we may want to show the records that has a name starting with capital A, for example. Like a search thing. Like, show me the results that has the capital A for a starting character. So, rather than equals to, rather than an equal sign, I can use this LIKE keyword. So, show me the name where name is like something like this. So, it's again very explicit in this case. And we have a syntax like this. So again, we have to just write it in a single quotation marks. If we do something like that, 'A%', it means that first letter is A and the rest of it doesn't matter. So, A%. So, we can do this as well, 'J%'. So, it means that starting with J or H% starting with H. As you can see it brings all the results back. Or we can do something like this, '%D' ending with D or '%L' ending with L. So, first characters do not matter but it has to be L at the end of the name. So, this is a pretty common usage in the select queries as well. So, this is all for  VARCLUS, actually. But, we can improve this filtering by adding some additional stuff into our statements. So finally, I believe, we have to see the UPDATE keyword and DELETE keyword as well. So, let me select everything from test. So, for example, I have this James or Atil over here and age is 50. So maybe, I want to change the age of Atil. So, how do I do that? I can write 'UPDATE' keyword. So, it's going to update some values. So, I'm going to update the test table. And in this test table, I can 'SET'. So, this is a keyword as well. Age to be '33', for example. But if I do that, it will just set the age of 33 to every record, every row that we have. But if I only want to set the age for Atil, for example, I can just say, 'WHERE id = 1' or 'WHERE name = Atil'. So, if I do that and let me just select this after I finish this. Okay, like that. So, let me just delete this as well and then run this and see what happens.

So, the age is 50 right now, as you can see. And I'm going to turn it into '33' after I run this. And here you go, Now, the age of Atil is actually the 33. So, it's pretty good. So, we're going to come across this UPDATE. And also, we're going to come across this DELETE as well. So, I can just say, 'DELETE FROM test' and let me just delete everything over here because I'm going to have to take a note not to forget about this later on. So, 'DELETE FROM TEST'. And test should be... Let me just delete this and delete this as well because test should be in lowercase letters. So, 'DELETE FROM test WHERE id' is something or 'WHERE name' is something. So, it will filter out only the 'id = 4' and it will only delete the fourth row. So, if I 'Run' this and here you go. Now, we don't have the ID 4 over here. It deleted it. So, DELETE is a very dangerous command. You have to make sure that you apply filtering after delete or you end up deleting all the table or all the database. So, if you're working with your own database, just make sure that you apply filtering. So, that's it for the filtering thing, like  VARCLUS thing. But, of course, we can actually learn a little bit more advanced stuff when it comes to queries as well. We're going to see them in the next lecture.

 

About the Author
Students
944
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.