[MUSIC] >> So let's take a look at an example of an SQL injection attack in the Video Service class. Let's assume that we've written a method that is vulnerable to an SQL injection attack. And see what that looks like. And one thing to note is that although we're talking about SQL injection attacks, other query languages that are operating on databases are also vulnerable to this type of attack. It's important to remember it's not just isolated to SQL, but it's anything where you're going to execute logic on data that's in a database. So let's assume that we've created a method to allow users to go and search their own private videos. So we've got a method called myVideos that takes in a string for a name of a video that they're searching for. And so the goal is, we're going to return all the videos to a user that have a particular name. Now, let's also assume that, the way that our service is set up, users want to be able to have some private videos as well as public videos. And this is going to go and search all of the videos that only belong to a user, so it may include both private and public videos in the results that we're returning back to that user. So we've got our @controller, and request mapping annotations on this, we don't see them here because we want to focus on just this code. And the first thing we do is using some mechanism like Spring Security which we'll talk about later, we go and get access to the current user that sent the request. So somebody has logged in and we can get information about their user account. So the next thing we do, is we decide to go and construct a query to search the database for all of the videos that that particular user has uploaded that meet the search criteria. So we say, select star from video where owner equals, and we have a quote. And we are doing this as a string. And we are going to append with plus, the user name, because we want to go and search for a particular user. And then we say plus and we have another closing quote and our apostrophe. And name equals, and then we're going to put in the name and then we're going to close that. Now, if you look at this code and you think that this is secure code because we've got the quotation marks here, that are going to be indicating that this thing is a string. So therefore anything that gets passed in here will be interpreted as a string and there's no problem. You'd been unfortunately very wrong. This is a very insecure method. Any time you see code like this, you should immediately try to get it fixed. You don't ever want to write your own code like this that directly takes data from the client and then puts it into something that's going to be logic, that's going to get executed. You always have to be very very careful that the data that you are taking from the client and mixing it with your logic absolutely cannot be turned into logic itself. So in this case, is there a way that we can take the name data that the user or client is providing and cause it to change this logic? The answer is yes, absolutely it's possible. So let's take a look at how the client could provide data that would change the logic that's being expressed in this query that's being built up. So, assume that the user provides a value that's not malicious. And the user provides a value like, the owner is Coursera. What we're going to end up with is a query string that looks like select star from videos, or video, where owner equals Coursera, because let's assume it's Coursera. And name. And let's assume they're looking for SQL videos. Now, in this case if they provided, the user name happened to be up here was Coursera, and they provided the value SQL. Everything should be fine. It's going to only search through Coursera's videos and it's going to search through name equals SQL. Now, you can see in here how we're substituting, we're substituting name in here, and we're substituting the user name in right here. But, assume that the client provides the value, foo, or, a equals a. Now this looks like a strange string, so what happens if the client provides that? Well, if you go and substitute this in here, and let's assume, let's say the owner is foo, we get select star from video where owner equals foo. Now, things start to get more interesting and we say and. Now, we need to go, and substitute in the rest of the strings. We see and name equals, it has an apostrophe here. Now we're going to add on this value right here. And what we get is foo, or, a equals a. And then we've got the closing quotation mark, or apostrophe. Now, suddenly, the value that the attacker has provided has changed this query and added additional logic. This is new logic on this query. And this is suddenly a serious problem. Because, the designer of this query that's being built up on the string is assuming that the only logic that's being executed is the select from video where owner equal and name equals. They were not assuming that any additional logic could be tacked onto the end of this query. But because this is just simple string manipulation, there's nothing to prevent the attacker from taking additional data and using it to append logic to this SQL statement. So suddenly we've got a big problem. And the reason is, is because the a equals a part is always true. And so what this means is, is when this database goes and interprets this it's going to say I want to find any video where, owner equals foo and name equals foo. So this would actually be safe and would only search the videos of that particular user that was sending in the data. Or new logic here, a equals a. So that means if a video matches this or a video matches this second part, it should return it. And in this case, a equals a will always be true. So the attacker will get access to every video, regardless if it's public or private and who owns it, and get it returned. That entire list of videos will be returned. And you can imagine that if you can insert new logic here, you can insert arbitrary logic here that could do all kinds of bad things. Like changing the data in the database, or deleting data in the database, or getting access to sensitive data, or adding users, or all kinds of things. So if you'd, aren't careful and you begin mixing logic with data in an unsafe way, particularly with SQL statements, it's possible to end up in a situation where the attacker can inject new logic or do a SQL injection attack in this case. And suddenly the code that you are running in your server isn't just your own anymore. It's your code plus the attacker's code. The attacker's logic and commands are being executed right beside yours, and beside your sensitive data. So you need to be very, very careful that when you are building queries, or you're building any type of mechanism that's going to mix some type of client data in with executable logic. That you don't end up in a situation where the client controls the logic that you're executing to some degree. So, luckily, if you're building on top of Spring data or Spring data rest, and you're just using the findBy methods or the findAll or, or save or those other methods. It's automatically constructing the queries for you, and the SQL statements for you, and it's doing it in a safe way that will guarantee that you are immune to these types of SQL injection attacks. However, if you drop down to the lower level and you begin manually creating your queries as strings, you must be aware that this type of thing is possible. And if you ever go in, and you're reviewing older code that has these type of string based SQL statements in it, you should be very, very careful and make sure that they are built in a way that protects the application from having additional logic injected into it.
