SQL Injection - Lab #10 SQL injection attack, listing the database contents on Oracle

hi everyone welcome back to another  video in the web security academy series   in the previous video we covered lab  number nine in the sql injection module   so we got some hands-on experience by exploiting  a union-based sql injection in order to list the   database content on a postgres sql database  in today's video we'll be using a union based   sql injection attack to list the database  content on oracle databases if you do not   have an account on the web security academy you  can get one by visiting the url portswigger.net   web security and clicking on the sign up button  i already have an account and i am logged in so   to access the exercise i'm going to click  on academy go down select the learning path   select sql injection go down one more  time select examining the database and the challenge that we'll be working with  today is titled sql injection attack listing   the database contents on oracle so let's  click that all right let's get started   this lab contains a sql injection  vulnerability in the product category filter   the results from the query are returned in  the application's response so you can use   a union attack to retrieve data from other  tables the application has a login function   and the database contains a table that holds  usernames and passwords you need to determine   the name of this table and the columns it  contains then retrieve the contents of the table   to obtain the username and password of all users  to solve the lab login as the administrator user   alright so over here we've got a  couple of end goals the first is that we need to determine which table contains the usernames and passwords then we need to determine the column names in that  table and then we use that information in order to   output the content of the table which would be  the usernames and passwords of the users of the   application including the administrator user and  then we need to log in as the administrator user   all right so the nice thing about this exercise  is that it doesn't assume that we have any   prior knowledge of the name of the table or  for example the names of the columns in the   table like we did in previous slabs instead in  this lab we're going to have to figure that out   all on our own just like we would have to in a  realistic scenario okay so let's access the lamp and create an analysis section over here all right so it looks like the same shopping  application that we've been dealing with in   the past couple of exercises you can filter on  categories so if i filter on gifts over here   it'll only display the items that are related  to this category so the items that are related   to gifts and we saw over here the category filter  is in the url to confirm that this is vulnerable   to sql injection we just add a single quote which  is a character in sql and this results in a syntax   error in the backend database which results in  an internal server error in the application which   confirms that this is vulnerable to sql injection  now whatever gets entered in the category field is   displayed on the page and that means we could  use a union based sql injection to display   content from other tables like a table that  contains the usernames and passwords of the   users of the application all right so since we're  working with union based sql injection there's   a couple of steps that we need to perform  the first one is to determine the number   of columns that are being used by the vulnerable  query and we said the way to do that is using   the order by clause and iteratively ordering  by the number of columns that are being used   by the vulnerable query so we start with one if  we get a 200 response that means the vulnerable   query is using at least one column and then we'll  increase it to two and two three and so on until   we get an internal server error meaning that we're  trying to order by a column that does not exist   so to do that i'm going to use burp because it's  much easier when it comes to encoding the input click start okay let's move that over here for a little bit  go to proxy set foxy proxy extension to intercept   or to send requests to burp all right and now  when i load this it should get intercepted and   burped and it does perfect all right so i'm going  to send this to repeater because i'm going to be   sending multiple requests and  i'm going to turn this off all right so we could see in the application there  has to be at least two columns one for the name of   the item and then another one for the description  of the item but again there could be more columns   that are not displayed on the page which is  why we have to enter this payload in order to   figure out the number of columns so we could start  off with one although i'm pretty sure that there   is at least two but let's do this iteratively  so ctrl u to url encode it hit send and we get   a 200 response code that means there's at least  one column and it's ordered by it let's do two and again ctrl u to url encoded   hit send okay we get a 200 response code that  means we have at least two columns let's try three hit send and we get a 500 internal  server error so let's write that down and what that means is we're trying to order  by a column that does not exist so the number   of columns that the vulnerable query is using is 3  minus 1 which is equal to 2. all right now that we   know the number of columns we need to find out if  these columns except type text and the reason we   do that is because the usernames and passwords  that we want to display from the users table   are of type text and so we need to be able to  output them in a column that accepts that type and   so to do that so let's just say find data type of  columns okay so to do that we use the union select   null statement so i've got two null values over  here because i know there's two columns based   on step number one now if we go back to the page  we could see the first column contains alphabets   and the second column contains alphabets so  i know with about a hundred percent accuracy   that this that both columns except type text so  instead of doing this iteratively column by column   i'm just gonna put type text in both of them so  a and a and see if it gives me a 200 response and again ctrl u to url encoded hit send and i get  an internal server error now that's weird because   you could see over here this accepts type text and  this accepts type text however remember that in   the title of the description it says that  we're dealing with oracle and we learned from   a previous lab that in oracle you need to have  the from clause and so we're going to use from   the dual table which is a dummy table that can  be used okay so let's try that and do control u and i did that incorrectly and i'm doing shift to  you here we go ctrl u hit send and we get a 200   okay and we should see our a characters somewhere  in the text and we see them right over here   all right so make a note saying  first that this is an oracle database and both columns except type text  all right so the third step usually would be   to determine what database we're working with  but just because it gave me an error on this   query over here i knew that it was an oracle  database and it works when it comes to dual so   i no longer have to display the version of the  database to see what database we're dealing with   so step number three in this case would be to  output the list of tables in the database and in   order to do that we're going to look at the hints  section which has the sql injection cheat sheet and i wanted to open that in a new page   and if we go down you could see the  section database contents tells you how to   output the list of tables in the oracle database  and it tells you for other databases but again   we know that we're working with oracle so we're  just going to look at this section over here   so you use all tables in order to output all the  table names in the database and we have to make   that work with our union based sql injection  so union select and we need two column names   over here so i don't know the column names of this  table over here so i'm going to google it and say oracle okay so you could see over here one of  the columns is table name the other column is   owner and so on i'm only interested in getting the  names of the table so i'm just going to say table   name and then the next column is going to be null  we have to have two columns again because over   here we determined that the number of columns is  two and then we comment out the rest of the query   copy that go back to burp ctrl u to url encoded hit send and we get a 200 response which is good now if  we go down we should see the list of tables so   you've got access that's a table name alert  and so on we're looking for something that   has the word users in it so that looks like a  built-in table so that's not what i'm looking for   same goes with this one here we go  so this looks like it's a custom   table for the application so i'm going to  assume this is the table we're looking for   and put it over here now that we know the  table name we need to know the column names   that contain the usernames and passwords of the  users of the application so the next step is to   output the column names of the users table and  to do that we'll look at the hints section again   and this is the way you output the column  names from the table so let's copy that again we have to make it fit with our union  base sql injections we'll add union select and   i need two columns over here so let's say column  and null we'll figure out what the column name   is in a bit and table name over  here so the table that we found and then comment out the rest of  the query all right so to figure   out the column names that are available  in this table again i'm going to google it so we've got owner table name column name  here we go so that's what i'm interested in all right so this should output the column names  of the users table so let's copy that go to burp ctrl u to url encoded and hit send again 200 response that's a good sign   and if we go down it should display the column  names of the columns in that table so you could   see there's this one over here there's this  one over here and i think that's it so it   only actually has two columns so the username  one let's copy that and the password column all right so now that we know the name of the  table and then the names of the columns that   contain usernames and passwords we could output  the usernames and passwords of the users of the   application so the next step is to output the  list of usernames passwords okay and to do that   we start off our union based sql injection  and then we say select the username over here and the password column from the users  table which is this one over here   and then comment out the rest of the  query let's copy that put it in burp ctrl u to url encode it hit send and again 200 response that's a  good sign that means the request worked   and if we go down you see that it's outputting  the usernames and then the passwords you've got   the username for administrator carlos and  so on so i'm going to copy this over here and remove the extra characters  all right so let's test this out so click on my account copy the username copy the password   hit login and here we go it says congratulations  you've solved the exercise all right so now that   we've successfully solved the exercise let's  try and script it so we'll save this one first and then we'll go to our script so  the first thing we do is we import the requests library and then  the cis library and url lib   3 we also add this line over here in order  to disable any insecure request warnings so dot exceptions dot insecure request warning  and then we set our proxy settings so that   anything any request that is sent in the script   gets sent to but first this way it allows us  to debug our script in case anything goes wrong all right so that's 127.0.0.1 and 8080.   all right next let's write the main  method so if name is equal to main and then we'll use our try and accept clause  all right so the way i want to run my script   is i want to give it one command line  argument which is the url and then   i hit enter and then what it does is i'm going to  assume that i already know the number of columns   and the data types of the columns i'll leave  that as an exercise for you to script instead   in my script i'm just going to do steps number  three four and five and so first we're going to   output the list of tables extract the table that  contains the user string and then we're going to   use that table in order to output the column names  and then extract the column names that contain   username and password and then we're going to use  the table name and the column names in order to   output the administrator's credentials so  over here i'm going to create a variable url and i'm going to take it from  the command line so arc v1 dot strip all right and if i don't give it the correct  number of arguments i don't want it to   print an error message instead i wanted to  print a message that explains the usage and   example instructions so the usage instructions  would be the name of the program and then a url okay and then example instructions and so over here www.example.com so this really  helps when you haven't run the program for a while   and it gives you instructions on how to run it instead of having to read  the code all right and then   i wanted to exit the program  because i ran it incorrectly okay so assuming i ran the program correctly  i wanted to print looking for the users table and then i'm going to create a variable called  users table and i'm going to set it to the return   value of the function sqli users table and it  takes in the url so this is a function that i'm   going to write in a bit and then if the user's  table is empty is not empty i want it to print   found the users table name and then the name of  the table based on the output of the function   so based on user's table variable  otherwise if the user's table is   empty or returns false then i wanted  to print did not find a users table   all right so let's write this function  over here so we'll do it in this section okay so it's def sqli users table url first we set the sql payload and that's equal to this one over here so the payload that  outputs the list of tables in the database next i want to make the request with my sql  payload however instead of doing it in this   function i'm going to write it in a new function  and the reason behind that is because we're going   to be making at least three requests so one for  this one one for this one and one for this one and   i don't want to repeat the code in every function  so instead i'm going to write it in its own   function called perform request and it takes in  the url and the sql payload and then i'm going to   set the path over here to be equal to the path of  the vulnerable function which we'll get from here and then i'm gonna make my  requests using the requests library and it's a get method as seen over here so it takes in the path takes in the url the path and then the sql  payload i'm going to set verify to be equal to   false because i don't want to verify certificates  and then proxies to be equal to proxy so that it   uses my proxy setting so that it sends the request  through but first just in case i need to debug it   and then i want it to return the response which  is r dot text okay so back to my function for   finding the names of the tables in the database  i'm going to call the function perform request   give it the url and the sql payload and then set  the response to raz so it's going to make this   request for me and save the response in res next  i want to extract the name of the users table from   the response so i'm going to use beautiful soup  which is a library that i haven't imported yet   so let's do that right now so from bs4  import beautiful soup and we also need to   import the projects library so soup is equal to  beautiful soup and then ras and html dot parser all right so to do that let's go back to  burp and go back a few requests all right   over here so in order to output the users table  we looked at tables that contain the string users in this case we actually have multiple  entries that contain users so we need to   refine our search a little bit more by checking  for tables that contain the string users and then   automatically right after that you get an  underscore so to do that we're going to   use the regular expression library so  users table is equal to soup dot find   text is equal to re dot compile so you're using  a regular expression and then we're saying   it starts with users and then match on the  underscore and then any number of characters   so extract this string over here all  right and then i'm going to return   the users table and let's save that  and run it and see if we get any errors so terminal new terminal and we're in lab number 10. let's screw that  and then it's python 3 sqli lab 10 dot py and   then we need the url over here which might have  timed out so let's try okay so let's copy the url paste it and hit run and we don't get any errors perfect so it looks  for the user's table and then it extracts it so   we've successfully completed step number  three next we have to do step number four   all right so let's make that smaller and go back to our main method  so once it finds the users table i want it to look for the username  column and the password column   and the way it's going to do that is using  a function that i'm going to write in a bit   so it's going to be called sqli users columns  and it takes in the url and the users table and if the username column and the password column are not empty then i wanted to  print found the username column name and print the name of the column so that would  be saved in the variables username column   and then i also wanted to  print the password column name   and again print the column name that contains  the passwords and that's saved in the variable   password column now if either one of  them is empty then i want it to print did not find the username and or the password   columns all right now we need  to write this function over here to do that we'll add a new function so sqli and  it's called users columns again it takes in the   url and the users table first thing we're going to  set is sql payload and that's going to be equal to   this one over here so i'll put the  column names of the users table but in this case we don't actually  know the name of the users table so   i'm just going to remove that and add it as a variable and take it from this variable  over here so users table and this is something that  we determine in this function   all right next i'm going to make the  request using the perform requests function   so perform request and it takes  in the url and the sql payload i also want to extract the username and password   from the response so we're going to do  exactly that i'm just going to copy it so we use the beautiful soup  library in order to parse   the response and then from there i'm  going to create a variable called username   column and from there i want to extract the  regex string which is any number of characters   and then the string username and then also any  number of characters so if we go back to burp and we see over here username was labeled as   username and then underscore and a random  strings and then same goes with password so password underscore and then a random number of   strings so that's how we're extracting it  we'll do that for the password column as well and we extract on the string password  all right and then we want to return   the username column and the password column okay so let's save that that  should have completed step number four over here but let's  see if we've got any errors so let's clear this and run it one more time no errors so far so that's good okay perfect no  error so it outputs the users table and then it   outputs the name of the column that contains  the usernames and then the name of the column   that contains the passwords all right so  we're left with one more step which is to   output the list of usernames and passwords  and to do that we go back to our main method when it finds the username  column in the password column   i want it to find the admin password so i'm going  to create a new function called sqli administrator crud and it's going to take in the url the  users table the username column and the   password column okay and then i'm going  to say if admin password is not empty then print the administrator password is and  then whatever is in that variable so the admin password variable otherwise  if it's empty i want it to print did not find the administrator password okay so now let's implement this function over  here to do that let's define a new function and it was called the sqli administrator  quad it took in the url the users table   the username column and the password column   all right so again it does exactly the same thing  that these functions do so i'm just going to copy   this and put it over here so the sql payload was  this one over here so copy that and put it in here so this was a variable that we figure out in  our first function and then this actually this   is the variable that we figure out in the  second function which is this one over here   same goes with this variable and then the user  stable is we figure out from the third function okay so let's add that over here so username column password column and users table  all right next we use the perform request function   in order to make the request with the  sql payload and save the response in rez   and then we use the beautiful soup library in  order to parse the response now this is not   going to work because now we're looking for  the user's password and in order to do that instead what we're going to  do is we're going to use text   dot find or actually text we're  going to use text is equal to administrator and then dot parent and i'll explain  that in a second don't find next and i know to do   this because we've done this in the previous  exercises and then dot contents zero all right   so let's explain this so if we go over here so the  request that outputs the administrator's password   and we do administrator so i'm trying to extract  this string over here and what i do is i'm saying   find the text administrator which is  this one over here go to the parent   element and then find the next td element which is  over here and extract the first string from there   which is this one over here okay and so it saves  in the users table which is incorrect i wanted   to save it in a variable called admin password  and then i wanted to return the admin password okay let's save that and run it one more time to see if it works hopefully it hasn't timed out yet  okay we get an error on line 57 and it says not all arguments converted during  string formatting so it tells me that this is   not string which is weird it should have  been a string unless it didn't find it   so i must have done something wrong over here  so we say soup.find and the text was equal to parent dot administrator.parent.find next td  dot content zero and then we're returning admin   password so what i'm gonna do is i'm just  going to run to admin password over here   so that i'm able to debug it okay so it does find it but it's still telling  me that it's not of type string which is weird   so let's go back oh and i see what i did  wrong over here i forgot to add the variable   and that's why we were getting the  error all right so it should have been   this over here so we no longer should get  there okay so it's outputting it over here   because i added the print function to debug it  so let's remove that and run it one more time   so that the output looks clean and if we go  to proxy we should see it making the requests okay so it's taking a bit of time so it  says looking for the users table and then   it performs this request over here and  it extracts the table so let's do users it extracts the string over here and displays  it next it performs this request over here   using the users table that we just found and  then it extracts the username and password   columns which are over here and then it uses  the username and password columns in the users   table in order to perform this request over  here and then from there it extracts the   administrator password which is this one over  here so we could use that in order to log in   to make sure that we've properly extracted  it and it's a bit slow today for some reason   let's confirm that burp is  off and it is so let's go back   all right so administrator and then the  password that the script output it and hit login and that should log us in so if we  go back to our code we could see   that was 64 lines of code just to exploit one sql  injection which makes you appreciate tools like   sql map which are much much smarter than  this code is and they do it automatically   for different types of databases and i  believe sql map is written in python as well okay so i think we've successfully logged in  here we go so it says congratulations you've   solved the lab so we successfully completed the  exercise to recap in this video we first manually   ran a sql injection union attack that allowed  us to list the database content on an oracle   database we then scripted the exploit so that it  automatically does that for us so far we've only   been focusing on how to exploit union based  sql injections so in the next lab we'll start   learning how to exploit blind based sql injection  vulnerabilities if you liked the video hit the   subscribe and share button so that it reaches  a wider audience also comment below what you   learned and what you would like to see more of in  the future thank you and see you in the next video

Channel: Rana Khalil

Views: 6,131

Keywords: security, web security, owasp, open web application security project, sqli, sql injection, portswigger, web security academy, python, offensive security, bug bounty, scripting, burp, burp suite, oswe, offensive security web expert

Id: 53mjCmPrsDg

Length: 40min 41sec (2441 seconds)

Published: Sun May 09 2021