r/badcode • u/Jolly_goodday • Oct 06 '21
sql So umm this is from Twitch source code leak . NSFW
365
u/kixass456 Oct 06 '21
That’s one way of blocking a user.
148
346
Oct 06 '21
Can you imagine being a programmer for twitch, creating a pull request for this code and it actually getting approved?
276
u/RichCorinthian Oct 06 '21
Except it wasn’t a single pull request, I bet. It was 200. It started as a simple emergency stopgap solution with three terrible names, and a dev saying “we’ll come up with a better solution soon.” The broken window principle in action.
72
7
u/Lost4468 Oct 07 '21
Oh that's terrible. I thought maybe this was auto generated, in which case it's not that bad. But they actually went and typed these names in? That's insane...
1
u/isinned Mar 23 '22
Probably zero more likely. I bet this file is from the early startup days. Would have been a hectic environment. No code reviews, everything is super urgent, you have to deal with everything, and you're sleep deprived. Super common for early stage startups experiencing quick growth. Stuff like this isn't bad given the circumstances, debt that made them go fast plus it's easy to clean up later if it ever becomes an issue.
1
u/TheTwelveYearOld Jan 06 '23
I know this is necroposting, but when I googled Broken Window Principle, it shows results for Broken Window Theory. Is that what you meant?
103
u/1SweetChuck Oct 06 '21
I can imagine someone in management, forcing some management level senior dev to fix the racist username problem as quickly as possible and the dev just doing something quick and pushing it through the PR themselves and merging it into main and deploying it. I’ve seen this happen at my company.
48
6
u/IAmTheSysGen Oct 06 '21
A for loop and regex would be much faster to implement.
The proper way is to use an O(log(n)) multiple search algo but like just a blocklist is enough
8
Oct 07 '21
I imagine this started with like 10 lines and they just kept adding to it. Now it got to a point where redoing it is just not worth it.
→ More replies (1)6
u/IAmTheSysGen Oct 07 '21 edited Oct 07 '21
Man, it's pretty easy to read from a file each line in a for loop.
You can also easily refactor this code. Copy the lines to a banlist.txt file.
Do a search and replace "OR ue.login LIKE " with ""
Do a simple
file = open("banlist.txt") for (line in file): if( re.match( ue.login, line )): rejectUsername()Or if you want to be as efficient as the OP
file = open("banlist.txt") if (re.match("|".join(file.readlines()), ue.login)): rejectUsername()It should take at most 15 minutes to refactor the code above into the proper way (tm).
→ More replies (2)→ More replies (2)2
Oct 07 '21
You could just put the banned names in a hashset and look them up in O(1)
→ More replies (2)9
u/lgylym Oct 07 '21
Then you will have a good performance review for committing enough lines of code. And I also wish I’m joking.
298
174
u/bugqualia Oct 06 '21
Where did you get this from?
422
u/DeepInTheAbyss- Oct 06 '21
Twitch saw Facebook and said "hold my hot tub". Apparently their whole source code and data got leaked
58
u/bugqualia Oct 06 '21
Oof
58
u/emax-gomax Oct 06 '21
Search for it on 4chan if you're interested. There's a magnet link going round for all of it. There's 125 GB so be prepared for a long download.
→ More replies (2)36
72
u/thanatica Oct 06 '21
It's not such a bad idea to opensource Twitch. Who knows maybe their code might get slightly more efficient if people with a hint of sanity in their skulls, can work on it.
Unfortunately, torrents is not the best place to host an opensource streaming platform.
34
5
u/curvedbymykind Oct 06 '21
Did FB source code get leaked too? Anyone got the link to either?
0
8
Oct 07 '21 edited Oct 11 '24
[removed] — view removed comment
7
u/pronuntiator Oct 07 '21
Any code can be leaked by a criminal employee. I don't think Facebook has NSA style working conditions where you can't use removable media or the Internet on your machine.
0
1
u/sorenkair Oct 26 '21
Could someone actually create a Twitch competitor using their source code? Or any possible interesting 3rd party apps?
1
44
98
u/XxClubPenguinGamerxX Oct 06 '21
is this seriously the best way to ban usernames? just a balcklist filled with names that are banned?
29
u/wampastompah Oct 06 '21
not names, but patterns. yeah. it's what basically any system uses. there can also be an automated layer that if a name gets enough reports, it'll be banned, and then sent to see what patterns in it should be added to the blacklist.
6
6
4
u/inre_dan Oct 07 '21
I'd keep a short file of the base words and have a function that extends it with alternate spellings at runtime. And I'd iterate through the words in some sort of data structure, not have identical switch cases for each one.
1
u/_kolpa_ Oct 07 '21
To extend that approach, I think you could also run an edit-distance algorithm on the base words to flag their closest alternatives.
3
2
u/Nick433333 Oct 07 '21
Blacklists are racist now.
11
u/Franks2000inchTV Oct 07 '21
The use of the term black to mean bad isnt racist in itself, but is part of a pattern that reinforces structural biases that have been shown time and again to lead to disparate financial, social and even medical outcomes for people of colour.
Techbros are all about believing science, unless it challenges their beliefs about racial justice.
→ More replies (1)112
Oct 06 '21
[removed] — view removed comment
62
u/XxClubPenguinGamerxX Oct 06 '21
It was posted here because it was done in sql and the query itself is bad but im guessing but even if you use a text file or change the query, having a database filled with blacklisted names cant be the best solution.
33
u/GeorgeDir Oct 06 '21
The First problem about this is that if you need to edit the list you need to change the query
If you want to keep a black list of words that's not a bad thing
This approach is also weak if you consider internationalization (you may want to ban words from other languages too)
13
5
u/TigreDeLosLlanos Oct 07 '21
you may want to ban words from other languages too
If it's somewhat like reddit, they don't care about other languages, not even slurs or hate speech.
31
u/Knuffya Oct 06 '21
what the fuck is this bullshit code
24
178
u/recycle4science Oct 06 '21
Oh MySQL.
27
u/Isvara Oct 06 '21
How do you know?
46
u/notPlancha Oct 06 '21
It's SQL for sure, idk if it's mysql
34
4
u/recycle4science Oct 07 '21
Tbh, I'm just assuming other DBMSs would have a better way to do this.
7
6
7
u/cybercuzco Oct 07 '21 edited Oct 07 '21
Well it is but they don’t know how to use it very well.
https://www.mysqltutorial.org/mysql-basics/mysql-in/
Select username from users where username in (select username from blacklist)
18
u/genikus Oct 07 '21
They have wildcards in there though. IN is not the same as LIKE. Similar problem: https://stackoverflow.com/questions/1127088/mysql-like-in
20
Oct 06 '21
They devs just didn't wanted to deal with all the confusing JOIN statements. It works after all..
1
30
47
u/KlaireOverwood Oct 06 '21
How else would you do it in SQL?
206
u/AeroSysMZ Oct 06 '21
Not do it in SQL but in the Business Layer above
51
Oct 06 '21
For real, how does this check even reach the database
61
u/AlexAegis Oct 06 '21
George runs it on every Tuesday if he's in office.
22
u/ebrius Oct 06 '21
Nah, it's setup to run as a cron job on his desktop, he just forgets to leave it on most nights
14
u/douglasg14b Oct 07 '21
in the Business Layer above
See, that's where you're wrong. No need for a business layer above if the database IS the business layer. Just throw it all in procs and you now have something only a very small handful of people are able to work on or debug.
internal screaming intensifies
→ More replies (1)7
u/TigreDeLosLlanos Oct 07 '21
No need for a business layer above if the database IS the business layer.
That's what it was originally intended when making SQL, but it's so shitty that everyone ended using it as a data storage manager exclusively.
→ More replies (1)8
u/michaelh115 Oct 06 '21
I assume this allows them to add new rules and retroactivly ban/clean up existing accounts
2
u/lestofante Oct 07 '21
but then you need to check only the new insertions rather than the whole list everytime. As someone pointed out, you can also use a table that contain all those pattern to check (but may need non-standard sql)
5
u/samsop Oct 07 '21
I have a colleague who does everything in SQL and hardly processes any of the data in any other layer. Everything is just shoved straight into a huge string variable that takes shape based on user input. It makes taking over any of his work extremely cumbersome
92
u/nagesagi Oct 06 '21 edited Oct 06 '21
Table of banned phrases and do a single query against the table. Adding an entry is easier than code change.
If the list is <10,000 entries, would instead cache the entries in memory and do comparisons in code. Table would be checked like once an hour for updates.
32
u/HK-Sparkee Oct 06 '21
And a lot of these would be easy to understand expressed in regex
19
u/Informal_Swordfish89 Oct 06 '21
Even then still... I'd probably choose to use regex.
It's incredibly flexible and allows easy upscaling.
→ More replies (1)14
u/FistThePooper6969 Oct 06 '21
It would depend on the scale imo as regex is MUCH slower than an efficient string operation.
→ More replies (2)20
u/frugalerthingsinlife Oct 06 '21
Forget runtime cost. Maintainability of regex would be tough as people come up with clever new names. And not everyone is a regex wizard, but anyone can add a bad word to a bad word list.
A bayesian filter, like they use in spam, would be more all-encompassing.
19
u/Maalus Oct 06 '21
"The plural of regex is regrets" is my motto, nothing good comes from having tons of regexes to check against.
→ More replies (4)14
u/FistThePooper6969 Oct 06 '21
Additionally, needing to update/add an entry would require a deployment which is unnecessary
4
2
u/Syscrush Oct 06 '21
And while we're at it, let's strip all symbols from the name and map numbers to the letters they can stand in for, THEN do the check with text against text.
→ More replies (1)33
u/alexppetrov Oct 06 '21
I am beginning to learn SQL now but wouldn't it be better to store the forbidden names in a DB and compare them that way rather than writing an individual row for every username?
15
0
8
u/RedditAcc-92975 Oct 06 '21
Create table "banned_username_patterns" ...
now in PostgreSQL there are a few ways. For example
select any (select ue.login ~ t.pattern from banned_username_patterns as t)
https://stackoverflow.com/a/13565021
Also, as others have mentioned this shouldn't be done on the DB. No need for using the database here. Unless someone was post-hock banning users.
→ More replies (1)1
u/MrMelon54 Oct 07 '21
you would do it before the data ever reaches the database
just check the value against a file listing all these combinations before saving it
1
1
1
1
Jan 18 '22
REGEXP (SELECT GROUP_CONCAT(bad_names_column SEPARATOR '|') FROM bad_names_table)
REGEXP lets you match multiple words and is formatted like "item1|item2|item3", and the subquery will concat all the cells in the names column with "|" between them.
20
u/Spynder shameless stealing content to r/YandereTechnique Oct 06 '21
What the hell is this r/YandereTechnique
6
19
u/trogan Oct 07 '21
My two cents. I know this looks bad… but it’s probably auto generated code from a list somewhere, rather than handcoded.
In addition doing this sort of “like” matching between tables or other more elegant means might have a performance hit over the hard coded script here.
5
u/christian-mann Oct 07 '21
I've normally seen this as a list of hashes, so that if source code or databases do get leaked, it's not necessarily feasible to reconstruct the banned words and discover gaps.
12
3
u/gipp Oct 06 '21
This sort of looks like a one off analysis script --sometimes easier to just take a list of names and do a bunch of regex replacements in vim than do anything proper if you're just going to do it once. But then why would that be committed to a repo? Any context for what this is used for?
3
2
2
Oct 07 '21
I wonder if this is the result of codegen commited to a repo (not as uncommon as you’d think) or part of a one off task that needed to be run
2
Oct 07 '21
I dont know much about programming, can someone eli5 explain what a better way of blacklisting would be and how this is bad? I assume maintaining a large txt file to look through for blacklisted words can be 1 way
1
2
u/Kazaan Oct 07 '21 edited Oct 07 '21
Actually I'm curious how it would be possible to make better than this considering it's a requirement making it from a SQL query.
AFAIK, it's not possible making something like
SELECT * FROM ueWHERE ue.login NOT LIKE IN ('%foo%', '%bar%')
Or even better
SELECT * FROM ueWHERE ue.login NOT LIKE IN (SELECT pattern FROM forbidden_words_patterns)
What is the best practice to check that a given value does not contain a value defined in a set of pattern ?
2
2
3
u/clxxiii Oct 06 '21
My guess as to the reasoning of this is that it's a list of usernames that were banned because of an inappropriate username. That means the username made it through a previous filter (if there is one) and then blocks the username from being used again once an account is banned
That's just my guess though
1
1
1
u/INTO_NIGHT Oct 06 '21
Well thats one way of checking for language guess they werent expecting a leak
1
1
1
1
1
1
1
1
1
1
1
1
1
1
u/jebhebmeb Oct 07 '21
I’m confused, couldn’t you just put this in a list 😂
1
u/fatso83 Oct 08 '21
I think the problem is that you cannot do this
SELECT * FROM ueWHERE ue.login NOT LIKE IN ('%foo%', '%bar%')
1
1
Oct 07 '21
Are Twitch devs getting paid by LoC or are they really that fucking ugly at programming lmao
1
1
u/GooberMcNutly Oct 07 '21
Obviously, this is Satan's own SQL WHERE LIKE clause, but what I can't understand why the are blacklisting it in the query? If there is a blacklist it should be in code when the account is being created. If this is their really bad way of blacklisting user logins, then that's an even more king sized oof. And I really have faith that humanity can come up with enough offensive user names that any reasonable SQL parser will give up. It's only 4k max query length for MySQL, there are probably at least that many offensive logins in this subreddit.
They should put the code in git and come out and say they will pay $100-1000 per PR accepted to fix any bugs.
1
1
u/mybotanyaccount Oct 07 '21
trying to get access to the source code for educational purposes can one help me out? Thanks
1
u/tombricks Oct 07 '21
idk how the system works, but would it not make sense to have a system that automatically accounts for 0s, spaces, 1s, etc, so you dont have to include multiple ones. you could just have one 'porn' instead of also having 'p0rn' and you could have... well im not going to say the slurs but you get my point
1
1
1
1
1
1
u/sgargel__ Oct 28 '21
Why not using a single regex with or operators for each capturing group instead?
1
1
1
1
1.4k
u/how_do_i_read Oct 06 '21
Why use
blacklist.txtwhen you can maintain a giant query instead.