What do Sony, Microsoft, Yahoo!, Kaspersky, Barracuda, TalkTalk, 7-Eleven, the British government, the
American government, the Turkish government, the Chinese government, and the Indian government
all have in common? They've all been hacked using an attack called SQL injection. Numbers differ
depending on where you look for this (largely because people don't want to admit to it) but
estimates are that anywhere from 51% on the very low end right up to 97% of all data breaches
used SQL injection. This is an attack that has existed for decades. Is it just me or we failing
to learn a lesson here, because it keeps happening! Right. I'm going to show you how this works; because
I want you to know how to protect yourself against it so you don't end up in this list as well!
Welcome back to The Pro Tech Show. What kind of website might be vulnerable to SQL injection?
The answer is any website that uses a database; which is basically all of them, which is why this
is such a big deal. Technically. it doesn't have to be a website. I should say any application that uses a
database, but websites are usually the front door on the internet where the bad guys come knocking.
A SQL injection attack is centred around the use of the Structured Query Language or SQL,
which I tend to pronounce "sequel". It's the language used to talk to databases but you won't need to
be a developer to understand this as it more or less makes sense in plain English and I'll walk
you through. Incidentally, I have a theory about the pronunciation. I think the pronunciation depends on
which database you encountered first. If you started out in the open source world you probably used
MySQL and pronounced it "S.Q.L.", and if you started out in the Microsoft world you probably used Microsoft
SQL Server and pronounce it "sequel". Just a theory. Let me know in the comments if you think I'm right.
Using this language you can send instructions to the database. Instructions like "show me some data"
or "change this data" or just "delete everything". Obviously if you're developing a website
you would never let an end-user of that website send their own instructions to the database.
That would be a catastrophically bad idea and yet it's exactly what SQL injection lets them do.
Let's use an example. Let's say we've discovered a SQL injection flaw in the internet's most
well-known online store. I am of course talking about LTTStore.com. On the real website they have a
search feature which you can use to find products. We're going to pretend they messed up when they
created that search. I should stress this is not real. I am not showing you how to hack this website.
I'm using it as an example so you can visualise how this works. I have no reason to think the real
website is vulnerable, and if it is you shouldn't hack it because that's illegal and not very nice.
Seriously, don't mess with this. You could go to jail! When we type something into the search bar
the website looks in this database for a product that matches what we typed and displays it to us.
Let's think for a moment about how it does that. It's talking to the database so it's using SQL.
The website is probably saying something like this.
"SELECT" means "give me information". "Name and "Price" are the pieces of information we want.
"Products" is the name of the table in the database that contains this information. Then we're telling
it to only return rows from that table where the name of the product has the text "water" in
it. Anything inside the quotes is interpreted as literal text rather than SQL code. The percentage
signs are wildcards that match anything; so it's not looking for a product exactly called "water",
it's looking for a product with "water" in its name, like "water bottle". The semicolon just means we're
at the end of our query. Congratulations! Now you speak database. Told you it wasn't that hard!
If the developer wrote literally this in their code then the website wouldn't be much use because
all it could ever do is show products with "water" in their name, so it needs to be adjustable. The
obvious thing they could do is have their code send this SQL query but change this bit here to
whatever value is in the search box. That way if you type "water" it would find water bottles and
if you type "t-shirt" it would find t-shirts. Hooray! We've got a working search feature on our website.
This would work. There are websites out there doing exactly this, and that's a problem; because that
method contains a massive flaw. Let me show you. Let's say instead of searching for "water" I search
for a single quote, semicolon, "DELETE FROM Products", semicolon, dash, dash. Why on earth will search for
that? Well look at what it does once the website inserts that search term into the SQL query.
Suddenly when you put it all together you can probably see the problem. We've got a search for
products (in this case any product at all), then a semicolon which means it's the end of our query,
then a new query that says "DELETE FROM Products". You don't need to be a programmer to guess what
that's doing! A semicolon to end that query and then our two dashes. The two dashes in SQL are the
start of a comment. A comment lets the programmer write any old text they want and it will get ignored
by the program. It's not code, it's just a way for the developer to make notes about the code.
That means you've got two instructions being sent to the database: a "select" instruction
and a "delete" instruction, and anything else is just treated like a comment and ignored.
The website is going to return every single product to me because of the select query,
right before it deletes every single product out of that database! This is SQL injection. I
have injected my instructions into the website's program because the developer
naively trusted a piece of information that I, a random person on the internet, had control over;
and ran it. That example will actually work in some cases literally as I wrote it. Some databases and
some programming languages won't let you run two SQL queries back-to-back like that because it's such an
obvious mistake, but there are loads of other ways to manipulate this flaw using the same
principle. If you use your imagination you can see just how dangerous this could be. Apart from
just destroying data, I could use it to extract confidential data that's held on the same server
and have it dumped onto the web page with my water bottle search results. Maybe I've found a
table full of customers' credit cards, for example. I could also change data. Maybe I extract details
about the database structure, find where the user accounts are stored, and then tell it to change the
passwords to one I know. I could insert my own data. Maybe I find a table that's used to hold refunds
that need to be paid out, and I stick my own bank details in along with a million dollar refund.
It's difficult to overstate just how bad this is; and there seems to be a never-ending parade of web
developers who've taught themselves just enough to get a website working, but nowhere near enough to
get it working securely; and they keep churning out stuff like this. So how should you do it?
There are a couple of approaches you could take. One that seems like a good idea, but isn't; is that
you take the user-entered text and you escape it. This means you look through the text for things
like this single quote and you stick a slash in front of it. The slash tells the database "I know
this looks like a quote, and ordinarily you would treat the quote as meaning the end of our literal
text; but in this case I want you to treat it as a literal quote character inside our text and
not the end of the text." Unfortunately, whilst this works in theory it's a pretty bad way of trying to
protect yourself; because it's very difficult to do perfectly and you only need to slip up once
to have your entire server cracked wide open. In our example there's more to worry about than what
I typed into the search box. There's a whole host of information transmitted by my web browser that
I could tamper with like HTTP headers and cookie values. It's less obvious than the search box but
it's my browser so I can still control it, and if any of that information gets passed to the database
that's another opportunity for SQL injection. Let's say our programmer is switched on to this
and painstakingly identifies and escapes every piece of information coming from the end-user.
They're safe, right? No. Let's take another scenario. I'm playing a game online
and one of my competitors is bragging about getting the last mouse mat from the LTT
Store. I really want that mouse mat so I plan to steal it; but first I need to know where they live...
I try to use SQL injection on the search box to find their contact details from when they placed
their order but LTT Store's developer thought of this and escaped my search terms. So I try another
approach. I sign up for an account myself. Let's say I know this person's username in the game was Jake,
so I provide my username as "Andrew", single quote, "OR Username", equals, single quote, "Jake". No luck because
the developer isn't going to trust a sign-up form where I can type anything I want, are they? They're
too clever for that so the SQL I try to inject gets escaped and passed to the database as literal
text rather than a command; but I'm not done now... Now I go to my account and browse the page containing my
contact details. I'm not going to provide any input here. There's no user-submitted data to be escaped...
or is there? To load my contact details the website first takes the session ID from my browser,
escapes it for safety, and looks up its table of logged in sessions to find my username.
It then runs a query against the database to find the contact details associated with that username.
You can see the problem. That username stored in the database was already poisoned and now the
website has just run a SQL injection attack on itself, displaying the contact details for Andrew
(who doesn't actually exist because that wasn't my username) and Jake (who does exist) so that's whose
name, phone number, email, and postal address I get to see. This is called a second order SQL injection:
where you inject your own SQL command knowing the website won't run it initially, but at some
point in the future that information is going to be retrieved and reused, and at this point
because it's data from the website's own database the programmer makes the mistake of trusting it.
It's not enough to just worry about user input. You need to worry about any information that might be
derived from user input, and any information that might be derived from information that might be
derived from user input, and any... you get the idea! Before you know it you're playing 4D chess where
a single mistake leaves your website vulnerable. This approach is far too error-prone. There are
so many possible scenarios and the attacker only needs to think of one that you didn't.
This is why so many websites still get hacked using SQL injection, and in 2022 there is no
excuse for writing code like this. There is a much better approach that stops SQL injection attacks
in its tracks without the developer needing to imagine and defend against every possible scenario.
The answer is parameterised queries. Another name for parameterised queries is prepared
statements. They're the same thing. When we write a parameterised query we don't construct the final
SQL command before sending it. We first make a template of sorts. If we go back to our first query:
select name and price from products where name is like... something. That's the template of our query;
but the "something" can change. So what we do is we send the query like this with the "something" still
unknown. We say "this is our query and we'll let you know what this something is in just a minute."
Well, not really a minute. We'll send it immediately after. The "something" in this case is "water". What
this does is it allows the database to understand the format of the query before the variable parameter
(the "something") is supplied. If that "something" contains injected SQL that's designed to change
the query from a "select" to say a "select" and a "delete" then it's not going to work because the
database has already seen the template and it knows this is just a "select" query. Every
time we're building a SQL query in code and there's a variable component that could change
we parameterise it like this. We don't need to worry about user input or information
that's derived from user input. If we always use parameterised queries for every variable we include
then the database always understands what the SQL query was supposed to look like.
Even if the parameterised parts that can change they aren't allowed to fundamentally change the
query into something different. If you're writing a website or any code that talks to a database -
parameterise everything. It is much easier than trying to predict every possible attack
scenario, and any query you don't parameterise is an opportunity for an attacker to think of
something you didn't. Assuming LTT Store's developer has done this like a good dev they can rest easy.
Remember, though; this is not just about websites. I don't write websites (although like many I have
dabbled with it in the past) but I do write scripts. Scripts to automate repetitive or laborious tasks.
Sometimes these scripts might need to access a database; and if they do, and if I haven't
parameterised those queries, my script might be vulnerable. A lot of people get this wrong, either
through ignorance or laziness; but now that you know, the extra couple of lines it takes to write
a parameterised query are an absolute no-brainer to protect you from the most common and otherwise
difficult to defend against cyber attacks in the world. If you found this useful then give it a like
and consider sharing it with anyone you think could benefit from it. In the meantime I have
injected the YouTube algorithm with a query for a good video based on your personal viewing history,
and it came up with this one so why don't you check that out next? Thanks for watching. See you next time!