there are big companies being hit by SQL injections all the time still in 2016 and if you
mess it up then all your usernames and passwords are out there in Pohnpei spin or what have you
and it's it's it's bad PR not to mention illegal you know data protection acts and so on and it's
yes bad news for everyone involved and inevitably bad news for the hacker as well who probably goes
to jail okay so I should just preface this by saying that you know if you do this you will go
to jail so really don't you know I'm doing it on my own website which is allowed because I've given
myself permission I sometimes go back and watch older computer file videos because you know maybe
I came to computer for a bit later because I know maybe I was busy doing something I don't but the
reason is so I go back and I look at these videos and the home scott did a reinserting video on SQL
injections which is exactly right okay so I'm not going to remit the wheel by talking about
exactly how they work but what I thought is I do some and perhaps demonstrate to anyone who's
got an interest in web development just how bad it is if you mess this up this little potential
I can social product anyone who actually does web development or any kind of design will see
immediately this is this is bad okay we've got a lovely little blue gradient here and a just
a box okay so I can search for hammer and when I do that I get a couple of hammers up so I get
a claw hammer in a sledge hammer right and I can search for other things nails is it I don't know
a common what I'm selling in this hardware shop yeah okay so different kinds of nails all right
these are all the nails I know it exists right different different arbitrary links of nail I
assume they it's elbows anyway all right this is not a shot that you would actually want to put
your credit card details in this shop also apart from being bad as a shop is also bad as a web
program because it's PHP and I haven't escaped strings like I should have done when I type in
hammer and click submit a post request goes off PHP receives this and constructs an SQL query but
says you know give me any roads that have hammer in and then asks the database for all those rows
and then spit some out on the screen problem arises is if we put in a special SQL command
character in this text box then the string but PHP forms is going to be malformed in some way
it's not going to make any sense as a query any more or it's going to make sense as two queries or
so on and bad news for everyone involved the first thing we do is we work out whether this text box
is vulnerable at all okay so if you put a single quote in right and we press submit and it says
server error we apologize for the inconvenience okay what's very nice of them but this is that
bad news right we're in a proper server would say there is no product with name single quote
because there isn't or it will return any products like by O'Reilly you know hammers you know that
have an actual single quoting it should have used that single quote as a character not as a control
structure so what can we do to attack this well the first thing we need to do is think a little
bit about what the query might be behind the scene so we can we can adjust it so I've got my my text
editor here and I'm going through I'm gonna type in a query so I'm thinking that the query is going
to be something on the lines of select something right some question mark from some table I don't
know what the name of it is it could be product or prod or you know IDs I don't read it could be
anything from some table where some column is like hammer if I search for amme which is the middle
characters of hammer I also see that it finds the hammer which means that is putting wildcards
in either side so that's what my query is select something from some table where something is like
hammer I have control over this bit this hammer word I can't change the rest of a query because
that's back-end PHP code that's already been coded so where are you typing this now this is
just a text editor I'm just doing this so my own head I can visualize the query right you wouldn't
necessarily have to do this if you were super good at this right but it helps and also it's
obviously illustrative to people who are watching also this does SQL markup syntax highlighting so I
can show you how the query changes as I change it so this is my generic query right how do I
edit it well what happens if I if I just put in a single quote so I'm going to copy this line if
I put in a single quote instead of Hamor that's what happens now you can see those few percents
of different colors and the reason is because what's happened is this first percent is inside
a proper string and now the second percent sitting on its own outside of any SQL string and that's
going to cause a server error so that's just that texture just to shown you by the color coding that
yeah this is showing me pretty much what would be going on in PHP when I type single quote into that
into that box which is that basically breaks SQL because it sends a malformed query okay that's not
a valid query the server will respond the database will respondent with an error and PHP will say
server sorry for the inconvenience so the question then becomes what do I change this single quote
into to really get information out of this and the first thing that's important to do is find out
what database management system is running this on the server so imagine that SQL is a language
that's shared by a bunch of different database management systems so SQL Server MySQL Postgres
and so on so forth they will all have very similar syntax slightly different but very similar
certainly select will be the same and the point is that if I know which one it is I can then start
targeting that one directly and for example find that where there lists of table names are finding
the listed table names obviously quite helpful for me to get rid of some of these question marks and
fill in some blanks first of all I can get rid of the rest of this malformed query by putting in a
end of command and the comment okay and you'll see in this intact if it's gone gray which tells me
but now this is the query that I'm typing in so if my input to this shop is single quote then a
semicolon and a comment then what should happen is it should change to this query here which is
select something from the table where wildcard which means everything so I should get all of the
products in the database which I do ok so this is all the product it's not very good shop the
two dozen I don't know okay no one wants these products north buy my things right so so that's
a start now what do we put in as well to try and break this so the first thing we can do is we can
put in something that will only work if MySQL is the database management system now I could also do
an equivalent for Mokpo stress or equivalent for SQL Server I happen to know secretly but this is
my SQL but you could automate this pretty quickly so what I'm going to say is select something from
something from where something like this thing okay and I'm going to put in hammer because I
only want to return the two items okay well that would make sense in a minute we're hammer and one
equals sleep too so what I'm saying there is find any products that are called hammer and also where
the function sleep to sleep for two seconds wait for two seconds returns a one so that should still
turn to hammers but it should take four seconds to do so because there's two hammers alright that's
my hypothesis let's see if it works so I'm going to copy exactly from hammer all the way to the
end of my quote there and I'm going to paste that in and we're going to see and it's thinking it's
thinking and it's taken four seconds it's not found any products which probably means sleep
return of zero not one so there's my bad right it doesn't really matter the point is it weighted
exactly four seconds and then returned us either some products or not some products so that was
basically you just trying to command just to see if it did what you asked it yeah so it inside this
query I've buried the request for the server to sleep for two seconds there's two hammers which
basically means to every row but it finds it's also going to wait two seconds which is two okay
four seconds now MySQL has a sleep function in it cattle eight asleep brackets the number of seconds
you want to sleep for its wait for delay in SQL Server so I would adjust this slightly and I try
again so if it didn't sleep I think I probably not SQL MySQL maybe I'll try the equivalent for
Postgres or try be equivalent there are what we call blind SQL injections where even if we're
not getting any output we can base it on just a time it takes to respond work out what's going on
you could do another thing where you said select from a certain table name and also sleep and if it
comes back slowly you know at the table with that name okay so you can even if there's no output use
this blind technique to work out what's going on okay it's going to take you a little while to do
right in my case I happen to get it the first time yay okay so good news right so now what can we do
so let's start with my hammer thing again so I'm going to take this I'm going to copy it okay next
up we're going to use the fact that we know it's MySQL to try and find some more information on
what tables exists and so on this isn't a blind SQL injection because if I search a hammer it
produces me a table so it's actually outputting to the screen now this is very common a shop you
search for product it's got to take that table information and turn it into an HTML format for
you to look at okay so even if it doesn't look like an actual table like on my bad website it
is in a sense that if taken table data have turned into some form so it will event also downloaded a
picture of the product you know some reviews I'll get rid of this bit this sleep and I say what can
I do that sticks information I'd like to extract from database on the end of this product so what I
really want to do is turn this table into a table that dumps out you know passwords or something
like that if such things exist in this database now in SQL we use the Union keyword to do this
okay so Union basically takes two tables with the same amount of columns and sticks and one on top
of the other so if I can find a way of sticking some personal private database information on
the bottom of the camera list then it's going to output it to the screen so select something from
some table now all we know about this miss select is what it's selecting three columns okay because
it's three columns output at least that's what I'm hypothesizing so select three columns from
some table where some column name is like hammer okay so what I'm going to do instead is I'm going
to say like hammer Union sub query select one two three from jewel now for those of you don't know
my spiel particularly jewel is essentially a placeholder table name to select from if
you don't really have a table to select for okay it's use for testing purposes mostly okay so I'm
literally selecting the numbers 1 2 & 3 in three columns from some hypothetical jewel table right
now if this works what it will do is stick one two three on a row or the bottom of my hammers
if it works okay so let's see so take my hammer I copy all the way to the end there we go and I
go into my website and I search and there's my one two three okay that's bad news for the event with
this website which coincidentally is me okay so the fact that I can output queries to the screen
is really bad it's not just bad it's catastrophic okay because we're only a few steps away from
full-on users and passwords so let's do it so yeah not to be too cavalier about the security
right so let's not select one two and free from jewel because maybe that's not very informative
okay let's select from MySQL information schema so there's a table in the information schema called
funnily enough information T its key médoc tables and that will tell us all the table information
names of tables what columns they have and so on it's the table of tables takes the table
tables right let's Union select table name table schema which is the database name and then a three
because we need to make sure the number of columns match up between our Union table and the original
hammer table does it just put it underneath it literally just pay put it underneath yet if you
want to merge columns it sort of it row by row that's a join and if you're just sticking on
top of us that's an appending that's a Union just just two different terms for sq up so select table
name table scheme work three from information schema dot tables notice it's got purple which
is a good sign because it means that this syntax highlighting recognizes I vouch typed in an SQL
come on so let's copy this the slow way with my fingers there we go and paste that in there there
we go so the information schema is essentially a metadata table but holds information on all the
columns and tables and number of rows and things for all the tables you would query if you want to
know what columns exist and what tables exist I've got a claw hammer and I've got a sledgehammer I've
got a funny blog table called evil and a funny blog table called posts which we may talk about
in a different video right I haven't bothered to put them in different databases that's with
honet right then we have a bunch of kind of almost semi random tables this is MySQL stuff we scroll
down and then right at the bottom we've got the shop tables and we've got a table called stock
and a table called users so we won't select anything from stock because I doesn't seems
very interesting particular as we can search for anyway but we use this table on your hand
might seem quite useful to us okay we don't know what's in there but we've got to assume some kind
of user data now if we go back to my SQL query we know what the table is what we don't know is what
the columns are in the table and if we want to put some output from that users table on the bottom of
our table we're going to need to know what the column names are cuz otherwise we can't select
them we can't just say select everything because they'll probably be more than three columns and
that won't union properly so we're going to say select something like hammer Union select
column name two three from information schema I'm off screen now let's keep scrolling info schema
dot-coms where table name so the information schema doc columns table unsurprisingly has
column information also has what table that column belongs to my table name equals users
okay well I thought about that there we go okay that looks good to me so let's copy that let's
see if it works there we go okay so what we've got is we've got our hammers again alright we're going
to keep seeing them and then we've got a couple of standard MySQL things like current connections how
many people are logged in and then we've got some actual column names user ID user login user hash
user type now anyone that knows anything about hashing passwords and so on nut can guess that the
ID is probably just for not a number representing that user probably just stuck in the table by
default the login is going to be their login name and hash is going to be a hashed version of their
password okay now most database cracks don't get unencrypted passwords people are slowly at least
wiring up a little bit to hashing although they don't tend to hash them particularly securely so
this is this is certainly a candidate for crack path of cracking for example so let's get those
things out so we need three columns from the users table and we want all of it right so we want let's
say user login user hash and user type so let's get rid of this select here we go and we've owner
so we're still unioning that's I don't know if that's an actual verb we're still performing a
union Select you name is it you name or is it you login you login okay so you you login you hash
you type from users and that's all three columns append it to the previous one any so we'll copy
that and see if it works and it does so we've got a bunch of users they're hashed passwords
and we also see but because of a type this guy's an admin okay so I would focus on his password
personally right so that's pretty scary okay it didn't take me very many queries to get through
this now before everyone goes off and cracks these passwords right what you're welcome to do if you
want but bear in mind this is a website that only runs on my computer and these are probably just
the word password or something like that because I wasn't being particularly careful when I created
this database these aren't real people whose password you're cracking here so don't waste
your time I advise it's scary how easy that how easy that was to do now Tom talks a lot about how
you stop this kind of stuff from happening okay parameterize queries sanitizing an input so but
I can't put single quotes in without them being escaped okay those sort of things are important
but you have to also think about things like second order SQL injections which is where I put
something into an SQL Server and then it gets used internally as a query so for example I make my
username a SQL injection right and it gets escaped properly but it gets stored in the database and
then when I go to change my password the injection gets run okay so that's a second-order injection
so there's loads of complicated stuff you can do that could get around some of these things
so if you're a web developer you have to pay a lot of attention to this because you don't
want your users hashed passwords and email address combinations stuck on the net because
it's embarrassing and illegal and really bad news for your customers I mean case in point talk talk
got hacked a few months ago and that was hacked by essentially a script a Python script that performs
what I just did but in bulk really really quickly it takes I've run it it takes about on my website
free commands to obtain what I just obtained and dump them to a file which is pretty terrifying
you know so I would suggest people start scientists analyzing their inputs pretty quick
is that if I obtain a cookie of you which is supposed to be secure then
I can send that to let's say Amazon or to a shop and say I'm Sean please you
know what's in the shopping basket what's his address what's his credit card details