Slashdot Mirror

← Back to Stories (view on slashdot.org)

Top 15 Free SQL Injection Scanners

Posted by kdawson on from the looking-for-trouble dept.

J.R writes "The Security-Hacks blog has a summary of the 15 best free SQL Injection scanners, with links to download and a little information about each one. The list is intended asan aid for both web application developers and professional security auditors."

103 comments

  1. asian aid by User+956 · · Score: 1, Funny

    The list is intended asan aid for both web application developers and professional security auditors.

    Ok, so that covers China and Japan, but what about Europe and the U.S.?

    --
    The theory of relativity doesn't work right in Arkansas.
    1. Re:asian aid by Mr0bvious · · Score: 1

      I don't think it was the editor's fault. His post appears to have been hacked, most probably using some SQL injection.

      --
      Never happened. True story.
    2. Re:asian aid by deftcoder · · Score: 0

      What next? They'll blame all the dupes on SQL injection, too?

      --
      Peace sells, but who's buying?
  2. Why is this needed at all? by Anonymous Coward · · Score: 5, Insightful
    If you just make sure you always use prepared SQL statements with positional arguments, you will never have any problems with SQL injection.

    I suppose the over-use of PHP (which for a long time didn't even support prepared statements (does it even do it today?)) combined with stupid users that created the current situation.

    1. Re:Why is this needed at all? by koh · · Score: 1, Informative

      The DB interface in PHP5 supports positional arguments AFAIK. Now, if only the service providers would switch to PHP5, there would be less problems. Unfortunately, it seems that, at least here, the major providers are still stuck in PHP4-for-compatibility-with-existing-apps mode.

      --
      Karma cannot be described by words alone.
    2. Re:Why is this needed at all? by neoform · · Score: 1

      mysql_real_escape_string() has been a function going back to php4..

      --
      MABASPLOOM!
    3. Re:Why is this needed at all? by ThwartedEfforts · · Score: 2, Insightful

      magic quotes ain't helping though either, but I suppose seeing backslashes littering the output (as is common on many websites) is better than SQL injection. The magic quotes setting, and the concept behind and naming of the stripslashes function, is so confusing that it's best to just turn off magic quotes, don't use stripslashes, and manually make sure you either quote everything (hard) or use prepared statements (much easier).

    4. Re:Why is this needed at all? by vdboor · · Score: 1

      For a security audit of an existing code base! Or are you willing to hire someone to browse code in a month time?

      I suppose the over-use of PHP (which for a long time didn't even support prepared statements (does it even do it today?)) Every language allows you to write libraries which do things properly. The language is not a limiting factor here.
      --
      The best way to accelerate a windows server is by 9.81 m/s2 ;-)
    5. Re:Why is this needed at all? by mabinogi · · Score: 5, Informative

      It's the completely wrong answer to the problem though, as it still promotes the idea of using SQL built by string concatenation.
      The result being that SQL injection is only one forgotten function call away.

      --
      Advanced users are users too!
    6. Re:Why is this needed at all? by matthewbarr · · Score: 1

      I have to agree - these tools could be very useful in getting to grips with a bunch of code that's been written by someone else. However, if you're writing the app yourself, sloppy code (concatenating queries on the fly, ignoring functions such as mysql_real_escape_string(), etc.) and not validating user input are your main concerns. PHP is not the problem if used correctly!

      --
      http://www.miitube.co.uk/
    7. Re:Why is this needed at all? by hclyff · · Score: 5, Insightful

      Every language allows you to write libraries which do things properly. The language is not a limiting factor here. PHP did not for a long time. And no, I don't believe that "magic quotes" allows you to write secure code properly. Any framework which relies on string concatenation for building an SQL command is inviting insecure code, because the programmer has to *actively* seek to fix injection problems. There is statistical certainty he will overlook something sooner or later. Coupled with the fact that PHP4 was (is?) prevalent compared to PHP5/6 for a long time, it just might be the single most contributing factor to why are SQL injections so common.
    8. Re:Why is this needed at all? by FooBarWidget · · Score: 1

      Prepared statements are slower because it needs an extra server roundtrip. I've recently discussed this issue on the Ruby on Rails core mailing list, and people mentioned this while asking why we need prepared statements.

    9. Re:Why is this needed at all? by jZnat · · Score: 1

      PHP 5 does natively via PDO, and PHP 4 (and 5) does via PEAR's MDB2 (older version: DB). There is also ADOdb which has a very similar API to Microsoft's ADO RDBMS API (acronym overload!).

      The availability of robust packages like those still doesn't stop newbie (and veteran) PHP programmers alike from just using the raw MySQL API subset known as the mysql_* functions (which were deprecated in favour of the newer MySQLi functions/objects that also support prepared statements) along with occasional use of addslashes().

      --
      'Yes, firefox is indeed greater than women. Can women block pops up for you? No. Can Firefox show you naked women? Yes.'
    10. Re:Why is this needed at all? by ensignyu · · Score: 1

      You could always use client prepared statements, which is mostly the same as building your own SQL statements strings but are much cleaner and does the quoting for you.

    11. Re:Why is this needed at all? by vr · · Score: 3, Interesting

      If you just make sure you always use prepared SQL statements with positional arguments, you will never have any problems with SQL injection.

      Actually, that is not true, as it ignores one problem: bugs in the database drivers. Seriously, there have been bugs in database drivers that have enabled SQL injection... I specifically remember a bug in the PostgreSQL JDBC driver a while back.

      I also remember seeing a JDBC driver that simply inserted arguments into the string containing the SQL statement, although I fail to remember exactly which driver that was. This was a while back, mind you, so hopefully errors like that have been fixed. :)

      Until I encountered these things, I believed that positional arguments was the silver bullet. The point here is that positional arguements in itself is no guarantee, it is only a part of an API. At some point you have to trust the developers of the database driver and the database itself, of course...

    12. Re:Why is this needed at all? by nurb432 · · Score: 1

      Because people sometimes goof, and its always good to test test test test.

      --
      ---- Booth was a patriot ----
    13. Re:Why is this needed at all? by xwipeoutx · · Score: 1

      I use prepared statements most of the time... however it's a pain in the butt when using the IN clause - there's no real way to do that, and I end up just concatenating SQL for those anyway

    14. Re:Why is this needed at all? by AnObfuscator · · Score: 2, Interesting

      If you just make sure you always use prepared SQL statements with positional arguments, you will never have any problems with SQL injection.


      Well, prepared statements have their own shortcomings -- they're not the magic bullet to solve all our DB issues. Some would have you believe they are, but don't be fooled.

      I suppose the over-use of PHP (which for a long time didn't even support prepared statements (does it even do it today?)) combined with stupid users that created the current situation.

      IIRC, when PHP 4's mysql drivers were written, MySQL did not support prepared statements. However, PHP 5's mysqli driver does support MySQL prepared statements. Also, PHP 5's PDO system offers a unified DB API with prepared statements.

      --
      multifariam.net -- yet another nerd blog
    15. Re:Why is this needed at all? by billcopc · · Score: 2, Insightful

      Gee that's an easy solution: dump the legacy apps! PHP 5's only been out for 3 years :P How hard can it possibly be to rewrite the vulnerable bits of SQL anyway ? I never really felt much of a shock when I switched to PHP5 years ago, the bulk of my coding habits were unaffected and the few things that broke involved a 5-minute fix or less.

      --
      -Billco, Fnarg.com
    16. Re:Why is this needed at all? by PhotoGuy · · Score: 2, Insightful

      It's the completely wrong answer to the problem though, as it still promotes the idea of using SQL built by string concatenation.
      The result being that SQL injection is only one forgotten function call away.

      I agree. I actually find it easier to use the call with parameters, rather than trying patch together a string. Putting in the "?" parameters in the string, and listing them afterwards, pretty damn simple. I'm amazed SQL injection is an issue at all. I guess there's a lot scarier programming out there on major sites than I can possible imagine.

      --
      Love many, trust a few, do harm to none.
    17. Re:Why is this needed at all? by ceswiedler · · Score: 3, Insightful

      Huh? Do you prepare your statements every time you execute them? That would certainly be slower, but if you prepare the statement once and execute it many times, the local overhead becomes minimal and the execution time in the database improves because the DB doesn't have to re-parse and re-plan the query.

    18. Re:Why is this needed at all? by GnuDiff · · Score: 2, Insightful

      I think it is due to 2 reasons:

      1) the string concatation technique being present in several pretty popular (and awful) PHP books, and (afai remember) in the PHP documentation itself, thus becoming defacto "standard";

      2) The general ignorance of a significant part of PHP developers of any database abstraction layers and in fact anything but the magic LAMP.

    19. Re:Why is this needed at all? by enrevanche · · Score: 2, Insightful
      I cannot answer for Ruby, but for Java/JDBC this is only true for the first call to a prepared statement. For the second and subsequent calls to a prepared statement, a prepared statement is always better or at worst equivalent. With connection and prepared statement pooling, performance can be improved dramatically.

      Note that this all depends on the database and the driver, as some databases do not cache query plans or the driver does not properly coordinate the query plan with the database.

      There is no simple answer, as this all depends on the database and the application.

    20. Re:Why is this needed at all? by FooBarWidget · · Score: 1

      "Huh? Do you prepare your statements every time you execute them?"

      Uhm, if you use PHP then you *can't* cache your prepared statements (unless you do the same query many times in the same PHP run, but that's unlikely). PHP is stateless, after one run it frees everything.

      "but if you prepare the statement once and execute it many times, the local overhead becomes minimal and the execution time in the database improves because the DB doesn't have to re-parse and re-plan the query."

      In theory, yes. But are there any benchmarks that support this claim? Many people say this but I've yet to see any hard numbers that prove it. I wrote a little test script which inserts 300000 rows in MySQL, and using prepared statements didn't improve performance *at all*. The Ruby on Rails guys also seem to be skeptical about the advantages of server-side prepared statements. Someone mentioned that it might help on complex queries with lots of joins, but I can't find any benchmarks for such a case.

      Do you know such a benchmark? I am looking for one.

    21. Re:Why is this needed at all? by Firehed · · Score: 1

      Only helps if you use it. It's easy to forget at least once, especially in a big project.

      --
      How are sites slashdotted when nobody reads TFAs?
    22. Re:Why is this needed at all? by Shados · · Score: 1

      Thats still fine with IN Statements: you concatenate strings, yes, but from a trusted source (your own!) to add the parameter placeholders, and then loop to add the parameters to the collections. So its completly safe, and still ends up as a prepared statement.

      Another way that I like, is (for DBMS that supports it) pass as a parameter an XML document with (if i'm paranoid) a schema, then pivot it as a table, and use the IN on that. Works pretty good.

    23. Re:Why is this needed at all? by encoderer · · Score: 1, Insightful

      1. Explain to me how PHP didn't allow libraries to be written? I don't understand that whatsoever. In PHP4 I'd write (and have written) a data abstraction layer that wrapped the inbuilt mysql functions, incorporating, among other things, string cleansing. The mysql library is procedural in PHP4 and below (and is both proc and oo in 5). So even if you're using <4 you can just write your own wrappers for all the functions and throw them in a file that gets auto_prepend()'ed to every page. (auto_prepend acts sorta like a generic front-controller)

      2. I never developed from scratch in PHP3 (just upgraded a few applications) or below but if, for some who-knows reason why people couldn't write such function wrappers, forget not that PHP is open source. Anyone could've written an extension in C and compiled it into PHP.

      3. There is nothing about PHP5 that makes injection any less likely than in PHP4 if the developer is still using the mysqli library the same as he used the mysql library in php4. And if the developer is enlightened enough to use the improvements afforded by mysqli library properly, he'd probably also be enlightened enough to realize you can use the mysqli library in PHP4 as well.

      4. Nobody is developing in PHP6 yet. It just made (EARLY) beta.

      5. In summary, you don't seem to know all that much about PHP. You certainly know more than most, but I don't know why you posted information that's just not true. Is it just more PHP FUD or were you just sincerely misinformed? I only even point this out because PHP gets a pretty bad rap on here even though version 5 compares favorably to any other modern web dev language. It's not in the league of ASP.Net or J2EE, but it certainly is a better language than the avg /.'er gives it credit for.

      I used to avoid PHP like the plague. I was comfortable using Java and C# for web development and even an occasional line of {shudder} VBScript. What I found when I began using it was a language that was very capable when used properly.

      People here give PHP a bad rap because of the PHP Developers using it. It's a great entry level language. It gives you what I call complexity on demand. It's a language thats useful for your first-time developer, he doesn't need to learn or concern himself with procedures or classes or design patterns, and also useful for an experienced developer, capable of OOD, reflection, etc.

    24. Re:Why is this needed at all? by Sancho · · Score: 1

      There's a lot to be said for learning proper programming practices up front. A language which lets you shoot yourself in the foot that easily will likely cause the student to learn bad habits which will make later languages harder to learn, and which lends itself to creating security holes. I'd definitely not learn on PHP first, unless secure practices are a part of the criteria (not the case for most self-learners who read through a book.)

    25. Re:Why is this needed at all? by smellotron · · Score: 1

      Personally, I like either of these two case (syntax off the top of my head):
      // Using PHP5's PDO
      $stmt = $pdo->prepare('SELECT f1, f2, f3 FROM foo WHERE x = ?');
      $stmt->execute(array($x));

      // Using native Postgres calls (ADOdb does this internally)
      $stmt = pg_prepare('SELECT f1, f2, f3 FROM foo WHERE x = $1');
      pg_execute($stmt, array($x));

      However, there are cases where prepared statements are insufficient, though they are certainly not mainstream. You can't prepare an incomplete statement. If the SQL query itself varies (e.g. what tables you JOIN and what constraints you put in your WHERE clause), you have some choices:

      • Prepare every possible query. This is secure by default, but tedious to enumerate all possibilities (in some cases increasing the chance of bugs due to duplicated SQL code)
      • Use the most detailed query, crafted in such a way that unnecessary JOINs and conditionals can be no-ops. This doesn't always work, but you can craft something like "WHERE (foo = ? OR ? IS NULL)" and pass in $foo to both parameters, to allow conditional checking of foo.
      • Dynamically generate the SQL query (it should still be parametrized). The downside is that there is no single block of text that contains the exact query, but it is consistently going to have the best performance and least duplication required.
      It's perfectly acceptable for someone to say "There will be no dynamic SQL generation in this project," the same way it's perfectly acceptable to say "We don't use #define macros ever." You're just denying yourself a useful tool.
    26. Re:Why is this needed at all? by smellotron · · Score: 1

      Because PHP is stateless, there is no connection pooling and SQL statements must be re-prepared at ever script execution. In my experience, there are very few cases where a query gets executed multiple times on a single script, so the benefit for preparing goes wayyyyy down. If you really need a complex query prepared, turn it into a procedural function in your database. This pushes the expensive query-parsing to the first function call for PL/PGSQL (Postgres), and turns all of your SQL queries into stuff like "SELECT * FROM my_select_function(?, ?, ?, ?)".

    27. Re:Why is this needed at all? by smellotron · · Score: 1

      The availability of robust packages like those still doesn't stop newbie (and veteran) PHP programmers alike from just using the raw MySQL API subset known as the mysql_* functions
      I'm a veteran PHP programmer, and I've worked in a dedicated Postgres environment (we were more likely to switch our scripting language from PHP to Python than our database from Postgres to anything). We used PEAR::DB, and frankly, I think it sucked. It didn't support modern Postgres features at all (including using addslashes instead of pg_escape, which itself was discovered as a security flaw for some non-ASCII databases). It was slow and bulky, to the point of being the #1 source of CPU time being used according to Xdebug and KCacheGrind.

      I enjoy PDO (with a few additions that I can manage with subclassing), and it is efficient. Using PEAR::DB made me want to claw my eyeballs out, and IMHO the Postgres environment I mentioned earlier should have been using the pgsql_* functions.

    28. Re:Why is this needed at all? by encoderer · · Score: 1

      Oh come on. The same thing (shooting yourself in the foot) could be said, nay, SCREAMED, about C. But nobody here is blaming bad programming on the language in that case. Anybody learning "proper prgramming practices" can do so with PHP just as well as she could with any other language.

    29. Re:Why is this needed at all? by Gotebe · · Score: 1

      I wrote a little test script which inserts 300000 rows in MySQL Do you know if your DB of choice has a cache of prepared statements (many do)? If no, what did you expect?

    30. Re:Why is this needed at all? by Sancho · · Score: 1

      The same thing (shooting yourself in the foot) could be said, nay, SCREAMED, about C. Absolutely. Did I say that C was a good learning language? The reason that I didn't mention C was because this is a thread about PHP, and because this is a story about SQL injection where one of the most common interfaces to the database is (wait for it)..... PHP.

      And if you'll note, I alluded to the fact that you could learn proper programming practices with any language, however that requires learning aids which mention them. In schools, this is sometimes the case. In books, it's much less so. Without the knowledge that these things can be insecure, it's hard to know what to do to secure them.
    31. Re:Why is this needed at all? by 1110110001 · · Score: 1

      which for a long time didn't even support prepared statements

      Let's take an example: http://viewcvs.php.net/viewvc.cgi/php-src/ext/oci8 /oci8.c?revision=1.1&view=markup - added 8 years ago. Prepare? Yes. Bind? Yes. For 8 years. Now that's what I call supported for a long time.

      The problem is more with this database named after a child, which didn't support any of the advanced features for years.

  3. Go on by Alterion · · Score: 0, Redundant

    go on just say an aide for 1337 HAXOR's - you know you want to

  4. Alternatives by stonecypher · · Score: 2, Interesting

    Of course, security through obscurity is badbear.

    That said, there are times - and this is one of them - that I'm glad my recently most common database isn't fundamentally SQL, or anything well-recognized. It also helps that (I believe) mnesia is immune to injection, given that its queries are never textual, but rather always functional, and given that data are always presented as arguments. Every route to injection I'm aware of just doesn't make sense in context (though if someone knows a way attack Mnesia, I'd love to hear about it.)

    Sure, there are times at which SQL is a major win over mnemosyne, but they're not common. Sure, it's nice to have a database be ready for you at any host, instead of having to take the time to find a good host who lets you run stuff. But when it comes down to it, the atypical performance characteristics of Erlang (especially since I write multiplayer games, for which massive concurrency is win) and the ridiculous speed of Mnesia make me miss SQL less every day.

    'Course, client stuff still needs to work on MySQL. :(

    --
    StoneCypher is Full of BS
    1. Re:Alternatives by Anonymous Coward · · Score: 0

      yeah, because processing input into SQL statements is such a new thing. Who the hell would have ever thought of something along the lines of stmt.setString(1,"%crap!%");.... (assuming stmt is already a prepared statement)

  5. Detecting SQL Injection is hard ... by Gopal.V · · Score: 4, Insightful

    The feedback factor for SQL Injection is very low. It is very hard to generically detect the after-effects of a successful sql-injection attack.

    In comparison, something like XSS is easy because if you inject a string, the string re-appears in the HTML returned (HTML injection). The XSRF and XSS attacks dominate the internet attacks because they are really easy to scan for - though technically that should be an excellent reason they shouldn't exist :)

    Rasmus Lerdorf has this awesome test-tool for XSS he keeps demo'ing (thankfully not released). You can see the tool in action in the background. But there's still no real easy way to reliably scan for Sql injection.

    --
    Quidquid latine dictum sit, altum videtur
    1. Re:Detecting SQL Injection is hard ... by Anonymous Coward · · Score: 0

      True, but a XSS attack is only going to hit individual users (usually ones stupid enough to click where baited) - a successful, or even accidental SQL injection can leave an application at the point where the only real option is to restore from backup (sure hope there wasn't any money being exchanged). Of course the best option is to avoid the situation via not php (or if you insist adodb or some other library that does honor prepared statement like functions - I'm sure some php functions do, but the problem being they are 100% incompatible with any other db functions, never mind dialect differences... {hibernate rocks, /me stops bitching about php}).



      As mentioned, SQL injection is hard to find, but as with anything if you can automate any portion do so now.

    2. Re:Detecting SQL Injection is hard ... by Anonymous Coward · · Score: 0

      Have to reply to my own AC comment - did forget to mention that regardless of whatever language you use if you do something along the lines of "SELECT crap FROM toilet WHERE id={$_GET[something],(HttpRequest)request.getParam eter('something')}" your screwed regardless, a programming language cant fix programmer error/noobness etc.

    3. Re:Detecting SQL Injection is hard ... by gratemyl · · Score: 1

      True, but a XSS attack is only going to hit individual users (usually ones stupid enough to click where baited)

      No need to click - it is easy to steal cookies and other information by injecting an img tag with onload=...

      --
      hackerkey://v4sw5/7BCHJMPRUY$hw3ln3pr6/7FOP$ck6ma8+9u6L$w4/7CGUXm0l6DLRi82NCe3+9t5Sb7HMOPRen5a17s0DSr1/2p-3.62/-5.23g3/5
    4. Re:Detecting SQL Injection is hard ... by Plutonite · · Score: 2, Insightful

      Rasmus Lerdorf has this awesome test-tool for XSS he keeps demo'ing (thankfully not released). Why thankfully? I've left this stuff a long time ago, but nothing has changed about security and obscurity. You cannot win this way, you only prolong a possibly undetected failure.

    5. Re:Detecting SQL Injection is hard ... by larry+bagina · · Score: 1

      Rasmus Lerdorf has this awesome test-tool for XSS he keeps demo'ing (thankfully not released).

      Actually, it's been released for a while now.

      --
      Do you even lift?

      These aren't the 'roids you're looking for.

    6. Re:Detecting SQL Injection is hard ... by Aeiri · · Score: 1

      I'm the developer of SQLIer (the first tool listed on the site), and I've also developed a (VERY PRE ALPHA STAGE) tool that scans for SQL Injection and XSS.

      http://bcable.net/project.php?vulndetector

      I use two methods, one which is an integer field scan and another which is a string scan.

      The integer scan works like so. Four pages are requested from the server:

      Page 1: http://www.example.com/asd.php?id=1
      Page 2: http://www.example.com/asd.php?id=2
      Page 3: http://www.example.com/asd.php?id=1%2B1
      Page 4: http://www.example.com/asd.php?id=1'

      Page 3's variable of course decoding to "1+1". If page 3 is equal to page 2, and not equal to page 1 or 4, then it's vulnerable. The idea there is that the extra crap "+1" hasn't been stripped off returning the same as Page 1, and it's not causing a MySQL error like Page 4 does.

      SQLIer also has a modified form of that integer scan to ensure a real SQL Injection vulnerable site has been entered in by the user. Since there are pages that when requested display different things each time (like if it has a time on the page or has a new forum post on a side menu), instead of comparing if two pages are equal, two pages are diff'd, then a percentage of how much of the pages are the same are calculated. So if 98% (I think, I can't remember what I have this set at) of the page is the same, it's considered "equal".

      The string scan works as follows. (this function is done for both ' and ", in the example I'm using ')

      Page 1: http://www.example.com/asd.php?id=qwe
      Page 2: http://www.example.com/asd.php?id=qwe' /*
      Page 3: http://www.example.com/asd.php?id=qwe' /*{randstring}

      If Page 2 and 3 are different, then {randstring} is not needed since it's clear that an error (and/or URL) is being output to the screen. {randstring} is set to null if that is the case.

      Then, Page 1 and 3 are compared, if they are different, then obviously an error is being thrown for the quote.

      Page 3: http://www.example.com/asd.php?id=qwe' and 1=1 /*
      Page 4: http://www.example.com/asd.php?id=qwe' order by 1 /*
      Page 5: http://www.example.com/asd.php?id=qwe' and '1'='1

      Page 5 is not requested if the quote does not throw an error. This is because the quote is obviously not causing a problem, and can't be closed.

      Page 1 is compared against Pages 3 & 4. If Page 1 & 3 and Page 1 & 4 are different, then it continues to Page 5 if necessary. If Page 5 is different than Page 1, then it fails (they should be the same). This step is skipped if Page 5 isn't checked due to quotes not causing an error immediately.

      Page 6: http://www.example.com/asd.php?id=qwe'"
      Page 7: http://www.example.com/asd.php?id=qwe' order by 999 /*

      If both Page 6 & 7 are both the same, then finally it's deemed an SQL Injection hole.

      Granted, the string check is incredibly complex and also potentially destructive, which is why it's disabled by default. The integer scanner is very quick and gets most vulnerabilities, and is incredibly accurate as well.

      As for XSS, that is incredibly easy. It just throws random strings into the query variables and sees if the resulting page contains that string.

      Hope this helps some people when trying to auto

  6. Sig by Anonymous Coward · · Score: 0

    9.81m/s^2

  7. what exactly is an sql injection? by siddesu · · Score: 2, Interesting

    i hear people talking about them from time to time, but i still can't figure out how they appear.
    ain't there query parameters in practically all database access APIs?

    1. Re:what exactly is an sql injection? by MickDownUnder · · Score: 3, Informative

      SQL injection attacks target code in which sql statements are dynamically created.

      e.g.

      'select * from employees where fullName like ' + mySQLInjectedInputFromUser

      where mySQLInjectedInputFromUser has been asssigned a value entered by the user:-

      Fred Flinstone; GO; delete employees; GO

    2. Re:what exactly is an sql injection? by cheater512 · · Score: 1

      Oh the common interface is there. Its just that people (like you) dont know about it.

      www.php.net/pdo

    3. Re:what exactly is an sql injection? by siddesu · · Score: 1

      pray tell, how does (using the example above, and an imaginary access api)

      this:
      st = api.createStatement('select * from employees where fullName like ' + mySQLInjectedInputFromUser);
      st.execute

      take more time to write than this:
      st = api.createStatement('select * from employees where fullName like ?' )
      st.execute(mySQLInjectedInputFromUser);

    4. Re:what exactly is an sql injection? by FooBarWidget · · Score: 2, Insightful

      What do you mean, "I'll bite"? What makes you think it's a troll? Can't people have a legit question these days without being seen as flamers and trolls?

    5. Re:what exactly is an sql injection? by Anonymous Coward · · Score: 0

      Ah you must be on the PHP core developer team!

    6. Re:what exactly is an sql injection? by weicco · · Score: 1

      Yep. And that's why you should use parameters instead of dynamically creating SQL commands as string. Not like this (C# .NET):

      SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT * FROM t_Table WHERE Something LIKE '" + InputString + "'";

      But like this (C# .NET):

      SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT * FROM t_Table WHERE Something LIKE @param1"; cmd.Parameters.Add("param1", DbType.String).Value = InputString;

      Lousy example, I know. A lot of languages and/or frameworks supports this kind of structure (in Perl I think paremeters are marked with '?' don't know about other languages) but unfortunately it is not used often enough. PHP has also all the fancy real_escape_string functions and such.

      --
      You don't know what you don't know.
    7. Re:what exactly is an sql injection? by Tablizer · · Score: 1

      Is there a way to turn off the execution of multiple statements per query? Why the API's allow this, I don't know.

      --
      Table-ized A.I.
    8. Re:what exactly is an sql injection? by Shados · · Score: 1

      Its not the language that makes it so you use ? or @name for the parameters, but the access layer and/Or the DBMS itself. For example, using ODBC or OLEDB, even if you connect to SQL Server with C#, you'll end up using the ? way of doing things. Just a side note :)

    9. Re:what exactly is an sql injection? by Sancho · · Score: 1

      I don't know if you can turn it off--it's probably a per-database or -interface issue, however why they allow it is simple: complex queries and operations can require it.

  8. Properly written software... by MickDownUnder · · Score: 1

    In well written software SQL injection attacks shouldn't be possible. Don't use dynamic SQL, use stored procs. If you think you have to use dynamic sql, then create the sql statement within a stored proc (not code) then access that stored proc using authentication details that permit only read only access. I've never seen a situation in which dynamic SQL was required for updating or inserting data, generally it only gets used when performing complicated searching and even then it's not the only option....

    http://www.sommarskog.se/dyn-search.html

    1. Re:Properly written software... by growse · · Score: 1

      Why go through the faff of stored procedures whn you can just use parameterized queries? As far as I know, you need to have access to the db server to create/edit stored procs, and also, afaik, there's no way to use a source code control system on them.

      Because a parameterized query sits in the code, you don't have to touch the db server if you want to do a code update.

      --
      There is nothing interesting going on at my blog
    2. Re:Properly written software... by heinousjay · · Score: 1

      In theory, that works, but there needs to be a very clearly defined separation regarding what ends up in the stored procedures. I'm a little gunshy about using this technique because I've seen it grow into an unmaintainable monster. Without a clear line between the DB interaction logic and the app logic things tend to settle all over the place. Granted, this isn't specifically a stored procedure problem, more a dev lead/team experience one, but it's still something that needs to be watched carefully lest things get out of control.

      --
      Slashdot - where whining about luck is the new way to make the world you want.
    3. Re:Properly written software... by MickDownUnder · · Score: 1

      It is common practice with database development tools to update stored procs from sql scripts which are checked into source control systems. Microsoft has a whole verion of Visual Studio for this purpose.

      Not using stored procs at all in your application is typically done by novice developers, it is not how an experienced professional works with a database.

    4. Re:Properly written software... by Anonymous Coward · · Score: 0

      Experienced, out-of-date professionals use stored procs. Stored procs are a maintenance and testing nightmare. Every system I've worked on in the last five years has used an ORM tool that generated prepared statements.

    5. Re:Properly written software... by growse · · Score: 2, Insightful

      Which I'm sure is fabulous if you're using .NET and MSSQL. However, I imagine that particular combination doesn't make up a very large percentage of all the database applications out there.

      Don't get me wrong, stored procs are a useful tool which are the correct answer to some types of problem. But completely overkill if you just need simple or even slightly complicated CRUD operations. Using stored procs when they're not really necessary is the mark of a developer who doesn't know how to use every tool in his toolbox properly.

      --
      There is nothing interesting going on at my blog
    6. Re:Properly written software... by MickDownUnder · · Score: 1

      Most database engines takes advantage of store procedures to create a pre-prepared execution plan which is used to optimise and speed execution time. Visual Studio will work with any database with an ODBC connection. All Visual Studio gives you is a nice environment in which to extract sql scripts and check them into source control, you don't actually need Visual Studio, you can just use notepad and edit sql scripts and check them in and out of source control manually.

      *sigh* I don't know what I was thinking to engage in any sort of serious technical discussion on slashdot.

      Anyhow run off and write your apps without stored procs the "SQL Injection Scanner" market needs you and your dodgy code.

    7. Re:Properly written software... by Ash+Vince · · Score: 2, Insightful

      What alovely idea, but here in the real world we have things called design constraints. Like maybe you have a web application that has been doing its job for the previous 5 or 6 years but is also constantly evolving. You have a lot of legacy code that was written to run against a mysql database from 5 years ago. That puts you on MySQL 4.0 with no stored procedures.

      Now I am not saying this doesnt need an upgrade (currently in the works), but when you are talking about a mission critical app that is already making money you have to be very careful about breaking anything, you cant just throw a new version of mysql on your master database server and pray to the gods of IT. You have to be 100% sure everything will work before you move to a new version of anything, otherwise you irrepairably damage the image of your business.

      Even when you are sure that it will work you have to perform the switch outside of core hours and warn customers of the potential for downtime. Things do not always go 100% according to plan and the most minor error can have serious consequences. Especially when in order to do something outside working hours you are doing this at 4am. It takes several days to switch your sleep patterns over to nighttime working but quite often in the run up to the overnighter you are too busy to sleep all day.

      Out here in the real world we have to deal with suboptimal platforms as the decision to go with a particular DB server might have been taken years before you started working for the company. You can not just go in and insist everything is changed to what you would prefer (even if it is a better platform).

      --
      I dont read /. to RTFA, I read /. to offend people in ignorance.
    8. Re:Properly written software... by Jaime2 · · Score: 1

      Most database engines takes advantage of store procedures to create a pre-prepared execution plan which is used to optimise and speed execution time.

      Most database engines also apply the exact same techniques to plain text queries. If the plain text queries are parameterized, then it works even better.

      All Visual Studio gives you is a nice environment in which to extract sql scripts and check them into source control

      Visual Studio also give you powerful tools to build parameterized queries and wraps them in class modules. It gives you the same tools for calling stored procedures. At the end of the day, what really matters is that you use some type of data access abstraction. Which type you use is a matter of personal preference and which technology is best for which situation. Microsoft's Transact-SQL is an absolutely horrible language that is very difficult to debug and had very poor error handling. Making a concious descision to implement complex code in that environment shows a serious lack of awareness of the costs incurred by your decisions. On the other hand, Oracle's PL/SQL is an OK language having better error handling and more useful constructs. However, PL/SQL still isn't as good as 90% of the languages that are common today. It is also fairly difficult to pass complex parameters into or out of stored procedures in any language.

    9. Re:Properly written software... by growse · · Score: 1

      I'm sorry, but if you think that stored procedures are the be-all and end-all to db query security, then it would appear that you don't have a clue.

      It is perfectly possible to write secure code without using stored procedures. It might be more difficult, in some cases, but not impossible.

      --
      There is nothing interesting going on at my blog
    10. Re:Properly written software... by Shados · · Score: 1

      While stored procedures are good, using dynamic sql inside the stored proc is (almost) as vulnerable as using normal dynamic sql.

      The truly secure methods are:

      1) Use stored procedure, with no dynamic sql whatsoever.
      2) Use prepared statements: this make dynamic sql just as secure as stored procedure, and in the more advanced RDBMS, allows query plan to be cached (aka: it is virtually exactly as fast as stored procedures. SPs being used for performance reasons is a myth folks, at least with the better RDBMSs)
      3) Use dynamic sql inside stored procedures, using prepared statements (I know at least T-SQL and PL/SQL let you do that, don't know about the others, never had to use it)

      Basically, it comes down to: use a method that allows the RDBMS to cache the query plan, and just think of it as being for performance reasons. The security will come on its own, that way you won't have to think about it. If the RDBMS can't cache the plan, there's chances of potential holes.

    11. Re:Properly written software... by Shados · · Score: 1

      Btw, what cracks me up is, bringing Visual Studio to the table, then talking about most database engines taking advantage of SPs for query plans...then forgetting...

      One of the database engine thats the most used in tandem with Visual Studio, does NOT. In SQL Server, the execution plan is not pre-prepared. Its made at runtime, and cached. What does that mean? That dynamic sql (using prepared statements ONLY!), and stored procedures, have the same performance advantage, so that argument falls flat. I know not ALL RDBMS are like that, and wouldn't be able to give you a list, but at the very least for a large portion of Visual Studio users, that argument is a poor one.

      Added to that, that the "SPs forever!" thing is a mostly Microsoft-world way of designing applications (keep in mind that I'm a .NET developer, and I love it, so I'm certainly not biaised AGAINST microsoft when saying that). In the rest of the world, SPs are part of the architecture and design decisions, like everything else, and that very, very often, they are not the right choice. An enterprise level application without any is probably designed wrong, I'll agree, but a lot of enterprise class java/unix/whatever apps use ORMs like hibernate or llblgen, and they are secure, scale perfectly fine, and perform quite well.

      The SP-only thing comes from the VB6 world if anything.

    12. Re:Properly written software... by MickDownUnder · · Score: 1

      The whole parameterized query thing vs stored procs argument has been argued from the day the internet was born.
      Here's the arguments I would make for stored procs.

      Most database engines also apply the exact same techniques to plain text queries. If the plain text queries are parameterized, then it works even better

      Database engines more often than not will generate a whole new execution plan for plain text queries, it can also cause the execution plan cache to fill up more quickly, plus it opens you up to sql injection. Using plain text dynamic sql is just plain unforgiveable, if you really want to write your sql in your source code then you should use parametertized queries.

      Microsoft's Transact-SQL is an absolutely horrible language that is very difficult to debug and had very poor error handling.

      Debugging stored procs in Visual Studio is easy. This of course is impossible to do with dynamic sql.

    13. Re:Properly written software... by Jaime2 · · Score: 1

      plus it opens you up to sql injection

      No, it doesn't. That's the false dichotomy that sp supporters like to espouse. A poorly called sp is vulnerable to SQL injection, while a well written, properly called ad-hoc query is not vulnerable. Just using a stored procedure does not protect you from SQL injection and just avoiding them does not make you vulnerable to it. I just worked up a real quick ad-hoc paraeterized SQLCommand in Visual Studio and here is what SQL Profiler shows me is really executing;

      exec sp_executesql N'SELECT * FROM Employees WHERE EmployeeID=@EmployeeID', N'@EmployeeID int', @EmployeeID = 3

      This is 100% SQL injection proof and is very likely to be cached and reused regardless of the value used for the parameter on the next call.

      Debugging stored procs in Visual Studio is easy. This of course is impossible to do with dynamic sql.

      That proves debugging is possible, not that it works well. Where are the conditional breakpoints? How do you attach to an already executing stored procedure? How do you inspect the contents of a local table variable in the midst of a procedure call? The MS Procedure debugger has all the features of a ten year old product that has not been enhanced. If I write my logic in C#, I get a far better debugger.
      As for the claim that it is impossible to debug with dynamic SQL... sure. But, most of the time I don't put non-trivial logic into SQL at all. The lack of debugging isn't a problem because there is no logic to debug. If a particular situation requires me to run complex logic on the database server, then I do use stored procedures. However, that is only about 5% of the time.

      I have embarked on a campaign at work to rewrite a bunch of 2000 line SPs as .Net code and the result is far more maintainable and debuggable code that gives us better error messages, better logging, and better performance. I'm not saying this will always be the case, but the people who first wrote this code blindly implemented it as stored procedures. This code is a bunch of complex data transformations that simply are easier to implement in a real language. Also, there are many processing exception that can arise and tracking those exceptions and alerting the correct people is far easier outside of the SQL server.
      What about my argument that T-SQL is a horrible language? You have one looping construct and one statement level conditional construct. Cursor loops require you to write the FETCH both before the loop and at the end of the loop making maintenance that much more tedious. Compile errors can be tough to track down as the messages are often vague. Hidden gotchas like compiling a procedure with QUOTED_IDENTIFIERS on and modifying it with them off are always fun. No select construct (as in a traditional programming language, not record selection). Until the latest version, no exception handling. Things like allowing run-time selection of record ordering have to be implemented with EXEC(), which actually opens you up to SQL injection vulnerabilities. Also, common constructs like this...

      SELECT X, Y, Z FROM MyTable WHERE (X = @X OR @X IS NULL) AND (Y = @Y OR @Y IS NULL) AND (Z = @Z OR @Z IS NULL)
      ... actually make reuse of execution plans undesireable. These techniques are often necessary to avoid having to create 18 million SPs each with slightly different criteria, but in doing so, you shoot your primary argument for using SPs in the first place in the foot. If you decide not to implement these types of solutions, then you have a maintenance nightmare for application that allow highly customizable searching. An EXEC() based alternative opens you up to SQL injection and also ruins the ability to grant execute permission to the SPs while denying access to the tables.

      This is just a handful of specific examples of the inferiority of T-SQL as a language for complex logic. There are many more as T-SQL hasn't really been enhanced in 15 years.

    14. Re:Properly written software... by Doctor+Faustus · · Score: 1

      What about my argument that T-SQL is a horrible language? You have one looping construct and one statement level conditional construct. Cursor loops require you to write the FETCH both before the loop and at the end of the loop making maintenance that much more tedious. Compile errors can be tough to track down as the messages are often vague. Hidden gotchas like compiling a procedure with QUOTED_IDENTIFIERS on and modifying it with them off are always fun. No select construct (as in a traditional programming language, not record selection). Until the latest version, no exception handling.

      Yes, T-SQL is pretty weak if you look at it simply in terms of a procedural language. The context of execution is right there with the tables, though, and it's just one language, which is pretty nice compared to an application language where you have to connect to the database and pull stuff over, or PL/SQL where you have to keep track of what's SQL and what's PL/SQL, and do inane things like "Select SqlFunction Into Variable From Dual;". Table variables are a glorious thing, too.

      And I spent two years or so doing most of my work in T-SQL before we started moving towards a Java web services architecture (we didn't know where we were going, but we knew we wanted to avoid writing more VB6), and in that time or the year or two before it, I never wrote a cursor loop (I used this technique.). I also never wanted run-time ordering of records, but that's because I'm more of a back-end guy. If there was a screen where the user could select the ordering, my instinct would be to do that in the recordset.

      Yes, T-SQL has plenty of weaknesses, but don't pretend the alternatives don't. It can be more frustrating, because T-SQL's weaknesses seem like they'd be easier to fix than those of the alternatives. User-defined functions in SQL Server 2000 and error handling in 2005 were good steps, though.

    15. Re:Properly written software... by Anonymous Coward · · Score: 0
      *sigh* I don't know what I was thinking to engage in any sort of serious technical discussion on slashdot.

      Yes, better to stick with forums populated by the non-technical. It looks like you just got your ass handed to you by people who simply know more.

    16. Re:Properly written software... by MickDownUnder · · Score: 1

      In my real world I don't come across many legacy apps that don't leverage stored procs. Stored procs for way longer than 5 or 6 years. They were officially made part of the SQL language in 1999 and they were unofficially around many many years before that.

      I don't go round re-writting everything just for the sake of making it mine. In the real world you evaluate every application on a case by case basis. In the real world it is very often the case that the time required to add new features or fix existing features on badly written legacy app is less than the time required to re-write it with modern programming tools.

      I also don't come across many jobs dealing with legacy apps, and the ones I do I tend to steer clear of. I prefer creating my own crap than dealing with somebody elses.

    17. Re:Properly written software... by MickDownUnder · · Score: 1

      No clue?

      Riiight...

      Well the biggest clue I have about security is that you build it into every tier of your architecture. Sticking security in one place is not a security architecture, well OK it might be, but as far as security architecture goes it sux.

    18. Re:Properly written software... by MickDownUnder · · Score: 1

      and... If your db is MS SQL Server use sp_executesql not EXEC

    19. Re:Properly written software... by MickDownUnder · · Score: 1

      Basically I think it comes down to consistency. I don't like writting an application where one half of my T-SQL code is in the DB and the other half in my source code.

      At the end of the day I find in just about every enterprise app there is at least one feature that requires a complex set of T-SQL instructions to be executed that have definite performance benefits when executed within a stored proc as opposed to parameterised queries executed from source code.

      So for consistency and extensibility I always stick my T-SQL into the database, it also keeps the DB administrators happy.

    20. Re:Properly written software... by Shados · · Score: 1

      As long as you put the right info (like application name) in your connection string, your DBAs will stay happy, no worries.

      And there is NOOOOOOOOTHING you can do in a SP that you can't do in a param query. Nothing. None. ZERO. The only performance benefit you'll get is the bandwidth (if you run a long query 1000000 times in a row, its a lot of data going).

      The rest is a decent argument, but one that only holds water in simple applications. In complex enterprise apps, you have a billion datasources. Probably 5-6 different RDBMS, ETL data sources, EDI, Biztalk, flat files from legacy system, integration of all the junk MS Word document from before you got hired and started smacking people, CLR queries, etc etc... So in the end the handful of T-SQL that would actually be benefitial (for example, a write-only query that stores sensitive information on a table that NO ONE has read access to, because of laws and stuff) just end up being an integral part of your service layer, like everything else. If your stored procedures are treated specially enough that mixing them with other data access methods becomes inconsistant, your app is way, way too tightly coupled with the domain model, and thats a future maintenance nightmare.

      If its a smaller application that won't have to do any integration, like most stand-alone apps, then it can make sense I suppose, depending on your architecture.

    21. Re:Properly written software... by MickDownUnder · · Score: 1

      Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server

      http://msdn2.microsoft.com/en-us/library/aa174792( SQL.80).aspx

    22. Re:Properly written software... by Gotebe · · Score: 1

      Don't use dynamic SQL, use stored procs. Ah, you mean like this: EXEC MyProc "UserInputHere". Yeah, that helps, especially if UserInputHere == "Nevermind""; drop table X". Coming from MS background, I reckon? Dynamic SQL and stored procs are largely orthogonal (using one doesn't preclude using the other).

    23. Re:Properly written software... by growse · · Score: 1

      Correct. And randomly throwing stored procs into your database when you don't need them doesn't make your database secure.

      --
      There is nothing interesting going on at my blog
    24. Re:Properly written software... by Ash+Vince · · Score: 1

      I also don't come across many jobs dealing with legacy apps, and the ones I do I tend to steer clear of. I prefer creating my own crap than dealing with somebody elses.

      Good for you, I used to do nothing but create new code in my last job.

      Nowadays I work in collaberation with serveral other developers and we all maintain each others code. Since I started I have learnt far more than I ever did when I only worked on designing my own projects from scratch. I also find it far more challenging and hence more rewarding too.

      I recommend you try it at some point if you want to grow as a developer.

      --
      I dont read /. to RTFA, I read /. to offend people in ignorance.
    25. Re:Properly written software... by Shados · · Score: 1

      Did you know that msdn has a lot of mistakes and errors, especially when it comes to SQL Server? Hell, I personally talked with some of its own engineers that had to be corrected by their superiors afte a discussion about its inner working. Thats not counting the insane amount of syntax error in their samples on SSIS, for example. :) That being said, that omits a simple detail: again, anything in a SP can be done in a dynamic sql statement.

      That means conditionals, loops, temporary tables, transactions, you name it. It -all- can be done. Take -any- stored procedure you made, remove the SP declaration (that gets replaced by the parameter insertion), and you're good to go. The first time I had this argument, I goofed around and tried it with a 1.5k line SP. Works pretty good. And the query plan will be the exact same.

      All these performance arguments come from 2 urban legends. #1: That there's something special about query plans in SQL Server when it comes to SPs. Thats wrong, you can dig in the documentation, or better yet, ask the SQL Server team directly: the code of the SP is literally stored as is, and when its invoked, its sent in the SAME pipe as dynamic queries get sent to, the query plan is created, the query is hashed and that hash is stored with the plan. If you send a dynamic sql, it goes through the same pipe, and the same process, in the same order. Hell, I think (but am not positive) that if you were to send a dynamic sql that matched the SP, you could reuse the SP's plan indirectly :)

      But yes: you can make an impossibly large dynamic sql query with conditional logic, loops, you can even declare variables, yadah yadah. Only some things that are structure specific won't go, like return values (have to use output parameters instead) and such, and it all gets the exact same kind of execution plan, that gets reused the exact same way. Most people don't seem to realise that. Anything else is pure misinformation, that is quite common, even among Microsoft's own employes. Its funny really.

      You'll still want to use some server side programming though. User defined functions are a blessing, even with param queries.

    26. Re:Properly written software... by MickDownUnder · · Score: 1

      Yes one day I could be as good a developer as you... dare to dream.

      My comment was obviously meant with humour. I collaborate with other developers all the time. I even go to user group meetings to get exposure to as many alternate views on development as possible.

      And like most developers I prefer to work with new or next generation technology rather than working within the confines of some sort of time bubble from 10 years back due to the restrictions of some legacy system's architecture.

    27. Re:Properly written software... by MickDownUnder · · Score: 1

      I think the only way anyone is going to settle the whole performance thing is to actually go off and do some performance and load testing with two systems using both methods. What you're saying (depending on the literature you read) sounds entirely plausible, however I think the proof is always in the pudding.

      I have faith that stored procs are actually going to win if only by the barest margin simply because there's an obvious reduction in network traffic for large complex queries. Plus I have faith that stored procs compiled once on a central server by SQL server are actually going to perform better than queries compiled several times in code running on a VM on every client connecting to the server.

      However I admit I really don't know what the outcome would be and I don't think you know either the only way to get half a clue would be to do performance tests.

      For me performance is not the driving motivator for using stored procs, it is the extra layer of security, and the manageability of stored procs.

    28. Re:Properly written software... by Shados · · Score: 1

      Indeed, its probably better to drop it, but just to settle something: I personally always go with the "best tool for the job" philosophy, and i'm always looking for ways to improve what I do. What that means is, I actually HAVE been doing benchmarks on every versions of SQL server I've used, sniffed network traffic, personally discussed with some of microsoft's engineers, stressed test real life scenarios :) I -do- work for for an extremely large corportation, so these things tend to be crucial. One of the main thing is that the network traffic is only for very long and complex queries, and it evens out with certain techniques that are very hard to use in SPs while keeping them maintainable, that can save truckload on network traffic by returning a minimum of data, so that part more or less evens out (or at least, it depends on the type of application you're making)

      The security thing, well, that we could argue all day and it depends how you do things, so I won't say anything for or against that: it depends on your architecture. It can actually hurt in certain scenarios, btw (rare though). And manageability, thats actually where the argument against SPs tend to be...so that too, is seriously a matter of opinion.

      For now though, I'm currently working on a project where the architect decided to make us write 90% of the code in SPs (the other extreme case. Where there's 20 lines of SP for every line of C#, you know something's weird). Driving me totally insane.

      Anyhow, in the end, I do agree with you on certain points. SPs ARE the right tool for certain jobs. Making an entire, composite, service vased, multi-environments, cross-database, efficient, and easy to maintain enterprise system, with ONLY SPs, is down right impossible. The important part is to UNDERSTAND the fors and the against, and pick accordingly. And -only- in the Microsoft world (blogs, forums, etc) will you EVER hear the "SP-only" thing. In the rest of the world (java, C++, unix, etc), they use both. As it should be :)

      Wow, and this was supposed to be a short reply. I promise I won't answer to this ever again, hahaha!

  9. Testing slashdot by mobby_6kl · · Score: 4, Funny

    blah'; UPDATE users SET uid = '1' WHERE uid = '668092';

  10. Wikipedia is your friend by CaptainZapp · · Score: 1

    For more information about SQL Injection

    --
    ich bin der musikant

    mit taschenrechner in der hand

    kraftwerk

  11. Top 15 _______? by kjart · · Score: 2, Insightful

    What is this, Digg?

    1. Re:Top 15 _______? by Jesus_666 · · Score: 1

      Hey, I'm not complaining; this makes my upcoming IT Security assignment much easier. Once again reading Slashot pays.

      --
      USE HOT GRITS WITH STATUE OF NATALIE PORTMAN (NAKED AND PETRIFIED)
  12. Validating Input by mtjo · · Score: 2, Informative

    Validating input prevents alot of problems. Prepared queries help but can still be exploited in poorly written statements. As in the classic SELECT query example, "where id=23 OR 1=1", using a datatype test as well as testing for null values for a $_GET or $_POST parameter before executing the query would throw back an error if expecting an unsigned integer.

  13. One of the best SQL injection attacks I've seen... by thewils · · Score: 4, Informative

    ...was in conjunction with an error page which displayed the results of failed SQL.

    I was able to change an innocuous 'select ... from catalog where section=1' into 'select ... from catalog where section=(select password from users where id=1)'.

    This was nicely reported back to me as a SQL error stating that SQL was unable to convert "sdfsdfsdfsdf" into an integer, where "sdfsdfsdfsdf" was user id 1's password. I reported the problem to the site's owners, and it was still a month before they fixed it.

    Moral of story - don't show the users any SQL errors, it gives them far too much information.

    --
    Once I was a four stone apology. Now I am two separate gorillas.
  14. do they check cookies? by brunascle · · Score: 2, Insightful

    it's not just URLs and post-back forms that can be vulnerable, cookies can be too. i didnt realize that until i found one on my own site. (it wasnt exploited, i found it on my own.)

    1. Re:do they check cookies? by Anonymous Coward · · Score: 0

      Darn slashdotters have to ruin all the fun! ;) I used to mess with cookies on a rather big site *cough* yahoo games *cough* and I would be "logged in" as some obscure names. Boy was it fun having 5-digit "ratings" on yahoo chess! I think they might have fixed it 5 years later, though.

      It is due to exploits like this (and numerous others I'd find in my free time, like being logged in as others in certain e-mail accounts) that I would never use the Internet to do anything important involving money transfers. Let's just say the problems and exploits out there are bigger than most people realize.

      Posting anonymously for obvious reasons.

  15. intresting by MadKad · · Score: 1

    intresting read thank you and well put together

    --
    Webmaster SEO Forum
  16. Non Issue by encoderer · · Score: 1

    1. You can easily run both PHP4 and PHP5 on the same server. When it's done, it's usually determined by a .php5 extension or <?php5 ?> braces.
    2. The upgrade from 4 to 5 CAN be quite painful if you've done a lot of OOP coding in PHP4. Nearly all of the upgrade was focused on increasing and improving OO support. Some of the changes made (like, for example, constructor naming) are backwards compatible, but most aren't.
    3. This is all moot because you don't HAVE to use the Circa-php4 mysql extension, anyway. MySQLi (the Improved mysql extension) can be used with PHP4 just as well as it can be used with PHP5.

  17. Bwah by bytesex · · Score: 1

    I don't need SQL injection scanners. I use perl DBI and I always put arguments in as '?'.

    --
    Religion is what happens when nature strikes and groupthink goes wrong.
  18. Re:One of the best SQL injection attacks I've seen by mig21bp · · Score: 1

    don't show the users any SQL errors, it gives them far too much information

    What do you mean by 'too much information'? Security through obscurity?

  19. Use INTEGER keys by Anonymous Coward · · Score: 0

    Prepared SQL statements with positional arguments (SQL parameters) cannot solve all SQL injections cases. Sometimes, the program must build SQL text at run-time. For example, a search must add boolan terms to its WHERE clause based on user-input.

    An effective way to protect a SELECT..WHERE query from SQL injections is to use INTEGER foreign keys. Don't expose the search form to text inputs, i.e. SQL injection. Instead, create another menu-table for each search term and use its primary key to index into the result-set.

    Example:

    Use these tables to search "restaurants" by "food type", e.g. "Italian".

    menu table food_type (food_id integer, food_name char);
    table restaturants (name char, food_id references food_type(food_id));

    The HTML form has a SELECT menu of food_type with the OPTION VALUEs equal to the FOOD_ID keys. The user sees the FOOD_NAME. Users can't choose food-types that aren't in the DB, and won't have to guess until they find one!

    This prevents SQL injection because the form input promises to be integer. We may build an IN() predicate of several FOOD_ID keys out of integers.

    Also, note that the SELECT-list is *only* the columns we want to expose to the web.

    SELECT name FROM restaurants
    WHERE 1 = 1
    -- added search term
    AND IN(
    -- list of FOOD_ID keys
    1, 4, 8
    )