1
00:00:02,100 --> 00:00:05,210
Now, after covering CSRF

2
00:00:05,210 --> 00:00:07,030
and cross-site scripting attacks,

3
00:00:07,030 --> 00:00:10,540
I wanna move on to the last main attack pattern

4
00:00:10,540 --> 00:00:11,730
you should be aware of

5
00:00:11,730 --> 00:00:15,160
and that would be SQL injection attacks.

6
00:00:15,160 --> 00:00:19,100
Now, throughout this course, we mostly worked with NoSQL,

7
00:00:19,100 --> 00:00:21,500
and I'll come back to NoSQL injection

8
00:00:21,500 --> 00:00:23,160
in a couple of minutes as well,

9
00:00:23,160 --> 00:00:24,980
but using a SQL database

10
00:00:24,980 --> 00:00:27,960
would have also been a perfectly fine alternative,

11
00:00:27,960 --> 00:00:31,690
and it's indeed very common to use SQL databases.

12
00:00:31,690 --> 00:00:33,680
Hence, you should know this attack pattern

13
00:00:33,680 --> 00:00:35,453
and how to protect against it.

14
00:00:36,470 --> 00:00:39,700
Now, how does SQL injection work though?

15
00:00:39,700 --> 00:00:41,350
Again, we have the typical setup,

16
00:00:41,350 --> 00:00:44,020
your backend talking to a database,

17
00:00:44,020 --> 00:00:48,170
and our client, the browser, and with SQL injection,

18
00:00:48,170 --> 00:00:51,960
we might get some user generated content again,

19
00:00:51,960 --> 00:00:55,980
and that user generated content might again contain

20
00:00:55,980 --> 00:00:59,730
some malicious parts that could be executed

21
00:00:59,730 --> 00:01:02,910
as part of our SQL queries.

22
00:01:02,910 --> 00:01:04,420
So as you can already tell,

23
00:01:04,420 --> 00:01:07,440
user generated content is always something

24
00:01:07,440 --> 00:01:09,610
you should be careful with.

25
00:01:09,610 --> 00:01:11,310
Now, this might be a bit vague.

26
00:01:11,310 --> 00:01:14,390
So let's again dive into a concrete example.

27
00:01:14,390 --> 00:01:15,930
Now for SQL injection,

28
00:01:15,930 --> 00:01:18,410
I again have a little example for you.

29
00:01:18,410 --> 00:01:21,020
So we can stop this server

30
00:01:21,020 --> 00:01:23,500
from the cross-site scripting attack part

31
00:01:23,500 --> 00:01:26,713
and go to the db-injection project instead.

32
00:01:27,670 --> 00:01:30,390
And I'll close these terminals here.

33
00:01:30,390 --> 00:01:34,430
In there, I got again a very basic application,

34
00:01:34,430 --> 00:01:37,320
no authentication because it doesn't matter,

35
00:01:37,320 --> 00:01:40,140
and again, I, in the end, have this view

36
00:01:40,140 --> 00:01:42,810
for outputting a discussion and some comments,

37
00:01:42,810 --> 00:01:45,480
and I got some routes related to that,

38
00:01:45,480 --> 00:01:50,100
but here very important, I am now using MySQL.

39
00:01:50,100 --> 00:01:52,130
So I'm not using MongoDB here

40
00:01:52,130 --> 00:01:55,630
because I wanna focus on SQL injection.

41
00:01:55,630 --> 00:01:57,910
In the discussion.ejs file,

42
00:01:57,910 --> 00:02:00,200
I'm also not just outputting comments

43
00:02:00,200 --> 00:02:03,170
and showing my form for sending a new comment.

44
00:02:03,170 --> 00:02:06,890
Instead, I also have a search form up there

45
00:02:06,890 --> 00:02:10,580
where we can search for a specific author by name.

46
00:02:10,580 --> 00:02:12,950
And therefore when comments get submitted,

47
00:02:12,950 --> 00:02:16,983
we also do accept a name now in that extra name field.

48
00:02:18,650 --> 00:02:20,280
Now, if you wanna follow along,

49
00:02:20,280 --> 00:02:22,990
you should make sure that your SQL Server,

50
00:02:22,990 --> 00:02:26,970
your MySQL Server is running behind the scenes.

51
00:02:26,970 --> 00:02:29,530
I did show how to set it up and start it

52
00:02:29,530 --> 00:02:32,540
in the MySQL introduction section.

53
00:02:32,540 --> 00:02:34,700
And once it is up and running,

54
00:02:34,700 --> 00:02:38,800
you have to go to the database file here

55
00:02:38,800 --> 00:02:41,360
and make sure that you're using your password

56
00:02:41,360 --> 00:02:42,773
for connecting to it.

57
00:02:44,640 --> 00:02:47,850
You also should make sure that you create this database

58
00:02:47,850 --> 00:02:49,960
on your MySQL Server.

59
00:02:49,960 --> 00:02:54,120
And therefore here, I did connect with MySQL Workbench

60
00:02:54,120 --> 00:02:56,310
to my running MySQL database.

61
00:02:56,310 --> 00:02:58,870
And in there, I'll quickly create a new schema,

62
00:02:58,870 --> 00:03:00,360
which is a database,

63
00:03:00,360 --> 00:03:02,090
and I'll name it security

64
00:03:03,170 --> 00:03:06,923
and click Apply and execute this.

65
00:03:06,923 --> 00:03:09,330
And then in this schema here,

66
00:03:09,330 --> 00:03:12,083
I'll create a new table real quick,

67
00:03:12,930 --> 00:03:15,985
which I will name comments

68
00:03:15,985 --> 00:03:18,800
because that's the table I'll look for in my code.

69
00:03:18,800 --> 00:03:22,720
And in there, I'll add an ID column, which is of type int,

70
00:03:22,720 --> 00:03:25,160
and which is also auto-incrementing.

71
00:03:25,160 --> 00:03:28,993
And then I'll add an author column, which is a var char,

72
00:03:30,350 --> 00:03:32,330
and not null let's say.

73
00:03:32,330 --> 00:03:34,930
And I will add a text column,

74
00:03:34,930 --> 00:03:38,713
which is actually of type text and also not null.

75
00:03:40,210 --> 00:03:42,820
These are the column names I'll be looking for

76
00:03:42,820 --> 00:03:44,350
in my code later.

77
00:03:44,350 --> 00:03:46,600
If you have a look at the routes there,

78
00:03:46,600 --> 00:03:48,040
you see that for inserting,

79
00:03:48,040 --> 00:03:51,330
I'm inserting into an author and a text column.

80
00:03:51,330 --> 00:03:52,870
And therefore, you should make sure

81
00:03:52,870 --> 00:03:55,690
that those columns exist here.

82
00:03:55,690 --> 00:03:58,990
And then this can be applied and executed.

83
00:03:58,990 --> 00:04:01,820
And now we have the database and the table we need.

84
00:04:01,820 --> 00:04:04,840
Now back in our code, you should really just make sure

85
00:04:04,840 --> 00:04:07,163
that your password here is correct.

86
00:04:08,140 --> 00:04:10,170
Once that is correct,

87
00:04:10,170 --> 00:04:13,530
we can now see SQL injection in action,

88
00:04:13,530 --> 00:04:16,000
open a terminal again,

89
00:04:16,000 --> 00:04:19,910
make sure we navigate into that db-injection folder

90
00:04:19,910 --> 00:04:22,550
and then run npm install and npm start

91
00:04:22,550 --> 00:04:25,530
to install all dependencies and start the server.

92
00:04:25,530 --> 00:04:26,713
This should work.

93
00:04:27,570 --> 00:04:30,960
And if you now reload local host 3000/discussion,

94
00:04:30,960 --> 00:04:32,763
you see these two forms here.

95
00:04:34,070 --> 00:04:36,230
Now initially, there are no comments.

96
00:04:36,230 --> 00:04:40,770
So I'll quickly add a first comment by Max,

97
00:04:40,770 --> 00:04:45,163
then a second comment by Manu like this.

98
00:04:46,100 --> 00:04:48,820
And now here to show the search functionality,

99
00:04:48,820 --> 00:04:53,280
if I search for Max here, I only find the comments by Max.

100
00:04:53,280 --> 00:04:55,780
If I searched by Manu, I find his comments.

101
00:04:55,780 --> 00:04:58,943
If I don't enter anything, I find all the comments.

102
00:05:00,240 --> 00:05:02,210
Now that's working.

103
00:05:02,210 --> 00:05:05,690
Now let's see what SQL injection is here.

104
00:05:05,690 --> 00:05:08,950
And for this, I'll actually start in my routes again.

105
00:05:08,950 --> 00:05:13,950
Here, we do have this insert query which is executed

106
00:05:14,430 --> 00:05:16,820
where the name and comment is inserted

107
00:05:16,820 --> 00:05:19,293
just as you learned early in the course.

108
00:05:20,230 --> 00:05:22,310
There's no problem with that here,

109
00:05:22,310 --> 00:05:26,113
but there is a problem in this get discussion route.

110
00:05:27,020 --> 00:05:30,760
Here, I have this alternative code for filtering

111
00:05:31,640 --> 00:05:35,500
where I add this filter part in my query

112
00:05:35,500 --> 00:05:38,710
if the filter query parameter is set,

113
00:05:38,710 --> 00:05:42,050
if I have an author query parameter,

114
00:05:42,050 --> 00:05:45,030
and we'll have that author query parameter

115
00:05:45,030 --> 00:05:50,030
if this discussion.ejs file is submitting this form here

116
00:05:50,370 --> 00:05:51,770
at the beginning.

117
00:05:51,770 --> 00:05:55,480
There, I'm setting a get request to /discussion.

118
00:05:55,480 --> 00:05:58,410
And if a form is submitted with a get request,

119
00:05:58,410 --> 00:06:01,920
all the form inputs in that form

120
00:06:01,920 --> 00:06:06,260
will be added as query parameters to the URL.

121
00:06:06,260 --> 00:06:09,180
And therefore, this author query parameter

122
00:06:09,180 --> 00:06:12,400
will be added when this search form is submitted.

123
00:06:12,400 --> 00:06:14,880
And in this author query parameter,

124
00:06:14,880 --> 00:06:16,890
we then have name of the author,

125
00:06:16,890 --> 00:06:19,380
which I add here in my where statement.

126
00:06:19,380 --> 00:06:22,610
And then I add the where statement, if I have it,

127
00:06:22,610 --> 00:06:24,010
to the overall query.

128
00:06:24,010 --> 00:06:26,007
That's how this works.

129
00:06:26,007 --> 00:06:29,730
Now here, the problem is that I'm directly injecting

130
00:06:29,730 --> 00:06:34,690
the value like this into this where clause.

131
00:06:34,690 --> 00:06:37,730
Now, if I'm trying to attack this page,

132
00:06:37,730 --> 00:06:41,460
I just have to guess how the database table

133
00:06:41,460 --> 00:06:43,120
might be named behind the scenes

134
00:06:43,120 --> 00:06:46,300
and how the query for searching for a specific author

135
00:06:46,300 --> 00:06:48,000
might look like.

136
00:06:48,000 --> 00:06:49,950
And it's not too difficult to guess

137
00:06:49,950 --> 00:06:51,910
that it will look something like this,

138
00:06:51,910 --> 00:06:56,370
select all from comments where author equals Max.

139
00:06:56,370 --> 00:06:58,570
This is really not too hard to guess.

140
00:06:58,570 --> 00:07:01,970
And you can try it if you're trying to attack this page.

141
00:07:01,970 --> 00:07:05,580
And therefore, as an attacker, what you could now try to do

142
00:07:05,580 --> 00:07:09,210
is you could try to submit a author name

143
00:07:09,210 --> 00:07:14,090
that actually ends this first query and starts a second one

144
00:07:15,140 --> 00:07:17,310
by ensuring that your author name

145
00:07:17,310 --> 00:07:21,203
starts with a double quote and then a semicolon.

146
00:07:22,080 --> 00:07:25,030
If your SQL statement contains a semicolon,

147
00:07:25,030 --> 00:07:28,270
it ends and a new one starts,

148
00:07:28,270 --> 00:07:31,510
and I have the starting double quotes here

149
00:07:31,510 --> 00:07:35,130
because I know that the author name will probably be

150
00:07:35,130 --> 00:07:37,330
between single or double quotes.

151
00:07:37,330 --> 00:07:40,703
So I can simply try both, one of the two has to be correct.

152
00:07:41,590 --> 00:07:44,220
So now I'm closing that author name field.

153
00:07:44,220 --> 00:07:48,000
I might also enter some author name first, then close it,

154
00:07:48,000 --> 00:07:52,690
then add a semicolon and then write my other statement.

155
00:07:52,690 --> 00:07:56,510
For example, drop table comments.

156
00:07:56,510 --> 00:07:59,220
Now, to make sure that we have a valid statement,

157
00:07:59,220 --> 00:08:02,240
we just have to do something with that double quote

158
00:08:02,240 --> 00:08:03,730
which is still there.

159
00:08:03,730 --> 00:08:05,330
And hence, we could add another query

160
00:08:05,330 --> 00:08:08,410
like select everything from comments again

161
00:08:09,300 --> 00:08:13,670
where author equals Manu.

162
00:08:13,670 --> 00:08:17,410
And we omit the closing double quote here

163
00:08:17,410 --> 00:08:19,880
because that will then be that double quote

164
00:08:19,880 --> 00:08:21,130
which we still have left.

165
00:08:22,060 --> 00:08:23,840
If we now take this here

166
00:08:25,120 --> 00:08:29,120
and enter this as a author name for which you wanna search,

167
00:08:29,120 --> 00:08:34,120
then this will actually execute all these SQL statements.

168
00:08:34,320 --> 00:08:38,179
If we now click find comments, it crashes here,

169
00:08:38,179 --> 00:08:42,100
but in MySQL Workbench, if we click this refresh icon,

170
00:08:42,100 --> 00:08:44,480
we can see that our comments table is gone.

171
00:08:44,480 --> 00:08:47,840
There are no more tables in this security database.

172
00:08:47,840 --> 00:08:49,370
So it was dropped.

173
00:08:49,370 --> 00:08:53,390
This SQL statement executed successfully.

174
00:08:53,390 --> 00:08:55,943
Now, how can you protect against this attack?