Forgot your password?
typodupeerror

How Prevalent Are SQL Injection Vulnerabilities? 245

Posted by kdawson
from the more-than-you-think dept.
Krishna Dagli writes to tell us of an investigation, by Michael Sutton, attempting to get an estimate of how widespread SQL-injection vulnerabilities are among Web sites. Sutton made clever use of the Google API to turn up candidate vulnerable sites. You might quibble with his methodology (some posters on the blog site do), but he found that around 11% of sites are potentially vulnerable to SQL injection attacks. He believes the causes for this somewhat alarming situation include development texts that teach programmers insecure SQL syntax, and point-and-click tools that allow the untrained to put up database-backed sites.
This discussion has been archived. No new comments can be posted.

How Prevalent Are SQL Injection Vulnerabilities?

Comments Filter:
  • by Reality Master 201 (578873) on Thursday October 05, 2006 @12:38PM (#16323417) Journal
    destroys thousands of lives a year. Sure, it starts small - a SELECT there, a few INSERTS on the weekend. Eventually, though, you're using stored procedures and trying to score triggers in the middle of the night.

    Just say no, kids.
  • by charleste (537078) on Thursday October 05, 2006 @12:42PM (#16323485)
    This is a possibility that was obvious back when I was developing web applications as far back as 1996 using CGI. The approach in TFA was a similar approach we used "back when" to demonstrate the need for (a) not using GET, (b) turning off verbose error reporting, (c) controlling *how* queries were made (e.g. architecture of the app and DB I/O), and (d) storing sensitive data encrypted. The sad part is that it is *still* a problem. I guess it underscores the need for a decent architect as opposed to letting whiz-bang do-it-yourselfers start coding without design, and the need for security analysis, et. Al. Just my 2 cents.
    • by CastrTroy (595695) on Thursday October 05, 2006 @12:48PM (#16323585) Homepage
      How does not using GET stop anything, you can POST anything you want to a webserver just like you can GET anything you want from a webserver. Only using POST will make things a little harder, but it doesn't stop anything.
      • Re: (Score:3, Insightful)

        by dgatwood (11270)

        Using POST doesn't make it any harder. It just makes the address bar less ugly.

        IMHO, any web application should be written to accept both GET and POST. I'm very annoyed with USPS.com for this very reason. I have a small Dashboard widget that I use to track packages. Short of writing some custom redirect script on a server somewhere, I cannot construct a URL that I can use to create a clickable link to the tracking results.

        I sent an email to the USPS webmaster and asked if there was anything they cou

        • by Goblez (928516)
          So you write a widgit that creates a request object and POSTs it to the server. It's the same info being sent, it's just how it's viewed in the addressbar.
          • by dgatwood (11270)

            You can't send a URL to another application with a POST request. If it isn't in the addressbar, it can't be part of a URL, so a Dashboard Widget can't make Safari display the page. So you only have three choices:

            • Write a CGI on the web server that converts a GET request to a POST request, sends it to the USPS server, and returns the results.
            • Write widget code that makes the POST request with xmlhttp, stores the results to a local file on disk, then opens that file in Safari.
            • Write widget code that makes
            • by Amouth (879122)
              why not have it call a javascript function that takes the info and posts it to a new window .. not that hard.

              or even have java script go get the damn thing and return it back
        • Your post made me curious about how Google does it via their Search by Number [google.com] feature. For example, if you search Google for "usps " plus your tracking number (or just the number), the results will be preceded by a link to "Track USPS package..." I suppose your widget could use Google's custom redirect script, but why is the functionality restricted to Google?
        • by Ford Prefect (8777) on Thursday October 05, 2006 @02:57PM (#16325777) Homepage
          IMHO, any web application should be written to accept both GET and POST.

          No. Web applications should use GET and POST where appropriate - GET for idempotent requests (showing database records, search results, those kind of recurring, non-database-changing things) while POST should be used for things which actually change data, user state, and so on.

          Using GET in the wrong places [slashdot.org] can lead to all kinds of irritations and miniature security problems. Imagine sending an email to your web application administrator containing something like the following: <img src="http://example.com/webapp/user_admin?action=e dituser&username=me&accesslevel=administrator" width="0" height="0">...

          Many web applications do get the two horrendously mixed up (I've seen search results done via POST, which is subtly, incredibly annoying) but they're definitely not interchangeable.

          For simply displaying a non-password-protected package shipment page, GET would probably be the best solution. But blindly accepting both isn't a good alternative.
          • Re: (Score:3, Insightful)

            by Bogtha (906264)

            GET for idempotent requests (showing database records, search results, those kind of recurring, non-database-changing things) while POST should be used for things which actually change data, user state, and so on.

            You are confusing idempotence and safety. "Idempotent" merely means that repeated requests have the same effect as a single request. You should use GET because it is safe, not because it is idempotent. For contrast, DELETE is an idempotent method, but it's certainly not a safe method. For

        • by Electrum (94638)
          IMHO, any web application should be written to accept both GET and POST. I'm very annoyed with USPS.com for this very reason.

          This seems to work:

          http://trkcnfrm1.smi.usps.com/PTSInternetWeb/Inter LabelInquiry.do?origTrackNum=99999999999999999999 [usps.com]
          • by dgatwood (11270)

            Thank you. Now why the &^#$^@ couldn't they have told me that instead of saying "Sorry, no GET url exists"!?! :-)

            *sigh*

        • by TheSpoom (715771) *
          You could probably create a clickable link if you could use Javascript as the onclick to postback to the URL in question. I know, an ugly hack, but it could work.
        • #!/usr/bin/perl
          use LWP::UserAgent;
          use HTTP::Headers;
          use HTTP::Request;
          my $hostname="http://localhost/cgi-bin/printenv";
          my $lwp= LWP::UserAgent->new(timeout=>10);
          my $h=HTTP::Headers->new;
          $h->content_type('application/x-www-form-urlencod ed');
          my $request=HTTP::Request->new("POST",$hostname,$h,$r equestcontent);
          my $res=$lwp->request($request);
          my $contents=$res->content($res);
          print $contents;
          exit();

          Just make $requestcontent what USPS expects and change the URL as appropriate a

    • by TheLink (130905)
      How do a) and b) help?

      Which "decent architect" designs a system where verbose error logs are sent to untrusted public users.

      And which "decent architect" writes web apps where GET would inherently causes security problems.

      In fact, post causes more problems if the target page doesn't issue a redirect, because then the form could be reposted. This shouldn't be a security problem of course (unless it's a login page), since duplicate posts should be handled gracefully. However with most browsers, users would be
    • by masklinn (823351)

      the need for (a) not using GET

      WTF? GET has uses just as POST has uses. If a request is to be idempotent (read-only query), then it's a GET. Otherwise it's a POST (or a PUT, or a DELETE).

      The fact that PHP fails at understanding HTTP doesn't mean that others do so too. For example, the web.py Python microframework behaves this way: you map URLs to classes, and these classes have any of 4 methods: GET, POST, PUT, DELETE (they can have others, but the others are not important). Each method is called when th

      • by arivanov (12034)
        Ahem. While this problem is prevalent in PHP/ASP(AFAIK pre .NET) code, it is long gone in most "sane" languages.

        If you follow elementary "safe" coding conventions in Perl, Python or Ruby and use prepare() statements it is nearly impossible to write injectable code. I am saying "nearly" because there will always be an inventive idiot to write a code which can allow injection in any language.
    • (a) not using GET

      At this point, I realized you didn't know what you are talking about, and stopped reading your comment.
    • #!/usr/bin/perl

      use LWP::UserAgent;
      use HTTP::Headers;
      use HTTP::Request;
      my $hostname="http://localhost/cgi-bin/printenv";
      my $lwp= LWP::UserAgent->new(timeout=>10);
      my $h=HTTP::Headers->new;
      $h->content_type('application/x-www-form-urlencod ed');
      my $request=HTTP::Request->new("POST",$hostname,$h,$r equestcontent);
      my $res=$lwp->request($request);
      my $contents=$res->content($res);
      print $contents;
      exit();

      whoo that was hard; all using POST is going to do is get you a slightly

  • Simple solution (Score:5, Insightful)

    by CastrTroy (595695) on Thursday October 05, 2006 @12:44PM (#16323523) Homepage
    The simple solution is to use parameterized queries. I don't know why more books don't know why more books don't push this methodology, as it makes you program faster, easier to read, and also makes you invulnerable to SQL injection attacks.
    • by revlayle (964221)
      Indeed, the last few project I have done use COMPLETELY parameterized queries. The only time the SQL statement structure for a command is remotely dynamically built is to add additional caluses based on specific criteria and allows no user-generated input to actually build the clause specifics. With these practices, we have absolutely *NO* SQL injection vunerabilities.

      The other side of the coin, that people still forget about, also, is data that is queried and made for display in HTML browsers. Without
    • by RAMMS+EIN (578166)
      ``The simple solution is to use parameterized queries. I don't know why more books don't know why more books don't push this methodology, as it makes you program faster, easier to read, and also makes you invulnerable to SQL injection attacks.''

      I guess it's because they're cumbersome. I found this example:

      SqlCommand command = connection.CreateCommand();
      command.CommandText = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";

      command.Parameters.Add(
      new SqlParameter("@Custo

      • by Fweeky (41046)
        Ew. An idiom I'm used to is:
        query("SELECT * FROM Customers WHERE CustomerID = ?", customer_id)
        Why do you need more?
    • by Goaway (82658)
      Here's why: PHP doesn't even support them out of the box.

      PHP is quite probably the single biggest cause of SQL injection attacks on the web.
  • The fact that tools can be used to put up insecure sites is not exactly a failing of the tool. The tool will have had a spec (even an informal spec) - which may have been "put sites up fast and let users sort out the security".

    It's only a failure of the tool, or the developer of the tool, if the tool is marked as being a one step solution. Of course a lot are, there is no shortage of snake oil salesmen, and in that case they take 100% of the blame. However most rapid deployment tools contain a clear disc
  • by Realistic_Dragon (655151) on Thursday October 05, 2006 @01:02PM (#16323821) Homepage
    There should be some kind of government run website somewhere.

    You would answer questions and it would give you license keys to software that you were qualified to use. For example, I might tick:

    Engineer (check)
    Artist ( )
    Manager (check)
    Linux (Check)
    Mac ( )
    Windows ( )

    And it would issue keys for website point and click installation software, Vi, apache and Latex - but deny me keys to powerpoint thereby saving the lives of people who might otherwise have to gnaw off their own leg to survive my 8 hour presentation on optimising synergisyms in a web 3.0 environment by sub molecular interactions.
  • testing methods (Score:4, Insightful)

    by Akatosh (80189) on Thursday October 05, 2006 @01:03PM (#16323841) Homepage
    This 11% was determine by a weak testing mechanism. For every site that baltently spews sql errors to the user there are two that silently return a generic sanitized error, and another two that return no error at all. It would produce more results if you take it a step further and ask yes no questions, such as:

    ?id=99999' OR '10

    and see if the page returns the results of id=10 as expected. It's also common for people to use weak regexp (regexp should NEVER be used to protect against sql injection, see mysql_real_escape_string) and miss some characters:

    ?id=99999)

    or fail to sanitize non us language encoding. Also, get variables are often the most protected. It is much more common to find sql injection in <input type=hidden variables, or in cookie data. The number 11% is extremely low. I'd guess more like 80%.
    • by ecklesweb (713901)
      Depends on how you use a regexp. If you're defining the characters that are allowed in the user input, that's one thing. If you're defining the characters that aren't allowed in the user input, then you might as well not have wasted your time.
    • by tehshen (794722)
      regexp should NEVER be used to protect against sql injection

      In Perl and Ruby, variables are "tainted" if they come from outside, and can't be used in sql or system. Running a string through a regex is the preferred way to untaint it. Your mysql_real_escape_string is the equivalent of a simple regex anyway (replacing characters with \characters).

      Does Python have a taint mode? I know that PHP doesn't (shame)
  • by moco (222985)
    From the article:

    * Many development texts actually teach programmers insecure SQL syntax. ...
    * Many sites are exposed to SQL injection attacks but don't know it.

    I agree completely! I've seen the texts, I've seen the hordes of VB+SQL programmers that learned from said texts moving to the web porting the same "vices" to the new platform.
    And I've seen the "oh-sh*t" face on a couple of developers after demonstrating to them that their software is vulnerable to SQL Injection. In both cases the vulnerabilities e
    • by valloned (1009727) on Thursday October 05, 2006 @01:14PM (#16324025)
      Microsoft VB.NET and ASP.NET texts are AWFUL in this regard. Nearly all examples use in-line SQL queries rather than paramaterized stored procedures. Why? Probably because they are trying to fit in with Microsoft's strategy that devoping applications should require absolutely no knowledge of code (or anything else for that matter). The big selling point for their VS 2005 suite is "no code required". That speaks volume.
      • by sevinkey (448480)
        You're right, all of the asp.net examples do just have in-line sql queries.

        Dunno why anyone would not use ObjectDataSources for their data in .Net, with parameterized calls to stored procedures behind though.... not only is this more secure than uglying up your UI level code, it's more efficient, and lets you bind to generic List objects instantiated to your own classes rather than using DataTables.
    • by jrockway (229604) * <jon-nospam@jrock.us> on Thursday October 05, 2006 @01:19PM (#16324113) Homepage Journal
      > Writing secure software is never easy.

      It's easy if you use good tools. PHP is not a good tool. Rather than hacks like mysql_replace_the_string_with_things_that_wont_com primise_my_database(), you should be using tools that make it impossible to inject SQL.

      Some ideas:

      Perl's DBI, whose docs tell you to ALWAYS write SQL like:

      $sth = $dbh->preprare('SELECT foo,bar FROM baz WHERE something=? AND another = ?')
      $sth->execute(q{''Some\ things"'}, 10);

      Notice that the programmer can't forget to escape the SQL -- because there's no escaping.

      Even better is something like DBIx::Class, which lets you write

      $resultset = $table->search({something => q{''Some\ things"'}, another => 10});

      Again, no opportunity for the programmer to fuck up the SQL in any way. It's just like getting data out of the hash... DBIx::Class will generate the SQL (for any backend), run the query, stream in the results as needed, etc. It's easier and it's better!

      Ruby on Rail's ActiveRecord is similar, but it's impossible to do certain types of joins. DBIx::Class is better in this regard. (And Perl is faster than Rails, and Catalyst is more complete rhan Rails :)... but both Ruby and Perl are MUCH better choices than PHP.

      PHP makes it easy to write insecure code. Perl makes it hard! (With taint mode, a selection of ORMs, 10000+ well-tested modules, and nicities like Moose, Moose::Autobox, etc.)
      • Re: (Score:3, Insightful)

        by Nos. (179609)
        Or use the appropriate PEAR classes with php, like MDB2, which documents how to do prepare/executes. Don't suggest that PHP is worse than PERL because PERL has DBI, PHP has addons as well.
      • by soliptic (665417)
        (I asked this last time SQL injection came up, but I was too late, I got no reply, so I'll ask again...)

        I don't understand how this magically fixes things.

        In your example (yes, I realise it is just that, a quick example) you have 10 which is a constant. Now clearly if you were just doing queries with constants then there is no danger in doing a straight SELECT * FROM table WHERE something=10!

        So your example obviously needs to replace "10" with "$numeric_var", ie $sth->execute(q{''Some\ things"'},
        • Re: (Score:3, Informative)

          by XanC (644172)

          Prepared statements aren't there to guarantee that every datum you insert is the "correct" type for what you're trying to do. What they do is guarantee that nothing in your variables will be interpreted as part of the SQL command.

          If I have "10; <naughty stuff>" in $numeric_var, it will attempt to insert (or select, or whatever) exactly that string, without interpreting it. The data may be useless, but it will not be executed.

    • Re: (Score:3, Interesting)

      by Ford Prefect (8777)
      And I've seen the "oh-sh*t" face on a couple of developers after demonstrating to them that their software is vulnerable to SQL Injection. In both cases the vulnerabilities exposed the customers to the posibility of serious financial damage.

      Have you seen the 'oh, that's not a problem, we're using SSL so it's completely secure' face yet?

      As for stupidest work-arounds - a site I was doing a vague security audit for (sans source-code, alas) was (is) rife with SQL injection vulnerabilities. On attempting to expl
  • by shawnmchorse (442605) on Thursday October 05, 2006 @01:10PM (#16323973) Homepage
    If anything, I'd question how FEW sites they claim are vulnerable to SQL injection. It's an insidious problem that just creeps up on you anytime you don't think about it sufficiently (as when writing something quickly, on a deadline... not that this ever happens!). I know that at my workplace we fell victim at one point to a SQL injection attack on one of our (many) custom PHP scripts. We eventually found out how it worked through the web logs and were able to fix it, but honestly even after we did our best to clean things up... I'm dead certain that there are still probably hundreds of places that we're still vulnerable. This is due to a number things including the sheer volume of PHP code in use, the fact that the code has been written at various points in time over a period of six years or so, and the fact that this code has been written by at least twenty different people. It's like trying to plug holes in a dam.
    • by drew (2081)

      It's an insidious problem that just creeps up on you anytime you don't think about it sufficiently.

      If SQL injection attacks are creeping up on you, than you are using the wrong tools. If you are using any halfway decent database access library (and suing it correctly) SQL injection is a non-issues. Example, using Perl:

      my $sth = dbh->prepare("select * from users where user_id = ?");
      my $rv = $sth->execute($dangerous_user_supplied_var);

      I know the same thing is possible in PHP (via PEAR:DB), although

  • by Bryansix (761547) on Thursday October 05, 2006 @01:23PM (#16324175) Homepage
    Why are the point and click or turn key solutions so vulnerable to SQL injection in the first place? I had a friend with a PHPBB site that got shot to all hell when some cracker came along and defaced it. Why wasn't it secure out of the box? Second of all, why is it that every website has to worry so much about security. I know about databases but I don't know the first thing about preventing an SQL injection attack and why should I have to. There is nothing sensative on my sites. Let me throw out this analogy.

    Let's say I own a house and around Christmas time I put out an inflatable snow man. Then some vandals come along and pop it. Are you going to walk up to me while I'm sulking over my snow man and say "Don't you know you have to wrap your snow man in kevlar to prevent vandalism and then put up an electified fence with constantine wire on it."? I would give you the strangest look if you did. Then I'd probably say something pertaining to the fact that the police should catch these bastards and presecute them.

    So why is it with technology that no emphasis is put on catching vandals and bringing them to justice and a ton of emphasis is put on protecting your site from attack?
    • by cyber0ne (640846)

      So why is it with technology that no emphasis is put on catching vandals and bringing them to justice and a ton of emphasis is put on protecting your site from attack?

      Because currently the latter is FAR easier than the former.

      While I generally agree with the sentiment that it's not the web developer doing anything wrong, it's the hacker who's wrong... I would also argue that while, by not caring enough about security, the developer may not be "wrong," he is perhaps being "unwise."

    • Re: (Score:2, Informative)

      by winomonkey (983062)
      Perhaps it would be more appropriate to use an analogy of vandals mortaring your snowman. For, you see, the problem with the e-vandals is that they are not actually physically present when they attack your snowman. Yes, there is proof that they attacked you, but chances are that determining their physical location is hard. Sure, you could look at the trajectory of the incoming projectile, but even then you are having to do a lot of imprecise and possibly flawed work to find out just where these attacks a
    • by RAMMS+EIN (578166)
      ``So why is it with technology that no emphasis is put on catching vandals and bringing them to justice and a ton of emphasis is put on protecting your site from attack?''

      Which would you rather have: people vandalize your website, but they get caught, or people don't vandalize your website?
      • by Bryansix (761547)
        Third option: People don't vandalize but not because it is impossible to vandalize but because people are afraid of getting caught.
    • by RAMMS+EIN (578166)
      ``Why are the point and click or turn key solutions so vulnerable to SQL injection in the first place? I had a friend with a PHPBB site that got shot to all hell when some cracker came along and defaced it. Why wasn't it secure out of the box?''

      With PHP, virtually anyone can write a web forum. And lots of people do. Unfortunately, not all these people are good coders. Not all of them are aware of the security risks. And PHP doesn't provide particularly secure APIs for SQL (or at least it didn't when I last
  • I've worked in web development a while and I find a SQL injection vulnerability in about 90% of the sites I've seen.

    It is extremely common to have people just cut and paste the bare-bones tutorial code they find on the web and reuse that same pattern on every page in the site rather than centralizing it in a wrapper. So not only is the string not being cleaned, but it's also a huge pain to fix.
  • by thewils (463314) on Thursday October 05, 2006 @03:03PM (#16325877) Journal
    If you search for "mdb" you can download the entire database without too much trouble.

    I recently came across a commercial site where you could substitute, for instance, "(select first_name from users where id=1)" into the page url and a nice error screen came up telling you that it couldn't convert "George" into an Integer.

    It's not the SQL Injection per se that is the biggest problem, but the nice error messages you get back giving you, more or less, a SQL command line interface. Errors should be detected and redirected to a sanitized page, or if you can't be bothered, an unceremonious crash.

    I notified the owners of that site by the way.
  • I've written about this before. Basically, SQL injection vulnerabilities would completely disappear if better APIs were used [inglorion.net]. The problem is that queries are composed as strings, which have no intrinsic structure. The programmer creates structure by inserting certain characters (in particular, single quotes) in the string. However, the exact same mechanism is used to add user-supplied data to the queries. Unless the programmer is very careful, this allows the user to affect the structure (and thus the effec
    • by Goaway (82658)
      Most languages (Perl, Python, Ruby, for instance) have had better APIs for years and years.

      PHP doesn't.

(null cookie; hope that's ok)

Working...