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