Feature proposal: 8-char alias IDs for pastes + canonical pretty URLs (SQL + PHP + rewrite steps

[alKODIque]

Hi again ;)

I’d like to propose an “alias ID” feature that we implemented successfully on our instance. The goal is to keep the existing numeric auto-increment pastes.id as the internal PK, but add a public, random 8-character alphanumeric alias (e.g. tbe503k9) used in URLs — especially for /raw/.

This prevents trivial enumeration and keeps URLs clean and shareable.

Below is the exact sequence of changes (DB → routing → PHP), in the order we applied them.

---

1) Database schema change (add alias column + unique index)

We keep pastes.id unchanged (still the PK used by FK tables like paste_views, comments, etc.) and add a new column for the public alias:

ALTER TABLE pastes
  ADD COLUMN p_code CHAR(8) NOT NULL;

ALTER TABLE pastes
  ADD UNIQUE KEY uniq_p_code (p_code);

Notes:

• In our schema, the PK is pastes.id (not paste_id).
• Existing FK tables continue to reference numeric IDs as before (no migrations needed).

---

2) Auto-generate p_code on insert (DB trigger approach)

Because p_code is NOT NULL + UNIQUE, every new paste must have a value. We chose a MySQL trigger so the app code doesn’t need to be updated everywhere immediately.

Trigger (random base36, truly “random-looking”)

DROP TRIGGER IF EXISTS pastes_set_p_code;

DELIMITER $$

CREATE TRIGGER pastes_set_p_code
BEFORE INSERT ON pastes
FOR EACH ROW
BEGIN
  DECLARE v_code CHAR(8);
  DECLARE v_exists INT DEFAULT 1;
  DECLARE v_try INT DEFAULT 0;

  IF NEW.p_code IS NULL OR NEW.p_code = '' THEN
    WHILE v_exists = 1 AND v_try < 50 DO
      SET v_try = v_try + 1;

      -- 36^8 = 2,821,109,907,456
      SET v_code = LOWER(LPAD(CONV(FLOOR(RAND() * 2821109907456), 10, 36), 8, '0'));

      SELECT COUNT(*) INTO v_exists
      FROM pastes
      WHERE p_code = v_code;
    END WHILE;

    IF v_exists = 1 THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Could not generate a unique p_code';
    END IF;

    SET NEW.p_code = v_code;
  END IF;
END$$

DELIMITER ;

Why we changed the trigger:

• A prior approach using UUID/UUID_SHORT-derived values tended to produce repeated prefixes (e.g. many starting with the same 4 chars). The base36 * RAND approach yields much better distribution visually.

(Alternative option: generate in PHP using random_bytes() and retry on collision. Trigger was simpler for our deployment.)

---

3) Apache / .htaccess rewrites (pretty URLs)

In our case, the rewrite rules were already correct (as documented in the README). The only step needed was enabling tidy URL generation in the app config:

• In config.php, set:

  • $mod_rewrite = "1"; (it was "0")

With that enabled, the app starts outputting pretty URLs everywhere (instead of paste.php?...), while the existing .htaccess handles routing like:

Options +FollowSymLinks
RewriteEngine on

<IfModule mod_rewrite.c>
  RewriteCond %{REQUEST_FILENAME} !-f
  RewriteCond %{REQUEST_FILENAME} !-d
  RewriteRule ^page/([^/]+)/? pages.php?page=$1 [L]
  RewriteRule ^archive archive.php [L]
  RewriteRule ^profile profile.php [L]
  RewriteRule ^user/([^/]+)/?$ user.php?user=$1 [L]
  RewriteRule ^contact contact.php [L]
  RewriteRule ^download/(.*)$  paste.php?download&id=$1 [L]
  RewriteRule ^raw/(.*)$       paste.php?raw&id=$1 [L]
  RewriteRule ^embed/(.*)$     paste.php?embed&id=$1 [L]
</IfModule>

<IfModule mod_rewrite.c>
  RewriteCond %{REQUEST_FILENAME} !-f
  RewriteCond %{REQUEST_FILENAME} !-d
  RewriteRule ^(.*)$  paste.php?id=$1 [L]
</IfModule>

---

4) PHP logic: accept numeric OR alias, and canonicalize to alias

4.1 Raw endpoint canonicalization

Requirement:

• /raw/25 must work, but redirect to /raw/abcd1234 so the alias appears in the URL.
• /raw/abcd1234 must serve the paste directly.

Logic (in paste.php raw handler):

• If id is numeric: SELECT p_code, content FROM pastes WHERE id = ?

  • If found: redirect to /raw/<p_code> (302 during testing, 301 in production)

• Else if id matches ^[A-Za-z0-9]{8}$: SELECT content FROM pastes WHERE p_code = ?

  • Serve raw content

4.2 View endpoint (optional)

Same idea for the normal view page if desired:

• /<id> can redirect to /<p_code> (canonical alias),
• or keep numeric canonical and treat alias as alternate. We implemented alias as canonical.

---

5) Verification queries (quick sanity checks)

SHOW COLUMNS FROM pastes LIKE 'p_code';
SHOW INDEX FROM pastes WHERE Column_name = 'p_code';
SHOW TRIGGERS LIKE 'pastes';

-- ensure no bad values
SELECT COUNT(*) AS bad
FROM pastes
WHERE p_code IS NULL OR p_code = '' OR CHAR_LENGTH(p_code) <> 8;

---

That’s it!

Not sure if this is something you’d want to add, but I remember this feature being requested quite a lot in the 2.x era.

Thanks :)

[alKODIque]

Note about existing installs (sites that already have pastes)

We implemented this on a fresh instance (no existing pastes), so adding p_code as NOT NULL + UNIQUE was straightforward.

For existing installs with rows already in pastes, adding p_code NOT NULL UNIQUE directly can fail because older rows don’t have values.

Two possible migration approaches (we haven’t tested these, so consider them suggestions):

A) No backfill (old pastes remain numeric-only for now)

ALTER TABLE pastes ADD COLUMN p_code CHAR(8) NULL;
ALTER TABLE pastes ADD UNIQUE KEY uniq_p_code (p_code);

Then add a generator for new inserts (trigger or PHP), so only new pastes get aliases.
In routing/PHP canonicalization, redirect numeric /raw/<id> → /raw/<p_code> only when p_code is present; otherwise keep numeric.

B) Backfill (give aliases to all existing pastes)

ALTER TABLE pastes ADD COLUMN p_code CHAR(8) NULL;
/* populate p_code for existing rows (PHP random_bytes() + retry, or SQL loop) */
ALTER TABLE pastes MODIFY p_code CHAR(8) NOT NULL;
ALTER TABLE pastes ADD UNIQUE KEY uniq_p_code (p_code);

Keep the generator for future inserts.

Hope it can help :)

[boxlabss]

awesome, thank you.

I'll add an option for garbled urls in the next update.