SQL Injecting Beyond Strict Filters - Union Without Comma

Key Takeaways

what's going on YouTube this is ipsec and I want to tell a story about a web pen test I did a couple months back I think it was in July specifically it was an API pen test and I thought I found a SQL injection in the URL I put a single quote it aired out I put a single quote and then two hyphens and it didn't error so that's like telling me I have SQL injection that being said whenever I try to X Bill any data by doing a union or anything else it would air I could never get it to work I went and tried SQL map with all the tamper scripts and it still told me it was a false positive I went over to burp sweet professional it can exploit it I also have a copy of I think it's called Invicta now but it was netsparker um a long time ago and it also just failed to exploit this injection so I was thinking there's some web application firewall in player or something that was just blocking requests so I used fuff to look for bad characters by putting a character after the SQL comment because that shouldn't change my query at all and I did find some bad characters namely one being the comma and the comma was used in almost every SQL payload because that's how you separate um columns you want to extract and if the union has like four columns you want to extract you need commas at least so I thought I asked chat GPT if there's a way to do a union select statement without commas and it told me it wasn't possible so when that happens I went to reading a lot of the documentation around my sequel and I was just drawing a lot of blanks I asked a good friend Tiberius who is one of the better web pen testers I know and after talking for a bit we came up with a technique but once we came up with the technique we knew what to Google and it was on hat tricks already so we didn't discover anything new we just didn't know how to search for what we wanted which is a common thing in this industry so I could just go and paste exactly what the syntax is this is like the trick of doing this SQL injection you do this weird as a ASB joins um but that doesn't really teach you much other than the technique so I want to take more time and typical ipsec fashion and try to go through the entire thought process of that day sum it up into just a 30 minute video and hopefully you can learn from that because if you just learn techniques one of three things or well I guess four things are happening you could just remember the technique and it helps you but there's a good chance that you forget the technique or um something doesn't work out of the box and you don't know how to modify the technique to adapt I've been doing web pen testing for almost two decades now and I still learn new things or at least new to me at this time because there's plenty of times I do something like man I just learned this and then go back like six or seven years and like oh wait I learned it way back then and just forgot so we're going to take a lot more time and try to build the foundations and learn the technique so you understand exactly what this means if you don't already um to do that I did build a um python application to kind of mimic what I had saw on the web app and if you want to follow along by yourself I do have a CTF script repository I decided I just put this in here and it is in this Dockers so you can build it yourself if you just um do a Docker build there is a Docker file for this but I don't like running it in Docker it's just easier for me to run the app in vs code and debug set breakpoints all that type of stuff if I'm not in Docker I know there's probably a way to hook vs code into Docker and have it do that but I'm a simple man right so let's go take a look at this actual application I have it running here it is going to be a flask app and it is using my sequel I'm not sure if this works for SQL Lite it probably does I think there's a demo I want to do on a different video that requires my sequel so that's why I put that as a requirement then you have two functions here this is just slash it's going to get all the posts and the database and you can get single Post in the database nothing really vulnerable here um we can look at the database I guess so you can get an idea of what it is I only have one post it has a title of Please Subscribe and the content of the post is to my channel and then we have a user the username is ipsec so right here is going to be where the vulnerability occurs and if you ever see raw SQL in code nowadays it's probably going to be bad or maybe it's go language doesn't really have a good orm um this is kind of why SQL injections fell off the face of the Earth or they're not as common in web apps anymore because when they were popular a lot of people are writing raw SQL by hand and not using prepared statements but now that they've started using Frameworks like laravel and PHP um SQL Alchemy and flask and Django I think it probably uses it as well you just don't have SQL injection because most developers know this syntax better than they know SQL it's not as efficient but it's safer and more intuitive right so I'm telling the DB to query the post table and filter it where post ID is the user supplied parameter there's no SQL injection in here but here we do have SQL injection we have the select styrofoam uses where username is equal to just raw input here we should do like an escape or even better a prepared statement which we'll cover later in the video but we don't so this is where the injection occurs and you see these red squigglies you may be thinking that is syntax error but no my IDE or vs code technically isn't an IDE but for all intents and purposes I think it is um we have it saying there's an SQL injection how does it do it well that's thanks to this video sponsor sneak sneak is a platform that scans your code dependencies containers and stuff to help you find vulnerabilities in real time and also it helps fix them so you saw I just scanned open source security is going to look at like requirements.txt look for any vulnerabilities and libraries I use and then the code security is actually going to scan the code and find the vulnerabilities so here we have it finding a SQL injection and then it recommends fixes for them so this is using the mysqldb Escape string if we look at a another fix and it's using it from other Open Source Products it's not going into your product and saying change your code it's just these are how other people have fixed them this is using pi MySQL a different library and then here you probably have the correct thing which is using a prepared statement we'll fix the code up in the end of this video um it wouldn't make sense to fix it now and then um not be able to exploit it right so if you want to use sneak if you don't know how to get it hooked into your IDE I do have this video here installing vs code with copilot and sneak via ansible and also if you don't use it when you sign up please go to sneak.co ipsec it is free and this is an affiliate link so I do get some credit for bringing you to the platform so now that that is said let's go over the rest of this code um there is going to be a trick let me just stop the web server because we don't need it running um this right now if you throw it to SQL map it's gonna say it's not vulnerable uh we can prove that real quick so let's just do um let's send it over to prep Suite so we can play with it so intercept is on and then we can go over to a browser let's do localhost 5000 user ipsec uh now we have to start the application I don't know why I had stopped it so press F5 run it and we get the thing back I probably should have removed the password because that's not realistic for it to tell you the password but oh well um I'm going to turn brip Suite on it is intercepting so let's refresh go over to the repeater tab we hit it and we see ipsec if I do a single quote I'm going to get a very verbose error we probably should disable it um because this is going to say exactly what it is um let's just share this what this looks like in the web browser this is the wonderful work Zug error if we had a lfi or a file uh file disclosure not lfi to leak some files off the Box we can steal the PIN code um if you want you can just go to ipsec.rocks lfi pin and get that but this is going to tell us exactly what we have this is why you should never have errors in production because it tells us exactly what it is it's an SQL error and it gives us the query so we could easily from this know it's vulnerable for sure so I'm going to go ahead and disable debug mode because you shouldn't see that in production and sneak also will warn me I think that it is yeah that is a medium finding tells me to turn debug to false so we can do that save it F5 to rerun so now when I do a single quote we get a internal server error so now let's go over to SQL map so I'm going to do SQL map Dash U and this is another thing that I had struggled with um is how to get a injection inside the URL because SQL map generally likes to see it in parameters and this isn't a easy parameter to find right so I'm going to do user and then we can specify star here and then we can say risk three level five I'm going to speed it up by saying the dbms is my sequel you guys know that is true because we saw it in the code so it says we found a custom injection marker do you want to process it yes and then the first page it tries is a 404. if we go here we see it just try to get user slash slash if we do a invalid username so we'll just do I or if we do slash slash it goes not found so we need to put some data here but we can tell SQL map to go ahead and ignore that so it's asking do we want to quit and make sure everything's okay nope we already validated exactly what happened so we're going to do no and then it found other non custom parameters do you want to proceed yes we do so now we have it going and trying all the SQL injection you can see it down here just flying along so how I discovered this injection is we had this and I didn't know what valid data was so I put the single quote and it got an error and then I did a dash dash like this and it didn't work actually I did a space and when I press Ctrl U H hold on control U to URL encode it had a bad message request or nope when I do a space is a bad magister's request because space is invalid in URLs um URL encoded it is a plus or a percent 20. and this is going to be really weird um this is a weird brip sweet Quirk if we go to convert selection URL and encode key characters it's a plus what happens if we do the other encode convert selection URL all characters it's percent 20. I got lucky in that because um pluses just didn't work for this we get internal server error but percent 20 is just fine so if I do a union select one and this is how I went about it let's just do convert URL all characters oh man that sucks um percent 20. that's the downside I wish um key characters did that we get internal server error that's not surprising because we haven't unbalanced thing between sides of this if we look at the actual output we likely have four columns ID username password email so we can do select one two three four and we always get internal server error so if we look at the code we can see exactly what is going on going back over here um it's checking if it's a false positive doesn't seem to be injectable and it completely errored I think if we gave it a result that had data so if I put ipsec here SQL map is going to tell me a little bit more so I'm going to do that while we um explain some more things always try to have some types of Recon going on in the background it's always a good habit to have so I wanted to show you the code on why this isn't exploiting so if we look we have this wrapper here validate username I'm using something called pedantic so if I go in the code we can look at models and we have this name model and that's going to force the parameter username to be a string between 1 and 256 characters so if I change this to 12 it's going to make injection really hard because all the payloads have to be under 12 characters um so pedantic is mainly probably performance and other things but it also helps a lot of security I had made a fast API app interact with I tried to make it vulnerable but injection or nosql injection requires you sending an object and you send that object via Json so when um node.js whatever you're doing interacts with it it converts it to an object and then you send the object to the database and do injection when I did this in fast API with pedantic I had the parameter as a string because that's generally how you do SQL or no SQL statements and when I sent it to Json it just flattened it out to a string and it wasn't vulnerable I couldn't use pedantic and have it vulnerable to no SQ well injection so that was really cool um and then we go one step further and have a validator it's going to look at the username parameter and make sure it contains these characters so not only do we enforce that it's a string we require these characters and since comma is not here it's going to raise an error if we look at this let's see or to buy technique appears usable so SQL map now that I gave it a valid thing it says there's four columns we'll go into exactly um how SQL map found that in a minute but I want to talk a little bit more about pedantic so if you don't understand what I mean about strict typing um I think I have a script and here I do so I have a very simple thing we're just going to import pedantic the base model and we have name and string so if I do a Python 3 Dash I on this then we can um set our user so what I did is just put me in a it ran the script and then put me into a python console so now I am going to do this is like me selecting from the database or accepting user input and I specified age as a string so if I do user.age I get an INT if I do user.name it shows me the single quotes around it so that's a string we could do type user.age and it gives me an end right but I defined it as a string so let's just do age is equal to 30. since I'm not doing the user object I'm not in pedantic I just want to show python will default this to string because I used quotes So that is just pedantic enforcing the static type of what I want um also if later on the code changed uh python will happily change the type of variable over to an INT because it redefined it um so that is pedantic and a nutshell now that we know that I'm going to take this generic error off and put just this value error so now if we did something and set a invalid character let's do a comma uh we have to restart the app of course and I'm going to send a comma we do get a validation error now um this will just be for us so we know if something goes wrong uh we didn't just crash the app it's a nice error so what did I want to show now oh SQL map so SQL map did tell us there were four columns and it used um I want to say it used or to buy I don't see it right here but we can go over exactly how that worked so if we do go back delete this so a payload is just this well SQL map did a order percent 20 by percent 20. and then one and that's going to use the first column in this case it's probably ID we got null so it worked we could also do it if we had ipsec here it Returns the data but we only have one user so order is not going to do anything two we're ranked by the name three we're ordering by the password because that's the fourth or third column for ordering by the email when we do five it crashes because there is no fifth thing to order by so it returns an error message six again whatever so that's how SQL map could determine there were four columns but it couldn't export anything because of the number the column uh commas so now let's see what do we want to do we did pedantic we did this um I guess now is the time to talk about the Bad characters so the next thing I had done is I determined okay there is some type of WAFF here right so what I'm going to do is I'm going to copy this and then I'm going to use fuff and we can do um Dash U put in the URL let's see if I had to put this in that actually generally how I use fluff is I copy the request so I can just do V I'm going to make a new directory let's just do this in Dev shm uh the request user.request yeah that's fine Ah that's ugly I probably should have used web Suite copy to file because we have to kill all these line breaks I always forget that the copy and paste does that okay this looks good and then here at the end we can put fuzz so now I want to do fuff Dash request user dot request Dash request Proto HTTP Dash W for word list opt seclist fuzzing and then there's probably a special characters right there is we can see only these characters return a status code 200. everything else probably returns a 400 and this is telling me that comma is a bad character if we do um match request or is it match code uh 400 there we go we can see all the bad characters and a comma is on this list so this is how I did it this is how I normally find Bad characters I just go over to fuff or I may just manly go and try things so if we do um a okay comma 400 start okay double quote 400 so that could be another way but fuff I generally find is much quicker and reliable um I'm guessing it goes up in time because that is flask and like my python web server going slow because I'm not running it in like uvi corn or anything that would thread it I'm not sure exactly why the duration is going longer but oh well so now that we know the comma is a bad character this is where I ask Tiberius for some help and he showed me a site that was really cool it was SQL fiddle so if you don't know SQL and you want to learn injection or you just want to learn SQL this is a great site I'm going to just do view sample fiddle and it's going to build a database for me and then give me a SQL statement over here if I want to change languages I can change various things so if I just click on run SQL it gives me the output um we're going to get rid of this and do select star from Docs uh it's lowercase like this and we have everything here I'm going to change a few things because the ID was not one to four I like ID being unique so it wants us to build the schema before we can run it again there we go so then we can do where ID is equal to one like this and we execute and we just get the first thing so this is where the SQL injection comes in right we have user input here so this is why we put the single quote in because we are interacting at this spot we put a single quote and then a comment so the quote by the application gets tossed away and I don't know what this error is I do build schema again execute okay that worked I have no clue what happened there uh so we can do Union select one two three because it's getting three parameters run SQL it's executing and there we go it ran and we can see it returned two rows so sometimes when doing injection and it only pulls the first result your union doesn't get anything the easy fix to that is make sure the ID returns nothing so there's no document with ID 0 then when we run it our injection or Union is the only thing and of course you can put text here if you put it in quotes so like test is going to go here and then you can do um just nested SQL like this if you wanted to like get data from other tables but you should know that if you watch enough of my videos if you don't go to ipsec.rocks type SQL injection and I'm sure you will find some plenty and plenty of videos so now the trick we want to be able to do this Union select without any commas so how we can do that is put this select let's get rid of this can I make this any bigger we're going to do select star from and then um another parenthesis and select one and then do as a and then a join and then select two as B and then a join and then a select three as C and we don't need a join because we only have three things we want to inject so we run SQL executes and we have the injection we can put text here because ABC or we can do Please Subscribe like this and this behaves just as a union so instead of the commas we're doing this as something and this really just gets thrown away the as a doesn't matter this is just um required as per the syntax we're essentially assigning it to a variable that we never use but if we didn't have it and just did all these selects and joins we have an error each derived table must have its own alias so the join is like comma so we're making another statement and going here so hopefully this makes sense or you can at least build your own um payload so let's take this and go back to prep Suite so we want to do a space here Union select start from and then we can do select one as a join select two as B join and I did not close this parentheses select three as C then of course join and we have four parameters so we gotta do a select four as d and then we can end it and now we have to do URL encoding and normally I just right click I'm sure if this works um I'm going to copy this before I do it and then we can do convert selection URL all characters uh validation error name must contain letters numbers apostrophes ipsec Union select stir I am not sure where we violated the pedantic did I do like a double percent because I that's probably what happened I URL encoded the percent so it sent a percent there we go that works but to me that isn't readable oh God oh God I don't know what just happened there we go so what I'm going to do is copy my request and I'm just going to go down to a terminal and I can do Echo the request and I'm going to use said I'm going to say all spaces now become percent 20. and this is a bit more readable if we don't URL encode every single character and again I can't just do that because pluses are bad it don't work with this so go here paste and we have it and of course if you wanted to take data from like information schema or something you just put it where this select is um maybe we have to add double quotes I don't think you would have to you could probably do single quotes and everything um ABC like this I just screwed my whole thing up there we go I had got rid of the parentheses so you could use single quotes you don't need double quotes so that is the how to do SQL injection without using any uh commas hopefully that was enjoyable but now let's go and just look at this and Harden it with sneak and try to do what it says so it wanted us to use prepared statements here to do that it should be relatively simple um we're going to get rid of passing a um parameter inside the statement and then in I want to say SQL Alchemy do colon and then the variable you want to call it um I'm gonna do use it here because that's going to probably make a little bit more sense in a minute and then I'm going to do a comma after this text so the text is my SQL statement and then we do a comma and give it a dictionary and we're going to say user is the username variable so what I did here is these two parameters match up and then username is what's getting passed from the user so if we save this then press F5 again we can go to brip suite and this payload previously worked and now it errors out I'm not sure why it aired actually um I actually was not expecting the error but was it doing mapping a list expected for parameters did I screw this up do I want username I was expecting the application to give me null this mapping or list so we did text username I was pretty sure it wanted a dict C the text is closed out I'm actually going to pause recording and figure this out instead of wasting more time so I don't have a solution yet but as soon as I hit pause I realized something I could have done um so we have this it is giving us that weird mapping index error right so what I'm going to do is highlight this code and I'm going to go to copilot chat and I'm going to ask copilot how can I fix this line and then I'm going to paste this let me make sure I copy that copy I get this error and we're going to ask copilot if it will fix us okay username so it says in the code provider it looks like you're using a dictionary to pass parameters to execute however dictionary keys username oh I am an idiot I was doing a comma not a colon um I don't know why I was doing that 'll fix it there we go we get null because this is no longer injectable it's just saying hey that user does not exist there we go so that fixes that and now sneak if we go it's going to scan the code for vulnerabilities and it will no longer find any SQL injection it definitely helps if you do syntax correctly but hey you guys got to see copilot chat and how helpful it can be um it definitely saves me time for just fixing those stupid small things it discovers those typos really nicely so um definitely worth signing up for I think that's in beta still so I think you just have to sign up you get on a waiting list and then get approved like a month or two later um it's still scanning for vulnerabilities in the code it's gonna find a cross-state scripting here I believe let's see let's click this and maybe it got hung somewhere I'm going to change the output let's see how do I change the terminal it's looking at output debug I am not sure so problems nothing from sneak yet still scanning normally doesn't take this long I thought so I am going to pause we're gonna let it finish scanning for vulnerabilities maybe something on this VM is going slow but once we resume we'll find out if sneak has decided this vulnerability has been patched and then also go and try to fix anything else it says okay it is now done um I had to restart Visual Studio code something hung up in code um the thing I was looking for was the sneak output and if you go to the output thing click on this combo box there's actually a scroll that it did not notice a second ago and you can click here and see the status of everything and it was saying the code scan was complete but it wasn't showing here so this was just a visual studio code bug we saw before and there's one vulnerability this Json dot dumps and we see it may result in cross-site scripting so if we look it wants us to use an HTML dot Escape so we can say um let's see json.dumps is probably going to be a string so I can just say HTML Escape here save it and we probably have to import HTML oh I already do have it imported so now we can run this file and when we go and grab a user let's see what happens nothing looks different but if I look in the source we can see it is going to be entity encoding all of the HTML characters so this output can no longer be cross H scriptable I'm guessing if we could put Crossfade scripting in the username or email parameter then it would have been injectable but now no longer is so definitely check out sneak if you haven't hopefully this video has helped you understand SQL injection a little more I'm probably going to do another one of Boolean injection in a little bit so um if you want to see that make sure you leave a comment down below it definitely does help me take care guys and I'll see you all next time