SQL Injection OWASP Top 10 with Prasad Salvi

Welcome to the Test Guild Security Podcast where we all get together to learn more about security testing with your host, Joe Calantonio.

Hey, it's Joe. And welcome to another episode of the Test Guild Security Podcast. And today you're in for a special treat. We have a security expert, Prasad Salva joining us to Talk all about SQL injection, the OWASP top 10. This is going to be a new segment that we're going to do once a month to go over each of the top 10 in OWASP. And so Prasad will be joining us as a security expert. We'll look at maybe news of this recent exploits using these type of attacks and what you can do to prevent these type of attacks. So a lot of good stuff here. Like I said, it's going to be a new segment happening once a month. You definitely want to tune into it. So Prasad, if you don't know from past episodes, is a application security consultant, a certified hacker and a CISA Certified Professional with a primary focus on providing services across the secure development, lifecycle, application security and penetration testing spectrum. He's also a part time bug bounty hunter. He is a blue team expert delivering security training courses to developers and testers to make them proficient in application security and develop secure products. He has worked with major financial institutions and vouch to create a culture of security. And if you haven't already, you should definitely go over to pluralsight and type in Prasad's name. He has a bunch of courses released around security and I'm sure he has more in the works. So you definitely want to check him out there as well. So you don't want to miss this episode. Check it out.

It's no secret that the number and complexity of applications are growing, but what is alarming is that 90% of security incidents result from exploits against defects in software. The need for better application security has never been higher and Micro Focus Fortify is here to help. Fortify is the recognized market leader in application security and it is the most comprehensive and scalable application security solution that works with your current development tools and processes. With Fortify, you could start security applications in a single day, including custom code, open source or commercial components and scale as your needs grow with an on premise as a service or hybrid implementation. Check them out. Head on over to micro focus.com app security.

Hey Prasad, welcome back to the Guild.

Thank you Joe. It's good to be back.

Great to have you. So I think it's going to be awesome. Thank you for helping come up with this initiative to do the top 10 of OWASP and we're going to cover it once every month. So I'm really excited to dive into the very first one. So I think the first one has been on the OWASP list for a while. And I used to think of maybe I'm wrong. I thought it was listed as SQL Injection. Now it just lists injection. So I guess before we go into it, what is an SQL Injection?

All right, so as you rightly said, it has been on the top most spot for almost a decade now, if not less than that. So the term says injection. Under it we have OS command injection, SQL injection. But the most popular or widely exploited vulnerability till date happens to be SQL Injection. Now as the name suggests, SQL, it has to do something with the database. So in this particular attack, SQL injection, we insert certain malicious SQL query via the inputs. Let's say there's a username and password field and instead of entering my own username and password, I tried to manipulate that because when I click on the submit button there would be some query which is being fired. For example, select star from users where username is prasad and password is password, for example. But what if I change that query through the input? Now once I'm able to do that, I can have a lot of impact on the application which can result in probably inserting or updating or deleting certain values in the database, shutting down the database, or execute some administrative tasks on it. So basically manipulating the queries that are run in the database through the inputs or untrusted input as we call it in the infosec world happens to be SQL Injection.

Very cool. So I actually did a quick search for news, I went to Google news, typed in SQL Injection and bam, right on top of it. Says over the past two years there's been an increase in high volume of attacks in the gaming industry. And I guess what they say is people are trying to do credential stuffing attacks, trying to gain access to gamers online accounts by using a list of usernames and password combinations. I think I said a substantial majority of those were SQL Injection attacks. Is there any reason for that or is that just a common way? Because obviously things are stored in a database. So the best attack to try first is a SQL injection.

Of course, I mean there are two sides of the coin. Now if you say that, why we go for the gaming industry, first of all, they don't have any regulations as such. Like if you talk about the banking and finance, they have certain regulations, for example PCI wherein we have to make sure that our applications are secure, we have to follow process. Let's say we use micro Focus fortify on demand to scanner applications code. But in case of gaming, probably they don't have much of a security in mind. Their end goal or the end game is that the users or the end users play their games, have that convenient part so that they don't put much security controls in there. And once I'm able to download your entire database through SQL injection, that's like the win win situation for an attacker, wherein he gets access to your entire database, then he can post it on certain websites. Let's say Pastebin is one of the famous websites where all the hackers post their data into whichever they have found. And then I can just go to pagebin, I can say, all right, xyzgaming.com data has been breached. These are the username and password combination. So credential stuffing basically means that you take a username and password which has been breached and try it on a different application. Now there is a possibility that the same person who is using the gaming site, he might use a similar password or the same password for other websites as well, like the social media or banking. That's why we always say that you shouldn't be having the same password everywhere. You should use different passwords which are strong. And again, coming back to SQL injection and the database, it also depends how you're storing your passwords. Like you cannot store your passwords in clear text in the database. If you do, then there's a major problem because if your application is vulnerable to SQL injection, then you're exposed directly. So the hacker doesn't have to do anything. Right? He gets all the clear text data. Also depends upon how you're storing it.

So interesting. That's a good point, I guess, if it's regulated or not. Maybe if someone's developing a game they don't even think about, why would someone try to hack my game? Could be like you said, just because you're storing data in a database and maybe it's cleartext, are there any other ways people can create it? So it prevents these type of attacks.

As the development code, SQL injection, of course. So let's say, like the example that they gave you, if there's a username and password field. Now there are two types of queries. There is dynamic query and there is static query. So let's talk about the dynamic query. Let's say I'll just enter my username and password and whatever I'm entering at that moment, I'M sorry, whatever I enter at that moment, a real time query is generated at the runtime and it's being fired in the database or the database interpreter. Now, let's say if I enter a single quote, because now everyone knows that an SQL query ends with a single quote. Now, whenever a query reaches the database interpreter, the interpreter will see, all right, where is the last single quote? Where do I have to end the query? Now, if, let's say I enter prasad and single quote in the username itself, and if it is a dynamic query which is being generated now, the query becomes select star from users where username is within single quotes, prasad and single quote and password, whatever I've entered. Now, when this query will reach the backend database interpreter, the interpreter will try to find where do I need to end the query? Where's the single quote? Now, because I have already given a single quote in my username box, the database interpreter might think that, or we can trick it to believe that this is where the query ends. Now, if it is a dynamic query and the errors aren't really handled well, the database will throw back a stack trace like all the errors on the screen, which would include the database errors, the line number or might be the code or the query as well. That's why we say error handling should be good as well. But in that case, even if I get a database error, let's say it's an oracle database. At the back end I see something called as ora, which is an oracle error. So an attacker would know just with a single quote that the database that you've been using for your usernames and passwords and all sensitive data is an oracle database. So I can go ahead and try to manipulate the input so that I can get into the database. Now this happens in the case of dynamic queries, where you just take anything that the user is giving you, you put it into a query and you fire it. Now there's a static query. This is the recommendation for SQL injection and the best practice. It is also called as a parameterized query. Now, for example, let's stick to the same username and password page. Now in this case, instead of creating a query at the real time or runtime, I will already create a query which says select star from users where username equals to at the rate name and password equal to at the rate password. Now and then I assign the values that the user is entering to add the rate name and add the password. Now at the name and the address password are just the placeholders I'VE already defined a query in my code. Every time anyone enters a username password, this query needs to be fired. Now what is at the rate name and a direct password it will have the details of the user or whatever the inputs that the user has provided in those text boxes. We just take those and take those values and assign it to like the name value pair at the rate name here would be my name and the value would be let's say Prasad single quote. So there's no dynamic query generated. We already have a query and every time that static query will run, the moment you hit the submit or the login button, this static query would fire. So in the same example, if you hit the login button now, this static query would be fired and then the database will check. All right, at the rate name says Prasad single quote. Now do I have Prasad single quote in my database? It wouldn't find anything and then it will throw back an error saying that invalid username and or password. So this has been the best recommendation for SQL injection since it has been found or since a decade now. If you go for dotnet you have something called as a parameters dot add function. So whenever you define say. So whenever you define a static query, you can use the parameters of add function and add those name value pairs. Like for example add the rate name, input dot get value whatever the user has entered or use an inbox. If you go for Java then you can use something called as a prepaid statement which is an equivalent of parameters queries, a similar thing but just a different syntax, but it works exactly the same way. And like there's a good website called as Bobby Tables.com, it has all the mitigations for SQL injection for all possible programming languages. Like if you're using Golang or Ruby or Python, you can just go to bobby tables.com and you will find a good secure code snippet for SQL injection so you can use that as is in your code.

Very cool. So I think these would be core features of an application logging in and things like that. So are people just ignorant about these attacks still? Or oversight or like, well, how do they still not do this? I guess these best practices.

Yeah, that's a great question, Joe. So what happens is now people know about security, like if I'm a developer I know that my username and password field should have all the security controls, or if there's a transaction page I should have all the security controls. But they fail to understand that once the user logs in everything that they see on the screen has to be secured in the same way that you have secured your transaction page or a login page. Now, let's say I open my application, I log in, and I see five different tabs out of which three are very critical, like transactions, get card statements or anything else. But the remaining two are just, you're giving me some reports or I'm searching something like when was the last time I paid $100 of bill? So I just like to and from like generative report of the dates from, let's say September to October. And I'll try to see what are the different transactions I've made. So. Or there might be a help page or a search box for you to search something. So in these cases, unfortunately, the development community does not focus because as per them, this is something that we have given, again, the convenience part that we spoke about the gaming world. Now, this is the convenience that we have given to our users to search anything that they want and that query would be fired in our database. Now that's why they probably become a bit lenient when coding for these pages while they put all the controls and all possible security things in the remaining pages. But again, once you log in, you should secure everything that you see on the page.

Well, that's a good point. So they may have an oversight like a page that gives them full search abilities and not even think about has to do. They could still get attacked that way, I guess.

Yeah, absolutely. So even if my search box after a login is vulnerable to SQL injection, my entire application is vulnerable, so I cannot say that. All right. No, it's in the search box, but my transaction page is safe. We cannot say that because it's after authentication and if one thing is vulnerable, your entire application is problematic.

All right, yeah, good to know. So like I said, it's not just SQL injection. They list a few other ones like it's just injection, SQL being one of them. Probably the most common. I like to go over like the three other ones. The other one is NoSQL. What's a NoSQL attack and how's it different from a SQL attack?

Yeah, thanks Joe, for asking about the NoSQL injection. Now these no SQL injection allows the attackers to inject different code or commands for the database that don't use SQL queries. Like, for example, you have a MongoDB that does not use any SQL queries. So in that case, if you're able to manipulate the code or you enter particular scripts where you can fetch the details from the MongoDB it is called as NoSQL. So anything which is not using a SQL is a NoSQL injection. Similar thing, but you do it through a code or a script rather than manipulating a query with a single code.

Okay. I guess the reason why this has become more popular is companies like Facebook and Amazon use things like Mongo, I think db. And it's not relational. Is that what it is? So it's a relational database?

Yeah. So MongoDB uses the binary JSON like we call it BSON data format and comes with a secure basin query assembly tool. Now, queries can also be represented as basin objects or binary data, which we call. So the direct string injection is not possible in that case. Now however, MongoDB deliberately can allow applications which might run JavaScripts on the server, where you have directives like $where and MapReduce operations on MongoDB. Now that's when you can get into trouble.

So rather than a SQL like a valid, like select star from passwords or something, you can do actual JavaScript injection. It sounds like wow, JavaScript.

Yeah, but that's only for MongoDB and that's why it's called NoSQL injection.

Gotcha. So anyway, to stop these type of attacks or try to harden your system against these type of attacks.

All right, that's again a great question. So you can, I believe in case of NoSQL your input validations and secure coding is comes to your rescue. Like if you're not using or not performing secure coding in your applications, like the untrusted data that we spoke about at the beginning, anything that the user is entering, you're accepting your application is accepting the user input that needs to be validated and there should be controls both at the client side and the server side. Like for example, if I'm using a MongoDB and let's say it expects some JavaScript coming in, so I would know what the JavaScript would be, right? For example, if I need to load some report on the page, so it would have some proper syntax. Now I can whitelist those particular syntaxes or the script tags. Now if someone says script alert, XSS or documented cookie, this means it has come in an input field. This means that someone is trying to perform either a cross scripting attack or trying to perform no SQL injection attack because it might have a code related to the database, like where I said maybe using the dollar where tag in MongoDB. So it's always good to perform secure coding, have adequate validations both at the client and server side. And also the white list of the allowed characters.

Cool. So the other type of injection is os and I don't know if I've heard of this one. Is that actually someone doing an operating system command that's able to inject it into an interpreter and to fool it to think that it's actually the operating system that's doing it? What is I guess what is an OS injection?

So os, it's called as OS command injection. So where the goal of an attacker in this particular attack is to execute different OS commands or like operating system commands. Now command injection attacks are possible when, let's say an application passes unsafe user supplied data like in the HTTP headers or in form the forms or cookies. Let's say I. There's again the you. Let's take a different example. Now there's. There's a page where you're accepting some values. Let's say you're generating a report. I give you the dates that I want my data from 1st of October to 30th of October, for example. Now the server will get certain requests once you click on submit. Now what if I'm able to manipulate or perform a man in the middle attack and change that particular request to include some OS command? Let's say open up calculator exe on the server or open a command prompt on the server. Now if there is again no proper validation the server side the server would trust that input coming from the user and then try to execute those commands at the OS level. Maybe you will gain access to the command prompt. There's something called as a reverse shell wherein or the shell axis. When you sitting at your home or your lab, you can get access to someone else's or the victim's command prompt through OS command injection. So usually OS command injection is common when you use Excel sheets or which are uploaded to your application. So file upload is also very important to check. What sort of files are you uploading? Is there any content filtering? What are the things that are included in an Excel file? Because if I'm expecting an Excel file which has my name and let's say date of birth, why would someone have a macro written inside the Excel? So it's important to check that as well through macros we can run and few OS related commands on the server as well.

So is this a common attack? Like I said, I haven't heard of this one before, so I didn't know.

Not very common, as I said. I mean you don't find this unless the application is like way too vulnerable or Intentionally vulnerable like we have, although mutual, but that's why SQL injection tops the list. But again, OS or LDAP injection are not that widely exploited, but they are still attacks which needs to be dealt with so that they are categorized under injection attack.

Absolutely. So that brings us to like, they're not that common, but they could still be very, very critical. And you mentioned ldap, which is the last one that's listed here. I don't know if there's others, but I just did a search once again in Google news for LDAP and last week Apple paid I think over 200,000 to a white hat hacker who ran on the company network and found a bunch of vulnerabilities and a majority of them had to do with the ldap, I guess they attacked. They get access to the internal LDAP service that stores the user's account credentials. So I guess a little bit about what is the LDAP attack and how you can prevent that. How even Apple missed it, I guess too.

Yeah. So LDAP is lightweight directory access protocol, which usually which is used in the active directory, like in the major organizations where you have active directories, which stores usernames and passwords of your employees. So LDAP protocol basically helps you or your users to log in, let's say through a single sign on or within your environment or within a network. Now again, to exploit this particular attack, it's not simple to exploit it. You have to be on the same network or you have to gain access to a particular device which is on that network so that you can be able to exploit LDAP injection. Like in case of Apple, maybe the bug bounty hunter got access to one of their network devices or a device which is on the environment already. And through that he could exploit the LDAP injection, but that happens within your active directory where you store your users, employees or your usernames and passwords. So that attack takes advantage of the active directory and tries to manipulate or gain access or fetch the different users and their passwords from the active directory accounts.

Okay. Yeah, because it said in the this article that the person used brute force to get a valid username and then once it had that username, it was able to log into several other user accounts until it found one that had core administrative privileges on the network. And I guess that's how they get access then to ldap.

Exactly. So once you have admin access, I mean, not in case of ldap, but when you talk about compromising an application or hacking an application, it's always the attackers always try to find out who the highest privileged user is. Because once you have the highest privileged user, you can do anything. You can add your own users, you can delete stuff. So in this particular attack, as I said, they brute force. Now this means that Apple didn't had an adequate control to check if, you know, they didn't have adequate monitoring or logging maybe, or they didn't had an account lockout or there was something missing or they didn't have a captcha, for example. In this way the brute force was successful and the attacker was able to find the admin user's username and then through that he could get into the LDAP server.

Cool. All right. All right Prasad, before we go, is there one piece of actionable advice you can give to someone besides all the actionable advice you gave them already? How they can help stop injection attacks and what's the best way to find or contact you?

All right, so as I've mentioned earlier, I like to retreat myself that everything that you code try to perform secure coding. Don't miss out on a single input field, every input field. Your application should be treated as secure as any other pages or any secure application would have. Now if they want to reach out to me, they can follow me on Twitter, they can reach out to me on LinkedIn, and also they can reach out to me via approval site if they have access to it and I'll be happy to help.

Thank you Prasad for your security testing awesomeness. Blake 17 of value we covered in this episode. Head on over to testguild.com S3test and while you're there, make sure to click on the Try Today link under the Exclusive Sponsor section to learn all about Micro Focus Fortify, which is the recognized market leader in application security. So that's it for this episode of the Test Guild Security Podcast. I'm Joe. My mission is to help you succeed with creating end to end full stack security testing awesomeness. As always, test everything and keep the good. Cheers.

Thanks for listening to the Test Guild Security Podcast. Head on over to testguild.com for full show notes, amazing blog articles and online testing conferences. Don't forget to subscribe to the Guild to continue your testing journey.