• Sole Purpose of VIsit (unregistered)

    I don't see that as a WTF at all. In fact, the solution has a sort of inevitable, ragged beauty to it.

    Of course, the fact that it's necessary is ... a bit of a WTF.

  • Greg (unregistered)

    ' We've all done something like this. All of us. '

    I'ld say: you haven't been working on production code until you have.

  • bvs23bkv33 (unregistered)

    prepare statements at compile time!

  • Cornify (unregistered) in reply to bvs23bkv33

    Prepare all the statements!

  • Gargravarr (unregistered)

    Hacks that are recognised as such and commented are considerably better than ones that assume 'this is the way to do it'. Good on the poster for 'fessing up, I wouldn't hold this against him if I found this in production. Of course, there can be no doubt this will be in place long after the prepared-statements bug is fixed...

  • Unhelpful (unregistered)

    First, I can totally see why messing around with the schema within a prepared statement would not work (but not that it crashes, only that it wouldn't work).

    I feel like I would approach this problem from the other direction.

    Rearranging tables "feels" like an "offline" process, as in, not one that you would initiate from within the client application - especially when this is a bulk import operation. Thus, without it running from the client, you are free to choose when and how this procedure is run - say, as a cron job on the database server itself, executing an idempotent script directly against the DB process. Also hideous? Yes. More hideous than this, which is SQLi-vulnerable and probably not transaction-safe? Probably.

  • MiserableOldGit (unregistered)

    If it's the only way to do it, it's the right solution. He's commented it up, so no issue. But I reckon I'd also rather have some sort of script or cron on serverside anyway for this kind of task, so it might be the real WTF is whatever painted him into the corner of needing the feature.

    Bigger WTF is the fact he seems able to bang do-whatever-you-want sql through the connection string, time for a visit from Bobby Drop-Tables.

  • Pierre Lebeaupin (unregistered)

    This pretty good, as far as hacky workarounds go of course. I would just make sure to add the proper escapes to the partition and table names when building the hacky SQL, though.

  • (nodebb)

    It is inevitable that an entire generic runUnpreparedStmt 'framework' is build around this 'feature'. With prepared statement like '?' parameter support. And then someone will start using it exclusively because preparing statements is considered unwanted overhead

  • Sole Purpose of VIsit (unregistered) in reply to MiserableOldGit

    If you're complaining about the very existence of ConnSettings, then, fine, I agree with you. The only reason it's there is because nobody could think of a clean way to set up an environment (at least one that covers all use cases).

    However, it's arguable that using this loophole to manipulate the schema is no different to using it for the purpose of altering the environment in any other way. And it's arguable that this usage (data mining/OLTP) is actually safer than the more general usage, which would indeed potentially be a Little Bobby Tables injection, potentially from an untrusted source, potentially created on the fly through whatever user shenanigans one care to think of.

    I can't even see why anybody would bother sanitising the SQL/DTD stuff in a schema update like this, because it's under your control in the first place.

  • MiserableOldGit (unregistered) in reply to Sole Purpose of VIsit

    Yeah, wasn't suggesting what he did in anyway decreases security (unless of course he is bunging unsanitised UI stuff in through that vector, which would be a different story). More that it highlights a vuln that's out of his control. We all know ConnSettings are a PITA, but I must confess I didn't realise with those alone you could hoof around with the metadata. That'll learn me to exercise much greater care about who/what can potentially fire a connection request at a DB server. I know it should be within our control, but from a security perspective we now have to assume the bad guys are, to some extent, already on the internal network.

  • JustHere (unregistered)

    "facilitate bulk data-loading..." "... use[ing] the ODBC driver for Python". Found TRWTF!

  • Ulysses (unregistered)

    It wouldn't be Python if you didn't have to snake your way around.

    That's unfair, but I couldn't resist.

  • DartRat (unregistered) in reply to Cornify

    Prepare all statements.. For great justice.

  • Ulysses (unregistered) in reply to DartRat

    You know what you doing.

  • Jan (unregistered)

    Goed verhaal,lekker kort. Loempia erbij?

  • Foobar (unregistered)

    I've been there. Recently I had to do a particular task that would be trivially easy after we upgraded to a newer version of a particular product, but the upgrade is scheduled for more than a year from now.

    I chose the slightly least hacky of three really awful options, and immediately wrote up a bug which I assigned myself to fix once we upgrade and there's an elegant solution.

  • Chris Werner (google) in reply to Greg

    There's not a project I've worked on... where I haven't looked back on it and went "WTF was I thinking?"... being in the weeds and on a time crunch makes all the difference.

  • Yazeran (unregistered) in reply to Chris Werner

    Yep.

    And the older the project, the more cringe-worthy the code seems to be.....

    Been there, done that...

    Yazeran

  • eliott (unregistered)

    that's how I write quick'n dirty code: first apologize heavily, explaining why I chose to write such ugly code, wiping off some tears and... code :-)

  • Free Bird (unregistered) in reply to Jan

    Koekje erbij? Lekker.

  • Blane D (unregistered) in reply to Gargravarr

    Thanks for the kudos! Full disclosure: I didn't actually write this function. Credit for that goes to my coworker Charlie. I did mention that it was a coworker in my submission. That said, it was my idea to try running the swap using the ConnSettings, so I'm allowed some credit. The colorful comments and variable/function names are all Charlie though :D

  • Blane D (unregistered) in reply to Unhelpful

    This actually was part of some library code used by Apache Airflow scripts that perform data warehousing jobs, not a client application. Luckily, we decided to bite the bullet and use JDBC instead, and just made as temporary fork of JayDeBeAPI to work around the prepared statement problem.

  • Blane D (unregistered)

    HEH, we had an entirely different problem with that one. Try using that with a multi-statement query. If one of the statements in the middle fails, it eats the error message. Suspiciously, the ruby vertica driver has the same problem...

  • Friendly_Reminder (unregistered) in reply to eliott

    Same. I too do it the same way, saying something like "yes, I know it's ugly and there should be a better way, but it has to be working NOW and this is what I could come up with in the short amount of time I had."

  • (nodebb)

    I found this article very confusing because while the database that I'm most used to has an API to compile and execute a statement in one go, it's only there for convenience as it's directly equivalent to a separate compile and subsequent execute, so I don't know how you would execute a statement without compiling it in some way.

  • Ian (unregistered)

    I was looking into using SWAP_PARTITIONS_BETWEEN_TABLES() in our project and we reached out to Vertica about this, who gave us this update:

    This bug was already fixed on 8.1.1-2, JIRA VER-55590 "Vertica crash on swap_partitions_between_tables when running in prepared statement." So if you are using the fixed version or later it is safe to proceed.

Leave a comment on “Swap the Workaround”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article