You already know how to build a basic web application with the Ruby on Rails framework. Perhaps, you have even taken Course 1, "Ruby on Rails: An Introduction" (we highly recommend it) where you relied on external web services to be your “data layer”. But in the back of your mind, you always knew that there would come a time when you would need to roll up your sleeves and learn SQL to be able to interact with your own relational database (RDBMS). But there is an easier way to get started with SQL using the Active Record Object/Relational (ORM) framework. In this course, we will be able to use the Ruby language and the Active Record ORM framework to automate interactions with the database to quickly build the application we want. In Rails with Active Record and Action Pack, we will explore how to interact with relational databases by using Active Record, a Ruby gem, which Rails uses by default for database access. We will then take a look at what role Active Record plays in the overall request-response cycle, when a client (the browser) requests data from the server, as well as how to submit the data to the server. Of course, when accessing data, security is of paramount importance! We will talk about vulnerabilities such as SQL injection, as well as how to secure access to data by authenticating and authorizing users accessing the data. Take this course to build a Ruby on Rails application with Active Record to automate the detailed SQL interactions with our database.
SQL Fragments and Dangers of SQL Injection
Loading...
Из курса от партнера Johns Hopkins University
Rails with Active Record and Action Pack
668 оценки
Из урока
Deep Dive into Active Record
In this module, we will continue exploring Active Record and look at ways to code advanced queries without exposing ourselves to risk from SQL injection (as well as what SQL injection actually is). We will then look at expressing relationships between entities in Active Record and validating the data being saved to the database.
Познакомьтесь с преподавателями
Kalman HazinsAdjunct Professor, Graduate Computer Science
Whiting School of Engineering
[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.
Ознакомьтесь с нашим каталогом
Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.
Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.
© Coursera Inc., 2018 Все права защищены.
