All right. Welcome back. This is Section 8. I'm Chris Gerber. Tonight I'm going to be talking about SQL injection. Cross-site scripting. Cross-site request forgery. The exec command. And all sorts of things that you can do wrong and how we can protect ourselves against these things. David just covered a lot of this. But tonight we'll actually get to play with some code that I've written so that you can actually see these problems happening live. As a starting point, I've already posted my code. You'll see it on the website tomorrow. But if you're following along at home, you can actually clone my code down right now to follow along. Get clone, it's up on bitbucket under my account, and it's section8.git. All right. So the first topic we're going to talk about is SQL injection. So we've got a snippet of code here. And I might ask, what looks like could possibly be wrong with this code? Any thoughts? [ Background Sounds ] Sure. [ Inaudible Comment ] So it's going to match the first row for the username and password, but what's wrong there? [ Background Sounds ] Nothing? [ Inaudible Comment ] Okay. So the 1 is actually something different here. The 1 just says, return the value 1 specifically, in a column called 1 coincidentally, if this record is found. But I think I had another possibility. 

Yes. Can someone potentially input, insert some SQL code inside the, one of the variables? 

Exactly. So the problem here is I'm not doing anything to protect these two variables, user and password. So someone could actually start injecting SQL code there. So what if password was set to quote or quote quote equals quote? Basically, telling me that I would append in another option here, the "or." Quote quote always equals quote quote. So this would always return true. So that gets interesting. The other one that's interesting is if user happened to be quote semicolon drop table users. Suddenly my entire users' table could be dropped from the database. So now we're actually going to see how this stuff works, which is, I think, a lot of fun. So I've got the rest of this code here. [ Background Sounds ] And this should basically look familiar. I'm going to show you the two exploits as we go through. But they're there in the comments if you want to try these at home later. We're connecting to the database. We're making sure the parameters are set. And then we get to the command we just looked at, here. And if we have success, we're going to return true from this function so that we can printout either successful login or bad username or password. And before I show you this, I also want to show you what our tables look like. They're relatively straightforward here. Everything's under this jharvard section 8. It's covered up, but it is, trust me it's Section 8. And the users' table has some values in it already. So I've just got these two columns, id and password. I've just put them in in plain text as another example of something you might not want to do. But makes it easier to demonstrate here tonight. And I've got these four rows in the table. The other table that I want to point out real quickly is I've created this second table called "test." Which is one column called "id." It's a number. Auto increment. It's just the fact that this table exists right now that's important. Now let's actually try to break things. So I'll go out to our website. Section 8 slash, and let's first try with an incorrect password just to see what things should look like. So the URL's a little tiny on the view here. But, basically, the parameters I've passed in are Chris and a password of BADPWD for bad password. My password in the database is the word "password." So it's come back and told me, no, this is not your username and password. So the next thing we want to confirm for proper functionality is if I put in the proper password. So I'll change the URL, and I'll change my password to "password." And you'll see now that, instead of bad username or password, we actually are getting a successful login. So this is the way things are supposed to work. Now, let's start exploring. Let's try that quote or quote quote equals quote. 

Is there any way to zoom in on things like that or not? The URL's tiny. 

I don't think so. It was in the previous slides. I hoping they can sort it out. 

Okay. 

All right. So after entering that, you'll see that we're still getting a successful login even though we shouldn't be. This is obviously not my password. And it's specifically because we've now injected this short bit of code that says, no matter what happened with these two ends, if quote quote equals quote quote, which will always be true, then this should return a 1. And we get our successful login. So this is not good. People can login sort of willy-nilly at this point. But we haven't done anything destructive yet. So the next step is to go a little further. Since we do have access to anything we do want to do in SQL, we can actually do, close the quote. And then add a semicolon to close the command. And then add the next command that we want, which in this case was drop table. And instead of users, I'm just going to drop table test. Just so I don't completely destroy my database while we're working here. And we can run that command. It tells me that I've got a bad username or password, but I'm not too concerned about that. What I'm more concerned about is when I come back to phpMyAdmin and refresh jharvard section 8, I now have four tables listed instead of five. You can see on the left I haven't refreshed this. Test used to be there. But now the test table is completely gone from my database, which is obviously not good at all. [ Background Sounds ] So that's a little bit about SQL injection. Next, I want to jump over and talk a little bit about cross-site scripting. So looking at this code, where might I have gone wrong in this example? Looking at code where I'm preparing a statement. So I've gotten a little better. I'm using a prepared statement now. I'm going to insert a record into a table called "comments." It's just a single value, which is the comment. Then I'm going to go back later, and I'm going to select those comments. And I'm apparently printing table rows out to the browser as I'm redisplaying the information that's in the database. Any thoughts on what might be wrong here? [ Background Sounds ] All right. So where things are going to start going off the rails in this case, I'm taking whatever the user has given me. Which we'll see where it's coming in. But anything the user is giving me and writing it directly into the database. I haven't checked what I'm writing to the database. It could be anything. And then I'm pulling it directly out of the database and writing it directly out into my HTML. Without checking for things like is there HTML formatting that's actually in there? Things like that. So this basic just dollar sign comment 1 here without an HTML special [inaudible] is where things are going to get bad. So given that, what if my comment is, for example, as I think David has shown before, script alert hacked close script. And we can start to look at some examples here as well. So I've got another script file here, xss.php. You can see I've got four examples that we'll get into. But, basically, we're just connecting to the database. I'm starting a session. To make it a little more explicit as to how things could really go bad, I'm actually going to store the user and the password in cookies as well. And then I'm just, as we had seen, going to insert the record into the database. And, sorry, if it's a post, I'm going to insert the record into the database. Otherwise, or even if it was a post, I'm going to display a table that has all the comments that had appeared in just this very short little page. So let's switch over. Let's see what the database looks like real quick for this. I've got two databases that I want to show you. One is this comments database. This is where we're going to store the comments for our real website. It's just got an id, auto incremented and then the comment is a text field. So just very simple, straightforward approach to storing comments in the database. The other table that I've create is one called "cookies." And we'll see what this is shortly. Same idea, it's just two columns an id, auto incrementing and cookie, which is apparently a text field. So let's go over to the actual web page. [ Background Sounds ] So it's a very simple page. At the top is the current comments. I've got a header on my table, but there's no rows. So there's not much to see at this point. But now I can add a new comment and hit submit. So I could, assuming this was a normal [inaudible] order, something like that, I might send out just a hello. And now that's in the database and is displaying in the comments table. Things like that are fine. The next person come in, comes in, hi. And the comments keep building up. But what if we start thinking a little more deviously. What if we do script alert hacked, as we had seen in the comments at the top of the source code. So this gets interesting. You'll see a very thin, little row appeared between hello and hi. Nothing happened. We didn't get the dialogue box. But what we did get in this very fine print down here is a message from Chrome, where Chrome is actually trying to help me out. It says specifically, "Refused to execute a Javascript script. Source code of script found within request." So Chrome is smart enough to realize that I just posted this bit of Javascript code out to the site. It's probably not good that it's coming right back to me. Now, unfortunately, it only checks the first time that you've submitted that. If I refresh the page now, I think I might, I left a break point in my code. Sorry about that. The dialogue box actually pops up. Now, that's still not too bad. I've created a dialogue box. But what if we got a little more interesting with that dialogue box? What if instead we alerted the actual document cookie? So we get our old dialogue box. Then Chrome picks up our mistake. But if we refresh, we're actually getting my actual cookie. The session ID. My user ID. And my password, all displayed directly to the screen. [ Background Sounds ] Now, that's okay, but we're telling the end user what this information is. And that's probably not as interesting. So I wrote another piece of code that I want to bring up for you really quickly here. It's a file called log.php. This is part 2 of my attack. This is going to take whatever it's passed, essentially, and just insert it into that cookies table in my database. [ Background Sounds ] And that is the entire program. Connect to the database. Write out whatever's passed to it. So as an example of that, I could do log.php?cookie equals test. No obvious display. But if I go to actually look in my database now. [ Background Sounds ] Did it go? [ Background Sounds ] I managed to break my code. I'll make sure this is fixed before I publish it back up to the git repository. What we should be seeing at this point is it should be writing just whatever was passed to it directly out. Oh, I know what the problem is. I renamed my parameter from cookie to X. Was it X or Q? It is X. [ Background Sounds ] Yeah. So now you can see that we do have a record in the database, where it's just written that test directly out for us. Much better. So going back to our xss.php. [ Background Sounds ] And get past that break point. I've got my two previous alerts. We could add a new item. And we'll also get a little more devious here. I've leave it here so that you can actually see the whole thing from the moment before I enter it. I'm going to actually put the word "hello." So it's not obvious that there's not just a blank row in the comment table anymore. But then I'm also going to inject this actual script here. And I'm going to use this one, X equals new XMLHttp request x.open get. And you might start to look at this and start to realize that this is sort of the Ajax approach that we had talked about just a lecture ago. I'm going to send out a get request to the section 8 slash log.php. And I'm actually going to pass the document.cookie in as a parameter. And I'm going to send that. So let's go ahead and add that to our database as well. We still have our old tool alerts. But we also have "hello" as our comment. Nothing visually incorrect with what's going on here. But if we look in our database again. [ Background Sounds ] Cookies. What did I do wrong? [ Background Sounds ] [ Inaudible Comment ] [ Background Sounds ] I did nothing wrong. Once again, Chrome has picked up that this was the first time I submitted that. And it has refused to run that bit of code. Because it knows what I'm doing is bad. And it's trying to protect myself, protect me from myself. Which is good until I refresh the page. Or someone else comes and views the page. First alert. Second alert. The comment happens. And, actually, this time, instead of that alert, in the fine print down here you'll actually see a response that tells me that my Ajax called completed successfully. Which is good news for me as the hacker. Bad news for you. And it's actually performed this right to my log at this point. So if we come back over and browse again, you'll see that we now have row 10, which has stored by PHPSESSID, as well as my username and my password in this separate database for me. Now, I took this one step further, and I was very interested to see where this went. I then created a second virtual host. So we've got section 8 that we're working out of at the moment. To make this a little more realistic, you'll see that I've also got another one here called "badguy" to create a completely separate host. [ Background Sounds ] And I've put this log function in here as well. And if you look, I've actually just used a link. So it's pointing to the exact same piece of code so that we know I haven't magically swapped anything out here. In fact, you could even just look right at the file and see it's the exact same file as we were looking at. But let's now take the same approach. [ Background Sounds ] But instead of just going to my server, let's see what happens when we go out to bad guys server. If I, let's say I'm trying to actually now write this back to a server that I own, being the bad guy in this case. [ Background Sounds ] One alert. Two alert. No alert. Refused to execute. Reload just to make sure that we know what's going on here. One alert. Two alert. We've actually gotten a new error here, which makes me happy because Chrome is, once again, looking out for me. I've got in the very fine print, XMLHttp request cannot load, badguy, slash, et cetera. Origin section eight is not allowed by access control allow origin. So, basically, what Chrome has realized here is that we're trying to take data from this one origin and write it into this other server in an uncontrolled manner. So it is in this case actually protecting me from doing the damage of writing this out to the database. [ Background Sounds ] All right. [ Background Sounds ] Now let's look into cross-site request forgery. So I've got an example here where this is some sort of stock transactions. We're trying to write into a portfolio the values of ID symbol and shares. The ID I'm getting right from the session. So there must be a cookie prepared or a PHP session prepared that's throwing the ID of the individual. And I'm getting the symbol and the shares right from a get request. Anyone have any concerns about this code? [ Background Sounds ] Well, what if I visit a page with this? So this is on another site. It's got script source equals, this is apparently coming out of buy.php at this point. Symbol equals GOOG shares equals 1000 close script. So this is going to be a piece of code that's embedded into someone else's page, not my page, and is going to try to buy shares on my behalf. So we could take a look at this. So the first thing I want to do, actually, let's, is show you the tables that we're looking at. Just so you have a sense. We'll go to portfolios. And let's look at the structure first just to make it clear. We've got the three fields that we're expecting. The ID of the individual. The symbol of the stock. And then the number of shares that's going to be stored. [ Background Sounds ] And currently this user 1, apparently, owns a hundred shares of Apple stock. So that's our baseline for where we're starting from here. [ Background Sounds ] A normal user might come along and go to buy.php. [ Background Sounds ] Oh, actually, let me show you this first. Buy.php, that's what my note was. [ Background Sounds ] This looks pretty much like what we're expecting so far. We've got the database connection. We've got the title on our page. [ Background Sounds ] If we're receiving through the get request, the symbol and shares, we're actually going to do an insert into our database. And tell the user that we bought those shares. [ Background Sounds ] The other file that goes with this is portfolio.php. This is little bit more, but relatively straightforward here. What this is going to be is a display of the shares that the user currently owns. So we connect to the database. We're starting the session. We're apparently forcing the ID to 1, just as a way to make this code work very quickly. I've got a function I can call to print the table of the shares that the user owns. And then this all appears within a very small page with the title portfolio and just shows you the current portfolio. [ Background Sounds ] The last file I want to preview is this actual attack. This is hosted potentially somewhere else. We'll actually demonstrate it in, you can either do it from the Section 8 site. Or I've also put this under bad guy as well, so you can test it from there. It tells you it's obviously a completely benign website, trust them. And then it uses two different approaches to try to trigger the purchasing of shares. One using a script tag. One using an image tag. As David had mentioned, there's many ways you can do this. And these are two possible ones to run code without the user having to do anything. All right. So the first thing I'm going to do is I'm actually going to close the window and start a new one. So deleting my current session. And we're going to go right to this attempted exploit to start things off. And we're going to do it on our site, not search Google for that. [ Background Sounds ] All right. So the page has appeared. It just says, this is my completely benign website. We do see that broken image. So that's one thing. But we don't see any indication around the script line that was in there. So if you were doing this yourself, you might not want to use the image tag. Because it might be a visual indicator. But just to show you the two approaches and what things might like look. So the one thing that's good at this point is I hadn't actually started my session yet. So there was no cookie in place to hijack at this point. So if we come back and browse again, you'll see there's been no change. We still just own a hundred shares of Apple stock. So off to a good start. Now we can take a look at what normal functionality would look like. So we can go to portfolio.php. And as we expect, it just shows our hundred shares of Apple stock. So things are still going well. We could now buy some shares. So we could buy. [ Background Sounds ] There it is. In this case I'm choosing symbol equals MSFT for Microsoft. And shares equals 200. And congratulations, you've just bought 200 shares of Microsoft. We didn't check your balances or anything. So this is great. We get free stock. And if we go back to the portfolio, we'll see those changes reflected. So we now have these two stocks. So everything is working well. But now we can go back to the exploit. The cookie is still live. I'm going off to some other page. And all we see here is some plain text and a broken image link. But now, if we go over to the portfolio, we'll see that, without the user doing anything, we've actually injected the shares of Google and Yahoo directly into their account for them. [ Background Sounds ] Now, we could also try this from bad guy, htp bad guy. [ Background Sounds ] So my font got very small. But it's otherwise exactly the same page. And if we go back to our main portfolio site, fortunately this time it didn't buy the shares for us. And I neglected to bring up the bottom. We should have seen that Chrome was understanding what was going on here and preventing that particular attack for us. So I'll leave that as an exercise so you have a good excuse to go home and play with some of this stuff when you get home. [ Background Sounds ] All right. [ Background Sounds ] So I've got one more thing that I wanted to demonstrate. And that's the exec command in php. Exec is basically the same as the backticks, as David has shown. In this very short example, I'm taking a command that's being passed in as a post parameter. So we feel like it should be a little bit safer. And then I'm just executing it and capturing the output. So what's bad about this? So many things are bad about this. [ Background Sounds ] So what if I do something like this? So W get, I don't think we've talked about. This is actually a command line tool that let's you download a web page. The minus Q zero minus or dash Q zero dash, all that's going to do is display the output to the screen rather than saving this to a file, just for convenience. I'm going to give it the web page, but what's interesting is I can actually pass in post data to this request right from this command line tool. So I can create a post of command equals who am I? [ Background Sounds ] So let's see what all this starts to look like. [ Background Sounds ] Here's the full code for what we're looking at with the exploits at the top. Same part that we saw where we're checking if the command was set. If so, we're going to execute it and capture the output. Otherwise the output's just going to be an empty array, just to be a placeholder. And then we're going to go down, and there's a very short page here. One thing that I had thought I was doing that was smart, I've limited the number of options there are. So it should only be the date command. The cal command. Or the ls command. But it's submitted through a post. And it's really just saying, take that value and do command equals whatever the value is, date, cal or ls. And when I hit submit, it goes. And then the last command is output out to the display. So we can look at this running as we expect, exec.php. It gives us a drop down of the available commands. So I could say, I want to know the date. Hit submit. And when I hit submit, it comes back and says, it's Saturday. I don't know how that happened but sounds great. We should all go home. We also can do things like the calendar. So I can see a current calendar for the month of July. Or I can do an ls command and see the files that I've been presenting here tonight. So this all works fine, and life is good there. But now we can actually jump into the terminal and test some of this alternate functionality. So using my W get command, we're going to go out to the site. Section 8. Exec.php. And we're going to pass in. [ Background Sounds ] Command equals who am I, for example. And what did I do wrong? [ Background Sounds ] All right. I was close. It's not Q zero. It's QO. My printout didn't differentiate my O's from my zeros. But you can see the output from the page. And tucked away under output of last command, we can see that jharvard was the result of that who am I command. Now, taking this another step forward. I might try a command like you name minus A. And now I'm going to learn everything about the locally running system. So this is apparently a Linux box. It's running 3.4.4-5.fc17.i686 symmetric multiprocessing. Time stamp. It's a 386 style architecture. Lots of good information here. Now, as we had talked about in lecture, as you start knowing things about, you know, a server is running Apache. A server is running php version 5.,et cetera. Additional information, like knowing what platform is actually running and what user name is being used, gives me a lot more opportunities to create attack vectors. So we might not be thinking about a web based attack at that point. But the more we can learn about the system, the more opportunities we have to attempt to do something evil against the system. So just the fact that this is completely unchecked. And any command could be run on the platform that is available to the user that's currently logged in is certainly something to be concerned about. So where does this leave us? A few things that you should really be remembering. We've told you these things through the semester. Hopefully, now that you've seen them in action, there's a little more reality as to why they could be a problem. You should always escape your SQL parameters. We talked early on about my SQL real escape string. We then later showed you how to use PDO so you could do prepare, bind, value and exec to handle those sort of issues. Any time you're going to escape, display something as HTML, you should escape that as well. So use your HTML special chars. You should confirm important transactions. So here we were able to buy stock. As David's talked about in class, Amazon requires you to authenticate again. Other sites use capture. Things like that. You should look for ways to prevent these malicious sites from tricking the user into performing an action using an existing session cookie. And the last one I wanted to point out is that post parameters are definitely not necessarily any safer than get. It adds a slight layer of inconvenience. But anyone who's really trying to get into your site using these parameters can readily construct a post request manually. So those are my thoughts for this evening. Are there any questions with any of the things we covered tonight? Yes. [ Inaudible Question ] Yeah. So the question was, through the SQL injection attack, is it possible to reveal the structure of the database? And that certainly is possible. I should know the command off the top of my head. But believe it's something along the line of show tables. But certainly, yeah, you've got access to any SQL command. So anything that potentially, depending on your permissions, that a DBA would have access to, would be available to you as well. Any other questions tonight? All right. Well, I hope everyone has gotten their Project 1 turned in. I hope things are going well with Project 2. We'll have folks around tonight to handle office hours. And I believe there's also an online section tonight? Yep. So the information for that would be posted on the website. Good luck with the projects. We've got one more lecture and one more section. So we look forward to seeing you Monday. And hope you have a good time coding over the weekend. Thanks everyone.