SQL injection isn't a common vulnerability that you'll find in applications built with frameworks like Laravel, because they usually come with some form of database layer like Eloquent. But they still do happen and it's still important that you're aware of what they are and how they work, so that you can avoid introducing them into your application. The point of SQL injection is to interact with the database by modifying a query. What we're looking for are ways in which the user interacts with the database in the hopes that there are vulnerabilities there that we can exploit in order to talk to the database and make changes and run our own queries. The first thing that jumps out at me on this page is the search for your next book entry.

database and make changes and run our own queries. The first thing that jumps out at me on this page is the search for your next book entry field. A search box or any sort of input like this that makes database queries is a fantastic spot to look for SQL injection. The way that we look for SQL injection is quite simple. All we have to do is type one of those in, a simple single quote. A single quote is useful because it's the string delimiter in an SQL query. And so by putting a single quote in and submitting it to the page, if the site is vulnerable to SQL injection, we should see something unexpected.

And when you're dealing with a database query or a database call or anything that interacts with the database, something going wrong is exactly what we want to see when we're looking for SQL injection because it usually indicates that the developer isn't escaping the values properly and the query has been broken. So we'll go back to the page and then we'll try something else in our search box and see what we can do. So rather than just break the query, what we're going to do here is do an OR 1 equals 1 hash. And what we're saying here is we want to end the current string that's being put into the query and then we're going to use MySQL's OR operator and then do a 1 equals 1.

And what we're saying here is we want to end the current string that's being put into the query and then we're going to use MySQL's OR operator and then do a 1 = 1. Now 1 = 1 evaluates to true, which means we're telling the database we want to, whatever it is we want to OR it with true, which means it's always going to be evaluated as true. And then we have the # here which comments out the rest of the SQL. So whatever comes after this in the query is going to be completely ignored and in theory we should return everything because we're basically passing in a condition of where true. So I'm going to click submit and as you can see we now have multiple records. So the page is loaded again, so we've fixed our query, but not only do we have the three

which will eventually be publicly listed, but think about what if this was say a User list or a document list or a scores list, results list. You could give access to a lot of information that you shouldn't have access to simply through the SQL injection. So I've just showed you two different submissions in this form in order to infer information about what's going on, but what if we want to load more information from the database and not just see books but see other things in the database. We could do it manually, but there's a much easier way and that's using a tool called SQLMap. So we're going to jump over into the console now and then open up SQLMap and I'll show

you really need to know at this point is that it knows a whole bunch of different attacks and different avenues for extracting information and it intelligently figures it out based on trial and error. It does create a lot of requests on the site, but that's acceptable in this situation, but if you're attacking a site it can be quite noisy, which is good from a detector's point of view, you can see the traffic coming in. But as I said, we're not going to go into detail in this talk, so I'm going to speed it up until it finishes the run, then I'll get back to you. Okay so, SQLMap has finished and it's found us three databases, Information Schema, Laracast and Performance Schema.

Okay so, SQLMap has finished and it's found us three databases, information_schema, laracast and performance_schema. So we can ignore the schemas, they're pretty default MySQL, and we have the laracast table there which is the one we want to look into. So over here I just want to point out that it's showing up here, these are the different methods it used to extract information. So it's using what's called a time-based blind, which is where it uses time to identify information. So it'll run a query, and it'll use a conditional, and it'll say something like if value is blah or wait or sleep, and then it measures the response time to infer per character by character what the value is based on the amount of sleep time that comes out, which is a really fascinating

what the tables are. Okay, so here are the tables that it's loaded for us, and as you can see here we've got a few tables, books, failed_jobs, migrations, password_resets, access_tokens, users, etc. So let's have a look at the users table. Okay, so we can see here we have the users from the users table, all loaded for us. We've got two users here, there's Steven and Alice, we can see their email address, we can see the admin flag, so we know that Steven is an admin. We've got password hashes, which is bcrypt, and we've got this interesting hash here, which is the profile key. So with access to this SQL injection attack, we can now load all this information from

information for you, and you can limit the timing, the duration, etc, so that it will be less obvious and throw less alerts if there is rate limiting running on the side or that sort of thing, to hide it more. So what do we do to fix this? What's going on in the code, and how do we fix this? Okay, so this is our route file. This is the route that is vulnerable to the SQL injection. So we have a look up here, in the whereRaw, we can see it's immediately obvious what's going on. Because we have the string being injected straight into the whereRaw with no escaping,

going on. Because we have the string being injected straight into the whereRaw with no escaping, it allows us to break out of the query. Now those who are familiar with SQL and Eloquent will know that we don't need to use a whereRaw for a like. So in this specific example, what we can do is get rid of this, throw that up there, put the like in quotes, and then put that in like that. And then what that will do is it will safely parameterise the query, and that is the purpose of using a tool like Eloquent, or one of the purposes I should say, there are a bunch of purposes, but one of the important reasons for doing it is so that you can use these

of using a tool like Eloquent, or one of the purposes I should say, there are a bunch of purposes, but one of the important reasons for doing it is so that you can use these where helpers, and they parameterise values, which means that when the database receives this query, it's going to receive something that looks like this. So it's going to go select * from, what is it, books, oops, just quote that so it's less annoying. Select * from books where, and then we're going to have slug like ?, oops, and is not null, order by blah blah, right, we don't care about the rest. So when the database receives this query from Laravel, from Eloquent, it's going to see this string, and it's going to get this ?, and Laravel is also going to provide

So when the database receives this query from Laravel, from Eloquent, it's going to see this string, and it's going to get this question mark, and Laravel is also going to provide the value of the question mark, which will be, well it won't be this exactly, it'll have that replaced. This is the parameter that gets replaced into there, and the database knows how to safely handle that, so when it receives that query with the question mark and the parameter, it will safely handle the input, which is here, the parameter value, and ensure that it cannot be used for anything malicious, that it cannot be used to break the string or do any sort of SQL injection, because this is the purpose of parameterised queries. So if we carry that, and we've got this here, we've changed our query now, if I go back

or do any sort of SQL injection, because this is the purpose of parameterised queries. So if we carry that, and we've got this here, we've changed our query now, if I go back to the page, and if I run it again, I go search, we now have no books found, because the query is no longer evaluating to true, and if I put a single quote in there, we still have no books found, we don't have an error, but if I go load, it's going to work, because we haven't broken the query. If I get rid of that entirely, we're back to our three main books. So we've now fixed the SQL injection attack, and it's no longer exploitable through that, and if I run SQLMap on it now, it won't find anything that it can exploit. But what if you need to use whereRaw?

and if I run SQL map on it now, it won't find anything that it can exploit. But what if you need to use whereRaw? What if you need to have raw values or custom queries that don't fit into the normal methods? Well, it's pretty simple, let's have a look. So as we can see here with this where, we have the three parameters, even if we're doing an exact match, we'd have the two parameters, but we've always got the parameters there. If you need to do a where, and we'll go back to our original code, like so. So in our original code, what if we wanted to use whereRaw? What if there was some complicated reason why this query needs a raw? Maybe it does not support it by Eloquent, and there are lots of reasons why you would

What if there was some complicated reason why this query needs a raw? Maybe it does not support it by Eloquent, and there are lots of reasons why you would want a raw in here. What we can do is put the question mark in here, and then do that. Oops, we want double quotes. And so we're doing the same thing here, we're passing the question mark and the parameter into the query, and so Laravel will pass that onto the database, which means this parameter here, which has our string, which needs to be properly escaped, will be safely given to the database, and then the database will replace the question mark with it safely when it's executing the query.

And any time in the database where you can do raw queries through whereRaw or any other raw method, you will always be able to add in parameters that will be passed into the database to keep them safe from injection. So that's my big tip, and the big thing to take away from this episode is that any time you're doing a database query, make sure that you're parameterizing stuff. Any user input has to be a parameter, has to go into a parameter somewhere.