SQL Fragments and Dangers of SQL Injection

Loading...

Из курса от партнера Johns Hopkins University

Rails with Active Record and Action Pack

696 оценок

Explore Related Videos

At Coursera, you will find the best lectures in the world. Here are some of our personalized recommendations for you

Johns Hopkins University

Rails with Active Record and Action Pack

696 оценок

Курс 2 из 6 — Specialization Ruby on Rails Web Development

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.

Из урока

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.

Welcome to Module 2: Deep Dive into Active Record3:08

Seeding the Database4:14

SQL Fragments and Dangers of SQL Injection9:36

Array and Hash Condition Syntax6:13

Познакомьтесь с преподавателями

Kalman Hazins
Adjunct 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., 2019 Все права защищены.

Загрузить из App Store

Загрузить в Google Play