 I guess we'll slowly work our way into the talk. I did everyone enjoy lunch. Anyone go out, grab some barbecue? I think you had barbecue here. Anyone go external? No? Anyone grab a coffee? Like this coffee? You guys are not giving me anything. Anyone grab a coffee? No? This guy grabbed a coffee. Nice. So many lifetimes ago, I worked as a barista at a popular coffee shop. And it was a mid-sized neighborhood store. There was a mix of traffic coming from the surrounding homes and business. And we were actually right across the street from one of the big game development studios. And business would just skyrocket anytime a new Call of Duty game was supposed to launch. And it was just a nice mix of customers. And most of them were great. But there was one. I mean, there's always one. And her name was Susie. And this name has been changed to protect her, even though she's totally guilty. Susie would come in three to four times a week. And she always had the exact same order. A double espresso, please. And I'd make small talk as we finish the transaction. Oh, how's it going, Susie? Oh, you know, not so well. And she'd launch into some tale of woe things that just weren't going well. Now, mind you, she was in three to four times a week. And it was always the same story. I swear she never had a good day in her entire life. It was just always the same deal. But then, right as I had hand her the change, she'd perk up a bit. And she'd say, but you know, if you made that a triple latte instead of a double espresso, my day would be a whole lot better. Every single time. The problem was that a double espresso cost about this much. A triple latte, with its extra shot and milk, well, it cost this much. And she would wait until the exact moment that that register closed, signaling that the transaction had officially ended before she tried to sneak something else in there. And that brings us to the topic, safety and security in coffee shops. OK, maybe not, but it's not quite right. Probably something more for a Java conference. Giving an active record talk have to do a pun in honor of Tinder love. So actually, we are a room full of mostly web developers. And I'll just say, I looked all over the internet to find the least creepy spider I could find, because I was not going to be surrounded on all sides by creepiness. So that's like a very friendly spider. And so like this very friendly spider, we're very friendly web developers. And maybe we want to talk about security in web apps instead. And at the risk of stretching that coffee shop metaphor just a bit too far, I'd like to argue that our web apps have a vulnerability that's actually very similar to the one that Susie was exploiting. And that's SQL injection. Now, I'll go into more detail in a bit, but in brief, SQL injection is when someone closes out a legitimate transaction with your database, and then immediately tries to sneak a little bit more SQL in there so that they can interact with your database and walk off with something a whole lot more valuable than coffee. And I know what some of you are thinking. We are web developers. There is nothing more valuable than coffee. But in this case, there is. I'd argue that your customer's private data is actually slightly more valuable than coffee. So when we talk about security vulnerabilities, I think it's more common to think that we're talking about something new, a vulnerability that was maybe just discovered in the past few weeks, something that's going to be on the nightly news, something like the Heartbleed, where it's announced publicly the very same day that a patch is released, because it's just that bad. But SQL injection is old enough to vote, at least in the US. I haven't checked everyone else's laws, don't know the ages, but in the United States, SQL injection could vote. It's 18 years old. It was first mentioned by Jeff Forestall in an online hacker magazine called Frack. And you can tell that's 90s graphics right there. That's actually still the logo on the website, did not update. So Jeff Forestall, who went by the hacker name of Rainforest Puppy, and I'd like to pause here for a moment, because when you start reading about SQL injection and security and all of those things, you come across a lot of hacker names. And I realized I haven't got a hacker name. And I did what any web developer would do. I went to Google and I Googled hacker name generators. And I started trying out a few different options, including one dubious generator that insisted it needed to know my mother's made a name and where I was born in order to generate my hacker name. And I thought, nice try and moved on to the next hacker generator. And after probably a bit too long on this, I was finally the proud owner of this doozy. And you know that's a legitimate hacker name, because that is a four. That's not an A. So back to my fellow hacker, Rainforest Puppy. He had discovered that SQL databases, which were just at the time starting to replace the more popular access database, they allowed batch commands. And so what that meant was that you opened up one interaction with the database and you could do one command. But with batch commands, you open up that connection to the database a single time and you can just send more and more and more requests in there. And that's not a big deal, because multitasking is a good thing, right? I mean, let's say I want to take a look at all of my employees. This is a fine looking group of employees. But say that I want to select just the ones that are developers. So just these two. And then I want to sum up or find the average of their chips consumed. And because I like to be inclusive, we could also average their chips consumed if you speak one of those other Englishes, whatever. But whether it's these chips consumed or those chips consumed, it's pretty high and we'll leave it at that. And because you can put more than one command in, you can just open up that database opening once and run those extra queries. And in a closed system, that is all well and good, because you're not going to attack your own database. But when you have input coming in from outside, when you have outside user data heading straight into your database, things can get a tiny bit dicey. So let's say a shady character, maybe like this guy, wants to use your innocent little web form to gain access to parts of your database that you don't want them to have. And they can do that by piggybacking their own SQL command onto your intended request, sort of like this. You have a SQL command and you give them the chance to, you provide them the ability to search for a restaurant where they can do sort of a match with the name. And so you select star from restaurants where name is like and then you add in the user input. And your user, Shady McShadester, searches for the following restaurant, which honestly does not sound like they're gonna have the best quality food to me, but you know, whatever, this is what they search for. And this is the query you actually end up with. So you're selecting star from your restaurant table where name is like and that actually closes out that name, that select statement, and then starts delete from restaurants. And that double dash there is a SQL command, which means everything after that is commented out. And so what you've done here is you've selected nothing from your restaurant table because they didn't give anything, it's just like an empty string. And then they delete every single record in your restaurant table. And that is SQL injection. Your entire restaurant table is gone. And I'm not talking about your table at the restaurant, it's your restaurant table in your database. It's actually still there, it's just completely empty, but you should also probably cancel this table because you're gonna spend the rest of the night backing up and restoring your database. And you're all doing database backups, right? Yeah, no? So it's not the scope of this talk, but if you're not, you should be because stuff happens. And yeah, it's just a good idea. So let's talk OWASP, the open web application security project. This group has, they only get together, and I think every three years they update their top 10 list of web vulnerabilities. And SQL injection, it's been out for 18 years. It's an old vulnerability, and yet it regularly ranks at the top of their 10 most critical web application security risks. And that's not really a top 10 list that you wanna be at the top of. And honestly, if you look at a recent scan of the news, you will find so many examples of companies, big and small, that have kind of seen SQL injection from the bad side. For instance, Sony Pictures. In 2011, Sony Pictures lost the data of over one million users, including their passwords, which were reportedly stored in plain text. So that really hurts. And you might think, oh, well, whatever, it's like Sony Pictures, big deal, but most people use the same password, and now you've got the password that the people use in their bank and all this stuff, and they can just go from site to site, build a profile on these people, and just steal even more information because you did something that you should not have and stored passwords in plain text. In December, sorry, October of 2012, hackers use SQL injection to get the personal records of thousands of students from over 53 universities around the world, including major universities like Harvard, Stanford, Princeton. And the reason they like students, like universities are actually a pretty big attack vector which I didn't realize they were such a target, and that's because students tend to have a wonderful combination of really clean credit records and they don't pay attention to their credit score. So you just have quite a few years of being able to totally trash their credit histories before their student loans do it for them. And that's, I guess, the one thing that we can say is good about crushing student debt is that by the time you graduate, no one wants to steal your identity anymore. All right, we're not done yet. In August of 2014, Russian hackers stole 1.2 billion, that's with a B, username and password combinations, and 500,000 email addresses from 400,000 websites. And these weren't all just like little mom and pop sites. Most of these were Fortune 500 sites. They're not sites made by your cousins, best friends, uncles, kids down the street who sort of knows how to develop a website. These were made by like professional developers like the people sitting in this room. And it wasn't pulled off by some, like elite gang of movie hackers. It was a group of less than a dozen men living in a small town in Russia that actually got their start doing email spam. And one new guy moved into town and was like, I can show you how to do SQL injection. And they became like the kings of SQL injection. And then I've got one more for you. This is actually mentioned in an earlier talk. And it's VTech, which is an electronic toy company. And they lost the data of over 5 million parents. So like email addresses, phone numbers, home addresses, because people had registered their products. They also lost the data of 200,000 children. Now this was just first names and email addresses. So it doesn't seem as severe until you realize that the data dump included the ability to link the parents with the kids. So now you basically had for 200,000 children their first names, their last names, their parents' names, their home addresses. I mean, that's pretty major. Like that goes beyond just like, oh, they're gonna have to change a password. Like that actually puts children at risk. Or in the last five years, the New York Times has suffered a SQL injection attack. Target has suffered from a SQL injection attack. Sony, yet again, suffered from SQL injection. The US Army, and believe it or not, the US Department of Homeland Security. All vulnerable to this 18 year old SQL injection. So what's the deal? Is there like this elite hacker Harvard out there that's training up just like code ninjas and teaching them how to sneak unwanted SQL commands into databases like there's no tomorrow? No, no, SQL injection is common for two reasons. The first reason is that it's really easy to automate. There are scripts that you can go out online and you can buy and they just bounce around the internet looking for common patterns and websites, looking for things that seem like they might be vulnerable and they'll like pop up a little gooey display and say, oh, hey, do you want me to start attacking this website? It seems vulnerable. Like you don't have to do anything. In fact, there's this guy, Troy Hunt, who's a web security expert, and he runs a website called haveIbeenpwn.com. And I think that's the best logo ever for a website that focuses on SQL injection. And he actually posted a video where he was teaching his three-year-old child to do SQL injection attacks using the most popular program. And this wasn't to show how brilliant his three-year-old was. The point he was making is that SQL injection is just ridiculously easy. These people do not even have to know how to use the command line. It's a gooey interface. The other reason that they do it is that it keeps working. So they use it, because even though we've known about it for 18 years, and even though there are very easy ways to avoid having this happen to your website, hackers year after year get gigs and gigs and gigs worth of valuable user data. So how do we put an end to this? It's kind of harder to make it, like you can't make it impossible for people to automate. Like they can crawl the web, they can do what they want. So you can't make it harder to automate. You could possibly give your table names like really weird names that are hard to guess. Most likely though, that's just gonna drive your dev team crazy and make development harder, and you're still gonna suffer from injection attacks. So that's probably not the idea. And the easier route is just, you know, make sure it doesn't work. Awesome, talk over. Okay, you guys wanna know how to make sure it doesn't work. Okay, all right. So my name is Jessica, and I work at a place called the Flatiron School. And the platform that I work on is a platform called Learn. And we use this platform to teach people how to code. And so we've got tons of students, tons of like brand new junior developers constantly on our platform learning things. And one of the things that we have as part of our teaching philosophy is that we like to have people step through and actually build basic versions of the tools they'll be using later. So it helps ensure that they kind of know what's going on when they get to like the bigger magical platforms. And so we make them work with SQL and kind of build their own lightweight ORMs. And after suffering through that for a while, we introduce them to active record. And everything is like magic. Instead of having to do select star from restaurants, where a type equals barbecue, which is simple enough, but like still kind of like not super intuitive, you can use, hey, I want the restaurant where the type is barbecue. Active record does it for you. And it really seems like all this magic makes sure that the only thing that you have to worry about is whether or not the rule is that your model name is supposed to be plural or singular and wait, is it restaurant dot where or restaurants dot where and that gets me every time. But it's like, if that's the worst thing you had to worry about things to active record, like game over or SQL injection is gone. But it's actually a bit more complicated than that. So we're at Rails conference and you guys came into a talk called, will it inject a look at SQL injection in active record? And congratulations because we finally made it to the title screen, whoo, yeah, only 70 slides in. And given this title, I think it's safe to assume that what you'd like to hear about is SQL injection and active record. And I kind of think that having someone just like yak at you about SQL injection and security, especially right after lunch when you're feeling a bit sleepy and I'm talking to you dad, it can be kind of boring. And so instead, we're going to play a little game that I like to call, will it inject? All right, so the rules are simple. I will show you an active record query. You guys are gonna tell me whether or not you think it's vulnerable to injection and you can just sort of shout it out and you guys watching this later from home, you should feel free to play to shout at your computer screen, I'm not gonna judge, I'm not even gonna be there. So, here is our first one and this guy, fine, he's sort of the heavy weight of active record and if any of you use active record, if you've built even a tiny little app, you've probably used this quite a bit. So, let's say you wanted to find the barbecue joint that has the record of one. So the record ID and the table of one. Do you guys think that if you left that open to user input that that's gonna be vulnerable? Do you think, will it inject anyone? Who thinks it's gonna inject? Just raise your hand, no? You guys think it's safe? Oh, all right, what's that? Let's use edge active record, no. How about whichever one didn't just come out because that's probably the one I'm using. It won't inject, it is safe. So that one actually only works for an integer, it matches, it's like looking for an integer value. If you shove something in there that's not an integer value, it's kind of gonna blow up, cause and error. Now, one of the things that someone could do is start messing around with those numbers and if you're not verifying that they should have access to that particular record, they can use this to see records that they shouldn't be able to see. But that is outside of scope of this talk, so you'll have to find someone else that can tell you how to avoid that. All right, find by. It's similar to find, but you pass in both the attribute you're searching for as well as the value that you want that attribute to have. And as you can see here, it will even let you look for more than one attribute at a time. So for instance, say your user is looking for a barbecue that has the type of burnt ends and a dad's rating of five. And if you know my dad, you know that this could only possibly mean one place, it's Arthur Bryant's, but yeah. You guys, if you haven't tried it already, before you leave Kansas City, stop, get some burnt ends at Arthur Bryant's, it's great. But whether you know my dad or not, do you know, will this inject? Who thinks it's gonna inject? Got a couple hands, couple hands, all right. Who thinks it's safe? Oh, snap. Yep, it's safe, it will not inject. When you pass the attributes in as a hash, ActiveRecord actually escapes any of the special characters and treats the entire thing like a string. So you can have your Shady McShade user's pass in all the raw SQL that they want and it will not inject. Okay, what about this guy? If you wanted to write a query, that search for records based on a SQL fragment. So in this case, and I mean, honestly you wouldn't need to use this particular SQL fragment because you could obviously just do barbecue.find name and then the input, but say you had a complicated query and you had to drop down to raw SQL. So barbecue.wear and then name equals and the user input. So you could say barbecue.wear name equals Oklahoma Joe's for instance. And you would find another great barbecue joint which became even greater when they dropped the Oklahoma and became Joe's Kansas City barbecue. Everything tastes better. But in any case, a great place for barbecue, but how does it fair against SQL injection? Who thinks that this one is going to inject? Oh, yeah, a lot of hands up. Anyone that's like, nah, we got this, this is safe. No, all right, you guys are right. That one does not fair so well because similar to that earlier conversation when we were just looking at SQL, your shady user could say that they want to find a restaurant called single quote, semi colon delete from barbecue dash dash, which again doesn't sound like a tasty restaurant. But the end result is that your entire barbecue table is lost and that's really sad. So how do we protect ourselves against this? Oh, look at this. Anytime you're using raw SQL, when you get to the point in the query where you are going to put the user supplied data, you can replace it with a question mark and then you just like comma and then put the input that you're gonna put in after that and then active record kicks off this thing that we like to call baby proofing the query. And the first thing it does is that it sanitizes the user input and that means that it escapes all the special characters so that the entire thing will be treated just like it's doing, it can't be executable. And all that magic happens if you're interested in reading any source code in sanitization.rb with a little help from quoting.rb. Me, I just like that it works. And no matter what nefarious thing people pass into my app, like if I've done it this way, it's sanitized and it's nothing more than a string and it can't do anything. And the other way that active record is protecting you there when you're using the parameterized queries and that's what they call it when it's a question mark, that's parameterized queries. It's that the SQL statement actually gets sent to the database with the placeholders and the database then parses the statement and it comes up with a query plan and it caches that query plan and sends the statement and a token back to your app. And when the actual values finally do come through if the statement that is trying to be executed differs from that query plan. So if somehow raw SQL did get in there and someone was trying to change the query that was being, had initially been asked for, it can tell that it doesn't match with that token that was sent through like that initial plan and it's like, no, we're not doing that. You cannot change the type of query that was already planned once you use the parameterized queries. So one thing you should know, not every database type supports this. The active record database adapter actually determines how it'll handle things when you use parameterized queries. So just if you are relying on this, make sure that you know how it works for the particular database you're using but most of the major SQL ones like Postgres and stuff, they do allow that. All right, moving right along back to the game, we're gonna step it up a notch. Will this filter by statement inject? Who thinks it'll inject? Yeah? Who thinks it won't? Who here recognize that this is Python using SQL Alchemy? Just trying to make sure you guys are awake. No, that's not even active record. Who even knows what all that stuff is? We're very confusing. But in case you're wondering, it kind of does the same thing as a find by and it turns everything into a string. So even that weird statement will not inject. Okay. What if you wanted to search for all the barbecue joints that are not expensive but to make it easier to determine which ones are delicious, you also want a group by dad's rating. Is this vulnerable to SQL injection? Okay, got some people, yeah, over there. I'm just gonna tell you guys someone from Rails Core just raised their hand so maybe I won't listen like, oh yeah, it's, it's vulnerable. You guys are right. That group method allows for SQL to be passed in. So if you're putting the user data directly into the query with that group by, you are gonna be vulnerable. All right, I think we have time for one more. So let's take a look at having. And this one actually almost always ends up at the end of a chain of like a lot of other queries. So in this case, you're looking for a barbecue restaurant that isn't expensive and you want to group it by location and you want to give the user an option of saying that they want a certain level of dad's rating. So maybe some of them, you know, they're like, I'm not too picky, a dad's rating of two or more. But in this case, this user wanted a dad's rating greater than four. So will that inject? Yeah, anybody? No? All right, who thinks it's safe? No, it injects. It's actually at greater risk than a lot of the other methods because it usually ends up at the end of the method chain. So because it's at the end and nothing follows it, it's even less likely with that particular one that just having something else coming after it in the query chain will stop the injection. So you definitely want to be careful with having. It just makes it easier for people to shove their own SQL in there. And the thing is that once again, this is easily fixed with the parameterized queries. You just pop that question mark in, put the statement and active record takes care of it. And you might be looking at this and say that's very similar to how you fixed the other one that had injection. And that's one of the great things about active record is that it does have like a lot of patterns that are the same. So if you figure out a way to avoid injection in some methods, chances are you're gonna be avoiding them in all the rest of them. So it's just easy peasy. Now, some of you might be thinking, look, if people are aware of these vulnerabilities and it's been 18 years and SQL injection seems pretty major, why haven't they fixed this yet? Maybe I'm gonna go back to my hotel room tonight and I'm gonna work really hard on a patch and I'm just gonna get this pull request in and then there's gonna be no more SQL injection in Ruby, in Rails. Not quite that simple. So the problem is, it's another example of that age old tension between freedom and security. Those vulnerabilities are there because they allow us flexibility. That coffee shop I worked at, they could easily have had a rule that said you were never allowed to give anyone anything for free ever. And if that had been the rule, we all would have followed it, customers would have stopped asking, Susie's shtick of, oh, but my day would be better if you gave me a lot of free stuff, wouldn't have worked. But there would have been a lot of magic moments that we would have lost. There were a lot of like beautiful individual customer interactions where you're just like, oh, hey, the coffee's on us today. Do you like an extra shot that just wouldn't have been possible? And it's the same thing with Active Record. Like you don't wanna lose the magic of being able to really like dig down into the data and drop down to raw SQL and do like a really crafty query because as beautiful as Active Record is for probably 90%, if not more of anything that you'd need to query it for, there's still the fact that SQL was made to talk to databases. And sometimes if you wanna do some crazy complex query, you could either spend like weeks figuring out how to do it in Active Record or you could do it in SQL. And that's gonna be the only straightforward way of getting it. And if Active Record didn't allow this flexibility, it would start to be less and less useful as our apps got more and more and more complex. And we'd probably spend a lot of time starting to write our own methods to talk to databases and we'd write them from scratch. And if the history of programming is any indication, when we start writing our own things from scratch and it's not the core of what we're trying to do, chances are we're gonna actually end up being more at risk than we would have if we had just figured out the Active Record stuff. And then our apps would be in the news but for all the wrong reasons. And we should apparently leave that to Sony because that's Sony's thing. They're the ones that are in the news for SQL injection, don't be Sony. And look, I get it. I know that it's a bit tougher than ye good old days where security was literally like a heavy wooden door with a big guy and a metal slit. And you didn't have to worry about automated scripts being run by precocious little three year olds that were looking for the slightest vulnerabilities in your code. And it was just a face-to-face interaction. And you probably got one chance to get it right. So you couldn't even sit there spamming a password like is it one, two, three, four, five, six? No, is it password? Like you had one chance. And like you either got it or you got chased away by the big guy. But the reality is that things aren't actually all that much more difficult now with the built-in security and active record and just a tiny bit more knowledge of what's going on behind the scenes with those helper methods. We can all write code that keeps our customers data and our apps reputation safe and secure. So I wanna give a quick shout out to my colleague, Eric Raffaloff. His Thursday code reading at work was actually the inspiration for this talk. He has since left us to go work in web security. So we're all safer for that, though we do miss him at work. And I wanna thank my husband, Josh, for hand drawing anything I asked him for and double checking my SQL. Coder animators are the best. If you guys would like to learn more and amazing resources this Rails SQL.org, just there are places that you can test it, see how it works. He goes through all of them in detail. Like it's a beautiful resource. There's also guides.rubionrails.org, security.html. That's actually the security guide from Ruby on Rails and it has a lot of good information you should check out. It covers a lot more than just SQL injection. There's the OWASP Ruby on Rails cheat sheet and you can also, this is crazy, just look at your own code, go to your own web forms, try to inject some SQL in there, see if it works, don't do it in production, do it in local. But if it'll work in local, it'll work in production and you should probably shore that up. So as I mentioned, I'm Jessica Rudder. I make codes about, or I make videos rather, about coding at youtube.com slash compchop. And if you like code and videos, I'd love to have you check them out and let me know what you think. I would also love to continue this conversation on Twitter. And if any of you are like new to programming and you wanna talk about learning to code or sort of learning Rails, I've borrowed the company credit card and we're gonna be doing a dinner tonight for beginners. So if you're just starting out and you want to like join us for that, just hit me up after the talk and I'll give you the details. So that's the talk. Are there any questions? And if they're mean questions, I'm just gonna mad dog you. So pre-warning, pre-warning. No questions, good. I answered everything. Yeah.