There's actually been a debate over how I should pronounce this.
I call it sequel injection.
The acronym is SQL.
Whichever way you call it,
it's a way to attack websites,
that really shouldn't work any more, but still does.
Sequel or SQL is a language in which you talk to databases,
and it's fairly...
it's fairly like English actually. You can actually say things like,
"Select from this table."
It's not a complicated language.
There are no great amounts of curly brackets and semi-colons and things like that.
It can be.
But equally you can pretty much type commands in near English into it,
and you'll get results back from your database.
And this has existed for years and years and years.
And that was all fine until the Web came along,
and now people are looking at websites and are thinking,
"These, these need to be hooked up to databases."
Because way back, when Tim Berners-Lee invented the World Wide Web,
it was pretty much "I'm gonna request a document
and you're gonna send that document back to me."
Eventually people worked out that
what do you really wanted to do was send a document
and have different things come back depending on what you sent.
Maybe you could type in a search request,
and that would go to a database and pull back something.
That's great, that's brilliant, that's a wonderful invention.
And unfortunately, some programming languages dealt with this in a sensible way,
and some did not.
And one of the most notable ones that didn't is a language called PHP.
I'm a PHP coder. It's a very easy language to write in.
It's a friendly language. I still haven't met any other language that lets me develop code
at the speed that I am able to.
It's very fault-tolerant,
within reason.
It doesn't always give you the best results when it does, but, you know,
it's friendly, it's easy to pick up and crucially
you can just write it into a text file, upload it to a web server (in most of the world),
and it will just work.
You can type in PHP code and have it just run.
So the barrier to entry is really, really low.
Which in one way is brilliant. It makes web programming much more accessible.
Facebook was originally written in PHP.
Innumerable things have originally been written in PHP and loads of things still are.
WordPress still is.
The trouble is, that if you're not careful, there's a lot of ways to go wrong.
And this isn't just PHP, but I'll use it as a example.
You talk to a database by issuing a command like this.
SELECT * FROM users
WHERE username equals
"tom"
Great!
And the database will send back all the details it knows about the user called "tom".
Brilliant!
But the catch is those quotation marks.
Because if I'm not careful about what I send,
then we can cause some problems.
Let's say, for example, that I have a web form that lets me login and I type in tom,
and it sends that and brings back "tom".
OK?
Now lets say I type in tom with a quote mark in it,
and if you are not careful, what will happen is the language will send something like this.
SELECT * FROM users WHERE username
equals
"tom" and then I put a quote mark in, and then it puts a quote mark in.
It fails because the quote marks don't match up.
And the whole thing crashes and it just sends back an error.
That's mildly annoying,
and a big problem, of course, is putting in any text that has quote marks.
The catch is you can do a lot of damage that way.
Because that language doesn't just have SELECT.
It has INSERT to add new stuff.
It has UPDATE to change stuff.
And it has DELETE to remove stuff.
So if I was to send, say,
a username that was 'tom";',
and then put another command in there, like,
'delete everything'.
Its not a literal command, but something like that.
It would work.
So we have a look how that works.
We've got the normal command, "SELECT * FROM users
WHERE username [mumbles] 'tom'""
Long command there.
But when you put in 'tom', I'm gonna send that,
and then I'm gonna send this:
DROP ALL DATABASES;
Hit enter, it will get converted into a plain English command,
in SQL language,
it will get sent,
and database will go
"Well that's exactly what I should do."
It's gonna understand that there's a new command at the semi-colon and that it should delete everything.
The main way around it is escaping.
When there is dangerous character, like a quote mark,
you put a slash before it.
And by 'you' I mean you, the programmer writing this.
You go through, and you use a function that says,
"Everywhere there is a quote mark, put this slash before it.
And before you send it to the database, you do that."
Input comes in from the user,
add some slashes to it to make it safe,
send it out to the database.
And the database looks at those slashes and goes,
"Right, every time there's one of those, this thing that's coming next?
Just treat it as a regular quote mark.
Don't treat it anything special, it's in the text, just treat it as that."
And if you wanna send an actual slash, you send two slashes:
the first one to say "Treat the next one as a real character", and then...
It works, but it's clunky.
And for a while, this kind of "Send the command in plain English"
was the only way to make things work in a couple of languages,
including PHP, the most commonly used web programming language in the world.
To make this worse, the command to add those slashes was the wonderfully unwieldy:
mySQL (it's the name of the database.)
_real
_escape
_string. And then you put whatever text you want there.
"escape_string" being what you want it to do,
"mySQL" being the name of the database,
and "real", because the first one didn't work
and they couldn't change it because of backwards compatibility.
So, anyone who'd used the original string, which is, like, more than 10 years ago now,
but anyone who'd used the original form of this:
completely insecure.
Rather than patch that, they just added the word "real".
Anyone who forgot to add that, or hadn't read through all the documentation?
Yep. Anyone can come along and effectively delete your database.
Or do more subtle things, like update other people's accounts or read other people's passwords.
Because once you've got access to the database, if you work out how it works,
there's really not much you can't do.
And the thing is, it is so easy to get this wrong.
If you get this wrong just once, anywhere in your code,
and there are lots of really subtle ways that I'm not gonna get into to get this wrong,
it's not just a case of forgetting to escape quotes.
There are lots of really subtle ways to get it wrong.
If you do that, then your web app is vunerable.
And if someone figures out that there's a way in there,
because they try and create a username with a quote mark in it,
then good luck! Say goodbye to everyone's passwords.
The way it should be done is something called prepared statements,
and if you are programming anything to do with a database,
you should be using prepared statements right now.
The way they work is...
It's a hack.
It's a hack on top of a hack, because, let's be honest,
sending that kind of plain English SQL command from a programming language,
that's a hack, and then we've had to put more on top of that, and more on top of that, and more on top of that.
But prepared statements at least keeps it safe.
With prepared statements, you send the query.
You send "SELECT * FROM users WHERE username ="
and then you just say '?'.
And that question mark, you then later say "Right, this is the data I'm putting in.
This is not a command.
Don't do anything to this, no matter what it looks like.
This is unsafe.
Just take it, treat it very gingerly, store it in the database and don't look at it beyond that."
It's a little more complicated than that, I am simplifying massively for, you know, talking to a camera.
And if you are web programming,
you should look up the recent security guidelines of what you should do, etc, etc, etc.
But,
this is what you should be using.
Because right now,
if you're not using prepared statements, it takes one mistake, anywhere in your application,
one thing where you've forgotten to put a quote mark in,
or messed it up in some subtle way that it uses Unicode characters,
or something wonderfully complicated,
particularly if you're using a Microsoft database.
That's from someone that uses Windows.
If you're not using prepared statements, you are vulnerable,
and you need to fix that.
But in the mean time, as hacks go, there are worse ones.