Is that cost money?

If you appreciate this education, please think about going to Eli the computer guy dot com and offering a one time or monthly recurring donation.

Welcome back.

As you know, I am the line the computer guy, and in today's class, I'm going to be showing you how to use.

Prepare to statements in PHP in order to insert records into Europe.

My sequel database tables Now up until this point, have been showing you how to insert records into your my secret database tables using Ph piece code simply by using the variables to plug in the values in my sequel statements and then sending that the entire my sequel statement.

So basically what happens is the P A P code parses and creates a my sequel statement and that full, my sequel statement is sent to the my sequel database server in my secret database server, then reads it and then process is basically whatever sequel that we have presented to it now.

Normally, if you don't have to worry about things like hackers, that's perfectly fine.

We send the entire statement the bicycle database server parses the statement.

As long as everything is correct things air process probably, if not an error, is returned.

No big deal, normal computing process.

But unfortunately, unfortunately, we've gotta worry about those hackers.

We have to worry about those hackers.

And so one of the things to be thinking about that we send the entire my sequel statement and you have something like an HTML form that the user is able to input data.

That data is then created as a value that for variable, that value is then put into the sequel statement.

And then the whole sequel statement is sent to my secret database server.

One of the questions that has to be Asa's Well, what if you have a hacker?

What do you have?

A hacker that knows a little bit about how my sequel works?

And so basically, what they do is they can actually put in text to escape out of the sequel statement that is supposed to be sent to the my sequel Database server and then actually insert their own ah sequel statement and order to do any any number of nefarious things on the My sequel database server.

Remember that my single database server.

It's simply going to parse the statement that it is presented with.

So again, if there was a former, if there's some other kind of input option where the user is able to offer something to the PHP code and they know how that PHP code is going to write that my sequel statement what they can try to do is they can try to escape out of the sequel statement that is being presented to the server and then basically insert their own sequel statement on then have them icicle database server parts.

That and then when.

When that happens again, any number of issues could can can occur.

Tables can be deleted, tables or databases can be exported.

Can be back up to, you know, off site servers, all kinds of fancy things.

So basically, we're talking about sequel injection attacks.

What we're talking about is the end user somehow is able to inject the sequel statements into what is supposed to be just a normal, innocuous transaction with the server, and then, by doing that, they're able to spend have some control of the server to do any number of things, and so that's a problem.

And the problem there is is that we're sending the entire sequel statement.

So basically again with the PHP code, what happens is we plug in the sequel statement and then there's dollar sign.

And what if the variable name is?

And so all that happens is the value is put wherever the variable s stated and in that whole statement ascent and into my single database server horses, that whole statement which could cause the problems.

So what prepared statements?

It was It was kind of an interesting way of doing.

It is what happens is you create a template for the sequel statement.

So basically, you created a template for the sequel statement and you you put in these little question marks to say where our values go.

That is incentive to the my secret out of a server.

And then after that, the values that go where those question marks was supposed to be our then sent.

And then what happens is my sequel, then just plugs in the values in where it's supposed to go into the sequel statement.

And then if somebody tries to inject any kind of code, basically all that's going to happen is an error, right?

Because the template, the template for the sequel statement first and then after that.

And then after that, the values are supposed to better supposed get plugged in our scent.

And so, basically, if those values are bad values again, there are escape characters or whatever else they just get flushed out and there's an error or some other thing occurs.

Basically, you don't have to worry about the sequel injection, where the end user that hacker tries to take over your my sequel database server.

So what I'm gonna be showing you how to do today is prepared statements.

It's relatively easy.

It's relatively simple.

They're no big warning warnings for today.

The only thing that I will tell you, especially if you are new to using PHP and my sequel and all that kind of stuff, is that when we do do these prepared statements, we add a couple of levels of complexity for being able to insert records.

And you're my secret database table so normal again, we just create the sequel statement Dollar sign SQL.

You know, whatever that sequel statement is, we just plug it in and everything is relatively simple with prepared statements is not a whole lot harder.

It's on a whole lot harder, but there are a couple of steps and you do have to be careful to make sure you do all those steps or you can run into some problems.

You can run into some errors.

So with that, let's go over the computer and I'll show you how these prepared statements work.

So here we are, back at my lab machine again.

I've got a bunch of desktop 18.4 Lt s running, but really anyone to desktop should work for you.

I have this running in a virtual machine in virtual box and this is running on my Mac book pro in order to get the full lamp stack Alexa Packet, my sequel in PHP.

I used a tool called Task Cell ta s k s E.

L that installed the unfold lamp stack onto the Sorbonne to desktop.

I have not modified any of the default options PHP that I and I v hosts all that are exactly the same.

The only thing that I've done is I've created a folder in the Apache Root directory called PHP, Just as a simple place to dump all of my PHP virus scripts.

A star is a text editor goes I am using G edit.

I'm not using any fancy I d.

E or anything, because again, when I'm doing simple Cody and I'd like to show you folks that yes, you really can use just a normal text editor.

Others g edit whether it's, uh, note pad, whether it's text, edit, whatever else.

So this is the system that we're dealing with.

So the first thing has always let's go over to our database server so we know what's going on.

So we're gonna get a terminal.

If I could spell today Terminal, we're going to log into my sequel.

Maya Segal Space Siphon, You usernames a siphon p for password.

123456 Now we're into my sequel were used class Thebe.

So Class Devi is the database that we're using for all of these labs.

Semi Colon, of course, once weren't here again.

We do show tables just so we know what tables were dealing with.

We have a whole bunch of tables in here.

On the table that will be dealing with today is the students table.

Just verify what's going on with students Table Do the EOE SC describe students semi colon s That shows us we have a student Underscore.

I d Field is a primary key.

It's an auto increment and it isn't interference again.

This is just our record record field.

We have a name.

We haven't age with a gender and we have a uniform field.

Name is text Age is an entity or a gender is text and uniforms Text We d'oh d'oh select Oh, from students, students.

If I could spell correctly today, uh, semi colon, we can see we have a whole bucket records in here.

At this point, we're all the way down to 34 records.

Last four, Senator was Lewis, 21 years old.

Is the boy does not have a uniform, eh?

So let's go over and take a look at the form that we're going to be the only with today s.

So this is an H basically html form.

So, yes, You understand what this little project looks like to go over the Firefox?

Oh, just go.

1 27.0 point 0.1.

This is Luke back address for its last PHP to get in that PHP folder.

And then what we want is the statement for So we created this the atrium out forum called statement form.

And so what this is gonna do is gonna ask for a name, is gonna ask for aid and is going to ask for a gender on.

Then you're going to be able to submit that and basically insert record.

So let's just take a look a form for a second.

This is just a completely normal a female form.

Nothing in here Open a female open body we have formed.

The action equals this nasty ass name.

Don't do it.

I d'oh, don't Don't do this.

You ever have those times when you just like trying to figure out the name for something and then the stupid thing is way too along anyways, so PHP prepared statement insert not PHP.

Basically, the name, age and gender is going to be sent to a script with that stupid name.

That's way too long.

Don't name anything like that, and it is going to be a post method.

So if you've been following along with the other classes again, this is just a normal HTML form.

Asked her name.

As for Nate, against her gender name.

Text.

It's named name.

Age.

Also the text box.

It's named age.

Gender.

This option box.

It's name gender default.

Value is nothing.

11 option is boy.

One option is girl on.

Then there's just simply submit button closed body close.

Html s.

So now we're gonna go over and actually take a look at this.

Nasty.

Yeah.

I don't name anything like this.

I don't know what I was thinking.

PHP prepared statement.

Insert up.

Horrible.

Now, anyways, let's take a look at this.

And a lot of this actually looks pretty close to everything that we've seen before.

That's okay.

The statement form is going to send the name, the age and the gender to the script.

We're gonna open up the pH be script.

We don't have to create the variables variables on the variable values that we're going to be inserting into the bicycle database table.

So we create dollar sign, name, dollar sign, a dollar sign, gender, these air going to equal the post values.

So post our dollar sign under sky are underscore Post on, then name a dollar sign on your post A dollar sign Underscore post gender.

So we've done this in other projects at this point, this is simply grabbing the value of these names that come from the form name a gender were then assigning those valley used to the new variables.

We've created a pH be here so that we can interact with him in PHP.

The next code here is the same code we've seen 1000 times of this 10000.78 years in a password and database.

So it's a local host.

So that's just a local host user name of Bob Password.

I want you to 456 database of Class B.

D.

B has just showed you We're then going to create a connection like we normally do.

So dollar sign common equals a new my sequelae.

We're gonna pass a servant and we're gonna pass the user name.

We're gonna pass Password, eh?

We're gonna pass the database.

There are closed parentheses, and of course, we do semicolon because this is P H.

P.

We're then going to do the if the connection doesn't work.

So for some reason, there's a connection error.

You misspelled the user name.

You screwed up the password.

Something like that.

Basically, dollar sign connection.

If there is an error kill, kill the connection, say the connection failed and then say whatever the air is same stuff as we've seen 1000 times this point.

Now, now down here is where we actually get to the interesting stuff s O.

The first thing that we're going to do is we're going to create our statements.

So this is the prepared A statement itself basically created a variable, just like we created the connection variable up here.

And so I just decided to call this a dollar sign a statement.

So you know what it is?

And so dollars science statement equals.

So cut the connection.

Dollar sign con.

So this whole thing on, then you're gonna do this little era symbols?

We're done for a few things in the past, and then we're going to say, prepare for creating that prepared statement.

We then d'oh parentheses.

So this is all going to be inside of parentheses.

They were going to do the double quotation marks, and then we're simply going to be the prepared statement.

So we're doing an insert so in search into students, same as we've seen to support parentheses.

Column names Name, age, gender So again saying is you've seen before.

Close parentheses, values same abuses as you've seen before.

Here's where it gets to be different.

We do question Mark calm a question mark comma question mark.

So these these are the three com.