So a few videos ago we changed our
content providers query function to use
appendWhereEscapeString rather than
appendWhere. Now I didn't go into a lot
of detail about this function because we
couldn't test the code at that stage, but
we finished the previous video by seeing
that appendWhere worked, but there was a
problem using appendWhereEscapeString.
Obviously it's not quite as
straightforward as just swapping one
function out for the other. So there's
something going on with the escape
String version of appendWhere, and it
would be really useful to log the SQL
that it produces to see what's going on.
Unfortunately though, we're using a query
Builder, and the query builder class does
not provide a function for accessing the
SQL that it creates. Now when you find
yourself in a situation like this, it's
worth having a look at the source code
for the classes that you're using. So in
this case we're interested in the query
Builder source code, which we can get by
control clicking on the query function
called in our code - so right down at the
end of the query function that we've
added to this AppProvider. So I'm going to
ctrl click that, or command if you're on
a Mac. You can see here now that we've
got to the source of the query function
that we're calling, and it doesn't really
do much. Now as this is a Kotlin course,
I'll very briefly talk about overloading
functions in Java. Now that's not
something we do in Kotlin, because Kotlin
provides default values for function
arguments. Java, on the other hand, doesn't
let you do that. Instead Java lets you
create different versions of the same
function or method, as it's Java with
different arguments. That's called
overloading the method. Now the query
method we're using is an overloaded
version of another query method. Now this
one doesn't do much - it just calls the
one it's overloading and returns the
result of calling that. Now if we
actually can click on the query function
that it's calling - this one here - we can
see the source for that one on-screen
now. Now this version of the query is
slightly different. It's got an extra
cancellation signal argument right at
the end of the parameter list. Now the
function we call just sets that argument
to null. In Kotlin we'd use a default
value of null, rather than overloading
the function. In Java however, you declare
the method again with the additional
parameter. Now that's a bit more
verbose, but that's all that's meant by
method overloading in Java. so watch out
for this when you're reading the Java
source code. There'll often be different
versions of the same method, and often
the one you will be using will call
another version with different
parameters. Alright so let's dig
in and see what this particular method's
doing. Now when I'm reading the Android
sources, I don't try to understand
everything in fine detail, at least not
at first.
Now and then you'll hit a problem that's
complex enough that you have to check
the source very carefully. Often though, a
quick scan of the methods and comments
is enough. Now this particular method's
quite straightforward. Firstly, if no
tables were specified it returns null,
right at the start of the method there -
you can see line 376. That makes sense.
We're not going to get anything back if
we haven't specified any tables to query.
The next bit of code down here, starting
on line 379, deals with the escaping to
prevent injection attacks. So you can see
that it's calling a validateSql
method there on line 389, and it's
calling that method of the SQLite
Database class. And that method will
throw an exception if anything dodgy is
detected. The comment gives that away - the
end of the line there, "will throw if query is
invalid". So far so good, but what happens
next? Well it builds up a query string.
You can see it's starting to do that on
line 392, and it's building up that query
string from the arguments that were
passed in. Now that string's very similar
to the sequel that it validated. So read
the comment to understand why it builds
the query string twice. So as it says up
here,
"An attacker can't create an expression
that would escape the SQL expression
while maintaining balance parentheses in
both the wrapped and original forms." So
basically, if an injection attack is
tried, this code will either throw an
exception when validating the SQL
string, or it'll attempt to execute an invalid
string that the database will reject. So
it's not possible for both the escaped
and original sequel to be valid in the
case of an attempted attack. So the
result is that an
attack string will either cause an exception
to be thrown by the validate single
method, or it'll cause an error when the
database tried to execute the SQL.
Whichever happens though, the attack
won't work. But with that said, we're
not here to understand the fine details
of how escaping works. We're trying to
see if there's anything that can help us
to work out why our query failed. So
scrolling down a little bit,
notice the code on line 396. That's
pretty interesting. After building the
original SQL string in the variable
SQL, that method's actually logging it. Now
it does things slightly different to how
we've been logging. Now we use debug
level logging, and rely on those log
calls being stripped out when we build
the release version of the app. But in a
library class like this one, stripping
out the debug logging calls would make
life very hard for people using the
class - us in other words. So to prevent
all those debug log entries from filling
up the logcat and slowing the app down,
this code checks if debug logging is
enabled and only calls Log.d if it is. So
at this point, yay we've struck gold. We
want to log the SQL and this method
is already doing it for us. So all we
have to do is work out how to tell
Android to enable debug logging for this
particular class. As it turns out it's
very easy to do, so let's do that. Now
what we'll need first is the tag that
the class is using, so I'm going to come
over here and click on the tag, and I'm
going to double click on the contents of
that tag. In this case it's set to SQL
iteQueryBuilder. I'm going to take a copy
of that, making sure that I didn't
include the double quotes when I've
copied it. We just want the tags value,
not a delimited Java string. Alright so
to enable logging at the debug level for
this tag, we can use adb to set a
property on the device or emulator. So
I'm going to open the terminal window, and I'm
going to type adb shell setprop log dot
tag dot. Then I'm going to paste in what I
copied from the Java source code,
in this case eSQLiteQueryBuilder space
then DEBUG in uppercase, press enter. Now
the properties name is log dot tag dot, and
then the tag that we're interested in as
you saw there, which I've pasted into the
terminal.
Now the class is logging the SQL at
the debug level, so that's why I've used
debug as the property value. And it's
actually as easy as that. If a class in the
Android framework does some logging, you
can enable it by setting the property
using adb shell. Now there was another
way to do that by creating a prop file
on the emulator, but as many students
are using their phones to run these apps,
I prefer not making a change to the
phone itself. So this method that we've
done here doesn't make any permanent
change, but what it does mean is that you
have to enter the command again every
time the device - the emulator - is
restarted. Now if you are using a
physical device for this course, reboot
it after this video to put the logging
back to how it was. Alright, so with all
that said, does it work? Well at the end
of the previous video we changed the
code so that it worked. So I'm going to
exit out of terminal now, and we'll
go back. We'll close down some of
these files now, we don't need
them anymore, or just that one file. So
looking at the code, we changed it back
to appendWhere on line 80, as you can see
there. So it's using that rather than
appendWhereEscapeString. So let's run the
app first and see what the valid SQL
looks like, because we know that that
that's working.
Alright so there's the debug coming from
SQLiteQueryBuilder, as you can see
there. So that obviously worked - the
command, the adb shell setprop command
that we used - and you can see it's using
a SELECT Name comma SortOrder FROM
Tasks WHERE, then parentheses underscore ID
equals 2 ORDER BY SortOrder. No surprises
there, and what I could have done to
get, to find that quickly, I could have
just done a filter on
logcat, sqlite query builder or just do an
sqlite to find that really
quickly. If you're having trouble that's one
way to quickly find it, but again, no
surprise there. That's valid SQL and
of course the app was actually working.
Alright, so let's now change the code
back to use the appendWhereEscape
String and see what went wrong. So what
I'm going to do is duplicate that line
initially, then I'm going to comment that
out. Then I'm going to set the second line to
EscapeString. So let's stop the app and
start it again.
Run it, then we'll swing over to the
logcat and see what it's done. Actually I'll open
the logcat again. You can see now that's
the cause of our problem. Looking at the
WHERE clause we've got parenthesis id,
underscore id equals two in single quotes.
We only want the value 2 here to be
escaped. We don't want the entire
conditioning quotes as showing at the
moment, so that's the reason why we're
not getting any data back. So to fix this
we can use appendWhere for the column
name, then appendWhereEscapeString for
the value - in this case our taskId. So let's
have a go at doing that. So I'm going to
uncomment this again, the appendWhere. I'm
going to change that now so it's just
got, in double quotes, dollar left and
right curly braces TaskContract dot
Columns.ID, then right
curly brace equals. I'm going to delete
that last part there. So we've just got
the equals and a space, and then let's
fix the next line that's already
got the appendWhereEscapeString, and I'm
going to delete everything other than
the dollar taskId. So you can see that
we're just escaping now, effectively, the
taskId. Everything else is just a
general appendWhere on the previous
line. So now that we've done that,
let's stop this and start it again, swing
over to our logcat.
And you can see now we've got some valid
SQL: SELECT Name comma SortOrder FROM
Tasks WHERE, and in parentheses underscore
id equals, then the value here in single
quotes 2. So that's now correct, and if I
actually delete this filter now, we've
got rows in returned cursor equals 1 and
we've actually got our records showing
on the screen there. Now we added the
appendWhereEscapeString to the code a
few videos ago, but trying to explain all
this when we couldn't run the app would
have been confusing. Seeing the different
SQL that it produces makes it much
easier to see how to use it correctly. So
you use appendWhereEscapeString to
append the values, not to append an
entire WHERE clause.
Alright, so now that we've got that fixed
I'll just close down logcat, and let's
make the same changes to the commented
out code for the TIMINGS and TASK
DURATIONS, because if we don't do it now
it'll be too easy to forget to do it
later. So what I'm going to do is take a
copy of that first line, change that to
just the appendWhere. I'm going to
delete after the equal signs, equal sign,
up and to the double quote. I'll put a
space after there. Then the second line,
I'm going to delete everything but, in
the double quotes rather, everything but
the $timingId, and we'll do the
same for the durations, TASK_DURATIONS_
ID. Change the first call to appendWhere,
in double quotes,
delete after the equals sign. Then leave
the appendWhereEscapeString as the
second call, deleting everything but in
this case, the $durationId. So that
should hopefully work when it comes time
to use that code. Now with that said, it's
quite easy to make a mistake when typing
code into a comment, obviously because
there's no syntax checking. But I'd much
rather have a minor mistake in there and
have to correct it when we uncomment the
code, rather than forgetting to use
EscapeString correctly. Alright, so at
this point queries against our database
using the AppProvider class are working
now. In the next video we'll finish off
the Content Provider, writing the code to
insert new rows, as well as updating rows
and deleting existing ones. So I'll see
you in the next video.