WEBVTT 00:00.680 --> 00:01.310 Okay guys. 00:01.310 --> 00:09.500 So this is where we have stopped in the last video by making sure the messages table is created in our 00:09.500 --> 00:11.120 SQLite database. 00:11.150 --> 00:12.500 Let's close it right now. 00:12.500 --> 00:16.040 And let's go back to the points that we need to cover. 00:16.910 --> 00:23.810 So now it's time that we start really working with a database in the sense that we're going to be inserting 00:23.810 --> 00:31.940 some data, reading it, and also seeing how we can sort the results to see the most recent comments 00:31.940 --> 00:32.870 first. 00:33.110 --> 00:39.140 So starting with this video we're going to be actually inserting the data into the database. 00:39.230 --> 00:43.880 This means we are going back to our contact post router. 00:43.880 --> 00:48.020 So we stopped at this point by just seeing the data is fine. 00:48.020 --> 00:52.640 Now it's time that we insert this data into the database. 00:53.960 --> 01:01.870 Now at this point we will jump back to the db php file to add a new function that will insert the new 01:01.870 --> 01:03.370 message into the database. 01:03.370 --> 01:10.630 And by the way, we are going to learn about some safety measures that we will undertake to make sure 01:10.630 --> 01:12.160 we are secure. 01:14.620 --> 01:18.460 So specifically, this is the prepared statements. 01:18.460 --> 01:27.280 I'm gonna talk about this in detail once we start writing this insert SQL query and also discovers how 01:27.280 --> 01:32.680 to use PDO, which is this database abstraction of PHP that I've mentioned before. 01:32.710 --> 01:41.050 Now I've also have this page open which basically describes in, let's say, a super technical way, 01:41.170 --> 01:45.970 how you should construct the query that inserts data into a database. 01:45.970 --> 01:51.610 So let me write a function called insert message. 01:52.270 --> 02:04.860 And here we're gonna be asking for name For email and for the message. 02:06.060 --> 02:12.330 And this time we're going to be returning a value, a boolean that will just say if we were successful 02:12.360 --> 02:13.020 or not. 02:13.020 --> 02:17.520 And maybe let me make the first argument, the PDO connection. 02:20.340 --> 02:26.160 So let's begin by writing the SQL statement into the SQL variable. 02:27.690 --> 02:33.510 So we're going to begin at this point because we just want to insert data every single time. 02:34.530 --> 02:36.540 So we just type insert. 02:37.290 --> 02:42.240 Then we go straight to into to specify where we would like to insert it. 02:42.270 --> 02:49.350 After that the arrow leads us to schema which we don't have a name for, and we just go straight to 02:49.380 --> 02:49.980 table. 02:49.980 --> 02:52.170 That's also a possible connection. 02:52.200 --> 02:55.200 Our table name is messages. 02:59.690 --> 03:06.920 And after that we open the parentheses as you see here, right after the table name, we jump to parentheses. 03:06.920 --> 03:12.110 And here we list the column names we would like to specify values for. 03:12.230 --> 03:18.380 In our case this is name email and message. 03:18.860 --> 03:20.690 Why only those columns. 03:20.690 --> 03:22.550 Let's jump back to the file. 03:23.180 --> 03:25.280 This is the structure of the table. 03:25.280 --> 03:30.440 We've got ID name email message and createdat id is auto incremented. 03:30.440 --> 03:31.880 I've mentioned that before. 03:31.880 --> 03:37.490 It's just the value for it is being assigned by the database. 03:37.490 --> 03:39.830 That's the next number. 03:40.130 --> 03:47.240 And Createdat is also automatically populated by the database because we have added a default value 03:47.240 --> 03:49.490 for it being the current timestamp. 03:49.520 --> 03:55.940 That's why we only need to insert something into three of those columns. 03:56.880 --> 03:57.810 What next? 03:57.840 --> 04:01.680 We close the parentheses and we add this values keyword. 04:01.710 --> 04:02.610 Let's do it. 04:04.770 --> 04:06.240 Then goes parentheses. 04:06.240 --> 04:13.620 And we either put expressions or we might do parameter binding which is what we are going to do. 04:13.620 --> 04:15.450 And I'm going to explain it. 04:17.550 --> 04:22.080 So at this point we would like to use a prepared statement. 04:22.080 --> 04:31.230 That's a generic SQL databases technique that separates the arguments and data that you insert update 04:31.230 --> 04:34.770 inside the database from the actual query. 04:34.770 --> 04:42.780 This means we won't be passing the values here like the name and email directly. 04:42.780 --> 04:49.590 At least we won't be passing them directly because we can't trust the user input. 04:49.620 --> 04:58.250 That's why we use the placeholders like name like email and message. 04:58.250 --> 05:00.380 I've came up with those names. 05:00.380 --> 05:01.640 You can use any names. 05:01.640 --> 05:06.320 I just want them to be sensible and match the column names. 05:06.650 --> 05:11.630 So use a column and you provide a name for a parameter. 05:11.630 --> 05:17.780 So bear with me because the next step is to prepare a statement. 05:17.810 --> 05:22.040 You run PDO prepare SQL. 05:22.070 --> 05:25.250 Now at this point we are ready to run the query. 05:26.150 --> 05:27.710 So we run the statement. 05:27.710 --> 05:33.350 By the way, the statement is just an object that is being returned by the PDO. 05:33.350 --> 05:34.490 Prepare. 05:35.270 --> 05:40.340 That's an object with some specific methods, one of which is execute. 05:40.670 --> 05:48.260 And now I can pass an array with the values of the parameters that I've given names here. 05:48.260 --> 05:55.660 And this would just prevent a cyber attack known as SQL injection. 05:55.660 --> 05:59.230 Let's stop for a second here because this is super important. 05:59.230 --> 06:09.430 So the SQL injection is as the name suggests, someone wants to inject some malicious SQL code into 06:09.460 --> 06:10.900 your queries. 06:10.930 --> 06:19.450 If you wouldn't separate the user input like we do using the prepared statements, and even worse, 06:19.450 --> 06:22.450 you will just just straight ahead. 06:22.480 --> 06:25.720 Add whatever user sends into this query. 06:25.750 --> 06:30.070 For example, doing it this way. 06:30.850 --> 06:39.130 This might contain some malicious SQL that might, for example, delete the data or even drop your tables 06:39.130 --> 06:43.090 if your database permissions aren't set up correctly. 06:43.090 --> 06:53.520 That's why we always use prepared statements to make sure that nothing wrong happens to our data, and 06:53.520 --> 07:03.390 by using prepared statements, we make sure that if the input for name or email is the SQL code, then 07:03.390 --> 07:04.800 it's just not run. 07:04.800 --> 07:10.020 Because if you think about it, what stops me from writing? 07:12.300 --> 07:24.000 Drop database or drop table here, which is a code that could potentially remove the database or remove 07:24.030 --> 07:25.290 a table. 07:26.430 --> 07:31.230 Okay, so with that said, now we should bound to the attributes. 07:31.950 --> 07:41.250 So we bound to name the value of name variable to email the value of email variable. 07:41.250 --> 07:49.020 And consequently to message we bound the value of message variable and only. 07:49.020 --> 07:52.190 This safely runs this query. 07:53.600 --> 08:01.100 Okay, so finally the return type from this function is boolean which should indicate if we were successful 08:01.100 --> 08:02.090 or not. 08:02.600 --> 08:11.120 So let's return it after the statement is run using execute, it will have another method that we can 08:11.120 --> 08:12.800 use called row count. 08:13.070 --> 08:21.440 So since we are inserting data here, if we are successful, the row count should return something that's 08:21.440 --> 08:22.400 more than zero. 08:22.400 --> 08:27.020 And this is how we can check if we were successful. 08:29.330 --> 08:37.280 Okay, now I think we can go back to our route and instead of VAR dumping data here, let's actually 08:37.280 --> 08:40.310 insert it into the database. 08:42.380 --> 08:47.000 So at this point we can call insert message. 08:49.250 --> 08:54.130 Let's grab the connection from here and just pass it to this function. 08:54.160 --> 08:56.860 We won't run any more queries in here. 08:58.210 --> 09:07.690 Then the parameters I can use named parameters because I don't remember the order name is name. 09:09.970 --> 09:17.440 Email is email and the message is message. 09:17.440 --> 09:23.230 So you can see how the name parameters is really a lifesaver. 09:23.260 --> 09:30.430 I could never remember the names or the order of the parameters names I can remember. 09:31.690 --> 09:35.230 Okay, so this returns a boolean value. 09:35.230 --> 09:36.670 So let's use it. 09:38.110 --> 09:39.760 Let's call it insert it. 09:39.910 --> 09:46.810 And now I think we owe people the message if they were successful or not. 09:46.810 --> 09:56.280 So let's say If inserted, then we also like to escape the name. 09:56.280 --> 10:05.700 So keep in mind that also nothing stops people from using HTML tags or some other meta tags that might 10:05.700 --> 10:09.660 even try to include some scripts, some malicious scripts. 10:10.020 --> 10:16.110 That's why when we are displaying the user input, we should always escape it. 10:16.500 --> 10:19.110 That's another thing that you need to keep in mind. 10:19.560 --> 10:25.770 In PHP there is this HTML special chars function that that can do that. 10:26.640 --> 10:34.200 I'm passing the name here because I'd like to just say thank you for sending the message and refer to 10:34.230 --> 10:35.850 the person name. 10:36.120 --> 10:39.180 I also want to escape the quotes. 10:39.180 --> 10:45.390 So I'm passing this constant and I want the UTF encoding. 10:46.970 --> 10:51.050 Then I can say thank you. 10:51.980 --> 10:57.170 Save name for your message. 10:58.040 --> 11:02.900 It was stored something like this. 11:02.900 --> 11:08.660 And at this point we would like to stop the execution. 11:08.660 --> 11:11.630 But it would just happen anyway. 11:15.230 --> 11:15.560 Maybe. 11:15.560 --> 11:17.810 Let's make sure we exit anyway. 11:17.810 --> 11:20.780 Because now if it wasn't inserted. 11:20.810 --> 11:25.790 On the other hand, I'd like different status code. 11:27.890 --> 11:33.380 This would be 500 that there was some kind of error without saying what it was. 11:33.380 --> 11:39.470 That's why I think we can jump to router and add another function of. 11:41.720 --> 11:46.380 This kind, which could be just server error. 11:46.410 --> 11:52.350 This is how it is often just described without going into specifics. 11:54.270 --> 11:56.550 That's the default message. 11:56.550 --> 11:57.510 It's void. 11:57.510 --> 12:03.000 And this is HTTP response code 500. 12:03.030 --> 12:06.300 This means it is an unknown server error. 12:06.330 --> 12:14.730 It typically is returned when there is something very bad going on, like database is down or the server 12:14.730 --> 12:21.510 isn't working, or there is some kind of a fatal exception fatal error in your PHP code. 12:21.540 --> 12:28.530 You also don't want to give people too many details about what happened, so it's enough to just tell 12:28.530 --> 12:31.230 them something went terribly wrong. 12:31.260 --> 12:32.250 Goodbye. 12:32.700 --> 12:40.290 Okay, so instead of using this code directly, let's use our server error function. 12:40.740 --> 12:42.840 I think this is how I call that. 12:43.860 --> 12:44.870 Let me check. 12:44.900 --> 12:46.640 Yes, that is a. 12:48.800 --> 12:55.430 Server error and let's say could not store the message. 12:56.630 --> 13:00.860 Sorry, I don't think we need this line anymore. 13:00.890 --> 13:05.030 Now, in the future, there can be a better way to handle this. 13:05.060 --> 13:12.650 We might store a so-called flash message that will be displayed for the duration of the next request 13:12.680 --> 13:13.370 only. 13:13.370 --> 13:17.150 And we can redirect the person to some other page. 13:17.840 --> 13:20.000 For now, let's keep it this way. 13:20.000 --> 13:23.900 So the final task here is to test that. 13:24.620 --> 13:32.660 So I'm going back into the form, and I'm going to use this bold tag to try out our functionality. 13:32.690 --> 13:34.610 I'm going to use my real email. 13:34.610 --> 13:36.740 And I'm going to just say hello. 13:37.340 --> 13:43.630 So as you see this displays the HTML tags as it would be a text. 13:43.630 --> 13:49.240 It's not treating this B element as this would be an actual tag. 13:49.270 --> 13:56.710 It's using the HTML entities, which means whatever tags anyone would like to use in the input, it 13:56.710 --> 14:01.090 won't be parsed by the browser, it would be just displayed as code. 14:02.080 --> 14:04.480 So the message was apparently stored. 14:04.480 --> 14:06.850 We don't really display those messages. 14:06.850 --> 14:09.880 That's why I will jump to the database. 14:10.840 --> 14:13.750 Okay, I had to press this refresh button. 14:13.840 --> 14:14.410 Okay. 14:14.440 --> 14:15.610 There we have it. 14:16.870 --> 14:20.920 For every single column we've got the values that I have just submitted. 14:20.920 --> 14:28.480 And you can also see that Createdat column was automatically populated with my current time, or at 14:28.510 --> 14:36.880 least the database current time, which confirms that everything works perfectly and that more importantly, 14:36.880 --> 14:41.770 we are safe when inserting data to the database.