WEBVTT 1 00:00:00.180 --> 00:00:01.560 Hi guys and welcome back. 2 00:00:01.560 --> 00:00:02.980 In this video, we're going to tell you 3 00:00:02.980 --> 00:00:05.350 about SQL injection attacks, 4 00:00:05.350 --> 00:00:07.400 what they are, how they happen, 5 00:00:07.400 --> 00:00:09.020 and how you can prevent them. 6 00:00:09.020 --> 00:00:10.860 It's very simple, but it must be done, 7 00:00:10.860 --> 00:00:13.020 and if you forget to or you do this wrong, 8 00:00:13.020 --> 00:00:14.850 it can be really dangerous. 9 00:00:14.850 --> 00:00:18.780 So SQL injection attacks are when your programmes 10 00:00:18.780 --> 00:00:20.820 are coded in such a way that users 11 00:00:20.820 --> 00:00:23.600 can execute SQL code that they want to execute 12 00:00:23.600 --> 00:00:25.800 without accessing your database directly. 13 00:00:25.800 --> 00:00:27.740 So essentially it's an attack vector 14 00:00:27.740 --> 00:00:30.750 for users to execute SQL code. 15 00:00:30.750 --> 00:00:33.343 Let's look at an example on how this can happen. 16 00:00:34.350 --> 00:00:37.500 Imagine you've got a query like this one in SQLite. 17 00:00:37.500 --> 00:00:40.400 Select star from users where first name equal 18 00:00:40.400 --> 00:00:42.010 and then a parameter that you want 19 00:00:42.010 --> 00:00:44.200 to allow the users to enter. 20 00:00:44.200 --> 00:00:45.320 This is pretty straightforward, 21 00:00:45.320 --> 00:00:47.450 you do this all the time when you want to ask 22 00:00:47.450 --> 00:00:49.910 the user for some information for filtering purposes 23 00:00:49.910 --> 00:00:53.210 or to show them some information related to them. 24 00:00:53.210 --> 00:00:55.410 But then let's say you decide to write 25 00:00:55.410 --> 00:00:59.080 your SQL query in Python like this. 26 00:00:59.080 --> 00:01:02.130 You've got get_user as your variable, 27 00:01:02.130 --> 00:01:03.650 and that equals to the query, 28 00:01:03.650 --> 00:01:06.440 select star from users where first_name equal 29 00:01:06.440 --> 00:01:07.990 and then curly braces. 30 00:01:07.990 --> 00:01:10.220 And that's because when you execute the query, 31 00:01:10.220 --> 00:01:13.220 you've decided to execute it like this, 32 00:01:13.220 --> 00:01:18.090 cursor.execute and then get_user, your query .format, 33 00:01:18.090 --> 00:01:21.530 and you're gonna format using Python string interpolation 34 00:01:21.530 --> 00:01:23.400 the username that the user gave you, 35 00:01:23.400 --> 00:01:25.190 probably in some user input, 36 00:01:25.190 --> 00:01:28.380 into the string which is the query, 37 00:01:28.380 --> 00:01:30.640 and then you're gonna execute that. 38 00:01:30.640 --> 00:01:31.560 This is all well and good, 39 00:01:31.560 --> 00:01:34.410 but smart users will probably try-- 40 00:01:34.410 --> 00:01:37.410 Well, not smart, but kinda of nasty users 41 00:01:37.410 --> 00:01:40.060 are gonna try to run something like this. 42 00:01:40.060 --> 00:01:42.210 This is gonna be your username searching app, 43 00:01:42.210 --> 00:01:43.350 this is a sample app. 44 00:01:43.350 --> 00:01:46.150 Enter your username and a nice user 45 00:01:46.150 --> 00:01:48.500 is gonna enter their username, like Rolf, 46 00:01:48.500 --> 00:01:50.960 and then your app is gonna search stuff. 47 00:01:50.960 --> 00:01:54.633 But a nasty user might enter something like this. 48 00:01:56.280 --> 00:01:58.360 Two quotations marks, two single quotation markers, 49 00:01:58.360 --> 00:02:00.583 semicolon, drop table users. 50 00:02:01.670 --> 00:02:05.760 So your Python code would now be running this query. 51 00:02:05.760 --> 00:02:09.390 Select star from users where first_name equal empty string, 52 00:02:09.390 --> 00:02:12.210 semicolon drop table users semicolon. 53 00:02:12.210 --> 00:02:14.240 So there's two queries now running here, 54 00:02:14.240 --> 00:02:15.890 and this is fine in SQL 55 00:02:15.890 --> 00:02:18.840 because with a semicolon we separate two queries, 56 00:02:18.840 --> 00:02:21.323 and therefore it would run both of them. 57 00:02:22.690 --> 00:02:26.300 So clearly this is going to find nothing 58 00:02:26.300 --> 00:02:27.680 because the first name is gonna be 59 00:02:27.680 --> 00:02:28.900 empty in the where clause, 60 00:02:28.900 --> 00:02:32.880 but then it's gonna immediately delete your users table. 61 00:02:32.880 --> 00:02:35.970 So use the question mark instead of string formatting 62 00:02:35.970 --> 00:02:37.690 when working with databases. 63 00:02:37.690 --> 00:02:39.610 So something like this would've saved you. 64 00:02:39.610 --> 00:02:42.000 If you say that the get_user query 65 00:02:42.000 --> 00:02:44.065 is select star from users where 66 00:02:44.065 --> 00:02:46.177 first_name equal question mark, 67 00:02:46.177 --> 00:02:47.640 and then in your cursor execute, 68 00:02:47.640 --> 00:02:50.980 pass the arguments using the tuple notation 69 00:02:50.980 --> 00:02:52.370 that we've already looked at 70 00:02:52.370 --> 00:02:54.860 rather than using string formatting. 71 00:02:54.860 --> 00:02:56.130 If you use string formatting, 72 00:02:56.130 --> 00:02:58.280 you're just putting whatever the user types 73 00:02:58.280 --> 00:03:00.350 into your string, that can be dangerous. 74 00:03:00.350 --> 00:03:02.490 If you use this argument style, 75 00:03:02.490 --> 00:03:05.800 then SQLite is going to take care of preventing 76 00:03:05.800 --> 00:03:07.520 stuff like this from happening. 77 00:03:07.520 --> 00:03:09.820 So it cleans up the user input for you 78 00:03:09.820 --> 00:03:11.980 so that it can't be done. 79 00:03:11.980 --> 00:03:13.060 All right, just wanted to tell you 80 00:03:13.060 --> 00:03:14.860 about SQL injection attacks. 81 00:03:14.860 --> 00:03:16.320 They're very simple to avoid, 82 00:03:16.320 --> 00:03:19.010 just use the argument notation provided 83 00:03:19.010 --> 00:03:22.140 by all database libraries, SQLite, 84 00:03:22.140 --> 00:03:23.500 PostgresQL library that we'll use 85 00:03:23.500 --> 00:03:25.540 later on called psycopg2 will support 86 00:03:25.540 --> 00:03:26.780 stuff like this as well. 87 00:03:26.780 --> 00:03:29.230 So we will be using that throughout the course, 88 00:03:29.230 --> 00:03:31.010 but just remember that. 89 00:03:31.010 --> 00:03:33.700 Don't try to use string interpolation. 90 00:03:33.700 --> 00:03:34.950 Thanks for watching this video, 91 00:03:34.950 --> 00:03:36.613 I'll see you in the next one.