1 00:00:02,100 --> 00:00:05,280 To protect against SQL injection attacks, 2 00:00:05,280 --> 00:00:09,140 you should not use the user input value 3 00:00:09,140 --> 00:00:11,470 that you might be using in your queries 4 00:00:11,470 --> 00:00:12,690 just like that. 5 00:00:12,690 --> 00:00:14,960 Instead, just as before, 6 00:00:14,960 --> 00:00:17,180 don't take the user input like that, 7 00:00:17,180 --> 00:00:19,060 escape it instead. 8 00:00:19,060 --> 00:00:21,370 Use the escape user input, 9 00:00:21,370 --> 00:00:25,010 which simply means if you're looking for an author name, 10 00:00:25,010 --> 00:00:28,590 and there are words like "select" or "drop" 11 00:00:28,590 --> 00:00:29,940 in that author name, 12 00:00:29,940 --> 00:00:32,270 it's probably not the name of the author, 13 00:00:32,270 --> 00:00:35,250 and you want to get rid of the overall command 14 00:00:35,250 --> 00:00:37,640 or of these malicious parts. 15 00:00:37,640 --> 00:00:39,270 And the good thing here is that 16 00:00:39,270 --> 00:00:42,050 SQL injection attacks are so common, 17 00:00:42,050 --> 00:00:44,540 that most SQL packages, 18 00:00:44,540 --> 00:00:45,890 which you might be using, 19 00:00:45,890 --> 00:00:47,470 no matter if it's for node 20 00:00:47,470 --> 00:00:49,550 or for other programming languages, 21 00:00:49,550 --> 00:00:52,060 have built in protection. 22 00:00:52,060 --> 00:00:56,160 Here I'm just working around that built in protection. 23 00:00:56,160 --> 00:00:59,410 You might recall for example, that earlier in the course, 24 00:00:59,410 --> 00:01:01,290 when I introduced you to SQL 25 00:01:01,290 --> 00:01:03,170 and the MySQL package here, 26 00:01:03,170 --> 00:01:05,730 we didn't build queries like this. 27 00:01:05,730 --> 00:01:08,960 We did not use this template string feature 28 00:01:08,960 --> 00:01:12,070 and inject values into it like this. 29 00:01:12,070 --> 00:01:14,270 Instead, we used question marks 30 00:01:14,270 --> 00:01:16,270 just as I'm doing it's down here 31 00:01:16,270 --> 00:01:18,590 for inserting values. 32 00:01:18,590 --> 00:01:21,280 And we should always use question marks 33 00:01:21,280 --> 00:01:22,770 because if you do, 34 00:01:22,770 --> 00:01:26,640 the values that will be placed for those question marks 35 00:01:26,640 --> 00:01:30,643 will be automatically escaped by the MySQL package. 36 00:01:31,660 --> 00:01:35,283 So if here, I switch from this, 37 00:01:36,830 --> 00:01:39,173 to this with the question mark, 38 00:01:40,530 --> 00:01:44,940 I can go down here and when I send the query, 39 00:01:44,940 --> 00:01:48,753 set my value for the offer like that. 40 00:01:50,330 --> 00:01:53,530 So this is then the value that will be used in place 41 00:01:53,530 --> 00:01:55,340 of this question mark, 42 00:01:55,340 --> 00:01:58,883 but it will be escaped first by the MySQL package. 43 00:02:00,030 --> 00:02:02,240 So if we now save this, 44 00:02:02,240 --> 00:02:05,780 and I quickly recreate this table which I had dropped, 45 00:02:05,780 --> 00:02:09,949 so I recreate this comments table with its ID, 46 00:02:09,949 --> 00:02:12,000 which is auto incrementing, 47 00:02:12,000 --> 00:02:15,403 and with the author field which is a varchar, 48 00:02:16,760 --> 00:02:18,720 and not null, 49 00:02:18,720 --> 00:02:22,300 and the text, which is a text which is not null, 50 00:02:22,300 --> 00:02:25,280 if I recreate this table, 51 00:02:25,280 --> 00:02:27,473 apply this in here, 52 00:02:28,490 --> 00:02:32,120 then you will see that if you go back to your discussion 53 00:02:32,120 --> 00:02:35,690 to local host 3000 slash discussion, 54 00:02:35,690 --> 00:02:37,080 I can, of course, again, 55 00:02:37,080 --> 00:02:40,120 enter values here and that works, 56 00:02:40,120 --> 00:02:42,300 and I can still find all comments 57 00:02:42,300 --> 00:02:44,143 or just a one by Manu, 58 00:02:45,020 --> 00:02:47,010 of course, Manu has no comments yet 59 00:02:47,010 --> 00:02:48,770 so I don't find anything there. 60 00:02:48,770 --> 00:02:52,320 For max it works or for no value it works, 61 00:02:52,320 --> 00:02:55,560 but if I tried that same trick from before again, 62 00:02:55,560 --> 00:02:58,740 if I take that malicious value here 63 00:02:58,740 --> 00:03:01,853 and I insert this, nothing happens. 64 00:03:03,170 --> 00:03:06,270 It just doesn't find any authors for this strange name, 65 00:03:06,270 --> 00:03:07,760 but if I then thereafter again, 66 00:03:07,760 --> 00:03:09,580 find authors without a filter, 67 00:03:09,580 --> 00:03:11,593 I do find my content again. 68 00:03:12,520 --> 00:03:15,620 And in MySQL work bench, if we refresh here, 69 00:03:15,620 --> 00:03:18,210 the table is also still there. 70 00:03:18,210 --> 00:03:21,620 Because now when using this question mark here, 71 00:03:21,620 --> 00:03:25,360 and letting the MySQL package inject the actual value 72 00:03:25,360 --> 00:03:26,410 into the query, 73 00:03:26,410 --> 00:03:28,283 we don't run into that problem. 74 00:03:29,480 --> 00:03:31,850 And actually the MySQL package also has 75 00:03:31,850 --> 00:03:34,350 another protection step, 76 00:03:34,350 --> 00:03:38,440 which I had to disable to show you this example here. 77 00:03:38,440 --> 00:03:40,700 You should always use these question marks 78 00:03:40,700 --> 00:03:43,620 for adding values into your queries, 79 00:03:43,620 --> 00:03:46,120 but in addition to what I did before, 80 00:03:46,120 --> 00:03:48,500 in this database config I gave to you, 81 00:03:48,500 --> 00:03:50,620 is here in this config. 82 00:03:50,620 --> 00:03:53,940 I actually set multiple statements to true. 83 00:03:53,940 --> 00:03:56,020 That is not the default. 84 00:03:56,020 --> 00:03:58,500 The default if you don't set this option, 85 00:03:58,500 --> 00:04:00,320 is that it's false. 86 00:04:00,320 --> 00:04:02,520 And this does what the name implies. 87 00:04:02,520 --> 00:04:03,670 If it's false, 88 00:04:03,670 --> 00:04:05,650 so if we have the default setting, 89 00:04:05,650 --> 00:04:09,640 and you would try to run multiple SQL statements at once, 90 00:04:09,640 --> 00:04:12,220 for example, because you'll try to send this 91 00:04:12,220 --> 00:04:13,580 as an author name, 92 00:04:13,580 --> 00:04:16,740 then even if you did use the approach from before, 93 00:04:16,740 --> 00:04:18,380 without the question mark, 94 00:04:18,380 --> 00:04:20,700 the MySQL package would block it 95 00:04:20,700 --> 00:04:22,640 because it's not willing to execute 96 00:04:22,640 --> 00:04:25,910 more than one SQL statement at once. 97 00:04:25,910 --> 00:04:28,790 And the one SQL statement here would be selecting 98 00:04:28,790 --> 00:04:30,060 that author by name, 99 00:04:30,060 --> 00:04:33,313 this here would already be the second statement. 100 00:04:34,770 --> 00:04:38,020 So you have multiple layers of protection built in 101 00:04:38,020 --> 00:04:40,310 and you shouldn't work around them. 102 00:04:40,310 --> 00:04:42,120 I did it here to make you aware 103 00:04:42,120 --> 00:04:43,620 of this attack pattern though, 104 00:04:43,620 --> 00:04:46,380 so that you don't accidentally run into this 105 00:04:46,380 --> 00:04:48,500 when you have some other use case 106 00:04:48,500 --> 00:04:52,700 where you do want to actually allow multiple statements 107 00:04:52,700 --> 00:04:55,220 and where you might build your query string, 108 00:04:55,220 --> 00:04:57,270 as I showed it to you before, 109 00:04:57,270 --> 00:05:00,870 where you directly inject your values into the string. 110 00:05:00,870 --> 00:05:02,060 Don't do that. 111 00:05:02,060 --> 00:05:04,600 Strongly consider sticking to the default 112 00:05:04,600 --> 00:05:07,290 of having one statement per a query 113 00:05:07,290 --> 00:05:11,200 and use the question marks for marking placeholders 114 00:05:11,200 --> 00:05:14,020 that should be replaced with concrete values, 115 00:05:14,020 --> 00:05:17,530 because then those values are escaped for you 116 00:05:17,530 --> 00:05:20,653 and they offer you protect against SQL injection.