{"id":5558,"date":"2024-01-26T18:51:04","date_gmt":"2024-01-26T18:51:04","guid":{"rendered":"https:\/\/balldiamondball.com\/blog\/?p=5558"},"modified":"2024-01-26T19:45:00","modified_gmt":"2024-01-26T19:45:00","slug":"some-horrific-sql","status":"publish","type":"post","link":"https:\/\/balldiamondball.com\/blog\/some-horrific-sql\/","title":{"rendered":"Some Horrific SQL"},"content":{"rendered":"

Yeah yeah, don’t carp to me about SQL.\u00a0 I’ll have you know that the language is mathematically derived, and is the correct way<\/em> to deal with data.\u00a0 God Bless all those elementary school teachers who tried to impress upon us the importance of the intersection of the set of all apples and the set of all thing that Johnny possessed, as they were on to someting, but they didn’t understand it any more than we did, and it showed.\u00a0 They had the teachers’ edition and we did not, and that was that.<\/p>\n

That nonsense of apples and Johnnies is a problem which still plagues database instruction to this day.\u00a0 Do you know the name Alfreds Futterkiste?\u00a0 I’ll stab that bastard if I ever see him.\u00a0 I recently saw an example describing how one might wish to filter a sharepoint list combining criteria with some AND and OR operators.\u00a0 Well and good, but the filters they combined were “class = mammal” AND (“species = cat” OR “species = dog”).\u00a0 This is patently stupid — every dog and\/or cat is a mammal, so there is no need to filter on class here, like Sillitoe.\u00a0 And so it goes, where fruits and mammals and occasionally make\/model\/year are trotted out as stultifyingly inappropriate examples to teach us how to use SQL.<\/p>\n

So it’s no wonder that I churn out highly successful but appallingly bad<\/em> SQL such as the following, trying to identify remaining lumps in my quest to sift the junk users up outta here:<\/p>\n

SELECT \r\nSUBSTRING(user_email, LOCATE('@', user_email) + 1) \r\n\u00a0\u00a0 AS da_domain, \r\n\r\nREVERSE(SUBSTRING(user_email, LOCATE('@', user_email) + 1)) \r\n\u00a0\u00a0 AS da_reverse,\r\n\r\nREVERSE(SUBSTRING(REVERSE(SUBSTRING(user_email, LOCATE('@', user_email) + 1)), \r\n   LOCATE('.', REVERSE(SUBSTRING(user_email, LOCATE('@', user_email) + 1))) + 1)) \r\n\u00a0\u00a0 AS da_domainlocal,\r\n\r\nREVERSE(SUBSTRING(REVERSE(SUBSTRING(user_email, LOCATE('@', user_email) + 1)),1, \r\n   LOCATE('.', REVERSE(SUBSTRING(user_email, LOCATE('@', user_email) + 1))) - 1)) \r\n\u00a0\u00a0 AS da_domaintop,\r\n\r\ncount(*)\r\n\r\nFROM wp_users \r\nGROUP BY da_domainlocal\r\nORDER BY COUNT(*) DESC \r\nLIMIT 1000;<\/pre>\n
What we are doing here:<\/p>\n
TAKE the email address of a user:\u00a0 ev.il.sp.am.mer.84383@morons.stupidmail.com.<\/p>\n
SPLIT it at the ‘@’ symbol and take the latter portion, which is the domain, called here ‘da_domain’ just to avoid namespace collisions.\u00a0 I’m lazy, not stupid.\u00a0 This could give us ‘morons.stupidmail.com’.<\/p>\n
And so we REVERSE that, yielding ‘moc.liamdiputs.snorom’.\u00a0 Then SPLIT at the first dot, yielding (on the left of the dot) the top level domain (TLD), which we will call ‘da_domaintop’, and on the right of the dot, the local part of the domain, which we call ‘da_domainlocal’.\u00a0 Good thing we aren’t working with AD or domain controllers etc.\u00a0 BUT we also must REVERSE these, so that our TLD which was ‘moc’ becomes ‘com’, and our local domain which was ‘liamdiputs.snorom’ becomes ‘morons.stupidmail’.<\/p>\n
We don’t actually need the ‘da_reverse’ portion, but it is left in there for reference.\u00a0 My reference.\u00a0 So I can understand the rest.\u00a0 Why?\u00a0 Because I am too lazy to figure out CTEs or subqueries or whatever right now.\u00a0 So I have this poor machine rolling and unrolling, reversing and unreversing things all over the place.\u00a0 This is truly awful SQL.\u00a0 You see, I cannot simply define ‘da_reverse’ and then WORK WITH IT, because until the blasted thing runs, ‘da_reverse’ does not exist.\u00a0 So I have to go everywhere that I would have wanted<\/em> to operate on ‘da_reverse’ and PASTE IN the same operations which generated it.\u00a0 So it’s doing an astounding amount of unnecessary work, like James Joyce.\u00a0 Well better it than me!<\/p>\n
ANYWAY, this gives us some powerful tools.\u00a0 We can then group by TLD or local domain, and count the number of bogus users INFESTING MY BLOG who registered from each\/any of those domain parts.\u00a0 Here’s one of the possible outputs, grouping by ‘da_domainlocal’:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
da_domain<\/a><\/th>\nda_reverse<\/a><\/th>\nda_domainlocal<\/a><\/th>\nda_domaintop<\/a><\/th>\ncount(*) \"Descending\"<\/a><\/th>\n<\/td>\n<\/tr>\n<\/thead>\n
gmail.com<\/td>\nmoc.liamg<\/td>\ngmail<\/td>\ncom<\/td>\n2998<\/td>\n<\/tr>\n
yahoo.com<\/td>\nmoc.oohay<\/td>\nyahoo<\/td>\ncom<\/td>\n963<\/td>\n<\/tr>\n
hotmail.comom<\/td>\nmomoc.liamtoh<\/td>\nhotmail<\/td>\ncomom<\/td>\n423<\/td>\n<\/tr>\n
meta.ua<\/td>\nau.atem<\/td>\nmeta<\/td>\nua<\/td>\n375<\/td>\n<\/tr>\n
all.codyting.com<\/td>\nmoc.gnitydoc.lla<\/td>\nall.codyting<\/td>\ncom<\/td>\n202<\/td>\n<\/tr>\n
aol.com<\/td>\nmoc.loa<\/td>\naol<\/td>\ncom<\/td>\n196<\/td>\n<\/tr>\n
thefmail.com<\/td>\nmoc.liamfeht<\/td>\nthefmail<\/td>\ncom<\/td>\n135<\/td>\n<\/tr>\n
comcast.net<\/td>\nten.tsacmoc<\/td>\ncomcast<\/td>\nnet<\/td>\n73<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
As you can see, the bulk of the remaining users are here from gmail, but this is out of over eleven thousand remaining users, to that nearly 3K does not tell much of the story.\u00a0 Obviously, I can delete all users coming from the nonsense domain ‘hotmail.comom’.\u00a0 Jut as obviously, I should not delete the populations of yahoo, gmail, aol, and so forth.\u00a0 There could be real people in there.\u00a0 For those, my next bulk-reducing trick will be to count the number of periods in the local part of the email address, which will get a lot for very little effort.\u00a0 These guys:\u00a0 ev.il.sp.am.mer.84383@whatever.<\/p>\n
There’s an issue hiding in the data above, which is that I have overspecified (grabbed too many fields), and the results are not what they appear.\u00a0 Because the table above was ordered up with GROUP BY ‘da_domainlocal’ and so forth, the count only applies to THAT column, and any data listed alongside in other columns is only an example, selected essentially at random.\u00a0 That is, YES it is true that there are 423 hotmail.[something] accounts, but I only know that at least one of them is actually hotmail.comom.\u00a0 The rest are very likely hotmail.com.\u00a0 So it is important to know what this barbaric SQL is saying and what it is NOT saying.<\/p>\n
So you get domains like yandex, which is the same as the search engine problem for frigging pinterest.\u00a0 I do not ever<\/em> want pinterest to show up in my search results.\u00a0 Unfortunately, you cannot just block pinterest.com, as that miserable noise content comes from everywhere — pinterest.com, pinterest.co.us, pinterest.co.jp, pinterest.co.cn, pinterest.co.ca, pinterest.co.tk, and it just never ends.\u00a0 Pinterest links are the real cosmic noise background. Well, now DuckDuckGo now<\/em> supports wildcards in the filters for result domains, but they didn’t always.\u00a0 Which brings us back to yandex, and yahoo for that matter, and probably gmail now that I think about it.\u00a0 Haven’t looked that far yet.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
da_domain \"Descending\" 1<\/small><\/a><\/th>\ncount(*)<\/a><\/th>\n<\/td>\n<\/tr>\n<\/thead>\n
zuxuhanuvunu.pro<\/td>\n1<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n<\/tr>\n
yaroslav-samoylov.com<\/td>\n1<\/td>\n<\/tr>\n
yandex.ua<\/td>\n6<\/td>\n<\/tr>\n
yandex.kz<\/td>\n10<\/td>\n<\/tr>\n
yandex.by<\/td>\n5<\/td>\n<\/tr>\n
yaicae.fun<\/td>\n1<\/td>\n<\/tr>\n
yahoo.it<\/td>\n1<\/td>\n<\/tr>\n
yahoo.in<\/td>\n1<\/td>\n<\/tr>\n
yahoo.fr<\/td>\n1<\/td>\n<\/tr>\n
yahoo.es<\/td>\n3<\/td>\n<\/tr>\n
yahoo.de<\/td>\n1<\/td>\n<\/tr>\n
yahoo.com.sg<\/td>\n1<\/td>\n<\/tr>\n
yahoo.com<\/td>\n950<\/td>\n<\/tr>\n
yahoo.co.uk<\/td>\n9<\/td>\n<\/tr>\n
yahoo.co.jp<\/td>\n2<\/td>\n<\/tr>\n
yahoo.co.in<\/td>\n1<\/td>\n<\/tr>\n
yahoo.co<\/td>\n3<\/td>\n<\/tr>\n
yahoo.ca<\/td>\n3<\/td>\n<\/tr>\n
y.otramovida.net<\/td>\n6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
This is when it’s handy to use the domainlocal part, which reduces like this:<\/p>\n\n\n\n\n\n\n\n\n\n
yaroslav-samoylov<\/td>\n1<\/td>\n<\/tr>\n
yandex<\/td>\n21<\/td>\n<\/tr>\n
yaicae<\/td>\n1<\/td>\n<\/tr>\n
yahoo.com<\/td>\n1<\/td>\n<\/tr>\n
yahoo.co<\/td>\n12<\/td>\n<\/tr>\n
yahoo<\/td>\n963<\/td>\n<\/tr>\n
y.otramovida<\/td>\n6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
It’s not perfect, but it’s better, and it is informative.<\/p>\n
I would really need to get into CTEs, potentially recursive due to the recursive nature of domain names, in order to formalize numbers about domains and so forth.<\/p>\n
You may wonder what the point of all this is, as easy answers are not exactly popping out of the woodwork here.\u00a0 Well, I already scooped up a bunch of the easy answers, deleting about 7K bogus users just by looking at obvious nonsense domains right out of the WordPress user management screen and then finding, filtering, and deleting matches for those.\u00a0 Really, the next valuable thing I could do is probably look for users whose email addresses are festooned with too many dot characters.\u00a0 They do that to break up the bad word filters, and wind up creating a far handier tell.\u00a0 but that’s not going so tolve alot of problems.\u00a0 To be most effective that search will be conducted without reference to domains at all :-\/<\/p>\n
I’ve wanted to develop this capacity for some time — I still have nearly 90K comments on another blog that I have to get rid of.\u00a0 That will of course be done by finding bad users and deleting them, and their comment go with them.\u00a0 But this one is priority.<\/p>\n
As I get further into reminding myself how SQL works, eventually I will get brave enough to run DELETE queries.\u00a0 For now, it’s just SELECT to point me toward value-added manual deletion activities, and so it goes, like Kurt Vonnegut.\u00a0 Sigh.<\/p>\n
Well, in case you wanted some truly disgusting, abusive SQL (not even in a clever sense), I got you covered.<\/p>\n
\r\n    
\r\n    \r\n                        <\/i>\r\n                <\/a>\r\n        <\/span>\r\n<\/div>
\r\n    \r\n                        <\/i>\r\n                <\/a>\r\n    <\/span>\r\n<\/div><\/div>","protected":false},"excerpt":{"rendered":"
Yeah yeah, don’t carp to me about SQL.\u00a0 I’ll have you know that the language is mathematically derived, and is the correct way<\/em> to deal with data.\u00a0 God Bless all those elementary school teachers who tried to impress upon us the importance of the intersection of the set of all apples and the set of all thing that Johnny possessed, as they were on to someting, but they didn’t understand it any more than we did, and it showed.\u00a0 They had the teachers’ edition and we did not, and that was that.<\/p>\n
That nonsense of apples and Johnnies is a problem which still plagues database instruction to this day.\u00a0 Do you know the name Alfreds Futterkiste?\u00a0 I’ll stab that bastard if I ever see him.\u00a0 I recently saw …  Continue reading → <\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-5558","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/posts\/5558","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/comments?post=5558"}],"version-history":[{"count":4,"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/posts\/5558\/revisions"}],"predecessor-version":[{"id":5562,"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/posts\/5558\/revisions\/5562"}],"wp:attachment":[{"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/media?parent=5558"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/categories?post=5558"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/balldiamondball.com\/blog\/wp-json\/wp\/v2\/tags?post=5558"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}