[Music]
all right hello and welcome to my talk
SQL injection a
history in this talk uh I'm going to go
over three main things the first one is
a brief history on the origins of
injection uh then going to talk about
the how guidance for defending against
it has shifted Through the Ages and
finally uh going to talk about why these
SQL injections are not a common finding
in pen test reports
anymore so a little bit about myself my
name is Will McCardell I was a software
engineer for a number of years then a
database administrator and now finally
I'm application security engineer at a
uh Security consultancy in Milwaukee k
CIS
logic when I was thinking about what
topics to give for this talk I realized
uh that I could probably speak on all
three things I've done so far with SQL
injections so here we
are so who here has been a part of us uh
data
breach you're just not aware of it
yet um data breaches are a common
occurrence these days and one of the
primary causes them uh especially in the
past was a sequel ejection uh attackers
would break into websites dump the users
table and
then uh put the information on the dark
reaches of the internet uh they were so
common in fact that uh the website
have't been pwned which is a website
that collects number of uh data breaches
in the dark web and other places get
sent to them Coates them allows you to
look up your usernames emails uh phone
numbers Etc um to see which uh data
dumps you've been included in uh
actually has a SQL injection as their
logo you see that on the top left and
also the giant thing on the screen
you've all read
already
so what is SQL injection SQL injection
is a vulnerability or weakness in an
application where an attacker is able to
modify user controlled input
to uh get the application database
queries to behave in ways that the
programmers did not intend to do so this
is almost always done if the programmers
chose to do what's called Dynamic SQL
generation with string concatenation
where they are jamming multiple uh
strings of characters together to create
the database queries and then sending
those
on uh so why would you do SQL injection
well it's a lot of fun um but you can
also do a number of other things for
example you can extract all database
data you can wipe or subtly modify data
in there in case it's a competitor um
you can do reconnaissance try to figure
out the structure of the application
behind the scenes so that you can uh do
more tailor made attacks to to penetrate
deeper into their systems you can use it
to establish lateral movement uh Beyond
a number of the defenses because you are
uh executing code that you wrote on
their database servers which are usually
fairly behind uh fairly deep into
defenses
Rocky uh in the late 2000s it was very
important to have a really bling Myspace
page and the uh rocku was a middleware
that sat on top of
the uh Myspace Pages your Facebook pages
Etc and doing a number of uh special
like plug-in like behaviors to modify
show photos Etc uh or glitter text as
you can see in this
example uh the why relevant to this talk
is that in 2009 they suffered a data
breach um which affected 32 million of
their users which was all of them and uh
in this data
breach the uh attacker who found this
determined that all the passwords for
the users were in plain text
and this
was uh a boom to security researchers uh
because they finally had a list of many
people what they actually use for
passwords uh there were a lot of ones
that
um still are used to this day you can
see um a list on the right hand side
here uh briefly but um security research
researchers were also able to determine
that the uh of the 32 million passwords
they had only 18 million of them were
unique so almost like 40% of the
password was duplicates found out in the
database um and rocku is still actually
used to this day uh it is a the password
list is a common thing that's reached
for by uh pen oficial testers hackers
attackers of all Stripes uh if they need
to log into a web page uh they'll send
in a number
of uh usernames with the rocku password
list after that can also be used for
cracking password hashes uh for example
up on the screen here we see uh a
command running hash C uh passing in the
rocku uh database list I mean uh
password list and it is able to crack a
number of the passwords hashes in
there so how to do a SQL
injection uh the primary way is you send
a single quotation mark into a user
controlled input and what that means is
an input field that the user is able to
modify it could be a username field it
could be the password field it could be
an order number field uh your mother's
maiden name field Etc um
could also be something like H an API
endpoint or a HTTP header uh Etc and the
first thing you do is reach for the
single quation Mark if
the application is vulnerable to SQL
injection this is likely to cause an
error of some sort so you send this in
and you look for unexpected Behavior
an example of that is this web uh login
page where we see the email uh has been
changed to just be a single quotation
mark and then the password is also
single quotation mark but you can't see
that uh we then log in and we are see
then see this object object pop up I
don't know what object object means
however it's not what they intended
almost certainly so that means this is
probably weak to sequel injection so the
next thing that people usually reach for
for is a set of other strings to see
what exactly they can do with the SQL
injection they
found uh a fairly common one is this one
single quotation mark or 1al 1 uh
semiquant D- what this basically does is
it does the single quotation mark uh
which closes off the injection Point um
inside the database query and then it
adds or 1al 1 to it and then politely uh
terminates the SQL query with the
semicolon and what that doing is uh the
database query is then going to check
for every piece of data in all the
tables it has access to it's going to
check to see does one equal 1 and since
that's usually
true um it's going to essentially return
all the uh data inside the
table um and then after that we see the
dash dash at the end and what that is
doing is telling the database to ignore
all the other parts of the query that
were originally inside the database
query uh after that because the Das Dash
is commenting that code out that's the
way to cause SQL injections to not cause
uh errors so we see an example right
here with uh that as the login mean the
email and then we're going to go to uh
login and we see that we are the admin
on the Juice Shop um which is great
cuz then we're
in the uh there are other things people
reach for for example in 2013 during
anti-government protest in Turkey
activists called red hack were able to
break break
into uh a utility website for the state
government and they were able to delete
a number of bills owed to the utilities
by the citizens um and after they did
this they posted on Twitter uh the
login uh how to break into it so other
people could mess around with the system
and what they say here as you can see is
single quotation mark or single
quotation mark single quotation mark
equals single quotation mark and that's
quite the mouthful uh but what ad is
essentially doing is it's going to check
to see if uh the concept of empty string
is equal to the concept of empty string
that is true everywhere so that's uh
allowed them to bypass the
authentication uh for this
website so the first publication for SQL
injection was in the magazine Frack I
assume some of you have heard of that um
in December 25th
1998 uh this was an article called n web
technology vulnerabilities and um it
went over the a number of
vulnerabilities found in late the latest
Microsoft
code and the author of of it was this
gentleman rainforest puppy also known as
Jeff
forestel uh who uh as an interesting
note he was also the very first person
to uh release a template
for uh responsible disclosure of
vulnerabilities and
applications uh so in the article he
also offered what Microsoft said was
definition of SQL
injection not a problem he and his
co-researchers talked to Microsoft
beforehand before publishing the article
in frack
and uh Microsoft's response was
essentially that this was not a problem
that he had to worry about and that they
were not going to fix
it they were very wrw about that uh and
in the rest of the article he explains
uh not only uh what how to do SQL
injection as you can see here uh the
very bottom query here is actually the
first SQL injection ever publicly listed
uh select star from table where x equals
1 and select star from CIS objects
um and he explains the benefits of doing
SQL injection how common you can see it
for example uh it was he had to explain
that by doing SQL injection you don't
need to know anything about the database
you don't have
to uh know the host name of it the
username password Etc because you're
essentially smuggling your code into
their database and it's interesting that
that's brought up
uh because it indicates that that's a
novel concept just the concept of not
needing to uh go through the defenses
and just like smuggling your code
through uh the rest of the
application he also talks about what you
can do if uh your SQL injection that you
found isn't returning any data or it's
not going to uh display
to uh UI and he essentially says that
you can use the built-in features of SQL
Server to email yourself the the queries
that you're writing
um and at the very end of it he offers
guidance for how to prevent SQL
injections um the first thing he says is
to escape the user controlled input and
then also uh better yet use custom store
procedures the first thing he suggests
is to escape the user input and by that
he means specifically wrap the entire
input that the user sends in in uh
double quotation
marks and then if there's any single
quotation marks in the input you should
double them up so they're double single
quotation marks and essentially what
that does is it gets SQL to
um I see you're taking photos don't do
that uh it gets sequel
to uh treat them as if it's not actually
uh going to modify the query itself and
the reason why uh said don't take photos
of this one is because you should not do
this this is is the last resort for
stopping SQL injections it is finicky it
is likely to break it is a highly
database specific and it is not
recommended but it was the second best
thing they had at the
time what he said he should do is also
use custom store procedures store
procedures um are pieces of code that
get shipped into a database by the
programmers and what's interesting about
them is that store procedure has the
query portion set in Zone ahead of time
uh once you create the store procedure
you are not going to be able to modify
the structure of the query and that is
what the defense against SQL injection
is SQL injection is trying to modify the
structure of the query to get to act in
ways that uh the developers did not
intend so by having the sort procedures
here already Shi into the databases it
is uh not something that can be have
sequel injection done against it and he
did say to use the parameters uh
functionality of this and that uh
doesn't have the arrow here but it's a
cs. set strain one cust name uh what
that is essentially going to do is send
in a customer's name as data
specifically not as part of a query so
we had that one right and that was 1999
essentially two years later this
organization came into existence OAS
open web application security project
um they worked for a number of years to
provide guidance and for how to test
applications for SQL for all sorts of
web application security problems
including SQL injection as well as
guides for how to defend against them
and in 2005 they released a guide at
black hat uh called a guides to building
secure web applications and web services
they offered four pieces of advice here
uh parameterized store procedures uh and
that is echoing uh rainforest puppy top
recommendation then prepared statements
omms for example hybernate uh those are
object relational MPP mapping systems
and then Dynamic queries with uh
escaping user
input so parameterized store procedures
we've already talked about these
um just going to skip that by to
prepared statements prepared statements
uh are a very similar to store
procedures except instead of shipping
the databas the code into the database
it's going to be stored in the
application um they also do something
where you are setting the query portion
of the uh query in stone and then you
are sending in the separate data uh
points to be modified so you can see
that here in the uh Tech second line of
code string query equals and that is
essentially setting the query portion in
stone the next line is preparing the
statement and telling SQL this is the
query part anything else you see after
this point is not going to be part of
the query and cannot modify it and then
finally we're seeing inserting the cust
name
there uh object relational mappings I'll
go over that in a bit but right now uh
you need to know that they are basically
automatically bind the applications
object data uh to the database databases
representation of that same data so for
example in this right here you see that
the um we see a SQL query that a
programmer would normally have to add to
an application but if they were using an
or system they would all they have to do
is take the cat's name uh and modify the
object uh for that cat I guess uh and
that would automatically flow down into
the
database and finally they say uh if you
need to you can do Dynamic sequ queries
with escaping user
input um then in October 2007 the most
important piece of guidance for sequel
injection prevention came out uh on the
security blog
XKCD little Bobby
tables uh little Bobby tables is one of
by far the most popular xcd Comics out
there and it was one of the first ones
to really go viral in the uh programmer
blogos
sphere uh it is still heavily referenced
I think it's a legal requirement to
mention it during presentations on SQL
injection and it is gone a long way to
educate people about what SQL injections
are what their impact is and also how
you can fix them or prevent
them uh fast forwarding fast forwarding
uh to this year uh we see new OAS
guidance for how to prevent SQL
injections uh so now the first option
they say to do is to use prepared
statements with parameterized queries
this is interesting because it's now
number one recommendation versus number
two that it was in
2005 uh now they recommending the use of
properly constructed store
procedures uh we'll get into why that
verb has changed a little bit later and
then they offered a new Option called
allow list input validation which is uh
an interesting approach uh and finally
they say escaping all user
input so prepared statements are number
one uh these are going to
be uh highly recommended these days uh
for one of two main reasons one star
procedures are a pain for other people
to uh for developers to deploy they
cause a lot of headaches uh and it's
generally not seen as worth the pain
these
days uh so that's one of the reasons
next one is that uh SQL in I mean store
procedures don't actually prevent SQL
injections uh previous guidance almost
made them seem like a Magic Shield that
as long as the code was inside a store
procedure um uh it was preventing SQL
injection and that's not the
case and that's why and that's why it's
called properly constructed store
procedures now um the main way to do SQL
injections is doing the the dynamic SQL
queries with uh string concatenation and
those are still things you can do in
store procedures I've seen far too many
of that so the call out now is properly
constructed store procedures and they
are explicit in the recommend ations to
uh not do Dynamic SQL queries inside the
S procedures unless absolutely
necessary uh and to use the
parameterized version and that's
essentially where you have the uh data
points explicitly called out for what
can be modified and SQL knows that they
cannot modify the query
itself next up is allow list input
validation which is a new one and it is
essentially in the situation where where
you need user input to be modifying the
database uh query structure somehow this
could be like uh if you have a table
that swaps out uh in a query based on
user input or if you want to change the
sort order in some kind of uh table view
from uh ascending to
descending and they say that in this
situation you should take in the user
input that would normally cause this and
check it against an allow list and and
then instead of sending in the user
input if it passes the allow list send
in another uh value that is not related
to the user input and this way is a way
to prevent the user input from actually
reaching the query and putting up a wall
there uh they also recommend this as a
secondary line of defense for any of the
other types of uh SQL injections uh
prevention
strategies
um and they do End by saying that if you
do have to use this this you should
seriously consider re architecting your
application because this should not
happen in a good code
base so after the recommended re
architect in your
system they say to option for is dynamic
queries with escaping input they are
explicit this is only as a last resort
when none of the other ones are
feasible uh this is a frail methodology
that's likely to break it's very bug
written uh and there's a lot of research
on how to bypass this sort of thing
and also because of all these things
they cannot guarantee the prevention of
all SQL injections you can kind of
imagine
the
uh Dynamic queries with escaping input
is like putting up a a chain link fence
around a query to prevent people from
getting in and just checking to see if
they have like special characters in the
uh the data
whereas uh doing something like prepared
statements instead of a chain link fence
it's a stone wall you're probably not
going to be able to get through that
whereas a chain link fence there are
ways through
it or at least much cheaper
ways um so I mentioned earlier that
penetration testers are not seeing SQL
injections as often uh anymore they have
become uh a bit of a
rarity
uh
and there were a number of reasons for
that that I'm going to go over uh here
so first was developer knowledge they've
been around for like 20 years at this
point 20 23 years um oasp has existed
for a lot of the time they have been uh
very
um Vigilant about teaching people about
this uh developers are now sharing the
strategies Etc we also saw the rise of
the object relational mappers these are
now basically the default uh way of
interacting with data they're extremely
easy to use and unfortunately the second
the security aspects of them are
basically secondary to why developers
use them but it's a happy coincidence
that they prevent a lot of SQL injection
by not requiring writing your own
queries next where the waffs web
application firewalls uh these basically
sit between the end users and the
applications that they are running um
and they prevent any attacks that they
they try to prevent attacks based on
pattern recognition these were actually
first me mentioned in PCI DSS in 2004 so
they were already pretty popular back
then and they were uh popular because
there a way to bolt on a layer of
security to Applications you couldn't
actually modify uh for example if you
bought some
functionality the growth didn't really
explode until the cloud came off came
out and which point it was essentially a
uh checkbox to enable it and that gave
you a pretty good set of rules until it
caus a production outage and they turn
it off in
frustration moving on microservices
microservices uh was a new architectural
pattern for databas uh software
architecture that started in the 2000s
2010s and instead of having one giant
application one huge database behind it
um where the order history table is
right next to the user preferences table
we instead have a number of small
applications each with their own
databases and what this is going to do
is uh because the users preferences
database is tables in a separate
database and the order history it
prevents the access uh from reaching it
so this is essentially uh enforcing
architecturally the principle of these
Privileges and this limits the blast Rus
SQL
injection um no SQL came into being I'm
not going to talk about that so uh
disappearance of SQL injection came up
for these
reasons um SQL no SQL did have a rise in
popularity so there was just less of
that uh for that reason
and there is one less question though
and that's actually if SQL injections
did
disappear uh the organiz organization
miter
um keeps track of the cve database
common vulnerabilities and exposures
which lists all the all the software
security vulnerabilities in applications
and as part of this they did a report uh
in 2022 to see what uh was the most
common weaknesses found in these STS and
SQL injection was third
place uh and this was actually up from
place so why aren't pentesters seeing
this anymore and why is this still the
third most common vulnerability um found
in the cve data set I think it's because
of a number of reasons uh one is that
the SQL injections um and the pentesters
view of them the pent testers are
testing more modern applications
generally speaking uh this be modern
software as of service things that
aren't going to be reflected inside the
cve data set and it's going to be
um
more uh or long lasting applications
that have had many years to fix all our
SQL vulnerability sequal injection
vulnerabilities in them we also see a
Divergence in what the uh pentesters are
doing versus what's in the CV data set
which is going to be mostly like open
source libraries products that you can
upgrade physical uh Hardware products or
a number of other things of that variety
that pentesters just aren't really
looking at these days uh partially due
to uh economics
um companies getting pent tests are
going to be either have uh more security
mindset to think through these things
and hopefully prevent them or they're
going to also have uh Regulatory
Compliance they have to follow through
uh that I'm very grateful
for
so um that was my
presentation um if you have any
questions uh you should really consider
asking OAS first they know a lot more
than I do uh but otherwise I'll probably
be over there after this
talk thank you very
[Applause]
much