Will It Inject? A Look at SQL injections and Active Record

RailsConf 2016

by Jessica Rudder

In her presentation at RailsConf 2016, Jessica Rudder explores the vulnerabilities of SQL injection attacks within Active Record, a popular Object-Relational Mapping (ORM) tool in Ruby on Rails. Rudder utilizes a coffee shop anecdote to illustrate how users can sneak unintended commands into database queries, paralleling the deceptive tactics seen in SQL injection attempts. She emphasizes that SQL injection is an age-old vulnerability that continues to rank among the top security risks, despite being well-known. Key points discussed include:

Understanding SQL Injection: SQL injection arises when malicious actors inject SQL commands into queries, potentially accessing or manipulating sensitive data.
Historical Context: SQL injection has been recognized since the 1990s, yet it remains a prevalent threat, affecting many organizations, including Sony, universities, and even government entities.
Active Record’s Role: Active Record simplifies database interactions but can still be vulnerable if raw SQL is used improperly, highlighting the need for developers to understand how to structure their queries securely.
Safe vs. Vulnerable Queries: Rudder engages the audience with examples of Active Record queries, leading a game of 'Will It Inject?' to demonstrate which queries are safe from injection attacks and which are at risk when user input is improperly handled.
Best Practices for Prevention: To prevent SQL injection, use parameterized queries and ensure user inputs are sanitized, as Active Record automatically handles this for certain methods.
Recognizing Human Error: The dialogue concerning SQL injection often connects to human behavior, emphasizing that even experienced developers can fall victim to such vulnerabilities due to the balance between flexibility and security in coding practices.

In conclusion, Rudder asserts that awareness and understanding of SQL injection can drastically improve web application security. By leveraging Active Record judiciously and applying best practices in coding, developers can protect sensitive data and maintain the integrity of their websites. She encourages further learning through recommended resources such as OWASP and Ruby on Rails security guides, ultimately reinforcing the importance of security in modern web development.

00:00:10.429 Alright, my time has started, so I guess we'll slowly work our way into the talk.

00:00:18.109 Did everyone enjoy lunch? Did anyone go out to grab some barbecue? I think I saw a few people with BBQ. Did anyone grab a coffee? You guys are not giving me anything. Anyone grab a coffee? No? Just this guy? Nice!

00:00:32.579 So, many lifetimes ago, I worked as a barista at a popular coffee shop. It was a mid-sized neighborhood store with a mix of traffic coming from the surrounding homes and businesses. We were 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. It was a nice mix of customers. Most of them were great, but there was one—there's always one. Her name was Suzie, and I've changed her name to protect her, even though she's totally guilty. Suzie would come in three to four times a week and always had the exact same order: a double espresso, please.

00:01:12.720 I’d make small talk as we finished the transaction, asking, 'Oh, how's it going, Suzie?' And she would respond, 'Oh, you know, not so well.' Then she’d launch into some tale of woe, recounting things that weren’t going well. Mind you, she came in three to four times a week with the same story. It was like she never had a good day in her entire life; it was always the same deal. But then, right as I handed her the change, she’d perk up and say, 'But you know, if you made that a triple latte instead of a double espresso, my day would be a whole lot better!' This was every single time.

00:01:39.509 The problem was that a double espresso cost about this much, and a triple latte, with its extra shot and milk, well, it costs this much. She would wait until the exact moment that the register closed, signaling that the transaction had officially ended, before trying to sneak something else in there. And that brings us to the topic of safety and security in coffee shops. Okay, maybe not quite right. That’s probably something more suited for a Java conference, but giving an Active Record talk, I have to do a pun in honor of Tender Love.

00:02:07.590 So, we are in a room full of mostly web developers, and I just want to say that I looked all over the internet to find the least creepy spider I could find. I was not going to be surrounded on all sides by creepiness. So, this is a very friendly spider, and like this friendly spider, we're very friendly web developers. Maybe we want to talk about security in web apps instead. 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 very similar to the one that Suzie was exploiting: SQL injection.

00:02:39.959 Now, I’ll go into more detail in a bit, but in brief, SQL injection happens when someone closes out a legitimate transaction with your database and 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. I know what some of you are thinking, 'We are web developers; there is nothing more valuable than coffee.' But, in this case, I would argue that your customers' private data is actually slightly more valuable than coffee.

00:03:02.999 When we talk about security vulnerabilities, it’s more common to think that we’re discussing something new—a vulnerability that may have just been discovered in the past few weeks. Something that’s going to be on the nightly news, like 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; I don’t know the ages, but in the United States, SQL injection could vote since it’s 18 years old. It was first mentioned by Jeff Forristal in an online hacker magazine called Frack. You can tell that’s 90s graphics right there—that’s actually still the logo on the website; they didn’t update it.

00:03:38.160 Jeff Forristal, who went by the hacker name of 'Rainforest Puppy,' is worth mentioning. When you start reading about SQL injection and security, you come across a lot of hacker names. I realized I didn’t have a hacker name, so what did I do? I went to Google and Googled 'hacker name generators.' I started trying out a few different options, including one dubious generator that insisted it needed to know my mother’s maiden name and where I was born to generate my hacker name. I thought, 'Nice try,' and moved on to the next generator.

00:04:02.220 After probably a bit too long on that, I was finally the proud owner of the name 'Fire Acid.' And you know what? That’s a legitimate hacker name because it’s a four—not an A! So, back to my fellow hacker, Rainforest Puppy—he discovered that SQL databases, which were just starting to replace the more popular Access databases, allowed batch commands. What that meant was that you could open up one interaction with the database and execute one command, but with batch commands, you open that connection to the database a single time and can send more and more requests.

00:04:36.360 This isn’t a big deal because multitasking is usually a good thing. For example, let’s say I want to take a look at all of my employees. This is a fine-looking group of employees, but say I only want the ones who are developers—just these two. Then I want to sum up or find the average of their chips consumed. Because I like to be inclusive, I would also average those chips consumed. Whether it’s these chips consumed or those chips consumed, the number will be pretty high—and we’ll just leave it at that.

00:05:07.110 Since you can put more than one command in, you open that database connection once and run those extra queries. And in a closed system, that’s all well and good because you’re not going to attack your own database. But when you have input coming from the outside, when you have outside user data heading straight into your database, things can get a bit dicey. 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. They can do that by piggybacking their own SQL command onto your intended request.

00:05:42.100 For instance, you have an SQL command that gives them the chance to search for a restaurant, where they can do something like match the name. You might write a query like: 'SELECT * FROM restaurants WHERE name IS LIKE ?'. And so, your shady user, let’s call him Shady McShane, searches for a restaurant that doesn’t really sound like it’ll have good quality food. This is the query you actually end up with: 'SELECT * FROM restaurants WHERE name IS LIKE '' AND DELETE FROM restaurants'. That semicolon closes out the query, and the double-dash means that everything after it is commented out. What happens is that you end up selecting nothing from your restaurant table and then, just like that, delete every single record in your restaurant table.

00:06:16.150 And I’m not talking about your physical table at the restaurant; I’m talking about the restaurant table in your database. It’s still there, it’s just completely empty, and you should probably cancel the reservation because you're going to spend the rest of the night backing up and restoring your database. And you’re all doing database backups, right? You know, I hope you are because stuff happens, and it’s just a good idea.

00:07:04.300 So let’s talk about OWASP, the Open Web Application Security Project. This group gets together daily, and every three years, they update their top ten list of web vulnerabilities. SQL injection has been out for 18 years, and yet it regularly ranks at the top of their ten most critical web application security risks. It’s not really a top ten list that you want to be on the top of. If you look at a recent scan of the news, you will find many examples of companies, big and small, that have experienced SQL injection attacks.

00:07:34.960 For instance, in 2011, Sony Pictures lost the data of over one million users, including their passwords, which were reportedly stored in plain text. That really hurts. You might think, 'Oh, whatever, that’s Sony Pictures; big deal.' But most people use the same password, and now you have the password that people use for their bank accounts, and all this information. They can just go from site to site, build a profile on these people, and steal even more information because they did something they shouldn’t have—that is, storing passwords in plain text.

00:08:06.820 In October 2012, hackers used SQL injection to get personal records from thousands of students from over 53 universities worldwide, including major ones like Harvard, Stanford, and Princeton. I didn’t realize that universities are actually a pretty big attack vector because students have a lovely combination of clean credit records and they don't pay attention to their credit scores. You 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, one thing we can say is good about crushing student debt—by the time you graduate, no one wants to steal your identity anymore.

00:08:53.830 Alright, we’re not done yet. In August 2014, Russian hackers stole 1.2 billion—yes, with a 'b'—username and password combinations and 500,000 email addresses from 400,000 websites. These were all little mom-and-pop sites, most of them Fortune 500 sites, and they weren’t made by your cousin’s best friend’s uncle’s kid down the street, who sort of knows how to develop a website. These were made by professional developers, like the people sitting in this room.

00:09:24.430 And it wasn’t pulled off by some elite gang of movie hackers. It was a group of less than a dozen men living in a small town in Russia who got their start doing email spam. One new guy moved into town, showed them how to do SQL injection, and they became the kings of SQL injection. I’ve got one more for you. This is mentioned in an earlier talk: VTech, an electronic toy company, lost the data of over five million parents, including email addresses, phone numbers, and home addresses, because people had registered their products. They also lost the data of 200,000 children, which included just first names and email addresses.

00:10:07.580 While that might not seem severe at first, the data dump included the ability to link parents with children. So now, for 200,000 children, you had their first names, last names, parents’ names, and home addresses. That’s pretty major—it goes beyond just changing a password; that actually puts children at risk. Oh wait, there’s more! In the last five years, the New York Times has suffered an SQL injection attack. Target has suffered from an 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.

00:10:56.090 So, what’s the deal? Is there some elite hacker academy out there training up code ninjas to sneak unwanted SQL commands into databases like there’s no tomorrow? No, SQL injection is common for two reasons. The first reason is that it’s really easy to automate. There are scripts you can buy online that just bounce around the internet looking for common patterns and sites that might be vulnerable. They pop up a little GUI display and say, 'Oh hey, do you want me to start attacking this website? It seems vulnerable!' You don’t have to do anything!

00:11:32.150 In fact, there’s this guy, Troy Hunt, who’s a web security expert and runs a website called haveibeenpwned.com. I think that’s the best logo ever for a website focused on SQL injections. He actually posted a video where he taught his three-year-old child to carry out SQL injection attacks using the most popular program. 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 don’t even need to know how to use the command line; it’s a GUI interface.

00:12:02.470 The other reason it keeps working is that year after year, hackers get gigs and gigs and gigs worth of valuable user data, even though we’ve known about it for 18 years. So how do we put an end to this? Well, it’s harder to make it impossible for people to automate attacks. They can curl the web; they can do what they want. You could possibly give your table names weird names that are hard to guess, but most likely, that’s just going to drive your dev team crazy and make development harder. You’re still going to suffer from injection attacks, so that’s probably not the idea.

00:12:38.600 The easier route is to ensure that it doesn’t work. Awesome, talk over! Do you guys want to know how to make sure it doesn’t work? Alright! My name is Jessica, and I work at a place called the Flatiron School. The platform I work on is called Learn, where we teach people how to code. We have tons of students, like brand new junior developers, constantly on our platform learning things. One aspect 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.

00:13:04.800 This helps ensure that they kind of know what’s going on when they get to the bigger magical platforms. So, we make them work with SQL and build their own lightweight ORM. After suffering through that for a while, we introduce them to Active Record, and everything seems like magic! Instead of having to do a query like 'SELECT * FROM restaurants WHERE type = BBQ,' which is simple enough but still not super intuitive, you can just ask, 'Hey, I want the restaurant where the type is Barbecue.' Active Record does it for you.

00:13:39.120 It really seems like all this magic makes sure that the only thing 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.where or restaurants.where? That gets me every time! If that’s the worst thing you have to worry about, then Active Record has it figured out. So, is SQL injection gone? Well, it’s actually a bit more complicated than that.

00:14:11.189 So, we’re at RailsConf, and you guys came to a talk called 'Will It Inject? A Look at SQL Injection in Active Record.' Congratulations; we finally made it to the title screen! Whoo! Only 70 slides in! Given this title, I think it's safe to assume that what you’d like to hear about is SQL injection and Active Record. I kind of think that having someone just yak at you about SQL injection and security—especially right after lunch when you’re feeling a bit sleepy—can be kind of boring.

00:14:50.500 So instead, we’re going to play a little game I like to call 'Will It Inject?' The rules are simple: I will show you an Active Record query, and you guys are going to tell me whether or not you think it’s vulnerable to injection. You can just shout it out, and those of you watching from home, feel free to shout at your computer screen; I’m not going to judge, I’m not even going to be there!

00:15:21.620 Here’s our first one! This guy—he’s sort of the heavyweight of Active Record. 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 want to find the barbecue joint that has the record ID of 1. Do you think that, if you left that open to user input, that’s going to be vulnerable? Do you think, 'Will it inject?' Anyone who thinks it’s going to inject, raise your hand!

00:15:50.810 You guys think it’s safe? Oh, alright! What’s up? Let’s use Active Record. No? How about whichever one didn’t just come out, because it’s probably the one I’m using? It won’t inject; it is safe! That one actually only works for an integer; it’s looking for an integer value. If you put something in there that’s not an integer value, it's going to blow up and cause an error.

00:16:11.370 Now, one of the things 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’s outside the scope of this talk. Now, 'find_by' is similar to 'find,' but you pass in both the attribute you're searching for and the value you want that attribute to have. For instance, say your user is looking for BBQ with the type 'burnt ends' and a dad’s rating of 5.

00:16:46.390 If you know my dad, you know that this could only mean one place: it’s Arthur Bryant’s! If you haven't tried it yet, before you leave Kansas City, stop and get some burnt ends at Arthur Bryant’s; it’s great! Now, do you all think that this one is going to inject? Anyone think it's going to inject? Got a couple of hands! Alright, who thinks it's safe? Oh snap, yeah, it’s safe! It will not inject; when you pass the attributes in as a hash, Active Record escapes any of the special characters and treats the entire thing like a string.

00:17:16.280 So, your shady McShane users can pass in all the raw SQL they want, and it will not inject. Okay, what about this guy? If you wanted to write a query that searches for records based on a SQL fragment, you can do something like: 'BBQ.where(name=?)'. You wouldn’t need to use this particular SQL fragment because you could just do BBQ find. But say you had a complicated query and had to drop down to raw SQL, such as 'BBQ.where(name_equals?)' for instance, where you look for Oklahoma Joe’s.

00:17:41.090 Now, how does it fare against SQL injection? Who thinks this one is going to inject? Oh yeah! A lot of hands up! Anyone think it's not going to inject? Alright, you guys are right; that one does not fare so well. This is similar to that earlier conversation. When we were just looking at SQL injection, your shady user could say they want to find a restaurant called 'single quote semicolon delete from BBQ'—that doesn’t sound appealing—but the result is that your entire BBQ table is lost, and that’s really sad.

00:18:19.370 So, how do we protect ourselves against this? Look at this! Anytime you’re using raw SQL, when you get to the point in the query where you’re going to put the user-supplied data, you can replace it with a question mark, and then put the input that you’re going to insert after that. Active Record sanitizes the user input, escaping all the special characters so that nothing can be executable.

00:18:36.920 Once again, the magic happens, and if you are interested in reading any source code on how this works, it’s in sanitization.rb, with a little help from quoting.rb. I just like that this method works—no matter what nefarious thing people pass in, if I’ve done it this way, it’s sanitized, and it’s just treated as a string. It can’t do anything!

00:19:12.610 The other way that Active Record is protecting you is when you're using parameterized queries, which is what they call it when it’s a question mark. The SQL statement actually gets sent to the database with the placeholders, and the database then parses the statement, comes up with a query plan, and caches that query plan. Since the statement is sent as a token back to your app, when actual values finally come through, if the statement that's trying to be executed differs from that query plan—if raw SQL gets in there and someone tries to change the query—it can tell that it doesn’t match the token that was sent.

00:19:50.360 So it rejects the change, and nothing happens! One thing you should know: not every database type supports this. The Active Record database adapter actually determines how it will handle things when you use parameterized queries, so just know how it works for the particular database you’re using. But most of the major SQL databases, like Postgres, do allow for this!

00:20:24.150 Alright, let’s move on to the game again, and we’re going to step it up a notch—will this filter by statement inject? Who thinks it’ll inject? Yeah? Who thinks it won’t? Who here recognizes this as Python using SQLAlchemy? Just trying to make sure you guys are awake! No, that’s not even Active Record.

00:20:55.780 In case you’re wondering, it kind of does the same thing as 'find' because it turns everything into a string, so this weird statement will not inject. What if you wanted to search for all the barbecue joints that aren’t expensive, but to make it easier to determine which ones are delicious, you also want to group them by dad's rating? Is this vulnerable to SQL injection?

00:21:26.300 I heard some people say, 'Yeah!' Another person just raised their hand and said, 'Oh yeah, it's vulnerable!' The '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 going to be vulnerable.

00:21:46.290 Alright, I think we have time for one more. Let’s take a look at 'having'. This one usually ends up at the end of a chain of queries. In this case, you’re looking for a barbecue restaurant that isn’t expensive and you want to group it by location. You want to give the user an option of saying they want a certain level of dad’s rating. Maybe they’re not too picky and say they want a dad's rating of two or more, but in this case, the user wanted a dad's rating greater than four.

00:22:19.110 So, will that inject? Hmm, alright! Who thinks it’s safe? Oh, 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. Since it's at the end and nothing follows it, it's even less likely that anything 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 users to insert their own SQL.

00:22:56.180 The good news is that, once again, this is easily fixed with the parameterized queries! You just pop that question mark in the input statement, and Active Record takes care of it. You might be looking at this and say, 'That’s very similar to how you fixed the other one with injection!' One of the great things about Active Record is that it has a lot of patterns that are the same.

00:23:31.790 So, if you figure out a way to avoid injection in some methods, chances are you’re going to avoid them in all the other methods too, making things easy-peasy. Some of you might be thinking, 'If people are aware of these vulnerabilities and it's been 18 years, why haven't they fixed this yet? Maybe I’ll go back to my hotel room tonight and work really hard on a patch, and then there’ll be no more SQL injection in Ruby on Rails.' Not quite that simple.

00:24:14.920 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. The coffee shop I worked at could easily have had a rule that said you would never be allowed to give anyone anything for free. If that had been the rule, we all would have followed it.

00:24:50.410 Customers would have stopped asking Suzie’s gimmick of ‘Oh, but my day would be better if you gave me a lot of free stuff.’ It wouldn’t have worked. But there would have been many beautiful individual customer interactions we'd have lost, like saying, 'Oh hey, the coffee is on us today! Would you like an extra shot?' That just wouldn’t have been possible.

00:25:27.690 And it’s the same thing with Active Record. You don’t want to lose the magic of being able to really dig into the data and do some really crafty queries. Because as beautiful as Active Record is for probably 90% or more of what you need to query, SQL was made to talk to databases.

00:25:59.080 Sometimes, if you want to do a crazy, complex query, you could either spend weeks figuring out how to do it in Active Record, or you could do it in SQL, and that would be the only straightforward way of getting it done. If Active Record didn’t allow this flexibility, it would become less useful as our apps got more complex, and we’d likely find ourselves writing our own methods to talk to databases, and we’d be at risk of being more vulnerable than we would have been if we had just figured out the Active Record methods.

00:26:36.730 Perhaps we would start getting our apps in the news for all the wrong reasons, similar to Sony’s situation—something we really want to avoid! I get it; I know it’s a bit tougher than the good old days when security was literally a heavy wooden door with a big guy in a metal slit. You didn’t have to worry about automated scripts being run by precocious little three-year-olds looking for the slightest vulnerabilities in your code.

00:27:05.970 It was just a face-to-face interaction, and you probably got one chance to get it right. You couldn’t even sit there spamming passwords like, 'Is it 123456? No, is it password1?' You had one chance; you either got it right, or you got chased away by the big guy. But the reality is that things aren’t all that much more difficult now with the built-in security in Active Record.

00:27:59.160 With just a bit more knowledge of what’s happening behind the scenes with those helper methods, we can all write code that keeps our customers’ data and our apps’ reputations safe and secure. So, I want to give a quick shout-out to my colleague Eric, who actually inspired this talk with his Thursday code reading at work. He has since left us to work in web security, so we’re all safer for that, though we miss him.

00:28:47.120 Also, I want to thank my husband Josh for hand-drawing anything I asked for and double-checking my SQL code. Animators are the best! If you’d like to learn more, an amazing resource is OWASP.org, where you can test how SQL works and see how it operates in detail.

00:29:24.670 There’s also guides.rubyonrails.org/security.html, which is the security guide from Ruby on Rails with a lot of good information. You can check that out as it covers much more than just SQL injection. There’s the OWASP Ruby on Rails cheat sheet, and you can also just look at your own code. Go to your web forms, and try to inject some SQL to see if it works.

00:30:06.680 Don’t do it in production; do it in local! If it works in local, it’ll work in production, and you should probably shore that up. As I mentioned, I’m Jessica Rudder, and I make videos about coding at youtube.com/compchop. If you like code and videos, I’d love to have you check them out and let me know what you think.

00:30:38.060 Also, I’d love to continue this conversation on Twitter. If anyone here is new to programming and you want to chat about learning to code or learning Rails, I’ve borrowed the company credit card, and we’re doing dinner tonight for beginners. If you're just starting out and want to join us for that, just hit me up after the talk, and I’ll give you the details.

00:31:02.860 That concludes the talk. Are there any questions? If they’re mean questions, I’m just going to mad-dog you, so just a pre-warning. Oh, no questions? You must have the answer for everything!