Entity Framework Core 8 SQL Injection Attacks

[Music] we're going to be looking today at a feature of Entity framework core and something that's kind of come of age in Entity framework core version 8 which is just going to be coming out this month so I'm going to demo on the release candidate not the official release but I don't imagine there'll be any changes but it's a nice feature although it does possibly lead to some bad coding if you don't get it right and it's all to do with a terrible thing called an SQL injection attack that I'm sure you've heard of in around for years and we're going to see what those are all about and how this new feature can help with them but also maybe cause problems as well so what I've got here is a pretty simple web API application and if I run this up then we can see we've got a lot of end points but basically we've got a collection of books and each of the books as well as an ID you can see has a title an author first name and an author last name and those are all just being stored in an SQL light database that we can see there and then we've got some end points to access all of that and the two bits of code that I'd really like to highlight are what we've got here these two functions where we've got bio the last name interpolation and bio the last name formattable and if we look at those two functions we can see that they look very very similar there's some subtle differences one is that here we're calling a method called SQL query and here we're calling them method called SQL query raw and you can also see getting a warning on the SQL query Ro which we'll talk about when we come to that and also one really subtle difference you'll notice here where we're doing the interpolation so where author last name equals curly brace's name that one doesn't have single quotes around it and that one does and we'll see what that is all about as well but the really big important difference between these two is this one is susceptible to an SQL injection attack and this one is and this is why I find this feature a little dubious because those two so much look the same it should be really obvious if you're doing something that might cause an SQL injection attack or not it shouldn't be a subtle difference but let's begin by seeing what an SQL injection attack is and so if we go back to having this application running as well as being able to get all the books you can see I've got a number of different ways of getting hold of a book by the author last name so if I go into this first one and try that out out and if we put in Austin and execute that then you can see we get back the two books we've got in our database by Jane Austin but if we look at how that's been coded we can see what's happening in here so there we've got the one by concatenation and what that simply does is says select star from books where also the last name equals and then we have a single quote which is part of the SQL then we put in the name which is that parameter of atin that came in and then we have the closed SLE quote and if we take a look at the generated SQL pretty obvious we're getting that so that's just being logged so select star from books where author last name equals Austin perfectly fine but that is the heart of an SQL injection attack because what a hacker can do is something like this rather than typing in Austin they could type something like X single quote semicolon delete from books semicolon D Das and if we execute that we don't get any response but if we now take a look at everything that we had in there we find that the entire database or that entire table at least has been deleted and if we take a look again at the console we can see there was the command that was issued to do the delete and what's happened is because it simply added that text together so we've gone the select star from books where author last name equals and then matching on X but then that's my close quote semicolon for new statement delete from books semicolon and then Dash Dash is an SQL comment which means that very last close quotes which is the one that we actually had on there doesn't confuse it in terms of syntax so we have been able to inject whatever SQL we like in there because that is working with string concatenation and so whatever we wrote gets through there and that is the essence of an SQL injection attack and although whoever is doing the hacking has to think about this a little bit they've got to understand what the structure of the database is what the name of the table that's what hackers are good at and they will get all that information and then that certainly decades ago was the cause of lots and lots of really bad hacks most people know about them now and really avoid them but they are still sometimes out there and you will see these causing problems there's actually a secondary problem there as well let me just repopulate the database so I've got this generate just because I knew I was going to delete everything so that will now mean that we've got everything back in there again but you'll notice that in this list of books I've got some by the author Patrick O'Brien and the thing about that is he's got an apostrophe in his surname which could well be confused for close quote so another problem you get which isn't actually an SQL injection attack but it's a side effect of the same sort of thing is if I type obrien in there and execute that we actually get back an error and the reason we get an error again if we look here with all that error but the problem is that it's taken the apostrophe as the close quote so we're searching for an orth last name of O and then it's just plunked Brown on the end of that and so it can't make any sense so if you do this string concatenation you've got two problems you've got the threat of the escal injection attack but also you're limiting what you can actually do your query on now it's also the same if we use string interpolation so if we just go back to what we've got here I've got another one of these that says by oror last name interpolation and this one does Select Staff from books where author last name equals and then rather than adding on the name we use string interpolation because we've got the dollar there and so that will insert the text but once again and this is where we're getting that warning that I'll mention in a bit once again we're getting the same problem that it will just put in there whatever text we write and so we're still prone to the error so if I go to the guey and go for that one by interpolation and let's not do do the delete but let's just do that O'Brien and execute that once again we get back the error and it doesn't know what Brian is so string concatenation string interpolation both of them are prone to SQL injection attacks because they simply take all the text that was received from the user who might be a hacker and they just stick it all together and the hacker can make some of that text re interpreted as actual SQL not simply as a parameter how is it that we would normally fix those well the way we traditionally fix those there's two ways we can do it one is simply to have a parameterized query so here we've got by oror last name parameters and what that does is it does the select but then it puts rather than the actual text straight in there we just have this at name so at is the syntax to indicate that this following is a parameter and then we provide the parameter separately so here we've got this new SQ light parameter and so that name is what we put in there and then that name is the actual value that's coming in little bit of a problem there because I'm using an SQ light database I've got to have an SQ light parameter this means this code is not particularly flexible across other databases you can fix that with some kind of factory but I'm not going to worry about that here that'll just be enough to work but the point of that is it doesn't just stick the text into the SQL and so if we run that one up and go for that with parameters and if we type in again our O'Brien that gets us back the O'Brians and if we look in here we can see it's done things slightly differently it's just sent off that query with the parameter still as a parameter so it's never trying to put that text into the SQL and in fact once that reaches the database engine the database will process the SQL part of it before it even inserts that parameter so there's no risk of that happening at all so it works with just that one like a Brian but it would also work if I tried to do a quote X semicolon delete from books Das Dash if we execute that all that does is it tries to search for something where the name is all of that doesn't find it but it hasn't actually treated that as SQL so if now we take a look at all of the books we can see they're still there and so that's been stopped you should probably have some other code in there that's trying to identify the attack so that you can see where the attack's coming from that sort of thing but at least we've prevented it in that way so that's one way of doing a traditional way using parameterization the other way which is the way that I'd most normally use is to use Link to Entity framework because here here we have that same thing as a link query so from B in books where b. author last name equals name select B and that also automatically does this parameterization it also generates the SQL as well but it does the parameterization and it gets around that problem of us having a particular type of parameter the SQ light one in that case so also if we run that up and go for the Via link once again if we try that out put that in as I Brien we get back the O'Brien and if we look at what's been output we can see it's very very similar to what we had when we wrote the SQL the only difference is it's individually named The Columns rather than using a star but apart from that it really is pretty similar to what we had so parameterization works in the same sort of way and that's actually one of the features that's come in with Entity framework that allows us to use interpolation for parameterization so let's take a look at that and let's just if we go in here look at what we mean by that so I'm just going to go to let's just do this in the first one of these just somewhere to put some code and talk a little bit about string interpolation so I can say something like int x = 100 I can say string s equals hello and then I could do something like VAR interpolated equals dollar and then X is x s is s and if we just put a break in there and run up to that point and just execute that one that we put it in doesn't matter about the details really but what we can see is we get X is 100 S is hello and that's an interpolated string which we've had for quite a while in net but if we do things slightly differently we can can change that behavior because what we can do is rather than having that as a VAR which is coming back as a string we could have this as what is known as a formattable string and you can see that's working perfectly well and it looks almost exactly the same but the point about a formattable string if we run that up and just go in there now if you look at that we can see although the two string is still saying x = 100 S is hello if we look inside it we can see that it's got this format which is just the original string with curly braces and zero and one in there and it's got two arguments and if we look at the arguments we can see we've got the 100 and the hello so a formattable string has all the information that we require in order to form the final string with interpolation but it hasn't actually done the job so it's still keeping the parameters separate from the actual format of the string which is really exactly what we want to do with SQL we want the SQL parameters to be separate from the body of the SQL that's doing the work and so that's the trick that's used with these formattable strings so if we then go to the ones down here that we had this one if we look at this SQL query and look at the parameter type the parameter type there is one of these formattable strings so when we pass in that interpolated string it doesn't immediately calculate that it's going to end with orth the last name equals O'Brien or whatever it is going to just send that interpolated string and use that to produce a parameterized query to do what we want to do so if we run that one up and go down to bio the last name formattable and try that out and again put in something like O'Brien and execute that we get back the O'Brien's and we can see that once again it has sent a parameterized query so it's done really exactly the same as it did when we parameterized the query for ourself so when we had this one here but it's done it in a somewhat away because it's just used the formattable string to pass in that information separately and that all works fine so that's really quite neat but then we hit this what I think is a slight problem and could encourage programmers to make a mistaken away because as well as the SQL query which is the one that takes the formattable string we've also got the SQL query raw and that just takes a string and there we have the problem because if you pass a string in there our formattable string actually gets turned into a string and so the insertion of that name text is done and then we send that all off so if I run that one up and we go for the name by interpolation and we do a try it out and again let's just put in the O'Brien we get the error again and if we look at the output we can see that it's done the O and then put in that quote and the Brian said that's not working so that one is just as prone to an SQL injection attack as the one we had up here where we were actually concatenating them together and that's where I see there's a problem here because those two statements look so similar we are getting a warning but I'll come back to that the only other difference though apart from the query and the crew raw is in this one we need need the quotes because we're forming up the string this one we don't need the quotes because it understands that that's a parameter now a really bad way to have done this which they certainly didn't do would have been just to have the one method SQL query and overload that for both the string and the format of string then it would have been really invisible as to what's going on but even so those are quite similar and although it does give you this warning if I were to just change this slightly to say string query equals and then take all of that and put that in there and then put the query in there we now don't even get the warning it can only give us the warning if you're immediately putting the interpolated string into that parameter and then it says well hang on if you've got an interpolated string surely you wanted to use the query not the query raw but if we just separate it out a bit now we're not getting any warning at all but this function as we saw is still prone to the SQL injection attack and we're getting nothing to warn us and it is so subtly different from the perfectly safe one that we've got down there I just worry that this will encourage people to think that that is always going to be safe and therefore we're going to get SQL injection attacks returning if you stick with link then it's not going to happen and that's what I normally do but there are situations where you can't perhaps get exactly the SQL you want with link and so you need to do this sort of direct coding so just be very careful about it because these two bits of code look so similar and it seems to me that you could easily think you're writing safe code like this when you're writing very dangerous code like that so that was an interesting feature but one that might have some problems in that the good code and the Bad Code look so similar people might be encouraged to write code that's once again prone to escul injection attacks there's lots of other features in net dat and cop 12 just coming out this month so over the next few videos we'll be seeing more of them hopefully some more positive ones but if you like that one do subscribe do click like and I'll see you next [Music] time

Channel: Coding Tutorials

Views: 1,293

Keywords: SQL, SQL Injection Attack, Entity Framework, .NET 8, String interpolation, FormattableString, Captioned

Id: xapb2qo7be8

Length: 18min 10sec (1090 seconds)

Published: Fri Nov 17 2023