• (cs) in reply to WTFer

    WTFer:
    I think is similar to what happens with Windows security vs Unix security. It's better to close everything and start opening things little by little, instead of giving everyone administrator priviledges. The same applies for SQL is better to create your own secure interface than living sql open and then trying to close it down.

    Obviously somebody hasn't used Windows 2003 Server where EVERYTHING is disabled by default.  IIS6 doesn't install by default, services are disabled, etc etc.  I think you meant "classic Windows security" which I will admit (being a Unix Admin myself) was horrible.  But if I'm going to defend Windows I should also point out that IIS6 is almost 2 years old and there hasn't been a single security flaw found, Apache (both 1.. and 2.0 have had many, about 7 in the past 2 years.

  • (cs) in reply to El Duderino

    El Duderino:
    Why is it wrong to expose your SQL in the presentation layer?  What "rule of programming" is this breaking?

    Well you have now hardcoded two seperate and unrelated systems.  Your application cannot scale well because it assume the database will be structured one specific way.  Your problem is that you are mixing layers, presentation layer should not understand the logic of a system that's on another box.  If somebody comes along and wants to improve the database, they MUST modify your code.  If they need to use a whole different database solution, they will have to modify your code again (instead of just the connection data, which should be outside the code anyways).

    And I won't even get into the security issue of sql injection, you can Google if you don't know about that. 

    Leave it to say, it's systems like YOURS that hackers love.

  • Pierce Brosnan (unregistered) in reply to travisowens

    Sean Connery is right. A SQL-based approach to the front end is very secure, if you know what you are doing. And, such an approach is extremely maintainable, if care was taken to structure everything well. Finally, there is such a thing as legitimate power users. (Gasp!) If nothing else, it makes it easier for other programmers to use your web site in interesting ways.

    Such an approach can lead to a very powerful and flexible front-end. Personally, I'd rather see more SQL-based frontends than 1,000,001 ad-hoc query languages that get buggily translated to SQL, or worse, a site that offers nothing more than a few pre-programmed queries to their users.

    Storing SQL in the URL string has the distinct advantage that now queries that originated with your web address are conveniently located in your webserver's log.

    Not to mention that the page already almost conforms to the RESTful philosophy of web development, which is probably a good philosophy to be using in the context to a web-interface to a database. (I can think of other web situations where REST is not applicable... Can you?)

    It all depends on the context. There simply hasn't been enough context in any of these discussions to make any real judgement on the merits and demerits of any approach described here. I've read little more than ideology.

    Beware the 180 degree turn. Be wary of the knee-jerk reaction. Recognize that one data point does not a curve make. All things in moderation, especially moderation.

    Now, what would be the best way to protect a site that opens up their database in such a way from SQL injection attacks? Properly setting up user accounts is a big start, but even that is not necessary. (Though I certainly would set up accounts, special views, and column permissions.)

    Sit down with the SQL grammar. Find a restricted grammar that produces a subset of SQL that you find acceptable. Then write a parser that recognizes that subset... it doesn't even have to do anything with the SQL command. (After all, a parser is not a compiler.) You should have the programming ability to do this without using YACC or regular expressions. (If you are using regular expressions here, I almost guarantee it won't work.)

    All your parser does is check that a query is in your restricted SQL grammar, and maybe some simple checks that can't be encoded in grammar. If your parser says, "yes! that string is in my grammar!" then pass it along, unchanged, to your SQL backend. Otherwise, return an error message indicating the point at which your parser stopped recognizing the string, and what could happen after that point.

    This really isn't difficult. But, oh yeah, I forgot. 80% of programmers with a B.S. in Computer Science don't really understand context-free grammars. Handed a CFG, they couldn't write a robust parser to save their life, let alone modify the grammar to suit their purposes. This is why we have such atrocities as the DOM, the W3C's verbose and easily-misunderstood English definition of XML, and widespread abuse of regular expressions.

    And no, that's not ideology. Context free grammars are a fundamental concept in computer science. They are not tied to any particular programming language, software engineering philosophy, or development methodology.

Leave a comment on “Do You Believe In Magic?”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article