#100DaysOfCode in Python Transcripts
Chapter: Days 88-90: Home Inventory App
Lecture: Scrub function - SQLite3 workaround

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 What we're looking at here is the add room function.
0:03 Now the reason I'm showing you this is because
0:06 it was a bit tricky to get around this.
0:09 We want our users of this application to be able
0:13 to add rooms and what that means is
0:15 they're going to be adding tables to our database.
0:20 Each room is going to be a table
0:23 and each room is going to be able to have certain items
0:27 added to it.
0:28 A name and then a value.
0:31 You can see that here.
0:32 We've got our item, meaning the item we're adding
0:35 to the room, which is text
0:36 and then a value which is a real, as in pretty much a float.
0:41 A real dollar value, a real number value.
0:45 The catch is we want the user to be able to specify
0:50 the name which means the user is essentially telling us
0:54 what our table name is.
0:57 That in SQLite3 is actually not allowed.
1:02 It's not Pythonic and it's actually unsafe
1:05 from a database standpoint because it allows people
1:08 to inject malicious code into your database,
1:12 into your execute command.
1:16 You can see here what we've done is
1:19 just ignore this line here.
1:21 Input, what would you like?
1:23 What name would you like to give the room?
1:25 Let's say we gave the room the name Living Room
1:28 or just Kitchen we'll go with Kitchen.
1:31 When the cursor executes to our SQLite database,
1:35 it creates the table.
1:38 Normally, you would just type the table name in here
1:41 within the quotes.
1:43 But we can't do that because we don't know
1:46 what the user is going to specify.
1:48 We need to parse it.
1:49 The variable that the input was assigned to.
1:53 So, name, we need to parse name into SQLite 3.
1:57 That's what actually incorrect.
2:00 That's what actually unsafe.
2:02 By going name.lower and essentially injecting it
2:07 into our SQLite 3 code here,
2:12 we run the risk of actually injecting malicious code
2:16 into our database, which could be very dangerous.
2:22 To get around this, SQLite recommends you use
2:26 question mark substituting.
2:29 On investigation, you can't use that for the table name.
2:34 You can use it for the rest of the query.
2:36 You can use it after that when you're talking about
2:38 data to add into the table,
2:41 which you'll see we use later
2:42 but you can't use it to actually specify the table name,
2:46 which is very frustrating.
2:48 But the way to get around this and it's a bit of a hack job
2:53 is to scrub the data that goes in.
2:57 So the user will add a name and we're going to scrub that name
3:03 to remove anything that's malicious.
3:06 We're going to define anything as malicious as spaces,
3:10 any non-alphanumeric characters.
3:15 Could be back slashes, could be apostrophes,
3:19 could be a percentage sign, plusses,
3:23 anything like that.
3:24 We're going to scrub all that out.
3:26 We have this simple one-liner here that does that for us.
3:32 So any name that gets parsed into scrub,
3:36 so let's say we're going to scrub name,
3:38 scrub of table name.
3:41 What we're going to go is we're going to return,
3:42 this is all in one line, Bob would love this,
3:46 and essentially what it's doing is
3:49 for every character in this table name,
3:53 if it is a alphanumeric number
3:59 or an alphanumeric character, I should say
4:02 then it's going to join it.
4:05 If not, it leaves it out.
4:08 So we could use this function here to then scrub
4:12 any word we want and turn it into the safe format
4:17 that our SQLite database will actually approve of.
4:21 So to demonstrate, let's run this up in the shell.
4:26 All right, so I've used a bit of magic and just copied
4:28 and pasted it in there.
4:29 So now we can scrub anything we want.
4:32 So let's scrub this string here.
4:38 We'll go Julian-Bob, oops.
4:44 And it comes back as just JulianBob.
4:45 It gets rid of that dash.
4:47 So let's scrub an actual room
4:51 and we'll go scrub Living Room.
4:56 You see, it gets rid of the space because all it's doing
4:58 is just capturing the letters and the numbers
5:02 and putting them together.
5:05 Let's try one more thing.
5:08 Mike99+.
5:16 Just mushed some rubbish in there
5:20 and it strips out all the stuff
5:22 that could potentially hurt us.
5:24 This is a nice little workaround to get around
5:27 the SQLite3 restriction on table names
5:32 and not being able to substitute them with variables.
5:37 So this is what I've put in there.
5:39 Hopefully, that'll help you out in the future, as well.
5:43 But that's just a quick explanation of the scrub.