• (nodebb)

    I used to be very anti-stored procedure, viewing them as something used by people who didn't"get" more modern techniques like ORMs. I've since come around to viewing them in a more positive light but this snippet is just moronic no matter how you slice it.

  • Hanzito (unregistered)

    Moronic? Only because it combines the worst of both worlds? Standards these days!

  • (nodebb)

    "he didn't understand what the problem was" -- because there is not a single problem, there are multiples, each with their own severity and impact. Given the provided context it would be impossible to determine which one was "THE PROBLEM"

  • pif (unregistered)

    That's the kind of stored procedures out sweet Little Bobby Tables loves so much!

  • Just a DBA (unregistered)

    I’ve seen worse. :grimly lights a cigarette:

  • (nodebb) in reply to TheCPUWizard

    "he didn't understand what the problem was" -- because there is not a single problem, there are multiples, each with their own severity and impact. Given the provided context it would be impossible to determine which one was "THE PROBLEM"

    FFS! SPs were proposed as solutions for two supposed problems (SQL injection and query performance), and "THE PROBLEM" that the dev didn't understand was simple: the specific SP solves neither of those problems.

  • PG- Flats - Properties in Delhi - Sky Properties (google)

    Really very happy to say, your post is very interesting to read .I never stop myself to say something about it. You’re doing a great job. Keep it up. “Anyone can start a blog, but the real test is getting readers.” Thanks for sharing your experience

  • Kj (unregistered)

    ORMs are really only a good idea if your application is really a lone isolated island.

    If you rely on them generating code by using some annotations, you're going to have problems.

    Most obvious is that database diffing is an absolute pain if your constraints aren't consistent.

    Also you generally want to seperate static and dynamic enumerations with schemas or some sort of method to know what the minimum data to be copied is for separate server setups.

    "Allowed States" --> static "User state" --> dynamic

    Without ORMs, you can change the database structure without much problem if the stored procedure returns the same output provided the same input.

    By restricting it that way you sorta hardlock your api structure without restructuring both portions.

    Generally you should enumerate your domain checks in the table and checked views and test it before the app plugs in because otherwise app bugs live forever in audits and backups

    Unknown access points from teams that don't communicate well can wreak havoc because the app was developed only validating user input.

    When reporting comes into play the downsides of not constraining data appears FAST.

    There are performance benefits from defining constraints in the database layer too.

    It's not normalized enough, if you need temporal stability, you'll have problems because unless your hardcore greenfield using something like temporal tables, that's some pretty strict designs constraints you can't ORM out of

  • Peter of the Norse (unregistered)

    I’m not familiar with MSSQL because of all of the ways it violates the SQL standard. Can you start a string with two ' or is this an example of excessive escapism?

  • t (unregistered) in reply to Peter of the Norse

    AFAIK, the latter. Strings use ' in MSSQL

  • Chris (unregistered)

    I know it's far from the worst part here, but I love how they felt the casts of UserID (numeric) and EndDate and StartDate (dates) to string would still need to have a left and right trim applied. Or if they do, that's an additional WTF.

  • David Mårtensson (unregistered) in reply to Peter of the Norse

    I have never seen double ' in mssql so that is more likely copy paste artifacts.

    Real double quotes " can be used instead of [] for fieldnames with spaces

  • 516052 (unregistered) in reply to Steve_The_Cynic

    No. Actually "The Problem" in this case is that in doing what he did the developer created a false sense of security making code that is not in fact safe appear safe to managment and anyone else who might have the power to tell him to fix it. This means that not only is there now a security hole but because the code is "up to standards" it will newer be checked or fixed ever until a breach happens.

  • (nodebb)

    You would be surprised at the sites that are vulnerable to this kind of attack'); drop table articles; --

  • (nodebb)

    "The problem" here is that the standard-setter has asserted that "Stored Procedures avoid SQL injection attacks and increase database performance". The example shows that stored procedures do neither. You avoid SQL injection by never using string concatenation to build SQL. Sometimes, mandating stored procedures will accidentally achieve this goal. But, it's much easier to state the goal directly and review both application code and procedure code for rule violations.

  • (nodebb) in reply to David Mårtensson

    Real double quotes " can be used instead of [] for fieldnames with spaces

    They're both quoting mechanisms, used when the name of the thing is not syntactically a valid identifier. Containing spaces can be one reason, but they also allow for field names like SELECT and FROM that are otherwise syntactically “fun”.

  • Sole Purpose Of Visit (unregistered) in reply to PG- Flats - Properties in Delhi - Sky Properties

    Thanks for sharing, and I'm really impressed that you got through the moderating system.

    Or then again, not to both.

  • Jay (unregistered)

    If you took out all the quotes, pluses, trims, strs, and associated parentheses, and just left it as a regular query in the body of the SP, it would execute mostly fine, except for the database name substitution.

    This is where you could use synonyms or better yet, views.

    I'm also wondering if there's a better way they could be authenticating with their databases than having each database authenticate against it's own users table

Leave a comment on “Classic WTF: All Pain, No Gain”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article