StreamAcademy Profiles - Link-in-Bio for Twitch Streamers
Around 2019 I was running StreamAcademy, a small German-language thing aimed at Twitch streamers. Calling it a company would be generous. It was a pile of subdomains: a WordPress site, a WooCommerce merch shop, a URL shortener that was sixty lines of PHP over a SQLite file, a giveaway API that stored entries as JSON on disk, and a bridge that let people prove they owned a Twitch account before getting ranks on our TeamSpeak server. The piece that actually had a community attached was a Discourse board, somewhere around sixty regulars.
profil.streamacademy.tv is the one this post is about. A streamer signed in with Twitch and got
profil.streamacademy.tv/theirname: avatar, bio, a row of social buttons, one big call-to-action
link, a colour theme. Linktree for people whose main link is a Twitch channel.
It ran from January 2019 to somewhere in early 2020 and picked up around a hundred and ten registrations in that time. I went back through the code recently off a backup drive, which is where most of what follows comes from, including a few things that were broken the entire time without me noticing.
None of it survives online, and there are no archive snapshots, so every screenshot below is a re-render: the original templates and CSS running in a container against a reconstructed database, with invented demo profiles. The pixels are the 2019 code’s, the people are not real.
The URL namespace is the users table
The whole site is one Apache rewrite and a six-line index.php.
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-l
RewriteRule ^(.+)$ index.php?page=$1 [QSA,L]<?php
include 'core/init.php';
include TEMPLATE_ROUTE . $route . 'overall_header.php';
include PAGES_ROUTE . $route . $page . '.php';
include TEMPLATE_ROUTE . $route . 'overall_footer.php';
include 'core/deinit.php';Everything interesting happens in core/init.php, and the routing table is a directory listing:
$pages = glob(PAGES_ROUTE . $route . '*.php');
$pages = preg_replace('(' . PAGES_ROUTE . $route . '|.php)', '', $pages);glob() the pages/ folder, strip the prefix and the extension, and whatever is left is the set of
valid routes. Drop a file in and you’ve published a URL. Genuinely pleasant, right up until you want
a URL that isn’t a legal PHP filename, at which point a hand-maintained alias map turns up to paper
over it:
$custom_pages = [
'account-settings' => 'account_settings',
'profile-settings' => 'profile_settings',
'lost-password' => 'lost_password',
// ...
];The part I still like is the fallback. If the path isn’t a page, it might be a person:
if(!in_array($page, $pages)) {
if(Database::exists('username', 'users', ['username' => $page])) {
$profile_username = Database::clean_string($page);
$page = 'profile';
$route = 'profile/';
}
else {
$route = '';
$page = 'not_found';
}
}flowchart TD
A["GET /something"] --> B["index.php?page=something"]
B --> C{"in glob(pages/*.php)?"}
C -->|yes| D["render that page"]
C -->|no| E{"in the alias map?"}
E -->|yes| D
E -->|no| F{"a row in users?"}
F -->|yes| G["render profile"]
F -->|no| H["404"]
Routes and usernames therefore share one flat namespace, and routes win. Nobody could register
login, register, dashboard or store, because those files existed and in_array matched
before the database was consulted. Right precedence, though not on purpose. It just falls out of the
order somebody happened to write the two checks in. Add a pages/marketing.php in 2020 and you
silently 404 whoever registered as marketing in 2019. Never came up, though on a user table of any
real size it would only have been a matter of time.
There’s also a split in here I haven’t run into anywhere else since: two parallel trees, processing/
and pages/. A list in init.php names the pages that get a controller pass before any output:
$pre_processing_pages = ['extra_settings', 'out', 'account_settings', 'dashboard', 'page',
'profile', 'pages_management', 'index', 'paypal', 'store', 'stripe', 'user_edit'];processing/x.php runs first and does its redirects while the output buffer is still empty.
pages/x.php is the template. MVC with the wiring left showing as two folders.
My favourite thing in the whole codebase lives here. pages/out.php is the view half of the
outbound click redirect. It has to exist, because the route table is a directory listing and out
stops being a route the moment the file does. The work is all in processing/out.php, which sets a
Location: header without dying, so this gets flushed out as the body of a 302 that no browser will
ever show you. The whole file is three bytes:
^_^Twitch login was the entire onboarding flow
Asking a streamer to fill in a profile form is asking them to retype things that are already sitting on a public API. So the only signup path I actually wanted was OAuth. The Twitch client is about 530 lines against the old Kraken v5 API, adapted from Xxplosions/twitchtv-oauth. The interesting bit is first login:
if($login_type == "twitch") {
$ttv_code = $_GET['code'];
$access_token = $twitchtv->get_access_token($ttv_code);
$userDetails = $twitchtv->authenticated_userDetails($access_token);
$username = $userDetails['name'];
if ($account = Database::get(['user_id'], 'users', ['twitch_id' => $userDetails['id']])) {
$_SESSION['user_id'] = $account->user_id;
redirect('dashboard');
} else {
// ...
$buttons = '{"facebook":"", ... ,"twitch":"'.$username.'", ... }';
$stmt = $database->prepare("INSERT INTO `users` (`username`, `password`, `email`, `active`,
`date`, `avatar`, `twitch_id`, `description`, `buttons`)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");There’s no registration step at all. The account gets created inside the login handler: display name becomes the username, the Twitch bio becomes the profile description, the channel avatar is downloaded and stored locally, and the Twitch button is pre-filled with the channel name. Collisions get a suffix:
while(Database::exists('username', 'users', ['username' => $username])) {
$username = generate_slug($username, '_') . rand(100,999);
}So a profile is never empty. One button, one OAuth screen, and you land on a page that already looks like you. Nearly everybody came in that way rather than through the email form, and I’d still do it like this for anything whose audience already has an account somewhere else.
There’s a defect in it I only spotted by looking at file sizes on the backup. The normal upload path makes a thumbnail:
resize($avatar_file_temp, ROOT . AVATARS_THUMBS_ROUTE . $avatar_new_name, '165', '165');
move_uploaded_file($avatar_file_temp, ROOT . AVATARS_ROUTE . $avatar_new_name);The Twitch path doesn’t:
file_put_contents(AVATARS_ROUTE . $avatar_name, $twitch_image);
file_put_contents(AVATARS_THUMBS_ROUTE . $avatar_name, $twitch_image);Same bytes, written twice. So every OAuth avatar is byte-identical to its own thumbnail, and the
thumbnail is what the profile page renders, inside a circle capped at 8rem. Biggest one on disk is
a 907 KB PNG being drawn at 128 pixels. Nobody ever complained, which tells you roughly how much
traffic there was.
There were three of them, differing only in where the card put the avatar, and a set of gradient
themes to pick from. Hardware and Spiele are the same two database columns on every template, named
companies and knowledge in the schema and relabelled in the German translation, which is why a
streamer’s microphone ends up stored in a column called companies.
The analytics measured the wrong thing
The landing page sold four features. One of them was Aufruf-Analyse, view analytics. Every profile got a dashboard with a fifteen-day chart split three ways: profile views, main link clicks, clicks per social button.
Outbound clicks go through a redirect rather than a direct link, so they can be counted:
public static function get_profile_out($user_id, $type, $type_id) {
global $settings;
return $settings->url . 'out/' . $user_id . '/' . $type . '/' . $type_id . '/'
. Security::csrf_get_session_token('url_token');
}/out/<user_id>/<type>/<type_id>/<token> resolves the destination, records a hit, redirects. Fine.
The recording is where it falls apart. Both counters want to avoid logging the same visitor over and over, so both run a “have we seen this recently?” check first:
$hit = $database->query("SELECT `date` FROM `hits`
WHERE `type` = 'profile' AND `user_id` = {$profile_account->user_id}
ORDER BY `id` DESC")->fetch_object();
if(!$hit || ($hit && (new \DateTime())->modify('-'.$settings->profile_hit_timing.' hours')
> (new \DateTime($hit->date)))) {
$database->query("INSERT INTO `hits` (`type`, `user_id`, `date`, `ip`)
VALUES ('profile', {$profile_account->user_id}, '{$date}', '{$ip}')");
}The WHERE clause filters on the type and the profile owner. It never filters on the visitor.
ORDER BY id DESC grabs the most recent hit on that profile from anybody, and if that hit is
inside the dedupe window, the new visit gets thrown away.
The throttle is per profile. Not per person. A profile records at most one view per window in total, however many people showed up. Two visitors in an hour is one hit. Two hundred visitors in an hour is also one hit.
The ip column is sitting right there in the INSERT. Written on every row, read by nothing. One
more condition and it would have been correct:
WHERE `type` = 'profile' AND `user_id` = {$user_id} AND `ip` = '{$ip}'The annoying part is that this bug is invisible while you’re small and gets worse the better you do. Individual profiles got few enough visitors that two landing inside the same window was rare, so the charts looked fine and the numbers were roughly right. If any of those profiles had taken off, its chart would have flattened out at exactly the point it started mattering, and the product’s own analytics would have been the last place anyone found out.
I never caught it because I never had enough traffic to catch it with. You can’t dogfood your way to a metrics bug whose error grows with the number you’re measuring. It needed a test that wrote two hits from two addresses. There were no tests.
One JSON key, two entries
Social buttons are configured in a flat JSON file, keyed by button name:
{
"paypal": {
"icon": "fab fa-paypal",
"title": "PayPal.Me",
"url": "https://www.paypal.me/%s"
},
"paypal": {
"icon": "fab fa-steam",
"title": "Steam",
"url": "https://steamcommunity.com/id/%s"
}
}I added Steam in August 2019 by copying the block above it and forgetting to change the key.
Duplicate keys aren’t an error in JSON, they’re last-wins, so json_decode hands back a single
paypal entry that points at Steam. Adding Steam deleted PayPal.
Both halves fail quietly. The renderer walks each user’s saved buttons and drops anything it can’t resolve:
foreach ($profile_buttons as $key => $value) {
if(empty(trim($value)) || !isset($available_buttons->{$key})) {
unset($profile_buttons->{$key});
}There’s no steam key to match, so nobody who set a Steam handle ever saw the button. Meanwhile
anyone who’d already saved a PayPal.Me handle kept theirs, still stored, now rendered with a Steam
icon and pointing at steamcommunity.com/id/<their-paypal-handle>. Nothing logs, nothing errors. You
would only find it by looking.
The indentation gives it away, incidentally. The second block sits a level out from the first because it was pasted, not typed.
What the security looked like
Worth being straight about. This is 2019 PHP and it reads like it.
Authentication is fine. Login is a prepared statement, passwords go through password_hash and
password_verify on the default algorithm:
$stmt = $database->prepare("SELECT `user_id`, `password` FROM `users` WHERE `username` = ?");
$stmt->bind_param('s', $username);Almost nothing else is. The data layer concatenates SQL and leans on one function to save it:
public static function clean_string($data) {
return self::$database->escape_string(filter_var($data, FILTER_SANITIZE_STRING));
}escape_string does real work. FILTER_SANITIZE_STRING doesn’t. It strips HTML tags and encodes
quotes, which is an output problem being solved at the input, and PHP deprecated it in 8.1 largely
because it kept getting used exactly like this. Database::exists() skips the cleaning entirely,
and Database::exists() is the function the router hands the raw URL path to:
public static function exists($what = [], $from, $conditions = []) {
// ...
foreach($conditions as $key => $value) $where[] = '`' . $key . '` = \'' . $value . '\'';What saves it happens two layers upstream and has nothing to do with the query.
parse_url_parameters() runs FILTER_SANITIZE_URL, the router runs htmlspecialchars(..., ENT_QUOTES), and between them the quote a payload needs is already gone by the time it arrives.
CSRF ships twice. The class in use derives tokens from md5(time() + rand()). The other one, loaded
and as far as I can tell never called, uses md5(time() + time()), which is md5 of twice the current
Unix second and can be enumerated by anyone who owns a clock.
Persistent login is a bearer token in a cookie with no expiry and no rotation:
if(isset($_COOKIE['username']) && isset($_COOKIE['token_code'])
&& strlen($_COOKIE['token_code']) > 0
&& $account_user_id = Database::simple_get('user_id', 'users',
['username' => $_COOKIE['username'], 'token_code' => $_COOKIE['token_code']])) {And then sitemap.php opens its own database connection with the credentials pasted straight into
the file, a second copy of what’s already in the config, and does SELECT * FROM users to emit a
<loc> per username. robots.txt points crawlers at that sitemap on profil.streamacademy.de. The
site was served from profil.streamacademy.tv. So it advertised a sitemap on a domain that didn’t
exist.
None of this is unusual for the era. What I notice going back through it is where the problems cluster. The login flow, the part everybody knows to be careful about, is done properly. The router, the sitemap and the remember-me cookie are where everything went wrong, and none of those feel like security when you’re writing them.
Deleting the paywall
There was a full monetisation layer in here: a points balance, Stripe and PayPal top-ups, monthly and
yearly Pro packages, auto-renewal that debited points when a subscription lapsed, and a reminder
email fired off a pro_due_date_notified flag.
if($days_left < 0) {
if($account->points >= $settings->store_pro_price_month) {
$database->query("UPDATE `users` SET `pro_due_date` = DATE_ADD(NOW(), INTERVAL 30 DAY),
`pro_due_date_notified` = '0',
`points` = `points` - {$settings->store_pro_price_month}
WHERE `user_id` = {$user_id}");
} else {
Database::update('users', ['pro' => '0', 'pro_due_date_notified' => '0'], ['user_id' => $user_id]);
}
}Pro stripped the StreamAcademy header link, the ad slot under the card and the sticky “make your own profile page” footer, and added a little heart badge by the name. Free profiles carried three pieces of our branding, paid ones carried none.
By July 2019 I’d commented the purchase flow out of the pricing table:
<span class="pricing-card-price pricing-card-header-text-free" style="color:white">Gratis</span><a href="https://twitter.com/StreamAcademyDE" class="btn btn-primary border-0">Meld dich bei uns!</a>
Pro became free, handed out manually, by asking us on Twitter. The commented-out <select> with the
monthly and yearly prices is still sitting in the file, along with the JavaScript that rewrote the
purchase link when you changed it.
That edit is dated 24 July 2019, two months after launch, and it’s the most honest line in the codebase. Charging a monthly fee to remove a footer from a page nobody visits was never going to work. Better pricing wouldn’t have saved it. The thing being gated just wasn’t worth anything yet.
A hundred and ten people
The database is long gone, so I’m going off memory here, but it landed somewhere around a hundred and ten registrations across the fourteen months it was up. Most of that arrived in a lump when I announced it, and the rest trickled in slowly enough that I stopped checking.
The lump came off the forum. Sixty-odd regulars on the Discourse board, a post from me, and a good share of them made an account that week. As launch conversions go that’s fine. It’s also not a number about the product. It’s a number about already knowing sixty people, and you can only spend that once.
Everything after was the product on its own, and the product had no way of finding anybody. Every profile carried a sticky footer linking back to the register page, which is the right loop for something like this, and that loop only spins if the profiles get traffic. They didn’t, because they belonged to small streamers, which was the whole reason I built it in the first place.
I’d build the OAuth signup again tomorrow. One click and a page that already looks like you before you’ve typed anything. What I’d skip is the several weeks that went into a points economy and a subscription renewal cron, written before a single one of those hundred-odd people had demonstrated they wanted the free version. The audience wasn’t the missing piece. I had one, sitting on a forum, exactly the right people. I just built the paid tier before finding out whether they wanted the thing at all, and then spent a July afternoon commenting the paid tier out.
The site’s gone now. There aren’t even any Wayback snapshots of it.
The code is up
I hadn’t planned on publishing any of this. It’s been dead since 2020 and a link-in-bio nobody uses isn’t much of a portfolio piece. Then one of the people who had an account on it got in touch and asked if they could see the source, which is a better reason than anything I’d have come up with myself.
So it’s on GitHub at FelixGerberding/streamacademy-profiles. Credentials are stripped and configuration is parameterised. The avatars are gone, because those belong to the people who signed up rather than to me.
Everything in this post is still in there, unfixed. The dedupe query still ignores the visitor and
the JSON file still has two paypal keys. I left them alone because a repo that quietly corrects
itself is a worse record than one that doesn’t, and because this post would stop matching the code.
They’re written up in KNOWN-ISSUES.md next to the security problems, which are the reason the
README tells you to read it rather than run it.