Jose: Hi guys and welcome back.
In this video, we're going to tell you
about SQL injection attacks,
what they are, how they happen,
and how you can prevent them.
It's very simple, but it must be done,
and if you forget to or you do this wrong,
it can be really dangerous.
So SQL injection attacks are when your programmes
are coded in such a way that users
can execute SQL code that they want to execute
without accessing your database directly.
So essentially it's an attack vector
for users to execute SQL code.
Let's look at an example on how this can happen.
Imagine you've got a query like this one in SQLite.
Select star from users where first name equal
and then a parameter that you want
to allow the users to enter.
This is pretty straightforward,
you do this all the time when you want to ask
the user for some information for filtering purposes
or to show them some information related to them.
But then let's say you decide to write
your SQL query in Python like this.
You've got get_user as your variable,
and that equals to the query,
select star from users where first_name equal
and then curly braces.
And that's because when you execute the query,
you've decided to execute it like this,
cursor.execute and then get_user, your query .format,
and you're gonna format using Python string interpolation
the username that the user gave you,
probably in some user input,
into the string which is the query,
and then you're gonna execute that.
This is all well and good,
but smart users will probably try--
Well, not smart, but kinda of nasty users
are gonna try to run something like this.
This is gonna be your username searching app,
this is a sample app.
Enter your username and a nice user
is gonna enter their username, like Rolf,
and then your app is gonna search stuff.
But a nasty user might enter something like this.
Two quotations marks, two single quotation markers,
semicolon, drop table users.
So your Python code would now be running this query.
Select star from users where first_name equal empty string,
semicolon drop table users semicolon.
So there's two queries now running here,
and this is fine in SQL
because with a semicolon we separate two queries,
and therefore it would run both of them.
So clearly this is going to find nothing
because the first name is gonna be
empty in the where clause,
but then it's gonna immediately delete your users table.
So use the question mark instead of string formatting
when working with databases.
So something like this would've saved you.
If you say that the get_user query
is select star from users where
first_name equal question mark,
and then in your cursor execute,
pass the arguments using the tuple notation
that we've already looked at
rather than using string formatting.
If you use string formatting,
you're just putting whatever the user types
into your string, that can be dangerous.
If you use this argument style,
then SQLite is going to take care of preventing
stuff like this from happening.
So it cleans up the user input for you
so that it can't be done.
All right, just wanted to tell you
about SQL injection attacks.
They're very simple to avoid,
just use the argument notation provided
by all database libraries, SQLite,
PostgresQL library that we'll use
later on called psycopg2 will support
stuff like this as well.
So we will be using that throughout the course,
but just remember that.
Don't try to use string interpolation.
Thanks for watching this video,
I'll see you in the next one.