• (cs) in reply to Licky Lindsay
    Licky Lindsay:
    <cfif storedProcs>
  <cfquery name="get_products" ds="#datasource#>
    select * from products where
    customer_id=#customer_id#
    and status = 1
  </cfquery>
<cfelse>
  <cfquery name="get_products" ds="#datasource#>
    select * from products where customer_id=#customer_id#
  </cfquery>
</cfif>
</pre>
<p>So if you ever turned &quot;storedProcs&quot; off, the app would run the wrong SQL, even through stored procedures weren't being used at all.</p>
</div></BLOCKQUOTE>
<p>Holy moly.  You should definitely submit this story as it deserves more attention.  As a side note, the simplest solution is to rename the &quot;storedProcs&quot; boolean to &quot;shouldTheProgramWork.&quot;</p>
  • Jay (unregistered) in reply to Licky Lindsay
    Licky Lindsay:
    The kicker here is the hexadecimal identifiers for each sentence. I've seen half-ass homemade stored procedures like this several times in real applications, so it would be only a mild WTF for me. But I don't think I've ever seen them named with this amount of opacity before. Wow.

    I once did a consulting job at a company that had a policy requiring all files to have names consisting solely of a string of random digits. They had a department responsible for assigning these file names/numbers to prevent duplicates. Their reasoning was that if a hacker broke in to their computer system, they wouldn't be able to figure out which files contained information worth stealing because all the names would be meaningless.

    Of course, this meant that the programmers had to maintain lists of what the file names were and what they really contained, and if the programmer who created a file quit and you can't find his cheat sheet, you're pretty much out of luck.

    To the suggestion that only sensitive files be given these meaningless names, they replied logically enough that any hacker would then know that the files with the meaningless names were the valuable ones. I suggested giving the sensitive files particularly innocuous names, like call the file with your top secret formula "letter.to.mom". That didn't fly. I also suggested that they carry this philosophy to the obvious next step, and require all paper files in the office of the manager who came up with this rule to have random numbers on the tabs. That wasn't well received either.

  • Trouvist (unregistered) in reply to TGV

    That actually sounds like it would be a hell of a lot of fun to implement, but fucking hilarious to maintain and actually use.

  • bshock (unregistered)

    I'm just coming off a contract where we had a db system that was almost as bad as this smoking turd.

    The idea was to start with a base class that could perform functions like select, update, etc., and then derive classes for every db table. In order to do this, each table class needed an elaborate array of constants for describing every aspect: fields, primary keys, foreign keys, indices, etc. When you wanted to write a WHERE clause, you had a Where class that needed to be filled using these constants, as well as several others that defined the logic. Combining Where objects in a particular order could create a very complicated WHERE clause, which could then be sent to the table class in question.

    Of course, if you wanted to add a join or subquery, or if you needed fields from multiple tables returned by a single select, these things could be done... through commands so complex and questionable (and undocumented) that you might spend half a day developing the statement.

    Adding a single field to a single table on this system could require an entire day. Adding a table might take nearly a week. The database itself was little more than a shell, with no referential integrity, no triggers, and no way to trace relationships through an ER Diagram.

    The programmer who created this monstrosity (which management still backed as a solid system) was long gone, but had left his mark throughout the code. Aside from the frequent ASSERTs that were commented as "This shouldn't assert, but if it does, call Eric immediately," there was one pragma message buried in an obscure module that defined this guy's attitude: "Eric is God."

  • (cs) in reply to GalacticCowboy
    GalacticCowboy:
    Even if what "they" say is true, it still doesn't make sense to solve the problem again and again.
    So true. A lot of developers seem to love nothing better than reinvent the wheel. They'll make any excuse to.

    Sometimes I think the problem is that a lot of devs think that implementing something is as quick and easy as how the problem looks when they first think about it in their head. (That's when you can be sure that they've never really challenged themselves yet.) "Oh I'll just add some code here and here and here. No problem." So they want to tackle anything that sounds glitzy, impressive or important because they think everything is equally easy. At least that's what it seems like sometimes.

    What's lame about this is that the more experienced people can sound like nay-sayers and/or not as qualified to do the work next to these guys who are convinced that they can do anything better than anyone else. Any shouldn't they believe that they can do anything, that's what TV tells them.

  • Dazed (unregistered)

    Having just finished an assignment at an organisation with a large collection of small-scale WTFs, reading this story (and some of the comments) suddenly makes me feel a whole lot better about my work.

  • Sigivald (unregistered)

    The critical design flaw here is the small address space.

    Only four hex digits? That can't possibly be enough.

    No, no. They should be using GUIDs.

  • (cs) in reply to pillus
    pillus:
    Misread that last part as Stoned Procedure. That could have been, too.
    And I mis-read "thoroughly analyzed..." as thoroughly paralyzed.
  • (cs) in reply to Zonkers
    Zonkers:
    GalacticCowboy:
    Even if what "they" say is true, it still doesn't make sense to solve the problem again and again.
    So true. A lot of developers seem to love nothing better than reinvent the wheel. They'll make any excuse to.

    Sometimes I think the problem is that a lot of devs think that implementing something is as quick and easy as how the problem looks when they first think about it in their head.

    That's one half of the equation. The other is that researching and learning existing API libraries (especially when they're not perfectly designed and documented) is much less exciting and can seem more difficult and time consuming compared to just implementing the simple, straightforward functionality they need right now.

    It takes some experience to realize how much more time it can take to actually implement and debug simple, straightforward functionality efficiently and correctly and handle all corner cases.

  • (cs) in reply to bshock
    bshock:
    "Eric is God."
    Actually, they used his last name. [image]
  • (cs) in reply to Anonymous
    Anonymous:
    FredSaw:
    vereor:
    All you credit cards and bank info are belong us.
    Wow! Multiple layers of Engrish!
    Welcome to the Internet. Yes, we know that these owls and cats look funny, we noticed that too.
    I haven't a clue as to what you're talking about. On the odds-on chance that you are likewise about my post, I will clarify:

    "All your base are belong to us." - classic, immortalized Engrish phrase. "All your credit cards and bank info are belong to us." - would have been a humorous quoting and reworking of the classic. "All you credit cards and bank info are belong us." - a mangling of the classic, due to the quoter's use of Engrish.

  • greg (unregistered)

    Perhaps they were using a non- SP DB such as MySQL until relatively recently?

  • Teh Optermizar (unregistered)
    • face-palm *
  • (cs) in reply to Stupidumb
    Stupidumb:
    Quis query ipsos queries?
    Hic. Or possibly Haec.

    I'll leave the verb well alone, but I suspect that you mean "querios." (Note for American readers: they're like "cheerios," but a little more chocolaty.)

    And now, back to my regularly scheduled dose of morphine.

    Really. Why does anybody expect people who claim to "understand databases" to be anything other than worse than useless, outside of a particularly egregious enterprisy environment?

  • (cs) in reply to FredSaw
    FredSaw:
    bshock:
    "Eric is God."
    Actually, they used his last name.
    Not quite as amusing as "Jesus Saves! <graffiti> But Dalgliesh scores on the rebound!</graffiti>"

    Then again, I don't remember a dog pissing up against the wall for that. Probably because dogs have a sense of self-preservation. Millions of Scousers would have killed it.

  • (cs) in reply to Ubersoldat
    Ubersoldat:
    “but I’ve heard they’re a big pain to maintain.”

    What? I'm no DBA but what? Maybe I missed something when I studied SP's but "a big pain" is not the idea I had. Maybe some experienced DBA can explain us in which way SP are a pain to maintain, must be so complex that my IQ doesn't reach there.

    Anyway, what a stupid solution for such a simple matter. Thank god they weren't using C/C++ and filled it with DEFINE's.

    Yet another misplaced apostrophe: but you're forgiven, my son. You are, after all, no DBA.

    That would be "#define". It tends to be deprecated amongst us C++ folk. I imagine that your IQ doesn't quite "reach" programming in C or C++, but, let me assure you: we Masters of the Universe would also agree that maintenance of stored procedures is fairly routine; in fact, trivial.

    You may well have intended "explain to us," btw. I understand that the entry-level qualifications for this industry are going down by the day.

  • anonymous for good reason (unregistered) in reply to GalacticCowboy
    What about the actual value? I recommend a separate (nullable) column for each data type. So, INTEGER_VALUE, DATETIME_VALUE, NVARCHAR_50_VALUE, etc.

    I work on a large enterprise(y) software package by a household-name company, and this is exactly how many of our DB tables work... these are people who should know better. The claim is that it supports "customization".

  • Me (unregistered)

    Wow just wow i'm looking after someones POS that does the exact same thing. Not only is it hard to maintain it's slow slow slow slow. but hey i don't have to recompile any code.....

  • Lash (unregistered)

    I saw a coworker do something similar in Access. Using VB code to open an ADO connection to itself in order to retrieve the queries from the sentences table.

    He would then open a connection to Oracle to execute the SQL sentence (an UPDATE query). Updating one record at a time, for each of the 70,000 rows that needed to be updated. Closing the connection right at the end of each iteration.

    The statements were in the form of:

    UPDATE usr
SET usr.SCORE = <user_score>
FROM csc.user_scores
WHERE usr.FIRST_NAME = '<user_fname>'
AND usr.LAST_NAME = '<user_lname>'

    The VB code after getting the statement would replace parts of the SQL statement:

     mySQL = rst.Fields(1).Value
mySQL = Replace(mySQL, "<user_score>", rst2.Fields("user_score").Value)
mySLQ = Replace(mySQL, "<user_lname>", rst2.Fields("user_lname").Value)
mySQL = Replace(mySQL, "<user_fname>", rst2.Fields("user_lname").Value)

    ... 5 hours later on execution, a crash!

    The value in the field "user_fname" had an apostrophe.

    It took him 5 more hours to figure it out. Once he did, his solution was to use one more Replace() to eliminate the apostrophes in "user_fname".

    .<

    Brilliant!!

  • zzp (unregistered)

    To avoid hardcoding the purpose of a variable in its name, I also suggest following the same naming methodology for all your variables: v_XXXX where XXXX is a unique hex number.

  • (cs) in reply to Cosmin Marginean
    Cosmin Marginean:
    - Did you hear about spoons? - Yes, but I also heard that they are a pain in the ass for some reason (I don't know why), so I'm gonna keep eating my soup with the fork.

    I heard there is no spoon?!

  • (cs)

    SQL Sentences? I feel sentenced to death.

  • Mike Dimmick (unregistered)

    The problem with making your SQL dynamically editable is that people will edit it on the live server directly. The same holds true of any other dynamically compiled language from JavaScript to batch files to bash scripts to ASP pages to any 'soft-coded' system. Then, it can become problematic to bring those changes back into the main code tree. Changes should only ever go forwards, from development to test to production, never the other way. The only possible exception is when doing so will fix a highly urgent, high impact bug that will simply take too long to go through the correct process, but the developer who makes the fix must ensure that it is immediately pushed through the correct process at a higher priority than any other work he's doing.

    Stored procedures are great for when you need to employ a security boundary. I have a no-touch system where the user account normally used by the server software to connect to the database has no rights to even SELECT from tables - it can only use the stored procedures. That should cut off SQL injection/information disclosure/repudiation attacks at the knees. (This is SQL Server - the user account in the database is only a member of the public role and I've granted the public role only EXEC permissions on the required stored procs.) Stored procedures also enforce parameterization.

    However, you can go too far. Stored procs work best when the shape of the query is fixed. I've seen people try to make a stored procedure change its shape in response to optional parameters, to avoid having to have multiple queries or IF statements within the procedure. This typically looks like:

    SELECT col1, col2
FROM table
WHERE 
  ( col1 = @col1 OR @col1 IS NULL ) AND
  ( col2 = @col2 OR @col2 IS NULL )

    This idea confuses SQL Server 2000's query optimizer completely and it will often build a plan which tries to use an index on col2 when @col2 was NULL, and vice versa. SQL Server 2005 does build a good initial plan but will cache and reuse the result; if the first query uses @col1 but not @col2, but a subsequent query uses @col2 but not @col1, the query processor will still try to use the first plan which used an index on col1, which is useless for these arguments.

    If you need to do this, you should generate dynamic SQL strings and submit them to the database as text. However, you should always generate dynamic parameterized SQL. All the major RDBMS support parameterized queries where parameter markers can be replaced with actual parameter values, with a twist: the actual values do not need to be encoded to fit the SQL syntax. You can, like stored procedure parameters, pass native data types. The risk of SQL injection would suggest that you should never use an unparameterized query for any case where user data is supplied to the query.

    SQL Server still caches the query plans for dynamic parameterized queries. It also caches plans for non-parameterized queries, but has a feature called 'auto-parameterization' where it tries to deduce what's a replaceable parameter. It weights the query plans in its cache so that in general, stored procedure plans will outlast parameterized queries, which are superior to auto-parameterized plans, which in turn outweigh non-parameterized plans. That's the only real difference among these features.

    I personally don't like SQL text to be included in other programming language code, because it can't be validated in there. I generally prefer stored procedures.

  • Hans (unregistered) in reply to Anonymous
    Anonymous:
    - Program a Turing machine interpreter in SQL (its possible! i've done this)

    Really? How did you deal with the requirement for an infinite tape? I bet you used some lame approximation of infinity there...

    PS. black characters on a very dark purple background for the captcha? Yes, I can almost make that out...

  • Inglorion (unregistered) in reply to bshock
    The idea was to start with a base class that could perform functions like select, update, etc., and then derive classes for every db table. In order to do this, each table class needed an elaborate array of constants for describing every aspect: fields, primary keys, foreign keys, indices, etc. When you wanted to write a WHERE clause, you had a Where class that needed to be filled using these constants, as well as several others that defined the logic. Combining Where objects in a particular order could create a very complicated WHERE clause, which could then be sent to the table class in question.

    Hey, Hibernate criteria queries!

    On the subject, this whole story reminds me of my experience with almost every Java framework I've worked with, especially the high-profile ones. There are so many layers of abstraction piled on top of one another that the thing becomes slow as molasses, bloated like hell, and it becomes a hell of a job to find out where some part of the behavior really comes from. And in the end your code is still tied to that framework, so all the abstractions have bought you exactly nothing.

  • Smoky (unregistered) in reply to bshock
    bshock:
    I'm just coming off a contract where we had a db system that was almost as bad as this smoking turd.

    The idea was to start with a base class that could perform functions like select, update, etc., and then derive classes for every db table. In order to do this, each table class needed an elaborate array of constants for describing every aspect: fields, primary keys, foreign keys, indices, etc. When you wanted to write a WHERE clause, you had a Where class that needed to be filled using these constants, as well as several others that defined the logic. Combining Where objects in a particular order could create a very complicated WHERE clause, which could then be sent to the table class in question.

    Of course, if you wanted to add a join or subquery, or if you needed fields from multiple tables returned by a single select, these things could be done... through commands so complex and questionable (and undocumented) that you might spend half a day developing the statement.

    Adding a single field to a single table on this system could require an entire day. Adding a table might take nearly a week. The database itself was little more than a shell, with no referential integrity, no triggers, and no way to trace relationships through an ER Diagram.

    The programmer who created this monstrosity (which management still backed as a solid system) was long gone, but had left his mark throughout the code. Aside from the frequent ASSERTs that were commented as "This shouldn't assert, but if it does, call Eric immediately," there was one pragma message buried in an obscure module that defined this guy's attitude: "Eric is God."

    So they invented their own very of Hibernate, except ten times less comprehensible and one million times slower. Brillant

  • (cs) in reply to bshock
    bshock:
    The idea was to start with a base class that could perform functions like select, update, etc., and then derive classes for every db table. In order to do this, each table class needed an elaborate array of constants for describing every aspect: fields, primary keys, foreign keys, indices, etc. When you wanted to write a WHERE clause, you had a Where class that needed to be filled using these constants, as well as several others that defined the logic. Combining Where objects in a particular order could create a very complicated WHERE clause, which could then be sent to the table class in question.

    In a statically compiled language I see nothing bad and plenty of good about using constants for table and column names. Especially if you build a tool to extract them from the DB schema and generate your header file (or equivalent in whatever language).

    It's a lot quicker to find out that you misspelled a table name from the compiler, than having to compile and run the code before you get feedback. Table and column names are less than intuitive in many databases.

    The basic idea here is good, but it sounds like it was executed really badly.

  • Péter (unregistered)

    Sentences?! Sentences are sooo simple. I want to read SQL essays, poems and drama!

    http://en.wikipedia.org/wiki/Abbas_I_of_Safavid

  • Paul (unregistered)

    The beauty of this approach is that there is no way for SQL server to ever optimize any query. Complex and slow too! Double head slap!

  • (cs) in reply to Mike Dimmick
    Mike Dimmick:
    The problem with making your SQL dynamically editable is that people will edit it on the live server directly. The same holds true of any other dynamically compiled language from JavaScript to batch files to bash scripts to ASP pages to any 'soft-coded' system. Then, it can become problematic to bring those changes back into the main code tree.
    This is a problem that has nothing whatsoever to do with whether a language is dynamically compiled or not (after all, who says you cannot install a C compiler on the production server to do hot fixes?), and everything with whether you have a sound release and deployment process.

    If you have to employ cowboy coders who would edit stuff on the production server if given the chance, don't give them that chance (i.e. the logins for the production server).

  • Bobby SELECT * FROM Tables (unregistered)

    It's still not enterprisey enough. They need to add, umm, flips to random page of nearby database book off-site replication via a data warehouse.

    I was going to say 'denormalization', but I don't think it was very normal to begin with.

  • (cs)

    In Access95-days, I learned my first DB skills from a guy who was too lazy to write out every query time and again he needed it. So he stored the most useful queries (not every) in text form into a separate table and keyed them with a short, but meaningful name. At least this guy did this to ease life on himself (It actually saved him time) and there was no redundancy involved... I still owe him for willing to teach me.

  • Joe (unregistered) in reply to J

    In a lot of larger organizations you need to engage a DBA through some insane bureaucratic process in order to write, test, debug, implement, or change a stored proc. It's just easier and cheaper to build the SQL inside the application. It may not be the right thing to do in terms of security or performance, but in some organizations it's really not practical to do otherwise.

  • Ryan (unregistered) in reply to Drum D.
    Drum D.:
    How can one become "lead" developer by relying on hearsay? Probably because the superiors "heard" this person "is fit for the job". UnbeWTFinglievable!

    Because hierarchies and over specialization kill meritocracies where this would have been rooted out.

  • matelot (unregistered) in reply to snoofle

    nd you comment is ?

  • Pope (unregistered)

    This reminds me of an interview I had with a law firm. After pretty much every technical answer I gave they asked, "Yeah, but how fast does the code run?"

    I finally had my fill and replied, "While it is important that code runs quickly, I've found that it's more important that code be maintainable than fast." And everyone at the table laughed at me. And this was after the lead programmer said he had trouble instantiating objects in .NET 2.0.

    Since I had just been in a situation where I worked 60+ hours a week to maintain super-dee-duper-fast code that had at least one major problem daily, I opted out of that position when they offered it to me.

    Sharing is caring.

  • Barry Kelly (unregistered)

    I've done something similar, but with different low-level details:

    • Running on JET - no stored procedures, so they can't be used
    • Putting the SQL in the database means that the differences between databases is abstracted away, all you need is the query to get the queries
    • Populating a cache at startup means that queries don't involve two round-trips to the DB.

    Of course, there were sensible names and no hard-coded SQL and none of the in other weirdness shown here that system.

    It was all in a college project a long time ago. Of course, these days, I have more and better options.

Leave a comment on “SQL Sentences”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article