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.
