SQL Injection - Lab #9 SQL injection attack, listing the database contents on non Oracle databases

hi everyone welcome back to another video in the web security academy series in the previous video we covered lab number eight in the sql injection module so we got some hands-on experience by exploiting a union based sql injection in order to query the database type and version on a mysql database in today's video we'll be using a union-based sql injection attack to list the database content on non-oracle databases if you do not have an account on the web security academy you can get one by visiting the url portsweger.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 go down select sql injection select examining the database go down one more time and the exercise for today is titled sql injection attack listing the database contents on non-oracle databases so let's click on 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 user names 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 we've got a couple of angles over here the first one is we need to determine the table that contains usernames and passwords the next one is to determine the relevant columns and then once we have the table name and the column names we will use that to output the content of the table and then we need to log in as the administrator user so the nice thing about this lab is that we're not assuming anything so in previous labs we were given the name of the user's table we were also given the column names that contain the usernames and passwords so in this lab we're going to have to figure that out all on our own which is more of a realistic scenario than the previous labs okay so let's create an analysis section and access the lab all right so it looks like it's the same shopping application that we've been dealing with in the past couple of labs you could refine your search based on a category once you do refine your search so we're doing it for accessories over here it'll only display the results that are related to this category and that's done based on a field in the url called category and we know this field is vulnerable to sql injection because the lab tells us however in a scenario where you don't know it's vulnerable to sql injection all you have to do is fuzz the application with sql characters to see if it breaks a query at the back end and it does so we get an internal server error which confirms that it's potentially vulnerable to sql injection all right the thing about this category field is that whatever we enter over here gets displayed on the page and what that means is that we could use a union based sql injection in order to display content from other tables in the database and that's exactly what we're going to do so i'm going to move on to using burp from now and on because it's just easier when it comes to encoding let's close that hit next start burp all right let's put that over here for a bit and set proxy proxy to use burp all right now when i load this it should be intercepted by burp and it is so what i'm going to do is i'm going to send this to repeater because i need to send this request a couple of times and i'm going to turn my intercept off okay so in order to exploit a union-based sql injection we said there's a couple of steps the first one is to find the number of columns that the vulnerable query is using so in this case the vulnerable query is the one that filters on category and the way to determine the number of columns is to use the order by clause so order by and the column number over here if we enter a column number that doesn't exist it'll throw an error so this way we could determine the number of columns that are being used by the vulnerable query i can tell based on this that there's two columns one for the title of the product and then the other for the description of the product and so there's at least two columns and let's confirm that with burp so let's copy that put it over here do control u to url in code hit send and we get a 200 okay response that means that this column exists which again makes sense because i said that there's at least two columns based on the output that is on the page so this one should also give us a 200 response code so again ctrl u to url encoded hit send and we get a 200 response code let's try three and again ctrl u to url encode it hit send and we get an internal server error so i'm just going to make note of that what that means is that the number of columns that are being used by the vulnerable query is three minus one which is equal to two all right the next thing to do is to find the data type of the columns and the reason we do that is because the username and password are going to be of type text and so in order to be able to output them from the database we need columns that accept type text and so let's see if any of these columns are of typed text and the way we do that is using union select null statements so we know there's two columns based on the first step over here and so what i'm gonna do is we iteratively try each one to see if they accept type string now if this is incompatible with type string it should throw an error if it's compatible you should see the character a on the screen now again just based on the output you could see that there's alphabets over here and there's alphabets over here so they both should accept type string and so instead of doing it iteratively i'm just going to try both at the same time just to save a little bit of time and let's try that and i copied the wrong thing okay ctrl u to url encoded hit send and we get a 200 response code and that's a good sign so we should see our characters a somewhere so you could see it over here and over here so we make a note both columns except type text okay before we try to output content from the database we need to figure out which database we're dealing with and the reason behind that is because the query that you use in order to output all the tables in the database is different depending on the database so one way to do that is to figure out the version of the database and to do that we're going to use the hint section in the exercise so the sql injection cheat sheet you could see over here database version so on oracle this is the query that you do in order to output the version this is microsoft postgresql and mysql now depending on which one actually gives us a 200 response and the version of the database will know which database we're dealing with but you could see over here that we're dealing with a non-oracle database and so we're left with three options microsoft postgresql and mysql so let's start with microsoft and again we need to fit that with our sql injection so it would be union select at version and then there's still another column we could just put null in there and comment out the rest let's try that let's do control u to url encoded hit send and we get a 500 internal server response so i'm just going to make a note saying not microsoft next let's try postgresql again we gotta fit it in our union-based sql injection and let's test that out and again ctrl u to url encoder hit send and we get a 200 okay response and so we should see the version over here post quest sql here we go so that's the version of the database so i'm going to make a note say 200 response 200 okay and it's a postgres sql database all right now that we know the database version we could use that in order to output all the tables in the database so that's our next step i'll put the list of table names in the database and the way to do that we go back to our hints sheet and we go to section database contents and it tells you based on the database how you could output the list of table names that are contained in the database and that's why we needed to do step number three because you'll see over here each one differs based on the database you're dealing with now we're dealing with postgres sql so this is the way to do it so you've got the information schema tables view and what that does is it allows you to get the information about all the tables with an ad database so we're going to copy that and again i need to fit it in my union-based sql injection so i'm going to say union select and we need two column names over here because that's how the union base sql injection attack works so i'm going to copy this and just google it to see the column names that are available to us and i'm actually going to say postgresql because it might differ click on columns all right so these are the column names so you've got table catalog table schema table name column name and so on so i care about the table name so i'm just going to copy this one and put it over here and then i'm going to say null because i don't care about outputting any other column in that table so i'm going to copy this put it in here and do control u to url encoded hit send and we get an internal server which means we did something wrong and we forgot to do the comment so hit send again and we get a 200 okay response so this should have outputted the table names that are available in the database so you could see over here there is pg partition table pg available extension and so on i'm looking for a table that contains the user's word in it and here we go so you could see there is a table called users underscore and then some random characters so i'm assuming that this is possibly my table that contains the list of usernames and passwords of the users of the application we'll confirm that in a bit using the next step and the next step is to output the column names of the table so what we're trying to do is figure out which columns contain the usernames and passwords because we're going to need that in our end query and to do that we go to the hand section and you'll see over here for postgres sql the way to do that is select star from information schema dot columns where table name is equal to table name here so let's copy that again it has to be part of our union based sql injection so we'll add that the table name is this one over here and we need two column names so to figure out the column names that are available we'll google it again and say postgresql okay so the view columns has these column names so i'm interested in this one over here so i'm going to say column name and null okay so that should give me the column names that are available in this users table so let's copy that put it in burp ctrl u to url encoded and hit send okay 200 response code which is good so what i'm looking for is a column that contains the word username that's good so we've got this one over here and then a column that potentially contains the word password all right and we found another one over here perfect so now we know the table name we also know the column name that contains the usernames and the column name that contains the passwords and so we could put this information together in order to output content from this table and so that's step number six which is output the usernames and passwords and that's just a normal query so let's have our union based sql injection payload and then we say select this column and this column over here rom this table and comment out the rest of the query so let's copy that put it in burp ctrl u to url encode it hit send and 200 response again that's a good sign let's see if it outputted the usernames and passwords and it did so you could see over here a username and a password we're looking for the administrator user and it's right over here so i'm just going to copy all that and put it over here okay so we've got the administrator's password now we can use it to log in as the administrator user which is the last step in our end goal okay click on my account select administrator copy the password click login and it says congratulations you've solved the lab all right so we've successfully completed the exercise now let's script it okay the first thing we do is we import the request library this will help us with sending post and get requests to the application next we import the sis library and the url lib3 library okay we add this line over here to disable any insecure request warnings exceptions.insecure request warning and i'm missing an l over here okay next set our proxy setting so that we can debug things using burp if things go wrong so http 8080 and we'll also do that for https and what that does is any request sent through the script goes through but first before it's sent to the application and any response from the application goes through but first before it's sent back to the script and it helps us with debugging all right next let's create our main method so if name is equal to main and then try and accept clause all right so the way i want to run my script is it would be script dot py and it takes in one parameter the url and the things that i want to do in my script are steps number four to six so i'm going to assume that i know the column numbers and the data types of the columns and the database that we're dealing with it would be a fun exercise for you to write a script from scratch that determines all that but for this one i'm gonna assume that i know that and what i'm gonna do is i'm going to script steps number four five and six so i'm going to first output the list of table names in the database extract the table that contains the user's string and then i'm going to use it in order to output the column names of that specific table extract the names that contain the string username and password and then i'm going to use those variables in order to output the administrator's credentials which is a little bit more than we usually do so this should be a fun scripting exercise so let's take in one parameter from the command line and that's the url parameter so system arc v one dot strip and then if i don't give it the correct number of arguments i wanted to print a nice message instead of an error and the message says the usage instructions so the name of the program and then url i also wanted to print example instructions just to make that really clear again name of the program and then let's say www.example.com all right and then i wanted to exit the program because we didn't run it correctly okay so assuming that we did give it the correct number of arguments and we gave it the url then i wanted to print looking for a users table because that's the first step that we're going to do and then i'm going to create a variable called userstable and assign it to the return value of the function sqli userstable url so this is a function i'm going to write in a bit and what that does is it outputs the table name of the users table okay and then as long as that variable is not empty it's going to print the users table name and then the name of the table so user's table variable if it's empty i want it to print did not find a user's table okay let's save that and now let's write the function that will figure out the table name of the users table so def sqli users table and again it takes in the url first thing i'm going to set is the sql payload and that's equal to this one over here so the payload that outputs all the table names of the tables in the database next i want to perform my request however since we're going to be sending multiple requests one for this one and then one for this one and then another one for this one instead of doing it within every function three times i'm going to create a function on its own that sends a request so i'm going to call it perform request and this way my code is much cleaner and there's less repetition in my code so it's going to be perform requests url and sql payload and what that does is we first set the path to be equal to the path to the vulnerable function which is this one over here and then i'm going to make the requests using the requests library and i'm going to say request.gat and the reason i say get is because this is a gap method over here and then it's going to take in the url the path and the sql payload let's set verify to be equal to false and proxies to be equal to proxies this way my request gets sent to burp okay and then i'm going to return r dot text all right so this function on its own what it does is it takes in a url and a sql payload and it just does a get request using the url and uh the sql payload and then it returns the response of the request so i'm going to use that function over here in order to get the response so i'm going to say res is equal to perform requests and give it the url and sql payload so it'll use the sql payload over here to perform the request that is used to output all the table names of the tables in the database okay so the next thing to do is to filter the output and extract the users table and to do that we're going to use the soup library which i haven't imported yet so let's do that over here from bs4 import beautiful soup and i'm also going to import the regular expression library okay so i'm going to create a variable called soup initialize it using the beautiful soup library raz and html parser and then i'm going to say the user's table is equal to soup.find where text is equal to re.compile so i'm going to use a regular expression to extract it and it's any number of characters and then the characters user and then again any number of characters all right so what this does is essentially if we go to proxy over here http history and we did it through repeater so it didn't get saved all right so let's go back to repeater and go back here we go so it was this one over here if we do users this is what i'm trying to extract over here so what i'm asking for is the characters users and any characters before and after so what it should do is it should extract the table name over here alright so once that's done i'm gonna say if users table return users table otherwise return false and save that all right so in the script we've completed step number four so let's run it and then try to do step number five and six so terminal new terminal python 3 sqli lab 9 dot py and this might have timed out let's run it one more time to see if it did and it did all right so i'm going to close it and generate a new instance of the lab and we don't need this anymore okay copy the url paste it and hit enter all right so it says looking for a users table and found the users table name users so and so so you'll notice over here this was randomly generated because it's different from the one that we got before okay so we finished step number one in the script now let's do step number two which is outputting the column names of the table and let's make that smaller okay so once we found the users table and we print out the user stable name i need to figure out the column names of the table and then i'm going to create two variables so username column and password column and those are going to be equal to the return values of a function that i'll create in a bit so it's going to be called sqli users columns and it takes in the url and the users table so the name of the table that is returned in this variable over here and then i'm going to say if username column and password column is not empty then i want it to print found the username column name and then the name of the column so username column i'm also going to print found the password column name and the name of the password column all right and the reason i know i found both is because i use the and operator over here so if username column and password column are not empty then print the following statements otherwise print did not find the username and or the password columns okay so let's write this function over here so it was def sqli and users columns it took in the url and the users table and again it takes in a sql payload and the sql payload that we're going to use is this one over here so the one that i'll put the column names of the table so in this case over here we don't know the name of the user's table so we're going to use this and add it based on the variable that gets passed over here so users table okay next we're going to perform the request and remember when i said i'm going to create a new function for it perform requests and the reason behind that was because i'm going to perform a request in this one i'm also going to perform a request in this one and then in the next step and so i didn't want to write it every time this way i could just call the function perform request give it the url and my new sql payload which is this one over here and then it'll output the response and save it in the variable raz next i want to extract the column names of the username and password columns so we'll use the beautiful soup library for that again and we'll say user name column is equal to soup dot find again text the re.compot is equal to re dot compile and i'm looking for any number of characters and then the string username dot any number of characters and then same thing for the password column so let's say password and i'm looking for the string password and then i'm going to return the username and password and let's save that so what this is going to do if we go back and say username it'll extract this over here which is the column that contains the user names and then it'll extract this over here the column that contains the passwords all right let's save it and run it one more time to see if it works and we get an error line number 33 okay and it's username column and password column because that's what we called it over here okay clear run it one more time okay perfect so over here i found the users table it used it in order to find the column names that contain the username and the column name that contains the password perfect so we're done step number five let's move on to step number six which is outputting the usernames and passwords based on this query over here okay so once we found the name of the users table and then we found the column names of the username column and the password column the next thing to do and i'm going to say step number six is to output the content of the users table set up with the usernames and passwords of the users of the application and to do that i'm going to create a variable called admin password say equal to sqli i'm going to create a new function called sqli administrator crud and it takes in the url it also takes in the users table so the name of the users table and it also takes in the name of the username column and the name of the password column now if this is empty so if admin password is not found i want it to print sorry if admin password is found i want it to print the administrator password is and then it prints out the administrator password admin password but if it turns out to be empty i wanted to print did not find the administrator password save it all right so now we need to create this function over here which exploits the union based sql injection in order to output the administrator's credentials so we'll add it over here and again it takes in the url the users table the username column and the password column we'll start off by using our sql payload and we'll get that from over here so this is different so i'm going to say so we'll extract that based on the variables that we pass and same goes with this one all right so the first one is the users table next one is the username oh actually the first one is the username column because that's the one over here and then the next one is the password column and then from the users table so users table all right so we need to make the request and again i'm going to use the function that i wrote over here so this one over here to make the request for me i'm going to give it the url and the sql payload and then i need to extract the administrator's credentials from the response and to do that we'll use again the beautiful soup library i'm just going to copy it from over here instead of rewriting it okay so for this one it's not as easy as finding a certain string because we don't know a substring in the administrator's password so instead we're going to use soup.body.find so find the text administrator and then once we find it so let's go over here and say administrator we could see that it's a th element and then the one below it is a td element and that contains the password so in order to extract it i'm going to say dot parent and then dot find next td and then the contents of the tt element all right so what this does is it goes to the parent element and then it goes to the next td element which should be the password and then it extracts the content from there all right and then we say return admin password and let's save that let's see if that runs let's clear this and run it again so i get an invalid syntax error and that's on line number 63. okay over here there shouldn't be a plus sign save that run it one more time hopefully no more errors and we do get one more error so line number 40. okay it's because i copied it and forgot to rename the variable so the variable over here should be called admin password and i'm making mistakes because it's late as you could see over here all right let's clear this and run it one more time perfect no more errors all right so you could see over here it looks for the users table actually let's go to the proxy and look at the different requests that it made and that's why i like passing all the requests that the script makes into burp because this way i could see them um so i think it starts off with this one here we go so this over here tries to find the table name so it does that using the information underscore schema.tables and then it finds it and it extracts it and outputs it over here next it does another request in order to find the column names of the username and password columns which is done using this payload over here and then it extracts from over here so the username table the username column and the password column and then next it uses the users table and the column names in order to extract the administrator password which is done using this request over here and it prints out the administrator password so let's try that and try to log in using that confirm that it's correct so it's administrator and the password is right over here and that copied incorrectly hit login and here we go it says congratulations you've solved the lab all right so we've 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 a postgres sql database we then scripted the exploit so that it automatically does that for us in the next lab we'll learn how to exploit a union-based sql injection vulnerability in order to list the database content on oracle databases if you like 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: 8,850

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: n2YxdgX5SJA

Length: 45min 35sec (2735 seconds)

Published: Sun May 02 2021