Detailed Notes
It may seem like a beginner question, however we often need to find a value in a database and do something with it. This brief video shows you how to leverage your MySQL/MariaDB database connection to create a query that avoids SQL injection attacks and gets a value based on a lookup (where clause) to pass back to your program.
You can find out more through our online classes at https://school.develpreneur.com and register for free. Registration will add you to our email list and you will periodically receive coupons for courses as well as notifications of the latest releases.
Transcript Text
foreign [Music] well welcome back we are continuing looking at our shorty application we're going to take a URL and we're going to do a shortener for it so if you give me some big nasty thing like for example this one I'm going to give you something simpler like this that you can use to get back to that now we early on built some functionality around it and now we're working on the database side so specifically this time we're going to get into basically we're going to do inserting a record into a table and then how do you search a table for a record now last time around let's see I'm going to clean these up a little bit so we don't have it all over the place so we created a table we created a database whoops don't need that and we create a database and we have some nice little utility functions floating around up as well if I can click right so what we're going to do this time this first thing we want to do is and here's our little thing we're going to come in we're going to create our class that stores our database related stuff set the database name for it we could actually put it up here but we're going to do it this way we're going to go ahead and connect to our database to connect we don't need to create a table because we did it in the past and then here we just had some extra stuff to list tables and look at that but now this time let's do we're going to do list records and we're going to give it a table name oh we're going to give a table name and I'm gonna have to write this one and the the table is going to be links that's what we created what if we go all the way up here yep links with the lowercase so let's do this first let's create that and uh let's see list rows is that what I called it I called it records Let's Do List rows so you're gonna get a little bonus stuff here uh and let's see here whoop don't mean that but I do need this oh open that up I want it to be the table name and then here I'm going to just simply do it this we're going to do select star from and then I'm just going to do table name this is really simple we there are better ways we can do this but we're going to go ahead and do it this way I'll show you the better ways in a moment we're going to execute it we're going to get all the rows we're going to print each row and we're just going to print the whole row because we're going to get possibly a bunch of information and there we go so now if we run list rows right now we've got that what we're going to see is nothing let's do it this way let's do print uh rows for the table and so we have that table but note we have no rows so let's create a few and the way we do that is we're going to have three values that we're worried about that ID we if you want to flash back but we have an ID that just gets Auto generated so we don't have to worry about that the other three values that we have that we're tracking in our table are original link code and user ID code is basically how we're going to get our link back so what we need to do is we just need to send the link the code and the user in it's going to insert that record you insert it we've seen this before we're going to do a little try except around it but we're going to say hey insert into links you give it in order the columns that you're going to be inserting into which we're going to do the table names are original link code and user ID and this is where we're going to get a little bit smarter about how we do stuff is we're going to so in order to avoid SQL injection attacks and things like that there are ways to protect your parameters a little bit in this case we're going to do is we're going to say hey we have column one two and three which is original link coding user ID that means we're going to send value which is a string so percent s means we're going to send a string another string and another string also comma separated within the sequel and so now what we're going to do is we're going to have this basically the data that we're going to call it we're going to have a tuple we'll call it that and we're going to take this link code and user that we sent those values can be put into that data which means link is the first one is going to go to this percent as code it's going to go to this one user is going to go to this third one we're going to execute it we're going to grab the ID of the row that we created which is in this case just last row ID because it's going to be whatever the last row was it was that we did a a some sort of a transaction on we're going to go ahead and commit which we actually want to do that beforehand let's do it that way and then we're going to return the new ID so let's do a very simple record we're going to come into my ID oh let's do it before we do list rows so here we're going to do my DB and we're going to where do we put that insert record links and it's going to be the original link the code and the user ID so let's go look over here and let's see in our little uh wait Where'd I have it here we go so let's do this one so let's say this is my big link so that's the first value I'm going to send is my big link and then my user id I'll just be a one it doesn't really matter what it is I mean it does in the long run right now it doesn't but then I'm going to give it uh this is going to be my ID one two three four five six I want so let's do one two three four five six and those are all strings actually that string doesn't have to be because it'll convert it actually we'll see let's go check that if I don't make that a string if I make that a number and note it's bright python if you haven't noticed we can swap double in single quotes as we want if I come in here and I do a DB create boom now I see rows for the table the ID is one here's that big long URL here's the resulting or the code that I'm looking for and then the user ID so that gives us the first part of our question that we're working on this time is actually getting set up for how do I search a table for record so now what I want to do because what we're going to end up doing is in the retrieve in the navigate we're going to give it a URL and then it's going to need to spit out what was the what was the bigger you know you get the short URL what's the bigger one so what we're going to do here is sort of the same is we're going to get one that we're going to call call it retrieve link and all we need to do here is send it the code we could send a user as well we're going to send it the code for now so this was going to be a little different we don't need a new I uh well let's call this result and let's just call this link not found and then here we want to select the original link because that's all we really care about now from whoops different lengths and it's not K SQL is not case sensitive by the way and then we're going to do where or code equals percent s and now our Tuple is just the code let's see and then we want to this time this is like we're doing with the listing so now we're going to execute it and instead we're going to do is rows equals cursor Dot fetchall should be one and actually we could do that we can say fetch one because it better be just one row and so let's do this row equals that and then we're going to do let's see Crystal close we're going to return the result in here the result is going to be able to let's just call it fetch one zero I'm going to take that first item out well let's do it this way we're going to do this um let's do this first because we're going to clean this up a little bit so retrieve White and we're going to present the result so we're going to come here we don't need to insert a record again so we're going to do list rows which is great and then let's do this let's do first let's retrieve blank four uh what do we call that one two three four five six and then print that so we're just going to call this search one and then we're going to do search two let's do it this way let's just pick a different number and let's see what we get from these two so now we have an error in our SQL syntax uh data table equals code oh I need to do it sort of like this um it's like this I think it's going to do if that works there we go so uh let's see let me do it this way it says a little easier to read so the first time we come through we search and guess what would get the uh oh here's rows for the table I'm sorry then we come back and we're going to get the result which is this one because we used one two three four five six and we're gonna get that link back Second Time Around we got none we didn't get anything there was no record found so where did we do that in the retrieve um so it did link not found oh here it's going to be so that would be if something totally broke but otherwise we came through and it says hey there was nothing found what we can do here is um we can say if result then result equals result zero so let me sort of clean this up a little bit if we do that now we get just the full bone link back so what we've been able to do is we've shown a little bit extra we've gone the extra mile a little bit here we've shown how to insert a record and how to get a record back we're basically doing it just you know whatever value you want to look on look for we're going to do it and this is for directly we're going to get that record back now we could do rows instead of fetch one we could do fetch all in which case then we would want to do something like let's do rows equals that and we could do four row in rows result equals row and that assumes that there's going to be one and if we do it this way oh and row now we get this so now we're going to come out and it says that in this case if nothing's found it's just going to leave that original result which says link not found otherwise we're going to get our link out and there's other things you do you could do select star and get all the data like we did with our select but this gives us use that where and now we can get some information out and we can see how to wheel and deal with such information as well so that takes care of us this time we're going to come back answer a few more questions as we continue working forward on our application thank you for your time and we'll catch you again next time around hello this is Rob with developmentor also known as building better developers wanted to announce that we have school.developmentor.com feel free to check it out if you like any of this information any of the content that we've sent and you would like to see more you can come out you can enroll for free we have free courses we've got places for you to get better at just learning a technology or how to's you can work on your business skills we can help you with becoming a better developer as encoding and things like that a lot of the stuff you've seen on YouTube we also have out at school.develop anywhere always have it a little more of a educational format and a way for you to track your progress as you move forward becoming a better developer thank you
Transcript Segments
foreign
[Music]
well welcome back we are continuing
looking at our shorty application we're
going to take a URL and we're going to
do a shortener for it so if you give me
some big nasty thing like for example
this one
I'm going to give you something simpler
like this that you can use to get back
to that now we early on built some
functionality around it and now we're
working on the database side so
specifically this time we're going to
get into basically we're going to do
inserting a record into a table and then
how do you search a table for a record
now last time around let's see I'm going
to clean these up a little bit so we
don't have it all over the place so we
created a table
we created a database whoops don't need
that and we create a database and we
have some nice little utility functions
floating around up as well if I can
click right so what we're going to do
this time this first thing we want to do
is and here's our little thing we're
going to come in
we're going to create our class that
stores our database related stuff set
the database name for it we could
actually put it up here but we're going
to do it this way we're going to go
ahead and connect to our database to
connect we don't need to create a table
because we did it in the past and then
here we just had some extra stuff to
list tables and look at that but now
this time
let's do we're going to do list records
and we're going to give it a table name
oh we're going to give a table name and
I'm gonna have to write this one and the
the table is going to be links
that's what we created
what
if we go all the way up here yep links
with the lowercase
so let's do this first let's create that
and
uh let's see list rows is that what I
called it
I called it records Let's Do List rows
so you're gonna get a little bonus stuff
here uh and let's see here whoop don't
mean that but I do need this
oh open that up
I want it to be the table name
and then here I'm going to just simply
do it this we're going to do select star
from
and then I'm just going to do table name
this is really simple we there are
better ways we can do this but we're
going to go ahead and do it this way
I'll show you the better ways in a
moment
we're going to execute it we're going to
get all the rows we're going to print
each row and we're just going to print
the whole row because we're going to get
possibly a bunch of information
and there we go
so now
if we run list rows right now we've got
that
what we're going to see is nothing let's
do it this way let's do print
uh
rows for the table
and so we have that table but note we
have no rows so
let's create a few and the way we do
that is we're going to have
three values that we're worried about
that ID we if you want to flash back but
we have an ID that just gets Auto
generated so we don't have to worry
about that
the other three values that we have that
we're tracking in our table are original
link code and user ID
code is basically how we're going to get
our link back so what we need to do is
we just need to send the link the code
and the user in it's going to insert
that record
you insert it we've seen this before
we're going to do a little try except
around it but we're going to say hey
insert into links you give it in order
the columns that you're going to be
inserting into which we're going to do
the table names are original link code
and user ID and this is where we're
going to get a little bit smarter about
how we do stuff is we're going to so in
order to avoid
SQL injection attacks and things like
that
there are ways to protect your
parameters a little bit
in this case we're going to do is we're
going to say hey we have
column one two and three which is
original link coding user ID that means
we're going to send value which is a
string so percent s means we're going to
send a string
another string and another string also
comma separated within the sequel
and so now what we're going to do is
we're going to have this basically the
data that we're going to call it we're
going to have a tuple we'll call it that
and we're going to take this link code
and user that we sent those values can
be put into that data which means link
is the first one is going to go to this
percent as code it's going to go to this
one user is going to go to this third
one
we're going to execute it
we're going to grab the ID of the row
that we created which is in this case
just last row ID
because it's going to be whatever the
last row was it was that we did a a some
sort of a transaction on
we're going to go ahead and commit which
we actually want to do that beforehand
let's do it that way and then we're
going to return the new ID
so let's do a very simple record we're
going to come into my ID oh let's do it
before we do list rows
so here we're going to do my DB
and we're going to where do we put that
insert record links
and it's going to be the original link
the code and the user ID so let's go
look over here
and let's see in our little
uh wait Where'd I have it here we go so
let's do this one
so let's say this is my big link
so that's the first value I'm going to
send is my big link and then my user id
I'll just be a one it doesn't really
matter what it is I mean it does in the
long run right now it doesn't but then
I'm going to give it uh this is going to
be my ID one two three four five six
I want
so let's do
one two three four five six and those
are all strings actually that
string doesn't have to be because it'll
convert it actually we'll see let's go
check that if I don't make that a string
if I make that a number
and note it's bright python if you
haven't noticed we can swap double in
single quotes as we want if I come in
here and I do a DB create boom now I see
rows for the table the ID is one here's
that big long URL
here's the resulting or the code that
I'm looking for and then the user ID
so
that gives us the first part of our
question that we're working on this time
is actually getting set up for how do I
search a table for record so now what I
want to do because what we're going to
end up doing
is in the retrieve in the navigate
we're going to give it a URL
and then it's going to need to spit out
what was the what was the bigger you
know you get the short URL what's the
bigger one so what we're going to do
here is sort of the same
is we're going to get
one that we're going to call
call it retrieve link
and all we need to do here is send it
the code we could send a user as well
we're going to send it the code for now
so this was going to be a little
different we don't need a new I uh
well let's call this
result
and let's just call this link not found
and then here
we want to select
the original link because that's all we
really care about now
from
whoops
different lengths and it's not K SQL is
not case sensitive by the way
and then we're going to do where
or code
equals percent s
and now
our Tuple is just the code
let's see and then we want to
this time this is like we're doing with
the listing so now we're going to
execute it and instead we're going to do
is rows equals cursor
Dot fetchall
should be one and actually we could do
that we can say fetch one
because it better be just one row
and so let's do this row equals that
and then we're going to do let's see
Crystal close we're going to return the
result
in here
the result
is going to be able to let's just call
it fetch one zero
I'm going to take that first item out
well let's do it this way we're going to
do this
um let's do this first because we're
going to clean this up a little bit
so retrieve White
and we're going to present the result so
we're going to come here we don't need
to insert a record again
so we're going to do list rows which is
great
and then let's do this let's do
first let's retrieve blank four
uh what do we call that one two three
four five six
and then
print that
so we're just going to call this search
one
and then we're going to do search two
let's do it this way
let's just pick a different number
and let's see what we get from these two
so now
we have an error in our SQL syntax
uh data table equals
code
oh I need to do it sort of like this
um
it's like this I think it's
going to do
if that works there we go
so
uh let's see let me do it this way it
says a little easier to read
so the first time we come through we
search
and guess what
would get the uh
oh here's rows for the table I'm sorry
then we come back and we're going to get
the result which is this one because we
used one two three four five six and
we're gonna get that link back Second
Time Around we got none we didn't get
anything there was no record found
so where did we do that in the retrieve
um so it did link not found
oh
here it's going to be so
that would be if something totally broke
but otherwise we came through and it
says hey there was nothing found what we
can do here
is
um
we can say if
result
then result equals result zero so let me
sort of clean this up a little bit if we
do that now we get just the full bone
link back
so what we've been able to do is we've
shown a little bit extra we've gone the
extra mile a little bit here we've shown
how to insert a record and how to get a
record back we're basically doing it
just you know whatever value you want to
look on look for we're going to do it
and this is for directly
we're going to get that record back now
we could do rows
instead of fetch one we could do fetch
all
in which case then we would want to do
something like let's do rows
equals that and we could do four row in
rows
result equals row and that assumes that
there's going to be one
and if we do it this way
oh and row
now we get this so now we're going to
come out and it says that in this case
if nothing's found it's just going to
leave that original result which says
link not found otherwise we're going to
get our link out and there's other
things you do you could do select star
and get all the data like we did with
our select but this gives us use that
where and now we can get some
information out and we can see how to
wheel and deal with such information as
well so that takes care of us this time
we're going to come back answer a few
more questions as we continue working
forward on our application thank you for
your time and we'll catch you again next
time around
hello this is Rob with developmentor
also known as building better developers
wanted to announce that we have
school.developmentor.com feel free to
check it out if you like any of this
information any of the content that
we've sent and you would like to see
more you can come out you can enroll for
free we have free courses we've got
places for you to get better at just
learning a technology or how to's you
can work on your business skills we can
help you with becoming a better
developer as encoding and things like
that a lot of the stuff you've seen on
YouTube we also have out at
school.develop anywhere always have it a
little more of a educational format and
a way for you to track your progress as
you move forward becoming a better
developer
thank you