WEBVTT 1 00:00:00.160 --> 00:00:01.460 Hi, and welcome back. 2 00:00:01.460 --> 00:00:04.280 In this video, we're going to look at inserting 3 00:00:04.280 --> 00:00:08.180 a book into our database using our database interface here 4 00:00:08.180 --> 00:00:10.723 database.py and using SeQuaLite. 5 00:00:11.730 --> 00:00:13.750 We've looked at creating a table before. 6 00:00:13.750 --> 00:00:17.480 Now let's move on to the add_book function here. 7 00:00:17.480 --> 00:00:18.750 And the first thing to do, of course, 8 00:00:18.750 --> 00:00:21.290 is to set up the connection, 9 00:00:21.290 --> 00:00:23.660 including creating the cursor, committing, and closing. 10 00:00:23.660 --> 00:00:25.770 I'm just going to copy this, which again, 11 00:00:25.770 --> 00:00:27.575 you should not do, you should take this code, 12 00:00:27.575 --> 00:00:30.310 but I've typed this code many times already, 13 00:00:30.310 --> 00:00:32.163 so I think I'll be okay. 14 00:00:33.480 --> 00:00:37.570 What we want to execute now is going to insert some data, 15 00:00:37.570 --> 00:00:41.080 that's the syntax INSERT INTO. 16 00:00:41.080 --> 00:00:43.680 INSERT INTO, then we have to specify the table 17 00:00:43.680 --> 00:00:45.080 that we want to insert into. 18 00:00:46.010 --> 00:00:49.320 And then the values that we want to insert. 19 00:00:49.320 --> 00:00:51.600 Notice that now we put some brackets 20 00:00:51.600 --> 00:00:54.120 and in these brackets separated by commas, 21 00:00:54.120 --> 00:00:56.453 go each of the columns in here. 22 00:00:57.730 --> 00:01:00.100 So we could do one of two things. 23 00:01:00.100 --> 00:01:02.780 The first one is make this into an F-string, 24 00:01:02.780 --> 00:01:04.820 and then, insane quotation marks, 25 00:01:04.820 --> 00:01:05.913 put the name, 26 00:01:07.100 --> 00:01:08.870 the author, 27 00:01:08.870 --> 00:01:11.210 and zero because that's the red status, 28 00:01:11.210 --> 00:01:13.043 that means false to begin with. 29 00:01:14.320 --> 00:01:16.750 Notice how we've put these inside quotation marks 30 00:01:16.750 --> 00:01:20.520 because this is what's going to get sent to SeQuaLite. 31 00:01:20.520 --> 00:01:24.720 And quotation marks are around these. 32 00:01:24.720 --> 00:01:26.710 So that SeQuaLite knows they are strings. 33 00:01:26.710 --> 00:01:28.033 If we don't put them in, 34 00:01:28.990 --> 00:01:31.620 it will think this is a table name, 35 00:01:31.620 --> 00:01:33.090 and it's going to get very confused 36 00:01:33.090 --> 00:01:34.900 because we're gonna try to insert a table 37 00:01:34.900 --> 00:01:36.520 into another table, 38 00:01:36.520 --> 00:01:38.580 and it doesn't like that, of course. 39 00:01:38.580 --> 00:01:40.870 So make sure to put the strings in there 40 00:01:40.870 --> 00:01:42.870 so that it knows that these are strings. 41 00:01:43.770 --> 00:01:47.280 We can do this, let's run app.py. 42 00:01:47.280 --> 00:01:49.590 No sorry, I had it running before. 43 00:01:49.590 --> 00:01:50.963 I'm going to run it again, 44 00:01:52.760 --> 00:01:54.753 and there you see what happens. 45 00:01:55.760 --> 00:01:57.023 We get a traceback. 46 00:01:58.040 --> 00:02:00.230 Now how do we analyse tracebacks? 47 00:02:00.230 --> 00:02:02.920 Well, we already know. 48 00:02:02.920 --> 00:02:04.730 This is a SeQuaLite operational error. 49 00:02:04.730 --> 00:02:05.700 That means something happened 50 00:02:05.700 --> 00:02:07.840 when we were trying to perform an operation. 51 00:02:07.840 --> 00:02:09.420 And it gives us a nice description: 52 00:02:09.420 --> 00:02:11.443 table books already exists. 53 00:02:12.330 --> 00:02:14.850 We try to create a table books, 54 00:02:14.850 --> 00:02:16.930 but it was already existing. 55 00:02:16.930 --> 00:02:20.430 Let's close this and add in another bit of syntax. 56 00:02:20.430 --> 00:02:24.620 You can create table, if not exists. 57 00:02:24.620 --> 00:02:27.000 This is not terribly English like, 58 00:02:27.000 --> 00:02:28.850 but this will do the job. 59 00:02:28.850 --> 00:02:30.260 It will only create the table books 60 00:02:30.260 --> 00:02:32.050 if it doesn't already exist. 61 00:02:32.050 --> 00:02:35.750 And of course, another option is to delete data.db entirely, 62 00:02:35.750 --> 00:02:36.850 and that will be fine. 63 00:02:36.850 --> 00:02:38.780 So I'm going to do that as well. 64 00:02:38.780 --> 00:02:41.710 Delete data.db, run the app. 65 00:02:41.710 --> 00:02:43.050 Know it works. 66 00:02:43.050 --> 00:02:44.650 You can stop it. 67 00:02:44.650 --> 00:02:46.310 You can run the app again. 68 00:02:46.310 --> 00:02:47.470 Then, you can see that it still works 69 00:02:47.470 --> 00:02:50.410 even though we've already got the database there. 70 00:02:50.410 --> 00:02:52.020 Let's add a new book. 71 00:02:52.020 --> 00:02:55.820 We can see clean code by Robert. 72 00:02:55.820 --> 00:02:57.620 And it seems to work. 73 00:02:57.620 --> 00:03:00.420 Of course, if we try to list all the books 74 00:03:00.420 --> 00:03:02.450 it's going to go over to our adjacent file. 75 00:03:02.450 --> 00:03:04.530 So that's not too good. 76 00:03:04.530 --> 00:03:06.763 For now we can only add books. 77 00:03:07.780 --> 00:03:09.550 So as you can see, this works, 78 00:03:09.550 --> 00:03:11.430 and we can add new books. 79 00:03:11.430 --> 00:03:13.400 We cannot list books or you know, 80 00:03:13.400 --> 00:03:15.570 read books or anything like that. 81 00:03:15.570 --> 00:03:17.410 Let's stop the app. 82 00:03:17.410 --> 00:03:20.443 And I'll go back over to this execute statement. 83 00:03:21.710 --> 00:03:25.020 I said there were two ways of performing this execute. 84 00:03:25.020 --> 00:03:27.440 And that is the first one is using an F-string, 85 00:03:27.440 --> 00:03:29.853 but this is not the recommended approach. 86 00:03:31.230 --> 00:03:33.320 And here's why this is not the recommended approach. 87 00:03:33.320 --> 00:03:36.273 I'll just give you a quick piece of advice. 88 00:03:37.720 --> 00:03:42.720 We're going to put the contents of name into this string, 89 00:03:43.350 --> 00:03:45.590 and we're going to put the contents of author 90 00:03:45.590 --> 00:03:46.963 into this string here. 91 00:03:47.990 --> 00:03:51.700 But what you didn't know is that you can run multiple 92 00:03:53.050 --> 00:03:56.540 sequel statements all together when you do an execute. 93 00:03:56.540 --> 00:04:01.540 So for example, let's say the author was this. 94 00:04:02.340 --> 00:04:05.783 Let's see the content of the author was this. 95 00:04:11.010 --> 00:04:12.350 Now I understand that 96 00:04:12.350 --> 00:04:14.270 you probably don't know what this means. 97 00:04:14.270 --> 00:04:19.133 But if we put this code right in were author is, 98 00:04:20.450 --> 00:04:24.170 what happens is that now we get two statements. 99 00:04:24.170 --> 00:04:27.500 This one that inserts of value into books 100 00:04:27.500 --> 00:04:30.890 with a name, an empty author, and zero. 101 00:04:30.890 --> 00:04:33.810 A semi colon allows us to run another statement, 102 00:04:33.810 --> 00:04:37.283 and this statement, deletes the books table entirely. 103 00:04:38.870 --> 00:04:40.530 And then we get some invalid syntax at the end, 104 00:04:40.530 --> 00:04:42.090 which will probably throw an error, 105 00:04:42.090 --> 00:04:44.340 but nonetheless, these two statements would have ran. 106 00:04:44.340 --> 00:04:46.590 You would have inserted some books, 107 00:04:46.590 --> 00:04:48.480 and then you would have dropped the table. 108 00:04:48.480 --> 00:04:49.630 This is really dangerous, 109 00:04:49.630 --> 00:04:53.000 because this is called a sequel injection attack. 110 00:04:53.000 --> 00:04:56.163 And your users can be nasty enough to try to do it. 111 00:04:57.070 --> 00:05:01.500 So if you have a direct replacement... 112 00:05:01.500 --> 00:05:02.980 This called string interpolation. 113 00:05:02.980 --> 00:05:04.090 If you have a direct replacement 114 00:05:04.090 --> 00:05:06.640 of a string into your query, 115 00:05:06.640 --> 00:05:10.330 it potentially opens up this path of attack. 116 00:05:10.330 --> 00:05:11.250 So don't do this. 117 00:05:11.250 --> 00:05:16.110 Instead, what you want to do is remove everything 118 00:05:16.110 --> 00:05:17.210 and put a question mark. 119 00:05:17.210 --> 00:05:19.440 Remove everything and put a question mark. 120 00:05:19.440 --> 00:05:21.900 You can leave the zero there if you want. 121 00:05:21.900 --> 00:05:24.570 Then put a comma after the string, 122 00:05:24.570 --> 00:05:28.920 and inside a tuple type name, and author. 123 00:05:28.920 --> 00:05:31.070 Make sure that this is a tuple, 124 00:05:31.070 --> 00:05:35.990 and then SeQuaLite 3 is going to 125 00:05:35.990 --> 00:05:37.430 put this in there, 126 00:05:37.430 --> 00:05:39.050 this one in there, safely. 127 00:05:39.050 --> 00:05:41.970 So if they are a sequel injection attack, 128 00:05:41.970 --> 00:05:43.510 you will be safe. 129 00:05:43.510 --> 00:05:44.560 So this is what to do. 130 00:05:44.560 --> 00:05:46.440 Let's run the app again. 131 00:05:46.440 --> 00:05:48.100 I'm going to add another one: 132 00:05:48.100 --> 00:05:51.010 Test Driven Development 133 00:05:51.010 --> 00:05:52.630 by Ken Beck. 134 00:05:52.630 --> 00:05:55.000 And you can see that this all works as well, 135 00:05:55.000 --> 00:05:57.190 and this is a better way of doing things. 136 00:05:57.190 --> 00:05:58.450 It's more secure. 137 00:05:58.450 --> 00:06:00.740 Sequel injection attack really popular. 138 00:06:00.740 --> 00:06:03.250 Don't open yourself up to it in your applications. 139 00:06:03.250 --> 00:06:05.670 And the way to do that is do this. 140 00:06:05.670 --> 00:06:09.400 Do not use F-strings or direct string formatting 141 00:06:09.400 --> 00:06:11.723 on your queries because that's not safe. 142 00:06:12.690 --> 00:06:13.730 Okay, that's it for this video. 143 00:06:13.730 --> 00:06:16.860 We've looked at inserting values into our database. 144 00:06:16.860 --> 00:06:18.210 Let's move on to the next one. 145 00:06:18.210 --> 00:06:19.160 I'll see you there.