SQL SELECT Tricks & Truncation: Python Web Hacking | Natas: OverTheWire (Level 27)

Key Takeaways

this video showcases an interesting trick with the sequel select statement where we exceed the maximum character lengths get a string truncated and use that to leak out another accounts password from a database keep watching to learn how to do this and more right now what's up YouTube now we're looking at natus level 27 and this is a peculiar one because we're looking again at some more like username password credentials and so we're gonna be trying out a user and password set that I guess we can login with I don't know so I'll just try John and the password like wins or something and it looks like okay user John was created interesting let's refresh the page and if we were to login with that again if we some requested that same John and login it'll say okay now that you're logged in here's the data that you're receiving you can see your username and your password so that's an interesting thing small applications looks like it used the sequel database to just check whether or not there is a user in the database if it isn't there it will create one and then if it is there it will just tell you a little bit about the account in that it will give you the password so our objective is to check out the password for natus 28 to beat this level and move on so i've got the source code pulled up I've got a sublime text ready to rock with our Python script and let's take a look at the source code it looks a little bit better in sublime text for this level because I don't have the syntax highlighting in the regular page so here I am in sublime text again so we've got the same user schema that we've seen the database that we've seen before and other previous levels username and password strings or vark are 64 in length and not null blah blah blah a bunch of functions that are created but I want to at least take a look at the top level code that runs before everything else if we are requesting something so if we actually submit the username and password it will connect the database with the password not specified here select a database to use and then it will run a couple checks it'll run valid user which looks like a function up top on the username that were that we're selecting or entering and check prudential x' another function that's to scale this displayed up top again it's using the request username and request password and it says welcome the username here's your data and dump data looks like it will display the user name and password like we saw in the application so HTML entities is pH P's notion and attempt to secure things down like remove all the HTML entities if you were to try and do like cross-site scripting or inject HTML elements or JavaScript code into an input field so HTML entities is going to be a mitigation technique that is harming us here as the attacker we're not able to run any of those things like injecting script tag HTML tags etc so it looks like they do a good job of noting HTML entities and everything else they're trying to display otherwise it'll give us a wrong password if the user doesn't exist it will create it like you saw with my John account that I showed earlier and we can actually see if I were to log out here or if I can I don't think it will let me very easily I don't have that I don't have that option that's fine I can do it in the source code actually when we try and attack it with our Python script so the functions that we want to take a look at up here or check credential looks like we have the user and password that are being passed to this function just as variables and they're using the my sequel real escape string function on them again that's another PHP mitigation function that will try and lock down or protect a lot of regular sequel injection techniques so we don't have that at our disposal they are doing that to lock us down you can probably research and Google and find a lot of bypass options or ways to evade this function and maybe that's worth your while it's not what I've found as a solution to this challenge but I think that's something that would be really interesting if you do want to look into more of the bypassing this function so it goes ahead and runs a query here determining whether or not ok does this username exist do the credentials work are we going to get a results just like that and that looks like all that function really is valid user is another function that does the same thing but it's selecting only the username to determine whether or not this user already exists right we saw that down below they're checking if valid user ok comment the user exists if not it will create it so check credentials is just determining whether or not you have the right password and that is the check credentials up here ok so that looks like it occurs in any case just it whether it gets a correct result it will return true that's the peculiar thing to note and dump data is really where it will just display out that username and password for us so that's where and the magic happens where we can potentially get the password but again they're still using the real escape string so we're preventing our regular sequel injection however what they do here is they do an interesting thing after they execute this query they determine whether or not we've got a result do we have things that we can actually look through did we get data back from the database and it will return a number of rows and if it's greater than 0 ok we have multiple results so what it does is it does this a while loop that it iterates through every single row this is peculiar right because we only expected it to return one result like if if there's only one user we're trying to login as a specific user that would probably just be one but what if we are in the case if there are multiple results if there are multiple rows to look through and that is kind of the spark of ingenuity that will lead to the toying around tinker and experimenting to find us a trick and a technique that can let us attack this this application so the create user function is simple that will just insert into the database and again if it actually successfully executed it will return true otherwise false etc so let's poke around at what would normally be this database in the schema because there are some interesting tricks and techniques we can use to get around that notion of ok we don't know the password for natus 28 the next level but because maybe we could get it to return multiple results in some cases let's try just sending that string over here if I run get requests I can instead post now where data can equal I'm gonna put this on a new line so I can bring these side-by-side trying to minimize my mouse movement because I know that's pretty annoying when I'm trying to fly in and out of different panes between the script results in the actual source code and we don't need the first thing here we can just post this get our return output and you can see I have posted the page it gives me wrong password for user and natus 28 so I wasn't able to success to authenticate in there if I try John wins just go back to it okay user John was created the database must have reset we saw a comment that we didn't get it earlier the database will reset every five minutes so if I run this again we are logging in and it gives us our password and credentials but NAT is 28 not working for us cuz we don't know the correct password so now let's tinker a little bit I want to show off an interesting tool called sequel test net where we can just experiment with sequel database and a quick and easy way without spinning up a server so the top right you can select the database we're just using my sequel I'm going to change this to table name as users and we'll just use the user name as the actual field that we want to work with because that's the peculiar one that we are determining when we are running that check credentials or valid user function here all the valid user function is testing is whether or not the user exists and that's the first test here okay do we have a valid user well yes we can have a valid user because Matt is 28 is always gonna have to exist but then we have to get checked credentials to work so check credentials is the hard part but maybe we could have multiple results or multiple rows for natus 28 so I'm just going to show this example where I use if our car of 10 as our size so we can experiment with that and I'll insert into users values subscribe cuz ok so subscribe is nine letters long right yep so that's an interesting thing let's go ahead and select all from users to just get results here I scroll down there's a button execute sequel down on the very bottom and okay so we get a result subscribe so interesting thing to note here is that I set the length of this column the length of this field to only ten characters keep in mind for the actual application ours is 64 characters long but maybe it will act strangely if we give it some data that's longer than that length that's setup so let's say I have our car 10 right so let's insert another row subscribe space please right cool now that way can execute that again execute sequel we can see we have subscribe and subscribe it's returning both of those peculiar but there's the space kind of at the very very end there that's fine it looks like it got truncated the word please just cut off so it's being returned with the space okay so let's get let's just narrow this down to see if that space will go away if we were to ask for select all from users where username is equal to subscribe go ahead down execute this Peggy's do this I am executing it but you're not seeing the results change because of a really interesting bug here not a bug but a strange trick and functionality so I just removed the the results that you can see for real but if I just Rikka meant that and turn the execution back on we are getting both of the results subscribe on its own and subscribe with a whatever amount of padding using spaces to like exceed that number the maximum length for us but the spaces are still going to be returning it on its own interesting thing right so what if we could take advantage of that what if we could say all right we want to insert into the database because we know there's already an out of 28 username and pass from the database what if we had NAT is 28 inserted with however many spaces whatever we need to break it up to 64 characters and then exceed that and then we could have literally anything following this because anything will be truncated but the spaces will remain intact and the Select statement will still execute and get us what we're looking for execute sequel natus 28 both of these are going to be returned and because we're using that my sequel fetch associative because we're looping through every single row we may still get a result that's an interesting thing let's try it what I'm gonna do is I'm gonna use natus 28 over here our first insert into and we're gonna need to make this an insert into because we have to create the account the register the user that has more than 64 characters in the name so this is seven characters long so 64 minus 7 or you know 54 57 so let's go ahead and I'm not good at math we're not good at quick math on YouTube with people are watching so let's multiply some spaces let's go 58 or something to be safe and then we'll just tack on anything so it will be truncated we'll go ahead and post this make this call and you'd see user now does 28 blah blah blah anything was created okay so user now does 28 is in there now that's been created and now we can try and log in which is the regular natus account with our anything password the password that we used previously because remember in the source code this check credentials will work on anything where username and password is going to get a result that select will still have it get the native 28 we inserted previously with the truncated spaces and it will still return true great now valid user since that's going to work through the associative all of these rows here it's going to loop through all of these query that's gonna return true and then we'll be able to see the password let's try this we got it alright welcome to natus 28 here's your data array this guy right here is our password for natus level 28 cool let's save this as in out of 28 script so we can keep in mind for that and we're gonna move on but thank you guys so much for watching I hope that was kind of cool I hope that was kind of interesting I hope I didn't drag it on for too long but I thought that was a really interesting technique honestly I've never seen that before other than just digging at this and poking at it until we got it but that's a really peculiar thing is hacking away at that maximum length character seeing what strange things happen when you experiment with it when you exceed when you get your data truncated and how come the spaces just didn't didn't get evaluated or considered in that select statement interesting bizarre thing that sequel does but really cool to know for us and the capture the flag and pentesting scene so hey I gotta give a shout out to my supporters here this list is getting longer and thank you so much for you guys I really can't say it enough that's why I do this in every videos because I cannot say thank you enough thank you so much for being willing to go on this journey with me and helping support it really helps grow me and the channel it helps motivate me to keep working and keep pumping out really cool things hopefully they are really cool for you hey one month on patreon $1 a month on patreon will give you a shout-out just like this at the end of every video $5 and more a month will give you early access quote-unquote to the videos that I'm uploading to YouTube in case I record in bulk in mass and they schedule them on YouTube and they're really slowly so thank you guys again if you did like this video please do press that like button maybe if you're willing to leave me a comment let me know what you think what you liked what you didn't like what we could do better what else you'd like to see how you solved this if you'd like to subscribe and if you really want to support me please check me out on patreon cool thanks so much guys see you in the next video