• (cs)

    Meh! He should have earned peer respect by revealing this function.

  • Just a guest (unregistered)

    Just return 'false' would have done it I suppose.

    Maybe than whatever compiler would be used would optimize this function out.

    Also, those names look to be not allowed as column names or to-be-searched-for data either. Could cause heaps of troubles down the road ...

  • Peter (unregistered)

    "Wolfram and Hart"? "Joss"?

    I see what you did there.

  • Spudley (unregistered)

    I hope he also added a snarky comment above the function.

  • (cs) in reply to Peter
    Peter:
    "Wolfram and Hart"? "Joss"? "the One Ring was forged in Mount Doom"?

    I see what you did there.

    Fixed it for you.

  • Tractor (unregistered) in reply to Just a guest

    Not unless someone actually writes an invalid query. The real WTF is validating queries yourself. The database does that just fine on its own, and even tells you what was wrong with it (most of the time).

  • Ben (unregistered)

    EXECUTE IMMEDIATE foo USING OUT bar; -- Hey, validator, pretend this is a SELECT statement!

  • (cs)

    The simple solution would appear to be to name all of your SPs things like insert_foo or update_bar.

  • Mjk (unregistered)

    Jep ... not a lot of checking if you could fool it with a comment after semi-comma. Just return True and comment the rest. The job is done as good as before for injections and you don't have the restraint for your sql statements other than sql it self.

    for parsing sql for sql injection you have to work a little harder.

  • Anon (unregistered)
    throw new IllegalSciFiException("Unexpected One Ring");
  • Bernie The Bernie (unregistered)

    SELECT that developer AND EXECUTE him!

  • Geoff (unregistered) in reply to pjt33

    or maybe craft your statements like: "SELECT 1; EXEC SP_foo bar1, bar2, bar3;"

  • C10B (unregistered) in reply to pjt33
    pjt33:
    The simple solution would appear to be to name all of your SPs things like insert_foo or update_bar.
    Nice thought
  • eVil (unregistered)

    Did you just mix Buffy with Lord of the Rings? You, sir, disgust me.

  • Tipa (unregistered) in reply to eVil

    Angel, not Buffy......

  • eVil (unregistered) in reply to Tipa
    Tipa:
    Angel, not Buffy......

    If I recall correctly, the universe in which both shows reside is known as the Buffyverse. Presumably on the basis that that was the popular show that people liked, and Angel was the also-ran spin-off that people mostly ignored.

    Either way, mixing it with LOTR makes you a bad, bad person.

  • (cs) in reply to eVil
    eVil:
    Did you just mix Buffy with Lord of the Rings? You, sir, disgust me.

    This. We need to throw somebody in Mount Doom for this egregious offense to geekdom. Hell, it's an offense to God Himself.

    Somebody get me a pot and melt down some gold. We're giving this guy a golden crown to "reward" him for this "greatness".

  • Smug Unix User (unregistered)

    I would think it would be preferable to remove the function and references to the function call.

  • FTFY (unregistered)

    return int(rand(true/false))

  • Justin (unregistered)

    This is the type of situation where my team uses the George Bush Mission Acomplished guideline to 'finished.' GBMA is considered a viable(and in some ways preferable) alternative to Definition of Done when stumbling over this kind of ugliness.

  • Anonymous Paranoiac (unregistered)

    This reminds me of an email validation routine we used to have at work that verified the address had both an "@" and a ".". It allowed such things as "[email protected]", "[email protected]", "[email protected]@.baz", or just "[email protected]". It also failed to strip even non-printable characters. I re-wrote the routine myself. Unfortunately, the platform was truly ancient and did not support regular expressions.

  • (cs) in reply to eVil
    eVil:
    Tipa:
    Angel, not Buffy......

    If I recall correctly, the universe in which both shows reside is known as the Buffyverse. Presumably on the basis that that was the popular show that people liked, and Angel was the also-ran spin-off that people mostly ignored.

    Either way, mixing it with LOTR makes you a bad, bad person.

    The shame's on you. You do know that there were LOTR books before the movie, right? Just like a typical movie bandwagon-hopped. Peter Jackson directed the movies, but Joss Whedon wrote the original books.

  • ¯\(°_o)/¯ I DUNNO LOL (unregistered)

    It seems to be missing a line somewhere...

    SQL = FILE_NOT_FOUND

    Ah, much better.

  • Ironside (unregistered)

    INSERT INTO THE_VICE_PRESIDENTS_SICK_DAUGHTER

  • ZoomST (unregistered) in reply to Lorne Kates
    Lorne Kates:
    eVil:
    Tipa:
    Angel, not Buffy......

    If I recall correctly, the universe in which both shows reside is known as the Buffyverse. Presumably on the basis that that was the popular show that people liked, and Angel was the also-ran spin-off that people mostly ignored.

    Either way, mixing it with LOTR makes you a bad, bad person.

    The shame's on you. You do know that there were LOTR books before the movie, right? Just like a typical movie bandwagon-hopped. Peter Jackson directed the movies, but Joss Whedon wrote the original books.

    I'm lost. Where on Earth Lorne Kates said anything about the LOTR film? Because he was using "LOTR"? The book was called this way long time ago, such as when Balrog was typing in Usenet.

  • ZoomST (unregistered) in reply to ¯\(°_o)/¯ I DUNNO LOL
    ¯\(°_o)/¯ I DUNNO LOL:
    It seems to be missing a line somewhere...
    SQL = FILE_NOT_FOUND

    Ah, much better.

    +1

  • (cs) in reply to eVil
    eVil:
    Did you just mix Buffy with Lord of the Rings? You, sir, disgust me.
    It's called a mashup and mashups are the whole point of the Internet!
  • ZoomST (unregistered) in reply to Ironside
    Ironside:
    INSERT INTO THE_VICE_PRESIDENTS_SICK_DAUGHTER
    If she is sick as in sex crazy driven, sign me up.

    Captcha: nobis, as in "pubis pro nobis". Her pubis, I mean. For us. For the world.

  • ZoomST (unregistered) in reply to dgvid
    dgvid:
    eVil:
    Did you just mix Buffy with Lord of the Rings? You, sir, disgust me.
    It's called a mashup and mashups are the whole point of the Internet!
    Then the kitten video is missing somewhere.
  • Smouch (unregistered)

    Seeing as he recognized the total uselessness of the validator function, why did he add the execute keyword at all?

    Why not comment out the code and replace it with

    return true;

  • (cs) in reply to ZoomST
    ZoomST:
    I'm lost. Where on Earth Lorne Kates said anything about the LOTR film? Because he was using "LOTR"? The book was called this way long time ago, such as when Balrog was typing in Usenet.

    The Usenet was only mentioned in the Simarillion. I don't consider canon any of the postmortem work written by Brandon Sanderson.

    I'm not saying it's bad fiction, but it's disrespectful. Tolkien's will clearly stated that he wanted all his unpublished work cremated with his body, and scatted from the International Space Station-- a fitting end for the Starfleet Engineer he portrayed in TV and film.

  • (cs) in reply to dgvid
    dgvid:
    eVil:
    Did you just mix Buffy with Lord of the Rings? You, sir, disgust me.
    It's called a mashup and mashups are the whole point of the Internet!
    I thought it was looking at cat pictures and arguing with strangers about nothing.
  • Aris (unregistered)

    There is simply not excuse for not using prepared statements. No excuse. And this code demonstrates that they do not use it.

  • faoileag (unregistered) in reply to warmachine
    warmachine:
    Meh! He should have earned peer respect by revealing this function.
    That function is in Visual Basic!!! How can you expect to earn peer respect in a shop that uses Visual Basic? And uses that language to create a SQL validator that thinks "drop table my_table; select" is fine and valid SQL?? Sheesh, some people! ;-)
  • faoileag (unregistered) in reply to Ironside
    Ironside:
    INSERT INTO STORY THE_VICE_PRESIDENTS_SICK_DAUGHTER
    Here, FTFY
  • faoileag (unregistered) in reply to Just a guest
    Just a guest:
    Also, those names look to be not allowed as column names or to-be-searched-for data either. Could cause heaps of troubles down the road ...
    Why not? "ALTER TABLE select RENAME update;" should return TRUE if fed into ValidateSQLText().

    And "SELECT * FROM update WHERE stmnt LIKE '%insert%';" should also be fine.

  • QJo (unregistered) in reply to FTFY
    FTFY:
    return int(rand(true/false))

    No that's no good. That means you will be able to try it again with the same command and in due course you'll get the "true".

    Here's a way to really spoil a developer's day.

    What you want to do is set up a map whose key is the command, and whose value is True or False. The first time you call the SQL function, it check to see if it is in this map. If it is, then it returns True or False accordingly. If not, it randomly assigns either True or False and puts it in the map.

    The upshot is that every SQL command will consistently either pass this stage of validation or not. Which one it will be when you write your SQL command is undetermined.

  • QJo (unregistered) in reply to Lorne Kates
    Lorne Kates:
    ZoomST:
    I'm lost. Where on Earth Lorne Kates said anything about the LOTR film? Because he was using "LOTR"? The book was called this way long time ago, such as when Balrog was typing in Usenet.

    The Usenet was only mentioned in the Simarillion. I don't consider canon any of the postmortem work written by Brandon Sanderson.

    I'm not saying it's bad fiction, but it's disrespectful. Tolkien's will clearly stated that he wanted all his unpublished work cremated with his body, and scatted from the International Space Station-- a fitting end for the Starfleet Engineer he portrayed in TV and film.

    Disgraceful. You'll have Commander Vimes on your tail at this rate.

    What do you mean you ain't got no tail? You're a code monkey, aren't you?

  • Xarthaneon the Unclear (unregistered) in reply to Anon
    Anon:
    throw new IllegalSciFiException("Unexpected One Ring");

    SELECT one_ring FROM star_trek WHERE jedi_mind_meld = true AND DATE(blue_girls)

  • Yazeran (unregistered) in reply to QJo
    QJo:

    Here's a way to really spoil a developer's day.

    What you want to do is set up a map whose key is the command, and whose value is True or False. The first time you call the SQL function, it check to see if it is in this map. If it is, then it returns True or False accordingly. If not, it randomly assigns either True or False and puts it in the map.

    The upshot is that every SQL command will consistently either pass this stage of validation or not. Which one it will be when you write your SQL command is undetermined.

    Oh man, that is just so Evil... :-)

    You just need to bury it sufficiently deep that a casual debugging do not find it..

    Yazeran

    Plan: To go to Mars one day with a hammer

  • Whatever (unregistered)

    I just want to add that I loved the sketchy references to everything and what not. Keep it up:-) Made my day!

  • eVil (unregistered)

    You all just broke my brain.

    Now I have no idea what characters did who, where, or why in anything I've ever read, seen or heard.

    Or indeed, what order it happened.

    :o(

  • faoileag (unregistered) in reply to Whatever
    Whatever:
    I just want to add that I loved the sketchy references to everything and what not. Keep it up:-) Go ahead. Make my day!
    That would have been your chance to add one yourself! ;-)
  • faoileag (unregistered) in reply to eVil
    eVil:
    Now I have no idea what characters did who, where, or why in anything I've ever read, seen or heard.
    It's really simple.

    Once the mainframes ruled middle earth. But deep in the forests of mirkwood something was stirring. At first, it looked innocent and helpful. A pc, a "personal computer". Then a young wiz kid came along and forged one ring to bind them all and ruled for aeons, erm, decades. But the ring was lost. Nothing happened for a while. Then a young hobbit named Steve discovered it, picked it up and used it occassionally but to no great harm. Nevertheless it attracted the attention of the great wizzard Obi Wan, who understand only to well the power of that ring. On Obi Wan's request, Steve passed the ring on to the young Tim who set out on an epic journey to destroy it. After a while, Obi Wan and Tim were joined by the elf Han, the dwarf (erm, vertically challenged person) gimli and the doctor "Bones"...

    There, did that help?

  • (cs) in reply to Lorne Kates
    Lorne Kates:
    eVil:
    Tipa:
    Angel, not Buffy......

    If I recall correctly, the universe in which both shows reside is known as the Buffyverse. Presumably on the basis that that was the popular show that people liked, and Angel was the also-ran spin-off that people mostly ignored.

    Either way, mixing it with LOTR makes you a bad, bad person.

    The shame's on you. You do know that there were LOTR books before the movie, right? Just like a typical movie bandwagon-hopped. Peter Jackson directed the movies, but Joss Whedon wrote the original books.

    NO. You are wrong. Ralph Bakshi directed the movie.

  • Jay Enef (unregistered) in reply to QJo

    FTFY: return int(rand(true/false))

    I like: return mod(sum(lower(ascii values of statement)),2)?true:false

    This way the same statement will result in the same truthyness

  • warlaan (unregistered) in reply to Aris

    Prepared statements have a fixed number of placeholders, so if you want to INSERT the values cached over like 5 minutes, you would have to use some kind of workaround.

    Also there is a server limit for the number of prepared statements allowed, which can be surpassed depending on the number of statements per connection and the numbers of connections you need.

    But for 99% of the cases I agree with you.

    But wasn't this about stored procedures rather than prepared statements?

    In our company we don't use stored procedures either because that would lead to code being stored in the database rather than in svn, which opens up a lot more possibilities for version conflicts.

  • HowItWorks (unregistered) in reply to faoileag
    faoileag:
    eVil:
    Now I have no idea what characters did who, where, or why in anything I've ever read, seen or heard.
    It's really simple.

    Once the mainframes ruled middle earth. But deep in the forests of mirkwood something was stirring. At first, it looked innocent and helpful. A pc, a "personal computer". Then a young wiz kid came along and forged one Token ring to bind them all and ruled for aeons, erm, decades. But the ring was lost. Nothing happened for a while. Then a young hobbit named Steve discovered it, picked it up and used it occassionally but to no great harm. Nevertheless it attracted the attention of the great wizzard Obi Wan, who understand only to well the power of that ring. On Obi Wan's request, Steve passed the ring on to the young Tim who set out on an epic journey to destroy it. After a while, Obi Wan and Tim were joined by the elf Han, the dwarf (erm, vertically challenged person) gimli and the doctor "Bones"...

    There, did that help?

    Please be clear on the ring that was forged.

  • faoileag (unregistered) in reply to HowItWorks
    HowItWorks:
    faoileag:
    eVil:
    Now I have no idea what characters did who, where, or why in anything I've ever read, seen or heard.
    It's really simple.

    Once the mainframes ruled middle earth. But deep in the forests of mirkwood something was stirring. At first, it looked innocent and helpful. A pc, a "personal computer". Then a young wiz kid came along and forged one Token ring to bind them all and ruled for aeons, erm, decades. But the ring was lost. Nothing happened for a while. Then a young hobbit named Steve discovered it, picked it up and used it occassionally but to no great harm. Nevertheless it attracted the attention of the great wizzard Obi Wan, who understand only to well the power of that ring. On Obi Wan's request, Steve passed the ring on to the young Tim who set out on an epic journey to destroy it. After a while, Obi Wan and Tim were joined by the elf Han, the dwarf (erm, vertically challenged person) gimli and the doctor "Bones"...

    There, did that help?

    Please be clear on the ring that was forged.
    Mandatory token ring comic: http://www.dilbert.com/fast/1996-05-02/

  • faoileag (unregistered) in reply to warlaan
    warlaan:
    In our company we don't use stored procedures either because that would lead to code being stored in the database rather than in svn, which opens up a lot more possibilities for version conflicts.
    You don't store your db schema creation scripts in your rcs? Why not?

Leave a comment on “SELECTing Valid SQL”

Log In or post as a guest

Replying to comment #:

« Return to Article