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
withtext
column namedasterix
that would do a cartesian join on the largest tables in the production databases and concatenated everything into that field, withgroup_concat
and some quite evil filters, including the infamousWHERE ... 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.
-
-
That lends an alarming physical interpretation to
INSERT asterix INTO obelix;
...
-
CONTSTRAINT VIOLATED
Body is invalid
-
-
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 (...)
orINSERT INTO obelix (asterix) SELECT ...
-
Just go ahead and kill the joke with your technical jargon. It's not like it was that funny anyway.
-
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 -,-
-
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 todie()
seems a bit off to me as well, but I don't know enough about Perl to say for certain if it's a or not...
-
confess()
by default gives you a stack trace, whiledie()
doesn't. If someone forgot to pass the required parameter, it's helpful to see where the caller is. On the other hand, if theobelix
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 ;-)
-
snupix
Why on $Earth do they rename it in dubbed versions? (Here they use the original name Idéfix).
-
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.
-
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!
-
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?
-
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" );
-
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!
-
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…
-
including the infamous WHERE ... NOT IN (...) against the largest table
Why "infamous"? Misuse is infamous, but there's nothing wrong with
WHERE ... NOT IN (...)
.
-
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.
-
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.
-
WHERE developer NOT IN(dumbasses)
?
-
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 ...
-
-
Isn't there a more incorrect sql command that they could have sent?
SELECT error
or justASDF ghjkl
?
-
Isn't there a more incorrect sql command that they could have sent?
SELECT error
or justASDF 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.