Getting the database name, the WTF way



  • (Trigger warning: Perl ahead!)

    While pondering over our codebase, I have found this snippet which turns 10 this year. There are probably a few fancy ways to get the current database name from a MySQL connection, the most canonical of them being SELECT DATABASE();. But what if there's no time to reach for the manual, but the developer already (sorta) knows regular expressions? Here's what they did:

    # Returns the name of the database to which the handle is connected
    # It does it in a nasty way, but we don't know any better
    sub get_db_name {
        my %params = @_;
    
        if (not $params{dbh}){
            confess 'dbh is required!!!';
        }
    
        $params{dbh}->{PrintError} = 0;
        my $errmsg = '';
        eval {
            $params{dbh}->do( 'SELECT asterix FROM obelix' );
            if ( $params{dbh}->err() ) {
                $errmsg = $params{dbh}->errstr();
            }
        };
        if ( $@ ) {
            $errmsg = $@;
        }
        if ( $errmsg ) {
            #$params{dbh}->errstr() =~ m/^ERROR [\w\d\-]+ ([\-\w\d]+): Table '([\w\d\-]+).obelix'/i;
            $errmsg =~ m/^(DBD::mysql::db do failed: )?Table '([\w\d\-]+).obelix'/i;
            #$errmsg =~ m/^Table '([\w\d\-]+).obelix'/i;
            if( $2 ) {
                return $2;
            } else {
                return;
            }
        } else {
            die("The query executed successfully. I'm confused!");
        }
        $params{dbh}->{PrintError} = 1;
        return;
    }
    

    (The grammar of messages and comments, as well as identifiers and code style, have been anonymized somewhat.)

    Maybe my shop at that time favored generating lots of code over reading manuals and actually thinking, I can't really say.

    When I had found this code and showed it to my team, we momentarily considered creating a view named obelix with text column named asterix that would do a cartesian join on the largest tables in the production databases and concatenated everything into that field, with group_concat and some quite evil filters, including the infamous WHERE ... NOT IN (...) against the largest table, but dismissed the idea on the grounds that we still want to work here, for the time being, preferably without charges against us for maliciously DoSing the servers.


  • Winner of the 2016 Presidential Election



  • That lends an alarming physical interpretation to INSERT asterix INTO obelix;...



  • CONTSTRAINT VIOLATED

    Body is invalid


  • Winner of the 2016 Presidential Election

    @loopback0 said:

    FOREIGN KEY CONTSTRAINT VIOLATED

    FTFY



  • It's been quite a while since I used MySQL, but in that syntax isn't asterix a table name?

    An insert would normally be INSERT INTO obelix (asterix) VALUES (...) or INSERT INTO obelix (asterix) SELECT ...



  • Just go ahead and kill the joke with your technical jargon. It's not like it was that funny anyway.


  • Winner of the 2016 Presidential Election

    I'm still worried nobody even asked what happens to snupix in that whole ordeal...


    Filed under: Do you know how hard it is for me to search for anything Asterix related? Fingers keep trying to type Asterisk -,-



  • @wft said:

    die("The query executed successfully. I'm confused!");

    That's definitely the highlight of the code. It's so fucking tempting to create the obelix table it's looking for...



  • Switching from confess over to die() seems a bit off to me as well, but I don't know enough about Perl to say for certain if it's a :wtf: or not...



  • confess() by default gives you a stack trace, while die() doesn't. If someone forgot to pass the required parameter, it's helpful to see where the caller is. On the other hand, if the obelix table actually exists, the stack trace is of no help.

    They had this much clue, you see. Still not enough to go read the fucking manual and not produce this shitton of code...



  • Read the last paragraph on the idea of creating an evil view ;-)



  • @Onyx said:

    snupix

    Why on $Earth do they rename it in dubbed versions? (Here they use the original name Idéfix).


  • Winner of the 2016 Presidential Election

    @Bulb said:

    Why on $Earth do they rename it in dubbed versions? (Here they use the original name Idéfix).

    I'm not sure. I couldn't find the original name TBH (nor remember what they called him in the movies since that's the only Asterix thing I watched in French) so I used this one.

    They even changed some of the translations recently when they reprinted the comics here. Though, to their credit, I think they actually made the names closer to original. Still confusing for people who read the old editions though.



  • @dstopia said:

    That's definitely the highlight of the code. It's so fucking tempting to create the obelix table it's looking for...

    Yes, obviously, that is the WTF here.
    Had he, on success, continued to try the same with different table names (obelix1, obelix2, etc...), this would've been great!



  • @Evo said:

    Had he, on success, continued to try the same with different table names (obelix1, obelix2, etc...), this would've been great!

    Does SQL have a generate random table name function?


  • Discourse touched me in a no-no place

    @tar said:

    Does SQL have a generate random table name function?

    No. But you might be able to find out whether the name you want to use is present in the DB by reading the DB schema model. (Alas, that's one of the parts where standardization has penetrated least…)



  • Hmm...

        my $obelix = POSIX::tmpnam();
        $params{dbh}->do( "CREATE TABLE $obelix" );
    

    :triumph: :trident:



  • But you should also do a query on information_schema.tables to make sure you don't, by accident, have a table there by this exact name...



  • But, but, it's POSIX! I should be guaranteed a new name which isn't in use! :trolleybus:


  • Discourse touched me in a no-no place

    @wft said:

    But you should also do a query on information_schema.tables to make sure you don't, by accident, have a table there by this exact name...

    You should get an error if the table exists. “Fortunately” you'll actually get an error every time anyway…



  • @wft said:

    including the infamous WHERE ... NOT IN (...) against the largest table

    Why "infamous"? Misuse is infamous, but there's nothing wrong with WHERE ... NOT IN (...).


  • Discourse touched me in a no-no place

    @CoyneTheDup said:

    Why "infamous"? Misuse is infamous, but there's nothing wrong with WHERE ... NOT IN (...).

    The problem comes when the NOT IN (…) doesn't use an index.



  • @dkf said:

    The problem comes when the NOT IN (…) doesn't use an index.

    Oh, yes, but that's not the fault of NOT IN. That requires replacement of the developer behind the keyboard.


  • Winner of the 2016 Presidential Election

    WHERE developer NOT IN(dumbasses) ?


  • Discourse touched me in a no-no place

    @wft said:

    But you should also do a query on information_schema.tables to make sure you don't, by accident, have a table there by this exact name...

    DROP IF EXISTS ...



  • @Onyx said:

    WHERE developer NOT IN(SELECT pseudodeveloper FROM dumbasses) ?

    LOL. But...syntax. FTFY.



  • Isn't there a more incorrect sql command that they could have sent? SELECT error or just ASDF ghjkl?


  • Discourse touched me in a no-no place

    @Buddy said:

    Isn't there a more incorrect sql command that they could have sent? SELECT error or just ASDF ghjkl?

    To get the error message involving the database name? Probably not. They might've been able to choose a more sensible table name (e.g., one of the DB's standard metadata tables) but no. The real problem is that they're parsing an error message in the first place, which is always a Thoroughly Bad Idea.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.