Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Use placeholders. For SECURITY!

by tilly (Archbishop)
on Nov 14, 2003 at 00:46 UTC ( [id://306983]=perlmeditation: print w/replies, xml ) Need Help??

This won't be a terribly long meditation. But it is an important one.

I was bothered by Re: Re: (OT) SSL Certificates: Self-Signing and Alternative Solutions because it shows a fundamental misunderstanding of how easy it is to steal credit card databases. And Re: Perlmonk's "best pratices" in the real world bothered me more because someone came out and said openly that if you haven't used placeholders it is probably because you didn't need them yet. (For those who don't know what placeholders are, DBI lets you just put ? in your SQL, bind inputs to the query, and then the driver takes care of sending data to the database.)

These are both dangerously wrong, and they are wrong for connected reasons. Furthermore the responsibility for their being wrong rests squarely on developers' shoulders. Nobody may have told you that it matters, but it does.

I won't go through all of the details because I don't want this to be a HOWTO Steal Credit Cards guide. But the problem is what is known as an SQL injection attack. You have code that interpolates form data directly into the string. The cracker submits form data that closes off the quoted field, ends the query, and adds another query. The other query can do virtually anything. Depending on the database it can give the cracker a remote login, can determine the schema, can return all credit cards you have, can drop tables, etc.

Finding these is easy. Just walk through some complex forms and enter ' or " into each field until you find one that causes a crash. After that escalate the initial hole into better and better exploits using standard procedures that good crackers are very familiar with. When you are bored with that victim, seek another.

What is more, having the database securely locked behind a firewall won't help - anyone who can reach the poorly coded web page can nail the database. You also can't depend on there being any trouble in figuring out passwords. The attacker doesn't need to know passwords and accounts - you are handing them the login to wreak havoc with. OK, a good DBA can lock things down to limit how much damage having a completely compromised account can cause. But the odds that it has been done with yours are slim to none.

So, what can a developer do about this?

  1. Don't trust user input. Ever. Be paranoid. Using placeholders does this. Escaping things yourself is better than nothing, but by and large the ones who know how to do it right also know enough reasons to use placeholders that they do that instead.
  2. Don't leave debugging hooks like CGI::Carp in production code. The availability of detailed error messages takes virtually all of the guesswork away from the cracker and makes their lives easy.
  3. Upgrade to a recent version of DBI, turn on perlsec, and set TaintIn mode on.
  4. Submit your code to code reviews.
  5. Do a security review. (This is hardly the only common basic security mistake that developers make which nobody else can really do much about.)

There is little that I can do to comfort users about the seriousness of this problem. My guess is that most of us have had our credit cards stolen already, likely multiple times. Few of us would have any reason to know it though. Credit cards are readily available on the black market in bulk (premium prices if they have been tested for validity). Our main protection is that there are too many potential victims and too few crooks.

UPDATE: Fix the link on tainting and fixed a typo (both caught by BazB).

UPDATE 2: Added explanation of placeholders per comment by AM below.

Replies are listed 'Best First'.
Re: Use placeholders. For SECURITY!
by mpeppler (Vicar) on Nov 14, 2003 at 01:00 UTC
    Absolutely. The alternative at the database level can be to force all access via stored procedures, but even that doesn't necessarily protect you against this sort of problem.

    At one client we have set up a fairly elaborate security system where the front end servers hit a middle ware layer on a different server with a request that includes a service name and an MD5 key for that service, which gets validated in the database before the service is allowed to run. This should prevent unauthorized hosts from connecting to the database directly, and from attempting to execute unauthorized database requests (I say "should" because we all know that all software has bugs...). It costs us in terms of performance (for each database request there are multiple round-trips to the database to validate the request, etc.), but preserving the integritiy of our data is essential.

    Michael

Re: Use placeholders. For SECURITY!
by sauoq (Abbot) on Nov 14, 2003 at 03:10 UTC

    Of course, I agree.

    I understand why you were bothered by that node I wrote, but I'd appreciate it if you would re-examine the context in which I said it. It wasn't a node about database programming. It wasn't a node about CGI. It wasn't a node about security.

    It was, in essence, a node about pacing your growth as a programmer.

    In that node, I suggested that learning fundamentals was ever so much more important than learning details. The fundamental issue, in this case, is the security implication of untrusted input.

    You wrote, "Escaping things yourself is better than nothing, but by and large the ones who know how to do it right also know enough reasons to use placeholders that they do that instead." And I agree. But then again, I'd rather employ someone who knows how to do right and uses placeholders than someone who uses placeholders because they saw an article on a website that told them they should "for security." The former's understanding would be far more valuable than the latter's best practices.

    It's all too easy to focus on one or two small details and miss the forest for the trees. Yes, use placeholders. Yes, use tainting. Yes, have your DBA lock down the database to the best of his ability. Yes, use encryption. Yes, take every precaution you can and buy insurance (because you have to assume you're not secure anyway.)

    The one thing I don't agree with in this article is the order that you put your 5 points. Number 4 ("Submit your code to code reviews.") should be number 1. You can't write secure code in a vacuum. If you neglect all of the other points, don't neglect this one. That way, someone can tell you that you are neglecting the others.

    In further defense of my aforementioned node, I must note that I was addressing it to someone whose description of himself implied that he was a novice or maybe intermediate programmer. I could be wrong, but I didn't get the impression that he was writing ecommerce frameworks for a living. Yes, security is important, especially on the wild wild web; and it is paramount when you are handling other people's private data. But most people aren't.

    Ecommerce is sexy and gets all the press but most programmers are probably writing code for relatively mundane, in-house, non-mission-critical tasks. Of course, this is a very good thing because most programmers don't know everything there is to know about security, and projects like these give them a way to gain experience and earn a living without jeopardizing your Visa account.

    Finally, although I agree that the responsibility for security rests on the developers, it doesn't rest on the developers alone. For instance, that responsibility also lies with the people who hire the developers. Perhaps it should be suggested that they always ask interviewees to explain why using placeholders is so important. It seems, to me anyway, that addressing the meta-problem might actually be more effective.

    -sauoq
    "My two cents aren't worth a dime.";
    
      Replying out of order, let me start with the order I put my bullets in. I arranged those from easiest for a programmer to make happen to hardest. Trying to get co-workers who don't want to to do code reviews is definitely an uphill battle, which is why I focussed first on things that you can do which won't hit such political resistance.

      On how critical the data that people work with is, I can't speak for most programmers. Speaking for myself, I have never had a programming job where I didn't wind up learning very sensitive things about people. My sense is that this is pretty typical. People stick information in a database, I have access to the database. People want to improve workflow, I get access to whatever data is in that workflow. People want financial reports run, I get to see the financial data.

      I generally don't care about the data I have access to, but I get that access, and the fact that it is worth hiring me to work with the data means that someone thinks that it is worth a lot.

      On your node, here is a fuller response. The attitude that, "Whatever you know and are getting by with is OK" is one that I highly dislike. Because what you don't know, will bite you. But you generally won't see that it is biting you because you don't know that you can do better. Furthermore far too many people for my taste have the attitude that they know how to program perfectly well and have no desire or need to learn more. I don't like encouraging that. See my response in the thread starting at Life beyond CGI and DBI to see an extreme example.

      On fundamentals, I fully agree. And I agree enough that I wouldn't want to filter a person on whether a certain fact was known unless knowledge of that fact was indicative of basic background for the job that they need to do. I can let not knowing placeholders pass if you have the concept of not trusting user input down - you can learn placeholders pretty easily. Perhaps you don't know them because you used Class::DBI and never needed to look under the hood. But if you are thinking of using them and need a push, I definitely think that the push is worthwhile!

        On how critical the data that people work with is, I can't speak for most programmers. Speaking for myself, I have never had a programming job where I didn't wind up learning very sensitive things about people. My sense is that this is pretty typical. People stick information in a database, I have access to the database. People want to improve workflow, I get access to whatever data is in that workflow. People want financial reports run, I get to see the financial data.

        I generally don't care about the data I have access to, but I get that access, and the fact that it is worth hiring me to work with the data means that someone thinks that it is worth a lot.

        Well there's also the threat. If you are writing something that sells CDs on the internet, then anyone can access it and anyone can/will attack it. A web front end for some mid level managers though (passwd protected so only they can get to it) has a much lower chance of being attacked, so from that point of view while it might be sensitive screwing up and allowing XSS etc. isn't as bad as if it'd happened on amazon.com etc.

        For instance I've worked at places (I managed to leave quickly though :) where people mostly used telnet and had numerous machines where people had root access ... and one place where everyone used one machine for shell access, and gave the root password to it out. Hell one place I contracted at had single letter root passwords everywhere and they dealt with medical information. And while that is completely insane, IMO. The employees while having easy access to the gasoline and lighter, didn't burn the place down on a daily basis. Admittedly if they were knowledgeable enough and wanted to they wouldn't be seen ... but generally the people either weren't knowledgeable or didn't want to.

        But maybe I'm being somewhat too optimistic about the entire race :).

        --
        James Antill
        On your node, here is a fuller response. The attitude that, "Whatever you know and are getting by with is OK" is one that I highly dislike.

        I think that's a poor interpretation of what I wrote. It certainly wasn't my intended message. I hope it didn't sound that way to many others and I suspect it didn't because it seems to have been a relatively well liked node. I think (and hope) an attitude as you describe would attract more downvotes.

        The message I meant to convey was: what you know now is okay and you have to learn at your own pace and in your own way so don't get hung up on what you don't know, especially not details; concentrate on fundamentals and you will improve.

        I think that is appropriate advice to give to a novice-to-intermediate level programmer.

        -sauoq
        "My two cents aren't worth a dime.";
        
      But then again, I'd rather employ someone who knows how to do right and uses placeholders than someone who uses placeholders because they saw an article on a website that told them they should "for security." The former's understanding would be far more valuable than the latter's best practices.

      This seems like a simplification gone too far, personally for either PostGres or Oracle I wouldn't know the entire set that needs to be escaped (or I least I wouldn't trust myself to get it perfect -- which it would need to be). I wouldn't even trust myself to do it perfectly for bourne shell etc., which I use more often, without looking it up. I know the reasons behind why you would do it though, and I could write a non-perfect escaping mechanism (read less than useless). However I doubt there are many people who are cargo culting the use of placeholders ... it's just not that hard a concept. It's probably much more likely that those people are just passing data straight from the web form to the DB.

      Also, personally, if someone said they could escape the SQL properly without using placeholders ... I'd have visions of the thousands of C programers who said they could write buffer overflow free code without using an API that made it impossible not to ... so I might well not prefer them based on that.

      --
      James Antill

      I'd rather employ someone who knows how to do right and uses placeholders than someone who uses placeholders because they saw an article on a website that told them they should "for security." The former's understanding would be far more valuable than the latter's best practices.

      I think it's more common that programmers think they know how to do it right, and will probably even be able to fool an interviewer into thinking they know how to do it right, but will often miss an edge case. Or perhaps they know how to do it right for MySQL, but the same code doesn't catch a potential problem for PostgreSQL.

      I understand enough to know that I don't understand, which might sound self-detrimental, but it actually puts me way ahead of programmers who think they know what they're doing but actually get it wrong. Admiting the fact that I don't understand the complete problem set, I use placeholders because someone who does understand already did the work for me. If that person actually didn't understand (i.e., there's a bug in a DBD's placeholder mechanisim), it's a lot easier to change the DBD module then to fix a lot of programs in production.

      That's not even touching on the other benifits of placeholders, like efficiency of cached statements (especially in a mod_perl environment).

      ----
      I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
      -- Schemer

      : () { :|:& };:

      Note: All code is untested, unless otherwise stated

        I understand enough to know that I don't understand...
        Indeed. This is probably similar to what my Dad (an ex-air force pilot) used to say about pilots: beginners are very careful, and experienced pilots are careful. It's the intermediates that are really dangerous, because they think they know everything, but they don't know enough to know that they don't.

        Michael

        PS - I hope that last sentence makes sense :-)

        I think it's more common that programmers think they know how to do it right

        Sure, but I think you missed my point. I'd rather hire someone who understands the issues and uses best practices than someone that just uses best practices without really knowing why they are "best".

        I understand enough to know that I don't understand, which might sound self-detrimental

        That sounds both honest and like you have a realistic perspective on what you'd need to learn. Those are two other good qualities that one might look for in an interview.

        Regarding how one might fool an interviewer, that's quite true. It depends on the skill of the interviewer as well as his proficiency in the technical material being covered. (Assuming it's a technical interview.)

        -sauoq
        "My two cents aren't worth a dime.";
        
Know what you are doing For SECURITY!
by tantarbobus (Hermit) on Nov 14, 2003 at 02:15 UTC

    From my point of view, saying use placeholder for security is akin to saying use Java for secuity. If you don't know WHY you need to use placeholders, then you should not be writing code that deals with sensitive data, for using placeholder and writing secure code are orthogonal. Placeholder do not make your code secure. Writing secure code makes your code secure wether or not you use placeholder. I'd even wager to say that most that does prepare(q{SELECT this FROM that WHERE data_col = '$value'}) will have many other esaly exploitable problems in the code, and the SQL injection would be the least of your worries.

    The use of placeholders is normaly a feature that you will find in well-written code, but not always; I for one have seen code that uses $dbh->quote() to good effect (I know that the docs say that quote() is not required to be able to handle all input, but with some drivers quote and placeholder call the same functions). And, at times placeholders can give you a false sense of security, for example, in older version of DBD::mysql treating a string as a number in perl caused it not to be quoted when passed to a placeholder. Or how about that problem with the null byte in Postgres (I wonder if it could be exploited?) Or maybe you are dynamically generating SQL to use placeholders based on an abritray set of entries returned, but if you don't limit the set used to generate the where clause, instant segfault. eg.

    perl -MDBI -e '$h = DBI->connect("dbi:Pg:dbname=template1","",""); $h->prepare("?"x10000);' perl -MDBI -e '$h = DBI->connect("dbi:oracle","",""); $h->prepare("?"x10000000);'

    Both of the above problems would be fixed with good coding but not just using placeholders. (Site note, using $dbh->quote() would have worked just fine.)

    Do not get me wrong; I am not saying not to use place holder... USE THEM!! I am saying that if you are going to write secure code you have to know why your code is secure and not just fall back on a litany of simple rules like 'use placeholders'

      My point of view is that you teach people to care incrementally. If I tried to tell people everything that they should be doing to fix their code all at once, they would be overwhelmed and I would be exhausted. If I accomplish nothing more than to make people aware that there is something important to learn, then I have accomplished something which is quite important.

      Yes, the person who does not think about using placeholders is probably doing other things wrong. Yes, many of those other things are likely to be exploitable. But I disagree that SQL injection is the least of your worries. Because from the point of view of an attacker, SQL injection is very attractive. Lots of sites are vulnerable to it, you are likely to get at very valuable data pretty directly with it, traditional security measures (eg firewalls) don't protect against or log it, and you don't need to be extremely knowledgable to make it work. Using standard cracking tools that go after known bugs in commonly used software is easier still, but relatively few programmers write code that gets distributed enough to be the target of such tools.

      As for using quote instead of placeholders, it depends. Yes, some databases (eg MySQL) have drivers that just use quote under the hood. Others (eg Oracle) do not, and in those you often will find that being able to use placeholders is a big performance win. (I've seen top Oracle DBAs claim that avoiding placeholders is the single easiest way to get Oracle to not scale.) Sure, there are bugs in certain drivers. Over time the bugs get found and fixed. But if you roll your own, odds are that you will make the common mistakes and will have more security holes than if you didn't. (Exceptions exist. You aren't named Dan Bernstein, are you? OK, then that doesn't apply to you...)

      And finally, I hoped that my entire meditation would explain one reason to use placeholders, and make it clear that there is a lot more to secure code than just, "use placeholders".

        My point of view is that you teach people to care incrementally.
        As long as that doesn't mean that people should write secure code incrementally. One you put code that needs to be secure into production (or even in development or testing depending on the environment), it better be fully secure. It doesn't make sense to say "well, today I've used placeholders, next week I'll look into that -T thingy". Because you might be compromised before it's next week.

        Abigail

Re: Use placeholders. For SECURITY!
by dws (Chancellor) on Nov 14, 2003 at 06:51 UTC
    So, what can a developer do about this?

    1. Simulate injection attacks in your unit tests.

    A really simple way to do this is to use names like "O'Reilly" in your unit test data. If you're doing test-driven development, this is a very inexpensive strategy for avoiding a lot of trouble.

      And I can tell you of a number of large systems which are web based that have a problem with that! In one case I know of you put an apostrophe in an email address and the Carp output will then give you a clue to the 'backdoor' super-user type access into the system without having to authenticate.

      All for the sake of the most simple untainting. Whether it is a valid email address or not - an apostrophe is not permitted in an email address! Fortunately the data on the system is not extremely valuable. It is an email autoresponder system which handles a lot of marketting email. But then again I did wonder how an address of mine that was in somebody's newsletter list suddenly started getting spam. I suspect the spammers have been in through the back door and downloaded all the lists out of the system.

      jdtoronto

        an apostrophe is not permitted in an email address!
        It is: "'"@example.com is valid syntax.

        Abigail

Re: Use placeholders. For SECURITY!
by hardburn (Abbot) on Nov 14, 2003 at 15:20 UTC

    I was bothered by Re: Re: (OT) SSL Certificates: Self-Signing and Alternative Solutions because it shows a fundamental misunderstanding of how easy it is to steal credit card databases.

    ???

    That entire point of that node was that it is, in fact, really easy to nab CC nums from databases. So easy that you could throw away SSL entirely in many cases and the security of the entire system wouldn't substantially drop.

    I agree that this statement might not be clear:

    While we're bothering to educate users, why not explode the "Must Have Encryption on Credit Card Numbers" myth?

    The myth concerns SSL, not the database server. You should always have encryption on the CC nums in the database, if you must store them at all.

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    : () { :|:& };:

    Note: All code is untested, unless otherwise stated

      My point was that you characterized the act of stealing them as waiting for a good exploit to come along and then finding a poorly secured box that you can target. Which says that for most developers, worrying about it is Someone Else's Problem. It also comforts a lot of people that they are OK because they have a firewall in place.

      I think that developers should be far more paranoid than that.

      Also while I agree that people have a lot of obvious problems which cause more failures than whether or not to use encryption, that is not a good reason to avoid using encryption. Sure, one step in the chain being done right or wrong doesn't usually make that much of a difference. But if people at each step assume that all of the others are wrong, then you don't really have much of a chain at all. Start getting things right where you can control them (your piece) and work from there. SSL doesn't solve your basic problems, but it is an easy thing to do that does solve some that come up.

        My point was that you characterized the act of stealing them as waiting for a good exploit to come along and then finding a poorly secured box that you can target. Which says that for most developers, worrying about it is Someone Else's Problem. It also comforts a lot of people that they are OK because they have a firewall in place.

        Ahh, I see what you're saying now. Certainly, just because you have a firewall in front of your database doesn't make you secure. I pointed it out only because an awful lot of places don't have a firewall in place, and often store cleartext CC nums, and there is nothing SSL or anything else client-side can do to change that fact.

        SSL doesn't solve your basic problems, but it is an easy thing to do that does solve some that come up.

        Agreed. SSL is out there, it works, and it's reasonably easy to set up. I only wanted to point out that well-meaning people have taught Aunt Nellie that if that little padlock shows up at the bottom of the browser, her CC num is secure, when it often isn't. As long as the Internet community already went to all the trouble to get SSL working, we might as well use it. However, it's by far not the weakest link in the chain.

        ----
        I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
        -- Schemer

        : () { :|:& };:

        Note: All code is untested, unless otherwise stated

Re: Use placeholders. For SECURITY!
by zentara (Archbishop) on Nov 14, 2003 at 15:48 UTC
    Alot of what you all said about security, sort of goes down the drain, when the "dumb IT manager" forgets or refuses to "turn on encryption" ; as in the recent Lowe's wireless caper. You know the type....graduated top in his/her class at business school and knows nothing about computers except the Microsoft hype. Who gets hired and put in charge of operations. My point being that there are bigger holes in the "system" than programmer errors. Things like disgruntled employees selling numbers, organized crime extorting numbers, etc. High level trusted employees ripping the system off. I don't know how many times I've heard about some major bank or fund getting ripped off by some trusted employee, then the bank refuses to press charges, because of unwanted bad publicity.

    As much as I hate to say it, the only real protection for credit cards, is transaction tracking, epitomized by the Washington "Homeland Security" Plan to track all ecommerce, from sale to delivery. Many sites now will only deliver to the "address of record" on the credit card, which is a pretty good first step. The next step would be snapping photos of all people using the card in a public place.

Re: Use placeholders. For SECURITY!
by bradcathey (Prior) on Dec 01, 2003 at 16:08 UTC
    I'm sorry I missed this thread when I needed it, which is now. I hope someone finds this reply...er, question when viewing Newest Nodes. I quote from the original post:
    "You have code that interpolates form data directly into the string."
    Do you still need placeholders for security if you are untainting all user form input?

    CGI:
    use Validate; use strict; use CGI qw(:standard); my $firstname = Validate->alphanum( param( 'firstname' ) ); print "Missing or invalid first name\n" unless $firstname; my $stmt = "INSERT INTO tablename VALUES ('$firstname')"; excute...
    Module:
    package Validate; sub alphanum { my ($class, $value) = @_; return unless $value =~ /^([A-Za-z0-9 -]*)$/; return "$1"; } 1;

    —Brad
    "A little yeast leavens the whole dough."
      It is a judgement call, but I would call it still adviseable.

      Your validation will not let through anything that can trigger a successful SQL injection attack. And you are therefore safe in not using placeholders. But other fields with different validations are not safe to use because they allow quote marks. If you are in the habit of skipping placeholders when not needed, then you are more likely to omit them when you do need them. Worse still, even if you don't, people who follow you are more likely to not understand the difference and get it wrong.

      Further there is a maintainance issue. Suppose that you did the above for lastname. And then a customer named O'Connell complains about your application not allowing him to use his name. Well whoever gets the change request makes the obvious fix, and it is so simple that they don't really test it. (Yeah, I know..this kind of stuff happens in real life though. Bear with me.) And now your perfectly safe code has become unsafe instead. Why risk it?

      Of course this is all in addition to major performance gains with some databases. Yeah, I know and have given the optimization rant plenty of times. But an issue that routinely causes a single point of failure in key systems to fail is worth being aware of.

        Okay, tilly, you are making me a believer. Armed with Paul DeBois' book MySQL and Perl for the Web and your posts, I'm starting to get it.

        The other thing I've been doing is escaping the quotes and apostrophes in strings (like "O'Connell") with $text =~ s/('|")/\\$1/g and it looks like placeholders takes care of all that for me. 1 stone, 2 birds. Thanks.

        —Brad
        "A little yeast leavens the whole dough."
Re: Use placeholders. For SECURITY!
by theAcolyte (Pilgrim) on Nov 21, 2003 at 11:58 UTC

    Sorry for a pretty late reply to this post. While I agree with Tilly's post (using placeholders being a Good Thing) if your database is open to subjugation via sql injection, you ought to rethink a few things ...

    I don't have any "professional" programming education, but it became aparent to me on my first day of playing with mySQL that you ought to do two things when you have a database table storing credit card info on the web (or anything else that sensitive):

    • Acesses that table with a mySQL user that ONLY has write access permission. You can even set up a 2nd db that has NO user with read permissions (no user that submits a web form). Also, I generally assume you would not chose to name the table of CC numbers something obvious like table_creditcards.
    • Encrypt the CC data in the table

    I would imagine, even if you don't understand the idea of placeholders, or preventing a sql injection attack, this ought to stop many potential problems.

    BTW, if I'm completely wrong, and these steps accomplish nothing, tell me. :-)

    - theAcolyte

A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://306983]
Approved by mpeppler
Front-paged by diotalevi
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2024-03-19 02:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found