• Arioch (unregistered) in reply to boh
    boh:
    silent d:
    Yeah, a safety setting like that would be so hard to bypass:

    UPDATE usr SET email = 'nonsense' WHERE 1=1

    you have to bypass it intentionally.

    ...and all this is absolutely irrelevant to the case.

    the damn article:
    update usr set email = 'www.%com' where email like 'www.%com';

    You see, there was another error ! The guru just cannot tell file renaming (with implied pattern matching and substitution, done to some degree even by most stupid OSes) from SQL command, that hardly ever does something implied and non-specified (MySQL does, see MySQL gotchas article). I cannot see easy (straight jackets would be a bit too much) fool-proofing tool, that could analyze such a WHILE to see if it is does has sense or not.

  • (cs) in reply to aliquot
    aliquot:
    So you have a several-years-old Oracle database... which almost certainly doesn't have ANY of the security patches of the last few years applied... and contains enough fresh, critical business data to sustain a reporting application. Nice. Where did you say you worked again? ;-)
    They updated the security (and the schema) at the same time they did the Informix port. They just didn't create a Dev or QA version.

    Nice try, though. :)

  • (cs) in reply to Anonymous Otaku
    Anonymous Otaku:
    Kermos:
    Those keyboards actually replace the space bar with special 'shift' keys that allow all Kana to be mapped onto the same keys as the roman alphabet. However, I'm not quite sure where space is on that layout... :)

    why would they need one? japanese doesn't have spaces

    The keyboards still also do have a roman alphabet layout so you don't have to write just Japanese with them. So a space key I think would be useful. Also, while you're correct in that Japanese sentences don't use spaces, there still is the occasional time where a space can be useful for formatting reasons.

  • (cs) in reply to Keybounce
    Keybounce:
    You would be surprised at how many places assume that a domain name cannot end in a ".".

    So, if you throw "[email protected].", then it will be rejected.

    Not valid in the host part of an address per RFC822 (or the updated 2822, or 821/2821 SMTP protocol). There are many faults in email address validators; this is not one of them.

    Incidentally, did you know that the username part can't begin or end with a dot either, or have two dots next to each other, unless it's a quoted string?

  • (cs) in reply to Douglas
    Douglas:
    anon:
    "[email protected]" might be a perfectly valid email address (if there's an MX record for www.anyco.com).
    It's also a perfectly valid email address if there is no MX record, but an A record.

    It's also a perfectly valid email address if there's none at all - validity of email addresses does not depend on DNS resolution in any sense. (but you're right insofar as an A record is sufficient for delivery)

  • (cs) in reply to jaq
    jaq:
    Severity One:
    Vechni:
    THE COMPANY IS PEPSI. GET IT? PEPSI CHALLENGE! I FINALLY FIGURED OUT ONE OF THESE. FUCK YES.
    Fantastic!

    Now, for the next challenge, name the key between 'tab' and the left 'shift' key.

    'Control'

    Freak.

    (And I mean that in the nicest possible way.)

  • Stiggy (unregistered)

    SQL> select comment_text from tdwtf.comments 2 input column comment_text Oh.My.God;

    input column comment_text Oh.My.God; * ERROR at line 2: ORA-00933: SQL command not properly ended * SQL> update tdwtf.comments.new 2 set comment_text = Oh.My.God; update tdwtf.comments.new * ERROR at line 1: ORA-00971: missing SET keyword * SQL> drop schema tdwtf drop schema tdwtf * ERROR at line 1: ORA-00933: SQL command not properly ended * SQL> drop schema tdwtf; Schema dropped.

    SQL> select comment_text from tdwtf.comments 2 select comment_text from tdwtf.comments * Connection lost.

    (I'm certain I've hosed something up in the quest for this gag. But I'm equally sure I'll be corrected...)

  • (cs)

    This was 9 years ago before "highly paid consultents" had Google to find the answers to guestions the should already know. http://www.google.com/search?q=sql+oracle+UPDATE+syntax&sourceid=navclient-ff&ie=UTF-8&rlz=1B3GGGL_enUS208US208

  • IHazYourCheezburger (unregistered)
    Rhialto:
    anon:
    "[email protected]" might be a perfectly valid email address (if there's an MX record for www.anyco.com).

    One of my pet peeves is when some lackwit implements a "validation" rule that rejects legitimate entries. Unless you know for sure that something is invalid (and can point to an RFC that supports your position) then you should accept what the user has supplied. Let the user fix it if it's wrong.

    Indeed. I get sick and tired of all the so-called email-address-validations on websites that claim that "[email protected]" is not valid. It is perfectly valid, and not even one of the most obscure cases at that.

    I hear you. I had to change my postfix settings so that I could use "[email protected]". And even the - isn't allowed in some cases. But then I just flame the frakking stupid mail admin until it is fixed. Or I just don't use that service.

  • nydjhgcikjhvktyd (unregistered) in reply to Severity One
    Severity One:
    Vechni:
    THE COMPANY IS PEPSI. GET IT? PEPSI CHALLENGE! I FINALLY FIGURED OUT ONE OF THESE. FUCK YES.
    Fantastic!

    Now, for the next challenge, name the key between 'tab' and the left 'shift' key.

    THANK YOU! WITH THIS KEY ITS EASIER TO PRINT BIG LETTERS!

  • (cs) in reply to Joe
    Joe:
    The Bruces:
    Rule 1: No SQL Gurus.

    Rule 2: There is NO using human-entered information as a database key. .............

    Main Article:
    The most important column in the table was the user’s email address, as that was used as a login and served as the only method for contacted users of the system. As such, the column had a UNIQUE constraint defined and a rather sophisticated CHECK constraint to make sure the value at least resembled an email address.

    I don't mean to pick on The Bruces, but can anyone point out where exactly in the main article it says anything about the Email actually being the key on the field. The email is the username.

    Good point - I saw a "one of the key tables" and "unique constraint" and erroneously turned it into "key field". Also thought I saw something about the email address showing up in other tables, but upon a re-read I can't find that.

    Even so, email address was what I'll call a virtual key, since it was used as the login. Unlike real keys, logins should definitely be human-readable. But using email address for that is bad for several reasons (as many other people have pointed out). I also believe that requiring email address to be unique (even if it's not the login) is silly - I might have good reasons for wanting multiple accounts on the same email address.

  • (cs) in reply to chrismcb
    chrismcb:
    hey persto!:
    Tightly coupling a user account name with their email address is stupid to say the least.

    email addresses are not 1:1 with people, they can change and they can expire.

    Why is tightly coupling an email account with a user account a bad thing?

    About 10 years ago, I worked for a company doing enterprise knowledge management software. They used the email address as the username. I argued long and hard against it. I lost the argument and walked off muttering how it'd come back and bite us later.

    Shortly after deployment at one of the beta customers (a large one), the customer decided to change their email address format from something like [email protected] to [email protected]. We had to write something that would go and update all of the email addresses in the database, using an old->new mapping list. For some reason, the email address showed up elsewhere in the system (which seems like a WTF but made sense given the design of the system), so it was a fairly complex and time-consuming operation.

    If the username and email address were separate, users could have changed their own email addresses.

    I argued long and hard that we should take that opportunity to decouple the two before going to full release. I lost the argument and walked off muttering how it'd come back and bite us later.

    A few months later, when most of the dev staff was at Java One, a second customer had the same thing happen to them, and one of the developers had to spend his conference time dialled into the system to modify the email addresses instead of participating in the conference.

    That's why tightly coupling email and username is a bad thing.

  • (cs) in reply to Joe
    Joe:
    I don't mean to pick on The Bruces, but can anyone point out where exactly in the main article it says anything about the Email actually being the key on the field. The email is the username. Unless they did something else stupid that is not mentioned in the article, it is NOT THE KEY. The Unique constraint is so that there are not duplicate usernames. This is the same as you would do for ANY username field.

    I think that's something for BOFH to, ekhm, suggest to some noob developer: actually allow non-unique user ids. When you create a new user, there's a new record added with its own OID used as a key (say). Then, when you log in, the user selected is the one whose password matches.

    Cheers!

  • stoopid serf luser (unregistered)

    Mr. Papadamopolipolus, Please change the page TITLEs to say: The Daily WTF - <article name> so it will be in a consistent place in my bookmarks.

    Sorry for posting here instead of going through the proper channels, but whatever, right, bro?

  • Thomas (unregistered) in reply to ML
    ML:
    Jason:
    Gotta love those expert consultants. At a company I worked for previously we had one do work that specifically did satisfy the requirements given to him, but did not do what was needed. And he knew it too.
    Sorry, going to side with the consultant on this one. I've been in that situation myself. You get requirements that say "Do X". You tell the customer, "You don't want to do X, you really want to do Y". The customer is steadfast, "You were hired to do X, so do X or we'll sue you for breach of contract." ... and so you do X. Unfortunately, what they needed isn't on paper, what was required is on paper. When it comes to a courtroom, what's on paper (the requirements) is what trumps, so the consultant is going to do what's on paper each and every time.

    If there's a discrepancy between what is needed and what you gave to the consultant as a requirement, is it REALLY the consultant's fault that he took what you wrote down over some vague notion of what you need?

    Sorry, that does not work in the real world. As a consultant, you are ALSO obligated to inform the user of the consequences to your actions and there is absolutely no question that removing a unique constraint could cause dire ramifications. It is unacceptable to throw your hands up and say, "Well, the client told me to shoot them in the foot. Even though I told them them it was a bad idea, I was obligated to do it."

  • subject ...no, wait (unregistered) in reply to YourNameHere
    YourNameHere:
    This was 9 years ago before "highly paid consultents" had Google to find the answers to guestions the should already know. http://www.google.com/search?q=sql+oracle+UPDATE+syntax&sourceid=navclient-ff&ie=UTF-8&rlz=1B3GGGL_enUS208US208
    WTF! They hadn't heard of Altavista?
  • (cs) in reply to RobFreundlich
    RobFreundlich:
    Oh, and here's an idea before hiring a guru: interview them as if you were hiring them for a permanent position. Include some coding questions. If they fail, boot 'em and take away their credentials.

    This only works if you have sufficient competency to hire the guru for a permanent position.

    It sounds like the technical team may have (barely) had sufficient technical expertise to do this, but management did not, and management was not aware of the technical team's expertise level - management only knew that it was not within the technical team's comfort area.

    Now, that having been said, whenever I'm involved with a guru in a specialty in which the local talent does not have sufficient expertise to properly interview the guru first, I make it a point to say, "we should watch everything the guru does, so we can learn from him." I don't say, but I think, "especially the bit about whether he is competent or not."

    Not, of course, that the bit about whether the guy is competent will necessarily help one get rid of him if he isn't. Apparently, in many organizations, saying that someone is not competent is a personal attack. It doesn't matter how much evidence you have to back up your claim.

  • Vertigo (unregistered) in reply to Simon
    Simon:
    Ahhh Consultants. They know a thousand different ways to make love to a woman, but have never actually had sex.
    I definitely should consider becoming a consultant.
  • Dan T. (unregistered)

    In my earlier message blasting people for putting an incorrect "www." in their e-mail address, I didn't mean to state or imply that there couldn't be perfectly valid addresses that do indeed include this string; my own web applications never alter such things for the customer because that could mess up a genuine address. I, too, hate bogus "address validators", and have been stung by them myself for my use of ".name" and ".info" addresses (some badly done validators assume that top level domains are three characters or fewer). However, when a customer gives their address as "[email protected]", I have to surmise that they're putting in a bogus "www." (does AOL even let you get usernames that include a dot?), though I don't take it out automatically.

  • LBD (unregistered)

    The real WTF is that they didn't have a backup they could use from imediatly before the outsider came in

  • DBGuy (unregistered)

    It will never ceases to amaze me how many DB/SQL experts do not believe in referential integrity and constraints. They say it makes the DB slower. I would go for slow in place of wrong data.

  • Oninoshiko (unregistered) in reply to Severity One
    Severity One:
    Now, for the next challenge, name the key between 'tab' and the left 'shift' key.

    The Control key?

  • nape (unregistered) in reply to rob

    So so clever and smart Bob

Leave a comment on “The SQL Guru”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article