Catch up on stories from the past week (and beyond) at the Slashdot story archive

 


Forgot your password?
Close
typodupeerror
×

SQL Injection Attacks Increasing 384

Posted by CmdrTaco from the so-easy-to-miss dept.
An anonymous reader writes "Help Net Security has a story that covers the dramatic increase in the number of hacker attacks attempted against its banking, credit union and utility clients in the past three months using SQL Injection." Article follows up on press release with a little more information. Not a lot here shockingly surprising, but it's worth mentioning that SQL injection is a real pain for web developers. You have to be very careful about checking user input.
This discussion has been archived. No new comments can be posted.

SQL Injection Attacks Increasing More

SQL Injection Attacks Increasing

Comments Filter:

  • Another web developer pain... (Score:5, Funny)

    by Anonymous Coward on Wednesday July 19, 2006 @08:32AM (#15742456)
    Sudden traffic surge from certain news sites can be a pain.

  • How difficult is it. (Score:4, Informative)

    by El_Muerte_TDS ( 592157 ) on Wednesday July 19, 2006 @08:34AM (#15742465) Homepage
    Simply forcing request variables to the correct type and escaping all strings is pretty much the only thing you need to do.
    Most languages provide the functionality to do that (in php: intval() for all integer request vars, and _escape_string() for string data.).
    It's just a small amouth of work, yet a lot of people are way to lazy.

    • Re:How difficult is it. (Score:2, Informative)

      by Goaway ( 82658 )
      Or, you could use a language that doesn't force you to do this by hand, which is pretty much every langauge except PHP.

      • Re:How difficult is it. (Score:5, Informative)

        by aymanh ( 892834 ) on Wednesday July 19, 2006 @08:46AM (#15742526) Journal
        PHP doesn't force you to do that by hand, you can make use of the numerous database abstraction layers for PHP, like PDO [php.net] or PEAR::DB [php.net].

        Here is an example, taken straight from PDO's page:
        $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':value', $value);
         
        $name = 'one';
        $value = 1;
        $stmt->execute();
        The framework is there, PHP developers need to make use of it, but sadly things like the following are still common:
        mysql_query('SELECT value FROM REGISTRY WHERE name = "' . $name . '"');
        • Your examples show exactly why web developers don't tend to use prepared statements / data binding for their DB queries: it is much *easier* to do it the other way.

          Frankly, this will continue until doing it the "right" way is almost as easy as doing it the quick hack way. The api should be:

          bind_query('SELECT value FROM REGISTRY WHERE name = ":name"', name=$name);

          I don't think this is possible in PHP, unfortunately, but the language ought to be extended to allow it. Many other languages can do it. This on
      • This is mostly correct.

        You can program incorrectly in any language. Plenty of people still sprintf into SQL statements and directly execute them with no params instead of prepare-ing them first and doing an execute with parameters. They do it with perl, python and other languages.

        A large portion of the programmers out there simply do not understand and do not care about the difference in security and performance between sprintf used with static-like SQL and proper dynamic SQL with parameter replacements. In

    • Re:How difficult is it. (Score:5, Informative)

      by eggoeater ( 704775 ) on Wednesday July 19, 2006 @08:40AM (#15742499) Journal
      Simply forcing request variables to the correct type and escaping all strings is pretty much the only thing you need to do.
      Or you could just use stored procedures.
      I've been doing that for years without any problems.
      I've also never had any issue with "business logic". I can keep my business logic
      seperate with stored procs. (I never understood that argument against them.)

      • Can you please tell me how to manage 600 stored procedures in a sane manner? You get this giant list of stored procedures which are not categorized in any way. There's no intellisense stuff when you're trying to use them in your code either. So, every time you need to call one, you have to search through your docs to figure out what it is. Also, I don't think that any of sql databases really handle source control very well. Sure you could have files with all your stored procedures in them, but then you

        • Re:How difficult is it. (Score:5, Informative)

          by beavis88 ( 25983 ) on Wednesday July 19, 2006 @09:08AM (#15742630)
          1) Use a sensible naming convention. eg P_User_Create, P_User_Delete, etc. Use the naming conventions to effectively categorize your stored procs. This takes a little planning and discipline, but what "best practices" don't? The "intellisense issue" is a red herring IMHO - if anything, you're worse off in this regard without sprocs.

          2) USE SOURCE CONTROL. Without trying to be nasty, you're insane (or a one man operation) if you use the database as your authoritative source for stored procs. If you have any environments beyond a production server, the ability to script installation/alteration of procs is essential.
          • Just what I was going to suggest with one more:

            3)600 procs?? It sounds like you've put too much in one database. I've seen groups do this and it usually leads to scalability problems. I'm not talking about multiple servers; just spliting things up catagorically into multiple databases in the same instance. In sql server they're called databases, in Oracle they're called schemas....not sure about db2 or sybase.
            The end result is you have all your customer related data and associated objects (views, sto

        • Re:How difficult is it. (Score:5, Informative)

          by CaptainZapp ( 182233 ) * on Wednesday July 19, 2006 @09:10AM (#15742648) Homepage
          Sure you could have files with all your stored procedures in them

          Bingo!

          but then you have to have 2 copies of everything.

          Stored procedures (like any DDL statements to set up your database schemas) should be handled like any other source code and treated as such. This includes version control

          There seems this "but I can pull it out of the database with my super GUI tool, so why should I keep it on file too?" attitude. Well, duh; it's mighty hard to pull anything of a database whoms disk just crashed.

          For recoverability reasons database objects (including stored procedures) should be scripted and version controlled. Period.

        • If you have 600 SPs, you're going to need 600 functions/classes that do the same thing. SPs are just as easy to name as functions/classes. Someone already mentioned naming conventions and source control, in addition, put all your SPs in text files and use a build script to install them. Much, much easier looking through systematically named SPs than code. We use something along the lines of __. Very easy to find SPs within seconds in Explorer.

          --trb
        • Or rather, you can use stored procedures, but that's not what is being discussed. We were talking about binding variables. Two different things.
          • Stored procedure: a function that runs within the database server.
          • Prepared/bound statement: something in code -- usually provided by the programming language's database layer -- that looks like the following:

          UPDATE things SET alpha = ?, beta = ? where foo = ? and bar = ?

          Then you set item 1 to some value, item 2 to another, etc. Other variations exist as well:

          UP

      • I'm on a project where they tried mandating everything be in stored procedures. For truly dynamic querying, it's just not feasible. We have one query where the user can input around 30 pieces of data and they're all optional. A query like that would be painful to write in a stored procedure, so for those we have parameterized SQL. Parameterization solves the problem just the same and allows flexibility to create SQL on the fly (not: we're using Sybase, not Oracle. Don't ask why.)

        --trb
        • In SQL Server you can do something like this (there's a way to do this in Oracle but I forget. Not sure about sybase.)

          create procedure myTestProc @someDateTime datetime = '1/1/2050' as
          --put insert, delete, update here....
          select * from someTable
          where (@someDateTime >= someTable.someDate or @someDateTime = '1/1/2050')

          The where clause basically says, if the optional parameter is not the default then check it, otherwise ignore it.
          I've never had 30 optional parameters but I've had quite a few and this
          tric

      • Or you could just use stored procedures.

        For most web applications, stored procedures are overkill and just too time-consuming to implement. If you have a relatively uncomplicated database (which is the case with 90% of web applications, I'd guess), then ad-hoc queries allow much faster development time. Even if you do make the effort to properly quote all your strings, and convert your numbers to numbers rather than using them directly.
      • Stored procedures buys you nothing in security. Just bind all variables, and you can dynamically create to your hearts content.

      • Re:How difficult is it. (Score:5, Informative)

        by Aceticon ( 140883 ) on Wednesday July 19, 2006 @10:36AM (#15743210)

        Or you could just use stored procedures.
        I've been doing that for years without any problems.
        I've also never had any issue with "business logic". I can keep my business logic
        seperate with stored procs. (I never understood that argument against them.)


        If your stored procedures are only very thin layers encapsulating low level database access operations (thus not much more than pre-packaged selects, updates, inserts and deletes) you should have no business logic in the database problems.

        On the other hand there's a couple of downsides to such a design:
        - It requires developers with a good level of expertise in both the language used to develop the core of the application and the one used for the stored procedures. This is true both for initial development and for maintenance.
        - It makes an application tightly couple to the database. If you want to port to another database, at the very least you will have to redo all the stored procedures.
        - It increases the likellyhood of having version conflict problems between the core application and the database application components. More specifically, the data-model is usually more stable across versions of the application than the actions executed on data in that model (eg "find all employees in more than Y departments and whose manager is level X"), and thus if you store in the database code which is tightly couple to the actions that the application executes on the data then previous versions of the database (for example, those restored from a backup) are not likelly to work with the lastest version of the application (nor are they likelly to be easilly "fixed" by a DBA).
        - It's harder to debug code that crosses platforms and languages

        More in general, the problem of SQL injection can be avoided simply by using prepared statements or any other type of SQL query that takes input parameters instead of using string concatenation to make SQL queries that include the input values.

        Using stored procedures to solve this specific problem is very much overkill.

        Beyond this, the only good reason i can see for using stored procedures like this is for performance reasons if you do some level of post-processing on the results or some sort of "smart" block updating of data. In this case, stored procedures should only be used in a very small number places (to solve high-impact IO bottlenecks between the application and the database) and not in a generic way.

        The last couple of reasons i see for such a design are:
        - A "job protection" measure by locking the application to the specific skillset combination of a specific developer
        - Because the developer prefers-to/is-more-confortable-with developing code in the database that in the core application
        Hardly good reasons IMHO

    • No no No no No no NO (Score:5, Informative)

      by IPFreely ( 47576 ) <mark@mwiley.org> on Wednesday July 19, 2006 @08:51AM (#15742552) Homepage Journal
      You don't need to escape strings.

      Just don't build your query on the fly.
      Bind ALL parameters to placeholders in a prebuilt query. Binding is an instant kill for any SQL injection attack. It is also much more effecient on many databases.

    • Re:How difficult is it. (Score:3, Informative)

      by hey! ( 33014 )
      How difficult is it? Well, like most things in life the real answer is it depends.

      In this case it depends on how many kinds of things you want to do with data. For simple stand alone applications, like a blog or something, it probably isn't much. Most insertions, updates and queries probably happen behind a DAO pattern anyway; it's easy to enforce semantic checks there, and it's no big deal if the data is stored in some kind of garbled looking encoding. But in the post internet bubble world, databases h
    • I'm a student web programmer for the webdevelopment lab in a major U.S. University. The platform they basically told me I had to program on is PHP with MySQL. The server doesn't support anything else and getting the server guy to update or add anything new is a major pain and usually impossible. Point in case: I'm still working with PHP 4.1.2 and MySQL 3!!!!!!!

      I still have to write some fairly secure applications (if they get breached there won't be any terribly sensitive information, but there are some thi
  • "SQL injection is a real pain for web developers. You have to be very careful about checking user input." Say what? All you have to do is use parameters, not string catenation. Of course, checking the user input is good for other reasons but not for SQL injection attacks. Or are there web application frameworks which don't support parameterized SQL statements?

  • Qualifications (Score:5, Interesting)

    by Chris Graham ( 942108 ) on Wednesday July 19, 2006 @08:35AM (#15742474) Homepage
    Perhaps all programmers working on professional database systems should have to get a professional qualification to show that they can write secure code. I wouldn't say the same should be manditory for things like usability or stability (except for special sensitive areas), but being able to write code that actually allows serious danger without qualification is pretty weird. Builders need qualifications, electricians do, gas installers do, ...
    • Politicians don't.
      • That's a fantastic point! Let's force politicians to be able to pass a democratically voted-for test before they can get office. It would include basic history, requiring an understand of, for example, how the Nazis got into power. Or they could be checked that they know a rough summary of the current budget.

  • Checking input is a "pain in the ass"?!? (Score:5, Insightful)

    by fractalus ( 322043 ) on Wednesday July 19, 2006 @08:36AM (#15742478) Homepage
    The only people who consider it a pain in the ass are people who are (a) lazy, (b) not adequately security-conscious, (c) programming without a framework that provides good tools to do this. The reason we have so many SQL injections is because we have legions of web programmers who were never taught how to write code in a hostile environment. Web programming is never presented in that light; it's always, "here's a quick little script that fetches twenty records from a database and displays them." Security is far too often a footnote or an appendix that beginning programmers never get to. Building apps for the web is not like doing your Data Structures I homework. You need a different mindset. It's a lot more like designing locks--for prisons full of inmates eager to get out.

    • Web programming is never presented in that light; it's always, "here's a quick little script that fetches twenty records from a database and displays them."

      It's actually worse than that, not only is security not adequately discussed, in a huge number of cases, sample code is given that is totally insecure. Newbies are being taught to write insecure code by ignorant tutorial authors.

      I'm not sure why, but there's something about web development that makes people with the tiniest amount of knowledge

      • Hell, there are some PHP books out there that do this. I have been primarily a PHP developer for a while now (not because it's the only language I know, I just find it to be the fastest and easiest for me over my experiences with Perl, ASP, and *shudder* ColdFusion, but that's personal preference), and I have found that almost ALL the knowledge I learned from books from the beginning of my experience with PHP is virtually useless nowadays (with the exception of the basics, obviously, like printf() and such)
    • To be more generic, the reason we have so many web security compromises at all is because we have legions of web programmers who have never been taught how to write code.

      90% of the friends I have who are web developers have no formal engineering training.

      It shows. (No offense to any of them who may read this... but seriously, your code sucks.)
    • The other category of people that consider that it is a pain in the ass are people that start working on an already existing project containing thousand of webpages developed in a time when security was no concern or when the application was not supposed to be made available on internet or when the application was supposed to be your team little private quick and dirty monitoring tools done by the boss kid during his internship.

      There is a lot of legacy code and lot of code that was never meant to see a prod

  • Hard for Devs? (Score:3, Insightful)

    by CHR1S ( 694833 ) on Wednesday July 19, 2006 @08:37AM (#15742484) Homepage
    How can it be that hard for web developers to check data before it is submitted? I wouldn't imagine trusting the data that an anonymous user can enter into my website.. so maybe I'm just trained to check data. Of course, I'm also glad I use MySQL with PHP where a simple mysql_real_escape_string can prevent any popular SQL Injection attempt.

    • Re:Hard for Devs? (Score:5, Interesting)

      by Goaway ( 82658 ) on Wednesday July 19, 2006 @08:41AM (#15742507) Homepage
      You're glad that you use pretty much the only langauge where this is not done automatically for you, but which instead forces you to use a function with a name like mysql_real_escape_string()? And that actually has a similarly-named function without the "_real_" that doesn't do the job right? Just kidding with that other one, here's the real one!

  • A pain for who exactly? (Score:3, Interesting)

    by MosesJones ( 55544 ) on Wednesday July 19, 2006 @08:38AM (#15742487) Homepage
    but it's worth mentioning that SQL injection is a real pain for web developers

    Which web developers would these be? MuppetsR'US ? SQL injection is a pain if you take the input and lob it directly to the database without doing any sort of validation that the information is sensible.

    Its a great example of all those people who scream "THIS IS SO MUCH QUICKER TO DEVELOP IN THAN THE OLD WAY" and then bite it after the system goes live.

    SQL injection isn't a pain, except for those who think they've found a new quick magic bullet that solves all the problems and the old fuddy duddy practices are now all redundant.

  • Interesting, given that SQL injection is one of the easiest attacks to protect against, by making all database access through an abstraction layer that escapes input, many web frameworks have support for database abstraction layers and prepared statements, like PEAR::DB [php.net] for PHP, developers just need to make use of them.

  • Input checking is a half-assed solution. (Score:4, Interesting)

    by cduffy ( 652 ) <charles+slashdot@dyfis.net> on Wednesday July 19, 2006 @08:41AM (#15742508)
    Checking input for escape attempts is error-prone. Passing in parameters as bind variables *isn't* error-prone (with regard to blocking SQL injection attacks); makes string quoting completely moot; and can result in a massive performance increase (particularly against Oracle) to boot.

    I continue to be in disbelief that anyone doing professional database work can *not* follow this widely accepted best practice and continue to be employed.

  • Use PreparedStatements with Java (Score:5, Insightful)

    by sbrown123 ( 229895 ) on Wednesday July 19, 2006 @08:42AM (#15742514) Homepage
    If your webapp is Java based, use PreparedStatements. Never use Statements. PreparedStatements are immune to SQL Injection based attacks since the variable replacements are never interpreted. PreparedStatements are also much, much faster.
    • Yeah. I never really got how injection attacks were really that much of a problem. Just use prepared statements. Not only will you system be more secure, it will run faster. You don't even have to worry about injection attacks because it's impossible. Failing that, you could just make sure that you do your own verification of the string before doing it, but really it's not worth the hassle. It much easier to write prepared statements.

    • Re:Use PreparedStatements with Java (Score:4, Informative)

      by IPFreely ( 47576 ) <mark@mwiley.org> on Wednesday July 19, 2006 @08:55AM (#15742579) Homepage Journal
      PreparedStatements can be immune to SQL Injection based attacks.

      You should stipulate that you must bind all parameters to placeholders. You could use PreparedStatement the same way as Statement and have the same problem. Bind all parameters, no matter what language you are using.

    • Amen, brother! PreparedStatements can mean the difference between a scalable app, and a thrash-monster.

    • Re:Use PreparedStatements with Java (Score:5, Informative)

      by (trb001) ( 224998 ) on Wednesday July 19, 2006 @08:58AM (#15742596) Homepage
      Additionally, make sure you use PreparedStatements/CallableStatements correctly. I've seen people mark up a PreparedStatement like this:

      String SQL = new String("select * from user where username = '" + username + "'");
      PreparedStatement statemnet = connection.prepareStatement(SQL);

      That does *nothing* for you, and is just as insecure. Instead, make sure you use parameterized statements:

      String SQL = new String("select * from user where username = ?");
      CallableStatement cs = connection.prepareCall(SQL, ...);
      cs.setString(1, username);

      Most databases treat the two very differently. In the second case, the database compiles the statement and then compares the username field with your value. In the first, your value is inserted and then compiled, allowing injection.

      --trb
      • String SQL = new String("select * from user where username = ?");
        This is wasteful -- it generates a new String object every time when it's just a literal. Just use
        String SQL = "select * from user where username = ?";
    • There are similar ways to do this in any "real" language. There are parameters available in ADO, ADO.Net, ODBC, JDBC, and just about whatever other database interface that exist.

      Working in the QA department for a database vendor ( -- shameless plug -- Intersystems Cache [intersystems.com] --) I can tell you that working with parameters is not all that much more difficult than blind SQL. Most languages can create bound parameters in a single extra line of code.

      Note that the statement about being much faster really does depen

  • Sure, if I'm putting together a blog for myself security may not be my top priority and in a situation like this the "I'm too lazy and this is a pain in the ass" excuse is fine (just dont complain if things go wrong).

    However, it's an alltogether different story if you're doing professional web development - the "I'm lazy" excuse doesn't cut it when you're developing something commercially. It is your job to make a functional application and a (large) part of that is making it resilient towards exploits. H

    • Sure, if I'm putting together a blog for myself security may not be my top priority and in a situation like this the "I'm too lazy and this is a pain in the ass" excuse is fine

      This is great. I never knew people had blogs just for themselves. But may I ask, on the off chance that you and your insights were actually worth reading about would you give a thought to those commenting on your weblog? Their email addresses? usernames, passwords? How many of them will use the same password for their email as they wi
  • I thought if you just parse out the ' in user input you are immune. No?

    With all my Web Apps I create a function called SafeChar, and have it replace the ' with &#39 ;.

    How else is SQL injection done? It's an embarrassing questions to ask, and fortunately I write software for small companies internal use only... but if you don't ask I guess you don't learn.

    • Re:serious question (Score:3, Interesting)

      by cnettel ( 836611 )
      There are some possibilities if some part of your stack is using UTF-8, for example. What one portion doesn't interpret as a ' will effectively hide or be translated into ' at a later point. You can come up with more variations of the basic idea.
    • With all my Web Apps I create a function called SafeChar, and have it replace the ' with &#39 ;.

      How else is SQL injection done?
      It's done in base2, base8, base16, and base64

      There's also fun games you can play within/amongst the various encodings.

      It depends on what step of the input parsing your SafeChar function is.

    • No. (Score:2)

      by iabervon ( 1971 )
      If you just remove or escape any ' characters, you're depending on there not being any byte sequences that the database interprets as ' characters that your function doesn't. This has often turned out not to be the case, particularly with respect to invalid UTF-8 strings. The only safe method to avoid injection attacks is to make sure that no database code parses a statement including user input, because you never know exactly how the database parser will handle statements that programmers wouldn't send int

    • I thought if you just parse out the ' in user input you are immune. No?

      No. For example, an attacker could use a backslash as the last character in a string, and it would escape the quote you provide to delimit the string.

      Trying to roll your own escape function is insanity. Don't do it. Every database API in existence provides well-tested escaping functions. Use them.

      With all my Web Apps I create a function called SafeChar, and have it replace the ' with &#39 ;.

      Sounds to me like your

    • Mark me OT or mod me down with something, I'm fine with that.

      The responses to the serious question post are an example of what's good about /.. In many circles, this question would have gotten "do you want me to write your code for you?" or "RTFM", or "Google (something here)", or statements that question the poster's value in the world. I learned something from the replies, and I appreciated the tone of voice of the replies. I, for one, am so glad so many smart people post here.

      OK, back to your regular

  • Solution for PHP programmers (Score:3, Informative)

    by ylikone ( 589264 ) on Wednesday July 19, 2006 @08:48AM (#15742538) Homepage
    Make sure you specify where you get your incoming data from, like using $_POST, $_GET, $_SESSION, etc, don't just grab them from the air (with globals on).
    Make sure you use mysql_real_escape_string() on all incoming data that is headed for the mysql database (to get rid of SQL injection).
    Make sure you use strip_tags() on all incoming data that is headed for output on your page (to get rid of cross-site scripting).

    • Re:Solution for PHP programmers (Score:4, Insightful)

      by Bogtha ( 906264 ) on Wednesday July 19, 2006 @09:19AM (#15742699)

      Make sure you use strip_tags() on all incoming data that is headed for output on your page (to get rid of cross-site scripting).

      Please don't do this, it's bloody annoying when half your input gets chucked away because you used a special character. I really don't see why that function ever existed, it's a total fuckup and completely unnecessary when things like htmlspecialchars() exist. Encode your user-supplied data properly, don't simply chuck bits of it away.

    • For what it's worth, the $_SESSION array is only going to hold data that YOU put in there. Session data is stored on the server, not the client. It wouldn't be a horrible practice to run session data thru your normal "Cleaning" routine but it's not vital. If someone has the ability to stuff session variables then they're executing code on your server and you've got a lot more problems then injection attacks.
    • I should have also mentioned that Pro PHP Security [amazon.com] is a good book on the subject of creating secure PHP code.

  • it's worth mentioning that SQL injection is a real pain for web developers.

    I think it is worth mentioning that SQL injection is a real pain for poorly developped web applications. A simple paying-attention and good design of the application layers makes sure SQL injection cannot happen at a cheap cheapo price. 3-tiers anyone?

  • "Careful" vs. "correct" (Score:5, Insightful)

    by jc42 ( 318812 ) on Wednesday July 19, 2006 @09:11AM (#15742654) Homepage Journal
    You have to be very careful ...

    This phrase is a common tipoff to one of the main problems.

    The computer doesn't give a damn how careful you are. If you spend hours carefully crafting a chunk of code that, through your ignorance, has a big security hole, all your care hasn't helped a bit. You have merely produced bad code.

    OTOH, someone with good knowledge of the subject might toss off a 30-second routine that, due to their understanding, is highly secure.

    Carefulness has little to do with doing a good job. Carefully doing it wrong is merely doing it wrong, no matter how careful you are. And doing it right is doing it right, even if you hardly gave it a thought.

    What we need here isn't useless exhortations to "be careful". What we need is education about how code gets into trouble, and training in writing code that doesn't have problems.

    Yeah, I routinely write code that checks input. But if there's some hidden gotcha that I don't know about (typically in some library routine that's not visible to me), I'm quite aware that my careful checking might do little good.

  • Just stick it [php.net] around any non-constants you pass in to MySQL (especially ALL user input or user-influenceable input) and you should be good.

    Of course, to minimize the risk that you miss one, you might want to use functions or classes to wrap mysql_query. EX I might make a "function selectFromTableX" that takes one field name and one value to compare for equality in the WHERE clause (assuming that's all I ever use SELECT on that table for). Or you can make a class for every table and wrap up ALL queries f

  • Multi-tiered approach (Score:4, Insightful)

    by Billosaur ( 927319 ) * <wgrotherNO@SPAMoptonline.net> on Wednesday July 19, 2006 @09:13AM (#15742668) Journal

    First rule of writing CGI: never trust the data! I work in Perl, and when an app is exposed to the outside world, I have to assume someone is going to try and get in through some hole if they can (or worse, will do something stupid that would have a negative affect oon my systems).

    It starts with the web page -- validate input data. I know, I know, anyone can copy your page and rip out the JavaScript validation, but it doesn't hurt to put up a first line of defense. Next, before you actually use the data from the form for anythig validate it separately. In Perl, I have taint mode enabled by default for external apps and I treat all the data I receive as if it were dog crap. I massage it with regexes to make sure it is what it's supposed to be, and then pass it on to be processed. I find the best way to put up a wall is to have the form parameters sent to a validation script, then have the validation script call the script which would run the actual query, throwing back an error message to the user (and sending me a message in the process) if something's not right.

    Data validation is really not that hard, especially if you know exactly what the inout is supposed to be. It gets iffier if the user can put in pretty much anything -- then you have to be a little more paranoid.

  • Dynamic 'WHERE' clauses (Score:5, Informative)

    by TheRealBurKaZoiD ( 920500 ) on Wednesday July 19, 2006 @09:15AM (#15742681)
    I think one thing everyone is overlooking, and I didn't see it mentioned before I posted, is that alot of newbies, and even intermediate SQL developers either can't use stored procedures because they're using some old version of MySQL, or they have problems writing stored procedures that include dynamic WHERE clauses, or they just don't know that you can do that. It's been my miserable privilege to have seen some pretty goddamn bad SQL code in my life, code that was so bad it would make you physically ill, simply because the developer didn't know any better. Remember kids:
    1. Stored Procedures
    2. Parameterized Queries
    3. Learn the SQL-92 Specification (so that you're familar with the language beyond just SELECT, INSERT, UPDATE, and DELETE. There are all kinds of things out there to help you get rid of that dynamic code, like COALESCE, and CASE WHEN, etc.)
    Here's the SQL-92 Specification [cmu.edu] (pops in a new window)
  • Often when I am on a page that looks SQL-injectionable, I'll try a few things just for giggles. I've been doing this for a few years now. I'd say that there are much, much fewer injectionable sites then there used to be...
  • A better solution is to define all input fields by means of a framework that properly escapes apostrophes and other unwanted characters. This will effectively make SQL injection impossible with a minimum of fuss.

  • SQL Euphoria (Score:5, Funny)

    by digitaldc ( 879047 ) * on Wednesday July 19, 2006 @09:19AM (#15742706)
    The last time I did a SQL injection, I hallucinated that everything around me was displayed in an orderly array.
  • Many developers write code like execute("SELECT ... WHERE NAME='"+name+"' ...) because it's so easy, they are lazy, or because they are clueless. Many know that they should use bind variables, but not all (and peer reviews are not very common).

    There is a way to solve SQL injection problems: Disallow text literals in the database engine. Or even, disallow literals (including numbers) at all. This could be a setting in the database that is on by default, and only off for certain applications (ad hoc query t

  • But ... but ... IT'S CHEAP! (Score:4, Interesting)

    by Opportunist ( 166417 ) on Wednesday July 19, 2006 @09:40AM (#15742834)
    You get what you pay for. A lot of people already suggested easy solutions to the problem that are just as easy to implement and that would immediately make the problem disappear. So why is it not done?

    Simple: The people who write those insecure databases don't even know that those functions and features exist. Some ages ago, they learned a bit about SQL, maybe did a course about it (so they have a sheet of paper saying "Look, I can do it!") and that's it.

    HR managers tend to go by papers, and by price. Now, who do you think is cheaper to hire? A person with a well rounded education concerning computers, programs and the fallacies, pitfalls and security issues around them, or someone who learned his SQL statements by heart and has no clue what exactly is going down inside the server?

    Sure, both of them will create code that does what the specs say. As long as you only enter data according to spec (which is, interestingly enough, ALL that is checked, even under the SOA). The true quality of code is revealed as soon as you pit something unexpected and malicious against it.

  • As a banking internet security guy . . . (Score:3, Insightful)

    by Kope ( 11702 ) on Wednesday July 19, 2006 @10:22AM (#15743115)
    It amazes me that there are banks out there don't do code reviews and pen-test to prevent simplistic attacks like this prior to rolling something into production?

    God lord!

    We require 3 layers of data validation (as part of the web interface, as part of the middle-ware layer, and within the database as triggered stored procedures for updates and inserts.)

    Not doing this SHOULD be criminal in my mind.

  • Once upon a time. (Score:3, Insightful)

    by sgt scrub ( 869860 ) <saintium@NOSpAM.yahoo.com> on Wednesday July 19, 2006 @10:28AM (#15743153)
    I had a friend that was convinced her web front end to a database centric application was bullet proof. The user interface was accessed by clicking on a java script link which controlled the browser behavior. It brought up a browser window without toolbars. In the browser window all of the options were choosen via select boxes. Nothing new was added by the user through the application. She started to brag about the amount of code she didn't have to write to filter user input.

    I started a tcpdump -xX port 80 and host her.host Because everything was being passed plain text we could see everything in the uri. After a quick nmap -vv -sV -P0 her.host I connected via telnet her.host 80 After the required http 1.1 hello stuff I started submiting commands to her cgi script; alpha characters instead of numeric, big decimal numbers, negative values... It didn't take long for her to decide to rewrite it.

  • Its a pity... (Score:3, Interesting)

    by dcam ( 615646 ) <david AT uberconcept DOT com> on Wednesday July 19, 2006 @08:00PM (#15747059) Homepage
    .. because avoiding SQL injection is relatively easy to do.

    1. Use only prepared statements or stored procedures (Note even without concerned of SQL injection this is a good idea).

    2. If you use stored procedures do not use any of the passed in values to generate dynamic SQL (otherwise you have just moved the problem from the app to the database).

Slashdot Top Deals

On the eighth day, God created FORTRAN.

Close