so I mentioned towards the end of the
last video that the way we're building our
query strings makes a database
vulnerable to hacking attempts but
rather than explaining why further going
to actually demonstrate it
so what we're going to do is that the
user enter a song title in the console
and then we're going to call the query
song infoview method to get the
information for the title now in the
main method which we are in at the moment
we're going to prompt the user for the
title then we'll use that title rather
than a hard-coded one so what I'm going to do
is just change that a little bit here
just up here we're going to do...
....
....let just run that
title just go over to here
ok that's going to be.....
....as you can see there and we are
getting results back we're getting the
results of the Fleetwood Mac 4 albums
that particular songs on
alright so now we're going to do
something a little bit different going
to just demonstrate how you can actually
get into trouble for the song title this
time going to type in....
...
....lets press enter and you
can see what happened it suddenly we've
got
every row of the artist_list
view being returned to us go right up to
the top again
and you can see what happened with the
sql code there where title was
go your own way or 1 equals 1 or double-quote
double-quote so what we've done with
just performed what's known as a sql
injection attack now in this particular
case perhaps it doesn't matter if a user
gets to see all the songs in a view but
what happens if this was a view or table
of user IDs and passwords for example or
worse still it contained a credit card
details or other sensitive financial or
even medical information so that
wouldn't be a good thing
so how did this actually happen well the
thing was when we built the sql
statement we are blindly concatenating
whatever the users typed so when we
entered the above string the code built
the following sql statement which I
just mentioned here so select name album
track from artists_list where
title equals go your own way in double
quotes or 1 equals 1 or 2 double
quotes now since 1 equals 1 will be true
for every record all the records in the
artist underscore list view are returned
so this is called a sql injection
attacked because the user has injected
sql into the statement that we didn't
intend to run now the user scenario
would usually be a hacker and they try
this because they know something about
how applications that use databases are
coded they guess that we could be
concatenated the name into a where
clause as we're doing here and since
it's a string they guess that when
concatenating a closing quote which is why
they end their input string with a quote
now a malicious user can even do
something like drop tables since we're
using jdbc we do have some protection
against that at least when using the
sqlite jdbc driver the execute
and execute query methods won't run more
than a single sql statement so in
other words a user can't tack on
something like semicolon drop table
songs but if an application written in
the language like perhaps PHP was
running sql directly based on user
input rather than going through an API
that malicious user would be able to
execute whatever sql statements they
wanted so instead of building up query
strings and using this
statement class to execute them you should
really be using the prepared statement
class now prepared statements can
protect against sql injection attacks
because when we use them we don't concatenate
user input into the sql
statement that will ultimately be
running so let's look at an example now
as I've noted the only thing that
changes between queries is the song
title now I mentioned in a previous
video that it would be nice if we could
have placeholders in sql statements
for the values that change from query
to query and prepared statements allow us
to do just that
let's go back to our data source class and
we're going to add a constant at the top
of the class start down here on line 88
that's going to be...
...
...so the only difference here
between this constant and the one we
used before is that we're using a
question mark for the song title so this
is the placeholder character that we use
in prepared statements when we're ready
to perform the query will replace the
placeholder with the actual title we
want to query the sql statement
we are building is...
...
....so note more
importantly we don't put quotation marks
around the song title tal when replacing a
placeholder in a prepared statement the
database actually understands that a
string like going your own way is one
value so we only substitute a single
value for each placeholder and what we
also need to do is to declare an instance
variable for the prepared statement and
that's because we only want to create it
once we don't want to create it
everytime query because we only want to
be precompiled once now if we were to
create a new instance every time we did
a query we lose the performance benefit
that the prepared statement has so let's
add a prepared statement instance
variables .....
....now
we're going to look at putting that in
our open now we could check for null in
the query song info view method
and create the instance there but
instead that I'm going to do is create
the instance in the open method because
i think that's a better place to do it
so we're going to do...
...
...so that
we call the connection . prepared
statement method to create the instance
of prepared statement passing it the
sql we want to execute and that
sql remember contains the
placeholders this is the code up here on
line 90 that will be replaced every
time we use the statement to make a
query so now that we've done that let's
update the query method to use the
prepared statement now note that the
query was using a view of course
everything about prepared statements
also applies to tables
as well so we find our query song . info method
down to the bottom
so this one here the query song info view
alright so lets changes now instead of
doing a try with resources will go back to
regular try....
....
...and will fix that
shortly that is complaining about
the sql exception so we need to
handle that shortly we're going to just
copy this code down here which is all going to be
essentially the same I'm
going to cut all that and paste it.....
...
....
ok and that should be...
okay that's better what I can also do
now is fix that we don't need to string
builder anymore so i'm going to delete
all that and we're going to change this
to just be title....title being
name of the song has passed this method and
we're now using the prepared statement
we are setting the title as the first
parameter in the query for the prepared
statement and then you can see that I've
just removed a lot of the redundant code
now and went back to the old try-catch
so because total here on line 270
is a parameter is a string using here
on line 273 using the set string
method so it's got other methods
you can use....
and different values depending on the
type of variable using their....
...and we also have to specify the
position of the placeholder we want to
replace and that's because they can be
more than one and that's usually when
working with jdbc the position is one
based so again we see that set string 1
title we are referring back after the sql
code
referring to the first occurrence of an
equal in that code so if we have a
bit of code something like this....
obviously the first one will be set string
will be one the second one will be 2
the third-place will be three in
our case we only just one of the
string i'm using set string 1 to actually
change or replace total with that ? or
the contents of the ? replacement title
ok other than that the method is
actually the same so note that the
prepared statement which were using here
on line 273 is a subclass of statement so
in other words it's got all the other
methods that statement has such as
execute that's why we're able to use
this execute query on line 274 now
we're no longer declaring the resources
as part of the try statement because i
changed it so consequently what we need
to do now is put a finally clause in
there to close them
remember that I mention that if we
closed a statement any associated result
sets are also closed and i also
mentioned that there can only be one
active resultset associated with a
statement now if we use a statement to
do more than one query which is a very
common with prepared statements each
time we need to do a new query the existing
result set is closed and a new one has
created so the upshot of all this is
that we don't have to worry about
closing the result set when using the
prepared statement when we close the
prepared statement
whichever results it is active will also
be closed and we should also closed
prepared statement in the close method
as opposed to closing it in this
particular method let's go back to our
close method so here we're doing a
test to see whether it's the
connection can close successfully so we're
going to put some code...
....
here the order is important we can't
close the connection first because we
need an open connection to close
statements basically we always close
resources in the reverse order to which
they're opened now we don't have to
change the main method since it doesn't
know or care about what type of
statement
we're using to get the data let's try
running this to confirm the it
works okay so we'll try typing search
again go your own way again we're got an error
lets have a look what we done and I
think I made a mistake in the data
source lets go back and check that
out
this will be in the method that we just
write down the bottom here....
....
....and look at that made a mistake
they're actually got the query we are
setting the query song info view .set
string here on line 278 so therefore
on line 279 we don't want to pass a query
so that should actually be empty
you've already got a prepared statement
its pre-compiled it's already got the
code in their and that should be empty
because we've already specified the
title that's probably all that was
crashing let's go back and run that
again
ok right away this time we've got to
work but importantly now let's go back
and run it again and presume that this
time we are a hacker will try enter that same
code we did last time so that was a go
you're own way and it was a double quote
...
....
....press enter this time we get
a different message couldn't find the
artist for the song so we get the
messages you can see they're saying that
it couldn't find the song and no data
was returned
you might be wondering at this point
well how did the prepared statement
protect the database against the sql
injection attack if the string is simply
being substituted for the ? would we see
the same results as before well we don't
and that's because when we're using a
prepared statement the values being
substituted are treated as literal
values in other words nothing within the
value is treated as sql when we were
using a string builder to be build the
query statement and concatenate the
title effectively this is what was passed to
the database and not executed just going
to copy the code here so effectively that
scenario this is what was actually sent
to the database that was in the first
scenario string builder but in the second
scenario we actually get different
results in the second scenario prepared
statement this is what we're seeing so
you can see the difference that in the
second scenario what they've type in is
basically been substituted for the
song title that's really just as double
quote here that's making all the
difference and the databases is
being searched for song that equals out in
its entirety since there's no song in
the database with the title going your own way
or 1 equals 1 or double quote
no records were returned in other words
a malicious user can't inject sql into
the statement because anything
substituted for a placeholder is treated
as a single literal value and won't be
interpreted as sql so let's end
the video here but we will talk a bit
more about the prepared statements and
this further in the next video