Now, after covering CSRF
and cross-site scripting attacks,
I wanna move on to the last main attack pattern
you should be aware of
and that would be SQL injection attacks.
Now, throughout this course, we mostly worked with NoSQL,
and I'll come back to NoSQL injection
in a couple of minutes as well,
but using a SQL database
would have also been a perfectly fine alternative,
and it's indeed very common to use SQL databases.
Hence, you should know this attack pattern
and how to protect against it.
Now, how does SQL injection work though?
Again, we have the typical setup,
your backend talking to a database,
and our client, the browser, and with SQL injection,
we might get some user generated content again,
and that user generated content might again contain
some malicious parts that could be executed
as part of our SQL queries.
So as you can already tell,
user generated content is always something
you should be careful with.
Now, this might be a bit vague.
So let's again dive into a concrete example.
Now for SQL injection,
I again have a little example for you.
So we can stop this server
from the cross-site scripting attack part
and go to the db-injection project instead.
And I'll close these terminals here.
In there, I got again a very basic application,
no authentication because it doesn't matter,
and again, I, in the end, have this view
for outputting a discussion and some comments,
and I got some routes related to that,
but here very important, I am now using MySQL.
So I'm not using MongoDB here
because I wanna focus on SQL injection.
In the discussion.ejs file,
I'm also not just outputting comments
and showing my form for sending a new comment.
Instead, I also have a search form up there
where we can search for a specific author by name.
And therefore when comments get submitted,
we also do accept a name now in that extra name field.
Now, if you wanna follow along,
you should make sure that your SQL Server,
your MySQL Server is running behind the scenes.
I did show how to set it up and start it
in the MySQL introduction section.
And once it is up and running,
you have to go to the database file here
and make sure that you're using your password
for connecting to it.
You also should make sure that you create this database
on your MySQL Server.
And therefore here, I did connect with MySQL Workbench
to my running MySQL database.
And in there, I'll quickly create a new schema,
which is a database,
and I'll name it security
and click Apply and execute this.
And then in this schema here,
I'll create a new table real quick,
which I will name comments
because that's the table I'll look for in my code.
And in there, I'll add an ID column, which is of type int,
and which is also auto-incrementing.
And then I'll add an author column, which is a var char,
and not null let's say.
And I will add a text column,
which is actually of type text and also not null.
These are the column names I'll be looking for
in my code later.
If you have a look at the routes there,
you see that for inserting,
I'm inserting into an author and a text column.
And therefore, you should make sure
that those columns exist here.
And then this can be applied and executed.
And now we have the database and the table we need.
Now back in our code, you should really just make sure
that your password here is correct.
Once that is correct,
we can now see SQL injection in action,
open a terminal again,
make sure we navigate into that db-injection folder
and then run npm install and npm start
to install all dependencies and start the server.
This should work.
And if you now reload local host 3000/discussion,
you see these two forms here.
Now initially, there are no comments.
So I'll quickly add a first comment by Max,
then a second comment by Manu like this.
And now here to show the search functionality,
if I search for Max here, I only find the comments by Max.
If I searched by Manu, I find his comments.
If I don't enter anything, I find all the comments.
Now that's working.
Now let's see what SQL injection is here.
And for this, I'll actually start in my routes again.
Here, we do have this insert query which is executed
where the name and comment is inserted
just as you learned early in the course.
There's no problem with that here,
but there is a problem in this get discussion route.
Here, I have this alternative code for filtering
where I add this filter part in my query
if the filter query parameter is set,
if I have an author query parameter,
and we'll have that author query parameter
if this discussion.ejs file is submitting this form here
at the beginning.
There, I'm setting a get request to /discussion.
And if a form is submitted with a get request,
all the form inputs in that form
will be added as query parameters to the URL.
And therefore, this author query parameter
will be added when this search form is submitted.
And in this author query parameter,
we then have name of the author,
which I add here in my where statement.
And then I add the where statement, if I have it,
to the overall query.
That's how this works.
Now here, the problem is that I'm directly injecting
the value like this into this where clause.
Now, if I'm trying to attack this page,
I just have to guess how the database table
might be named behind the scenes
and how the query for searching for a specific author
might look like.
And it's not too difficult to guess
that it will look something like this,
select all from comments where author equals Max.
This is really not too hard to guess.
And you can try it if you're trying to attack this page.
And therefore, as an attacker, what you could now try to do
is you could try to submit a author name
that actually ends this first query and starts a second one
by ensuring that your author name
starts with a double quote and then a semicolon.
If your SQL statement contains a semicolon,
it ends and a new one starts,
and I have the starting double quotes here
because I know that the author name will probably be
between single or double quotes.
So I can simply try both, one of the two has to be correct.
So now I'm closing that author name field.
I might also enter some author name first, then close it,
then add a semicolon and then write my other statement.
For example, drop table comments.
Now, to make sure that we have a valid statement,
we just have to do something with that double quote
which is still there.
And hence, we could add another query
like select everything from comments again
where author equals Manu.
And we omit the closing double quote here
because that will then be that double quote
which we still have left.
If we now take this here
and enter this as a author name for which you wanna search,
then this will actually execute all these SQL statements.
If we now click find comments, it crashes here,
but in MySQL Workbench, if we click this refresh icon,
we can see that our comments table is gone.
There are no more tables in this security database.
So it was dropped.
This SQL statement executed successfully.
Now, how can you protect against this attack?