[MUSIC] Hi and welcome to lecture two of module two. In this lecture, we'll talk about how to include SQL fragments in Active Record queries and we'll talk about the dangers of SQL injection. So far we've talked about doing exact searches. So for example, you could do a find by id, you could do a find by hash, where the hash is the key value. So for example, last_name: certain last name, you know exactly what that last name is and you're able to get, find_by gives you one person. And you could the same with a where clause that gives you a collection, even if that collection only has one person in it. And these are nice if you know exactly what you're looking for. So for example you could drive down to the console and do person find by last name, James. And you could find LeBron, and it's all good. But it turns out that there are more powerful ways of searching the database where you could include SQL fragments. SQL obviously being the more native image to a database, and you could specify a SQL fragment that lets you specify SQL keywords to do more powerful queries. So, for example, you could search for people between age 30 and 33, with between being an SQL Keyword, and that produces the following query. Select people from people where age between 30 and 33. Or you could do a like query, like, again, being an SQL keyword. You could search for a person. Again, find_by versus where will only give you one person. First name like %man will give you anything that ends with the word man for first_name, and you could see how this is very powerful. It is very powerful, but you have to be aware of an SQL injection. Now what is an SQL injection? Basically, a SQL injection is a way that a hacker is trying to hack into your database by providing some unauthorized raw SQL. And this could sometimes drop your tables, delete data from your tables, or even worse Gain access to confidential information inside your tables. We'll see an example of this last one in a minute. There's a link on Wikipedia that gives your more information about SQL injection, and let's do a quick demo. So for a quick demo what we are gonna do is, we are gonna add a login column and pass fields to a people table. So again that is just a migration away. Rails generate migration add_login_pass_to_people login pass. There's no type, so that assumes a string. So important part here is that it's to people. login and pass into two column, so active_record says, oh, okay. I got you. I'm to generate a migration for you that adds two columns to a people table, so that it's gonna do login, string, pass, string to the people table. We're gonna run rake db:migrate, and we'll have a new structure. To our people table. Then we're gonna go back to our seeds.rb file. We're gonna blow away everybody who we had so far, because we're modifying the structure of the table. Might as well start over clean. And we're gonna add login and pass as our two fields to our database. Now, in general, storing a password in clear text like I'm doing here is a horrible, horrible idea. And we'll get to that in the next couple of modules and we'll talk about action pack and how to not do that, how to not store password in clear text in the database. But just for an example here, let's see what happens if we store data, a login and a password, in our people table, in clear text. How a hacker can potentially gain access to it. So we're going to do rake db:seed to repopulate the data in our people table. And, sure enough, we could verify that the new data in the people table is this data that has login and pass is our two columns that have the data for those two columns for people. So, now, you would imagine that at some point a user wants to login to his account. So, he would provide us with a login and a password. And then we will try to verify that that login and password actually exists in the data bases. So a query might be something like this where you say, person, give me a person aware of the login as some login variable that's provided maybe from a form in the browser and password is equal to something else. So the password, which is also provided, let's say in a form that's been sent over to us. And this is a query and that's what we're trying to do. We're trying to pull a persons account out of a database. To verify that this is the correct person logging into his account, as well as maybe give him some data about his account, maybe for future modification or something like that. So our app wants to pull out the information for a particular user based on his or her credentials. Now, of course the hacker, if a hacker is accessing this website. Hacker wants all users and passwords in the people table. Now, how is this possible? So let's take an example, let's say our login would be john2 and the password would be, no idea, which is actually a correct user on the database. If you go back, you could see that John2 with the no_idea password actually exists in the database. Okay, so if this would actually be the query to the database, you would get one person. Or you would get an array of only one record in it, where the first name would be the John, the age and everything else so it looks like this is what we want. Now but if a hacker gets wind of what's going on and tries to guess potentially what the structure of our database table is, he'll say pass you'll type in the password field maybe in the form in the browser, got you, or something else, with a single quote at the end. And what this does, is this actually closes the string, right? So, instead of saying, login and pass, got you would close this string right over here. And then he provides his own SQL because we're just executing whatever SQL he's providing us as part of our SQL statement. So he could provide OR "x" = "x." Now this seems like an interesting query, "x" = "x." Would that always be true? Yes, it would always be true. So what ends up happening is, the first clause gets ignored because the second clause is always true. Give me some data out of people table, and not specific data. Give me all the data, because 'x' = 'x' will always be true. And when you execute the statement that the hacker provided, specifically when he provided it as part of the password, which is got you or x = x, the same exact query will give you all the records out of your people table. So you could see what SQL injection is. Basically a hacker is injecting some SQL code into a query that you are formulating to go against the database and you also see that it's a horrible idea to store passwords in clear text in the database because well this is just an example of a hacker, but potentially anybody who has access to your database has access to your password, which is stored in clear text, and he could now go ahead and access your websites as you. In summary, you can easily include SQL fragments in queries. And that actually gives you pretty powerful queries. Unfortunately if you do it the way I showed you in this lecture, it could leave you susceptible to SQL injection and we're gonna see in the next lecture, how do you go around that? How do you get around that and how do you provide a way to avoid SQL injection? While still benefiting from a power of making custom, powerful SQL queries.
