#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.
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.
