Bestand wählen

Will It Inject? A Look at SQL Injection and ActiveRecord

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Erkannte Entitäten
the the the the the the the the the the the the what are the right hand my time is started so I guess will slowly work their way into the top 10 did everyone enjoy lunch anyone go out there have some barbecue at the level you hear and go external no anyway and grab a coffee the like this coffee he has not given any anyone grab a coffee now this guy grab a coffee now acts as so many lifetimes ago I worked as a various static popular coffee shops and it was a mid-size neighborhood store where there is a mix of traffic coming from the surrounding homes and business and we were actually right across the street from 1 of the big game development studios and business which is like the guy rocket anytime a new I call of duty game was supposed to launch and it's just a nice mix of customers and most of them were great but there was 1 and there's there's always and that her name was
CC the and the singers and change of attacked her you know she's totally guilty Susan would come in 3 4 times a week and she always had the
exact same order a double espresso please and make small talk as we finish the transaction call going Susie 0 you know not so well anti-modernism tale of woe things that just weren't going well now mind you she was in 3 to 4 times a week and was always the same story I swear she never had a good day in her entire life is just always the same deal the but
then the right side of her and her the she she forgot that and should say but you know if you made that triple lot 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 and a triple what
today with its extra shower and milk while it costs on this much and she would wait until the exact more that the register closed signaling that the transaction had officially ended before she tried it so something else and yeah and that
brings us to the topic city insecurity in coffee shops maybe not so not quite a it's how something more for job comp record types have to do a upon in honor of 10 so
actually we are in a
room full of it mostly web developers and others say I looked all over the internet to find the least creepy spider I find that there is not like surrounded on all sides by creepiness so that they can vary from a spider and so like this very friendly spider were very friendly web developers and I'm making a lot of talk about security in web apps instead and at the risk
of stretching that coffee shop metaphor just it too far I want to argue that are web apps have evolved about a vulnerability that's actually very similar to the 1 that easy was exploited and that's
sequel injections now all more
detail in a bit but in brief physical injection is when someone closes out a legitimate transaction with your database and then immediately faced that a little bit more sequel in there so they can interact with your database and walk off with something a whole lot more valuable and coffee and I know what some of you were thinking we on web developers
there is nothing more valuable than coffee but in this case there there it's I'd argue that your customers private data
is actually slightly more valuable
so we we have security vulnerability is 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 like going to be on the nightly news something like the hot
Hartley where it announced publicly the very same day that patches release because it's just that but sequel injection it 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 sepal induction can but it's 18 years old it was 1st
mentioned by Jeff forestall an online hacker magazine called Braque and you can tell that 90 structure that that actually still below on the website did not update so just forestall who live by the
hacker name of rain forest copy and like the positive moment is when you start reading about sequel injection insecurity in all of those things you come across a lot of happenings and I realized I haven't got it and I didn't like the web developer would do I went to Google and I
Googled happening generators and I started trying out a few different options including 1 dubious generator and insisted it needed to know my mother's maiden name and where I was born in order to generate my happening lot nice try and moved on to the next have a generator and after after probably a bit too long on this I was finally the proud owner of this
doozy yeah violence in the and you know that's a legitimate happening because that is the 4 that's not and so next to my fellow and reinforced
puppies he discovered that sequel databases which were just at the time starting to replace the have more popular access database and they allowed batch commands and so what that meant was
that you opened up 1 interaction with the database and you do 1 command but with batch commands you open up that connection to the
database a single time and you can just standard but more and more and more requests and their and that's not a big deal because multi tasking is a good thing right
I mean let's say I wanna take a look at all of my employees fine-looking groups of employees but say that I wanna select just the ones that are developers so just these 2 and then I will note some
of your fine the average of the chips consume and because I like to be inclusive
we also average their chips consumed if you state 1 of those other languages what I wonder whether it's these
ships consumed those ships consumed it's pretty high and will leave it at that and because you can put more than 1 command and you can just open up that database of 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 database what when you have a that
coming in from outside when you have outside user data heading straight into your database bills can get a tiny bit dicey so let's say a shady
character may like this guy wants to use your innocent little web form to gain access to parts of a database that you don't want them house and they can do that by piggybacking their own sequel command onto your intended what sort of like that you have a
sequel command and given the chance if you provide the ability to search for a restaurant where they can do is sort of a match with the name and the like FlexStar from restaurants where name is like and in united user input and your
user shading changed our searches for the following restaurants which honestly does not sound like they can have the best quality food to me but you know other this is the search for and this is
the query you actually in that way so you're selecting star from your restaurant table where name is light and that actually closes out that name but that selects statement and then starts the leap from restaurants and that double dash there is a sequel comic which means everything after that is out and so what you have done here is you've selected nothing from the restaurant table because they didn't give anything it's just 2nd in the string and then they delete every single record in your restaurant table and that its full ejection from your entire restaurant table is gone and not
something about the table at the restaurant it's your restaurant table in your database it's actually still there it's is completely empty but you should also probably cancel this table
because you're an after the 98 backing up and restoring the database and you're all doing database that right yeah I know so it's not the scope of this talk but if you're not you should be because stuff happens um and yet it's just a good idea and so let's talk so what's
the Open Web Application Security Project this group has found Billy get together and think every 3 years the at great like they update their top 10 list of Web vulnerabilities and sequel induction it's been out for 18 years it's an old vulnerability and yet it regularly brings at the top of the 10 most critical web application security risks and it's not really a top 10 list that you wanna be at the top of and honestly if you look at like a recent scan of the news you will find so many examples of companies big and small that have you know primitive scenes sequel injection from from that site for
instance Sony Pictures In 2011 Sony Pictures last data of over 1 million users including their passwords which were reportedly stored in plain text so that that really hurt and you know you might be gullible whatever it's like Sony Pictures big deal but most people use the same password and now you've got the password that people use in their bank analyst effort go from site to site to build a profile on these people and is but still even more information because you did something that you should not have in store passwords in plaintext in December
sorry October 2012 hackers use sequel 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 it like students at universities are actually pretty big attack vector which I didn't realize there is such a target and that's because students to have like wonderful combination of really clean credit records and they'll pay attention to the credits for to just have like quite a few years of being able to late holy trash their credit histories for the student loans to for them and I guess the 1 thing I that we can say is good about crushing student debt is that by the time you guys like no 1 wants to steal your identity and 1
aaai where nodes only at an honest in 2014 of Russian hackers stole 1 . 2 billion that's with the username and password combinations and 500 thousand e-mail addresses from 400 thousand websites and these were all just like little mom-and-pop sites most of these Fortune 500 sites they're not sites made by your cousins best friends uncles kid street sort of knows how to develop a web site these were made by a professional Belper's the people sitting in this room and it wasn't called off by some weak elite gained of movie actors it was a group of less than a dozen men living in a small town in Russia actually got their start by doing e-mail spam and 1 you that in time as I I can show you how to do a sequel injection and they became like that she's of sequel injection and then about 1 more
for you this election mentioned in an earlier talk and it's the type which is an electronic quite for a company and it may have lost the data of over 5 million parents so late e-mail addresses phone numbers home addresses this people have registered their products they also lost the data of 200 thousand children now this is just 1st names and e-mail addresses so it doesn't seem as severe and we realize that the data included the ability to link the parents with the kids so now you basically have hard for 200 thousand children their 1st names there last means their parents names their whole addresses and that's a pretty major that goes beyond just like 0 they're gonna have to change a password like that actually puts children at rest but
it yeah so all In the last 5 years The New York Times has suffered as a projection attacked target has suffered
from simple induction attack Sony yet
again suffered from simple induction
the US Army and believe it or not the US Department of Homeland Security all normal to this 18 year old civil interactions so what's the deal is there like this elite hacker
Harvard out there that's training up just like code ninjas and teaching Protestant unwanted sequel command into databases like there's no tomorrow not no sequel common for 2 reasons
the 1st reason is that it's really easy to automate their scripts that you can go out online and you can buy and they just bounce around the Internet looking for common patterns and site it's looking for things that seem like they might be vulnerable and nobly pop up a little we display and sale 81 to start attacking this website it's seems vulnerable you don't have to do anything in fact there's this guy
try countries the web security expert adherence a website called have I been home . com and I think that's the best logo ever for a web site that focuses on sequel injection and he alone actually posted a video where she was teaching
history year old child to do sequel injection attacks using the most popular program and this wasn't a show how brilliant his three-year-old was the point he was making is that sequel injection is just ridiculously easy these people do not even have to know how to use the command line it's into we interface no other reason that they do it is that it it's
working so they use it because you know we've known about for 18 years and you know they're very easy ways to avoid having this happen your website hackers year after year get gives a gives engage with the valuable user data the so how do we put an end to this but could harder to make
it the but you can't make it the impossible for people automatically they can crawl the web they can do it they want you can't make it harder automate you can't possibly give your table names like really weird names that are hard to guess most likely that that is going to drag a dead team crazy and development harder and you're still the suffer from injection attacks so that's probably not the idea and the easier route is just you know nature doesn't work all some talk over the OK you get lining of cremated that work but that are at so
many this just that my work at a place called the flat school and that the platform that I work
on is a platform called learned and we can 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 a platform learning things and 1 of the things that we have is private teaching philosophy is that we like to have people step through and actually build build basic versions of the tools of using later so it helps ensure that the kind of know what's going on when they get to like the bigger magical platforms and so we make them work with sequel and kind build their own lightweight Warens and after something to that for a while we introduced and active records and everything is like magic instead of having
to do select star from restaurants where time people's barbecue which is simple enough but we still kind of like not super intuitive you can use and what the restaurant where
the type is barbecue and just you know after rapid does it for you and it really seems like
all this magic make sure that the only thing that you have to worry about is whether or not the rule is that your model is supposed to be followed singularity way is that if restaurant out where restaurants start where it gets me every time but cited that's the worst thing you have to worry about the 2nd record late game over a single injection is gone but it's actually the more complicated than that so where else
topics and you guys came to a talk called will inject a look at sequel injection in Active Record in congratulations because we finally made it to the title screen but yeah I only 70 slide and given this title of it safe to assume that we'd like to hear about is sequel injection in the active and I kind think that having someone despite Yakut you about sequel objection security especially right after lunch when you want to it's sleep in you Dad I'm it can be kind of boring and so instead we're going to play a little game that I like to call I Will it
injects are 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 induction and you can just sort of shouted out and you know I was watching this later from home you should feel free to play to shout at your computer screen I'm not going to judge I think that here is our
first one and this guy finds the sort of the heavyweight of active record and if any of you use Active Record if you build even a tiny little out you probably use this quite a bit so let's say you wanted to find the barbecue joint that has the record of 1 so the record ID in a table of 1 do you guys think that if you wait left that open to user input that that's going to be vulnerable to the Willard injecting 1 this is gonna just raise a hand no it's a long error was that
and With use edge Active Record now about whichever 1 didn't just come out of this problem a 1 of user community of Evelyn injects it is safe so that when actually only works for an integer it matters that looking for an integer value if you show something in there that's not an integer value it's kind of been a blow up cause an error now 1 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 should be able to see but that is not the end of this talk so you'll have to find someone else I can tell you how to avoid that are
fine by it's still defines but you pass in both the attribute you're searching for a as well as the value that you want that a tribute to have as you can see here it will even let you look for more than 1 attribute at a time so for instance say your users looking for barbecue that has the type of bird ends and adapted in the 5 and if you know a dad you know that this can only possibly 1 place it's Arthur Bryant's but yeah I see that if you haven't tried already laid before you leave Kansas city stopped at somebody and said Arthur Bryant's out the weather you know my dad not dear will this inject it's can inject that a couple hands couple hands are it's these at 6 also not yet it's
safe it will not object when you pass the attributes in as the hatch Active Record actually estates in in the special characters and treats the entire thing like a string so you can have your she machista users Carson although Ross equal that they want and it will not inject to what about this that if you
want to write a query query that search for records based on a simple fragment so in this case and I mean honestly you wouldn't need to use this particular sequel fragment because you could obviously just you barbecue . find you know name and then the input but say you had a complicated query and you had a dropped under OS equals so barbecue . where and then named equals and the user input so you it's a barbecue where named equals Oklahoma jealous for instance and you won't find another great barbecue joint which became even greater when they drop the Oklahoma and became Joe's Kansas City barbecue everything tastes back but in any case good Boccioni but how is it fair and costs sequel injection thing so this 1 is going to inject but yeah lot of hands up anyone that's like not we've got this this is sick now are you guys are right that 1
does not fare so well because similar to that earlier conversation when we were just leaving its sequels you're shape
year shady user could say that they want to find a restaurant called single-quote cynical and delete from barbecue dash dash which again doesn't sound Battisti restaurant but the in result is that your entire barbecue table is lost and that's really sad so how do we protect ourselves against the on look at this edition using grass equal needed to the point in the query where you are going to put the user supplied data and you replace it with the question work and then you just like comment and then put the input data to put in after that and then
Active Record takes up this thing that we like to call upon you know baby proving the query and the 1st thing it does is that it
sanitizers the user input and that means that it is it's all the special characters so that the entire thing will be treated just like a string it cannot be executable and all that magic happens if you're interested interested in reading any source code and NIS sanitization . being with a little help from quoting . RT I mean I just like that it works and no matter what nefarious thing people passing my 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 the actor record is that 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 at the sequel statement actually gets sent to the database with the placeholders and the database then passes the statement and it comes up with the query plan and it caches that very plan and since the statement and a token back your out and then the actual values family do come through if this statement that is trying to be executed differs from the query plan so if somehow Ross equal did get in there and someone was trying to change the query that was being alone had initially been asked for it can tell that it doesn't not without tokens that was it through like that initial plan and like now where we're not doing that I you cannot change the type of theory that was already planned wants to use the parameterized on queries so I want you to know not every database type supports this I get record database adapter actually determines how little handle things we use parameterized suggest if you are relying on this mixture that you know how it works for that particular database using by most of the major ones like POS presence stuff they do that are at
moving right along back today we're going a step it up a notch well this filter by statement in Jack Jack who thinks things it won't who here recognizes
that this is Python using sequel alchemy just make sure you guys are way now that's it's not even Active Record who
knows what all that stuff us with very confusing but in case you're wondering it
kind of the same thing as defined by and it turns everything into a string so even that reinstatement will not OK what if you
want 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 by demonstrating is this global a single injection yeah the yeah and that's the people that were there was inside gasoline for real supporters raise their hands from was like 0 yeah that's since you the right that group
method allows for sequel to be passed so if you're putting the user data directly into the query with that provide you are going to be vulnerable are at a time for
1 more so let's take a look at having and this 1 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 is inexpensive anyone a group by location and you wanna give the user the option of saying that they want a certain level of God's ratings of maybe some along you know they're like I'm not too picky dad's rating of 2 or more of in this case this user wanted a doubts reading greater than 4 so well that into and no money new are success good to have no it's
injects it actually I at greater risk than a lot of the other methods because it usually ends up at the end of the method change so because it's at the end nothing follows it even less likely with that particular 1 that I'm just having something else coming after in the query chain will stop the injection so does only 1 be careful with having found it just makes it easier for people to show their own people in some
and the thing is is that once again this is easily fixed with the parameterized queries you just pop that question mark and that the statement and Active Record takes care of it and you might be looking at this as a that's very similar to how you fix the other 1 that injection and that's 1 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 going to be awaiting them in all the rest of them assets just using PCA now so you might be thinking
what if people are aware of these vulnerabilities and it's been 18 years and sequel induction seems pretty major but can they fix this that maybe go back to my hotel room tonight and work really hard on a patch and I'm to this poor question and there's going to be no more sequel injection in and reality the not quite that simple so the
problem is it's another example of an angel tension between freedom and
security those vulnerabilities are there because they allow us flexibility
that copies of the work that 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 role we all would have followed it customers would stop asking SUSY sticker overtly day would be better if you gave me a lot of free stuff wouldn't have worked but there was a lot of magic moments that we would have lost there were a lot of really beautiful individual customer interactions where just like all day the copies on us today due to a shot that just wouldn't have been possible and it's the same
thing with Active Record but you don't wanna lose the magic of being
able to really really to dig down into the data and drop down to Ross equal and do like a really crappy query because as beautiful as active record is further probably 90 per cent if not more than anything that you need to query at form there's still the fact that the sequel was made to talk to databases and sometimes if you wanna do some crazy complex query you could either spend like weeks figure out how to do it in Active record or you could do it in sequel and that's going to be the only straightforward way of getting it an effective record didn't
allow this flexibility it starting less and less useful as a apps got more and more and more complex would price and a lot of time starting to write our own methods to talk to databases and writing from scratch and its history program is indeed any indication when we start writing our own things from scratch it's not the core of what we're trying to do chances are again actually in being more at risk than we would have if we just figured out what the Active Record stuff and then our apps would be in the news but for all the wrong reasons and we should apparently be that to Sony cassette so these things they're the ones that are in the news political injection to say and what I get
I know that it's a bit tougher than in the good old days where security was literally like a heavy wooden door with the big guy in and metal split and he did not worry about automated scripts being run by 4 little 3 year olds they were looking for the slightest vulnerabilities encode loses the face-to-face interaction and you we got 1 chance to get right sequence that they're spending a prophet like the 1 who people facets known is the password you had 1 chance and
thank you either not only that his way by the bigger but the reality is that these are actually all that much more difficult now with the built-in security inactive record and just talk about war knowledge of what's going on behind the scenes with those Hopper method we can all write code that keeps a customer's data and ah ax reputation C and secure so
I'm going to fix out to my colleague aircraft lot this Thursday code reading at work was actually inspiration for this talk he has since left us to go work in web security so we're all safer for that so we do miss words and I I 1 I think my husband just ran handwriting anything I ask him for this checking my sequel coder animators are the last and if you guys
would like to learn more about and an amazing resources real sequel . word had just there are places that you can make test it see how it works he goes through all of them in detail it's beautiful resource there's also died that Ruby on Rails upward security . html that they actually the security guide probe Ruby on Rails and has a lot of information you should check out it covers a lot more than the sequel ejection there's the a lost Ruby on Rails cheat sheet and you can also this is crazy just look at your own code go to your web forums Triton Jackson sequel in their see if it works don't do it introduction do in a mobile of little work in local it'll work in production and you should probably be sure that so as I
mentioned I'm just a rather I make codes about our ability and videos rather about coding at YouTube . com flashed on top of the electron in videos that but have you check them out with new things I would also like to continue this conversation on Twitter and if any of you are like new to programming anyone talk about learning decoder sort of learning rails I borrowed the company credit card and we're going to be doing a dinner tonight for beginners and so if you're just starting out anyone dilatoriness 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 then if the mean questions understand and idea so the prewarning of new questions get answered everything thank you for your if the the the the the 1
Nachbarschaft <Mathematik>
Umsetzung <Informatik>
Gesetz <Physik>
Trennschärfe <Statistik>
Skript <Programm>
Elektronischer Programmführer
App <Programm>
Shape <Informatik>
Befehl <Informatik>
Güte der Anpassung
Profil <Aerodynamik>
PASS <Programm>
Generator <Informatik>
Rechter Winkel
Grundsätze ordnungsmäßiger Datenverarbeitung
Ordnung <Mathematik>
Lesen <Datenverarbeitung>
Tabelle <Informatik>
Folge <Mathematik>
Automatische Handlungsplanung
Demoszene <Programmierung>
Reelle Zahl
Deforestation <Informatik>
Delisches Problem
Primitive <Informatik>
Ganze Funktion
Attributierte Grammatik
Ruby on Rails
Spider <Programm>
Patch <Software>
Offene Menge
Wort <Informatik>
Abstimmung <Frequenz>
Statistische Schlussweise
Natürliche Zahl
Formale Sprache
Familie <Mathematik>
Fortsetzung <Mathematik>
Prozess <Informatik>
Mixed Reality
Freier Parameter
Physikalischer Effekt
Sampler <Musikinstrument>
Konfiguration <Informatik>
Zusammengesetzte Verteilung
Arithmetisches Mittel
Verkettung <Informatik>
Twitter <Softwareplattform>
Ganze Zahl
Projektive Ebene
Web Site
Interaktives Fernsehen
Physikalische Theorie
PERM <Computer>
Speicher <Informatik>
Einfach zusammenhängender Raum
Matching <Graphentheorie>
Physikalisches System
Objekt <Kategorie>
GRASS <Programm>


Formale Metadaten

Titel Will It Inject? A Look at SQL Injection and ActiveRecord
Serientitel RailsConf 2016
Teil 10
Anzahl der Teile 89
Autor Rudder, Jessica
Lizenz CC-Namensnennung - Weitergabe unter gleichen Bedingungen 3.0 Unported:
Sie dürfen das Werk bzw. den Inhalt zu jedem legalen und nicht-kommerziellen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen und das Werk bzw. diesen Inhalt auch in veränderter Form nur unter den Bedingungen dieser Lizenz weitergeben.
DOI 10.5446/31573
Herausgeber Confreaks, LLC
Erscheinungsjahr 2016
Sprache Englisch

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract If you've struggled through writing complex queries in raw SQL, ActiveRecord methods are a helpful breath of fresh air. If you're not careful though, those methods could potentially leave your site open to a nasty SQL Injection attack. We'll take a look at the most common ActiveRecord methods (and some of the lesser known ones!) with one question in mind....will it inject? If it's vulnerable to a SQL injection attack, we'll cover how to structure your query to keep your data secure.

Ähnliche Filme