Prevent Lost Web Leads by Persisting Unique Hex Identifiers to MySQL in PHP

When building a lead generation pipeline, routing website form submissions directly into a Telegram group or channel is standard practice for sales teams. However, dispatching notifications without persistent database storage or structured inline payload validation causes severe delivery and data-integrity failures.

Consider what happens when a web form posts directly to Telegram's API via cURL before writing to a local database: if Telegram's API experiences temporary 502 errors or rate-limiting (HTTP 429), the lead data is lost forever. Conversely, if developers send auto-incrementing database primary keys inside inline keyboard buttons (e.g., claim:12345), they expose global business metrics to anyone with access to the chat. Attempting to solve this with standard UUID v4 strings (36 characters) combined with namespace prefixes often approaches or exceeds the Telegram Bot API limit of 64 bytes for callback_data payloads.

This guide details how to construct a resilient PHP backend for web form processing. You will generate cryptographically secure 14-character hex keys using bin2hex(random_bytes(7)), enforce strict MySQL persistence *before* making outbound HTTP requests, format outbound Telegram alerts with safe HTML escaping, and handle incoming webhook callback queries atomically to prevent race conditions between competing managers.

Generating Cryptographically Safe Lead Identifiers and Enforcing Persistence

Using auto-incrementing integer IDs for outbound action buttons presents two core risks. First, sequential IDs reveal accurate real-time volume metrics. Second, relying on predictable identifiers allows bad actors to probe callback actions if bot security is compromised. Standard UUID strings solve predictability but take up 36 bytes. When you append action namespaces and status flags, you quickly breach Telegram's strict 64-byte callback_data ceiling.

To strike the right balance between collision resistance, entropy, and compact payload size, generate a 7-byte random byte sequence converted to a 14-character hex string using bin2hex(random_bytes(7)). This provides $2^{56}$ possible combinations—virtually immune to collision for web forms—while keeping payload strings like take:a1b2c3d4e5f6g7 around 19 bytes.

Before attempting any network communication with api.telegram.org, the submission must be committed to your relational database. If the database transaction fails due to schema constraint errors or connection timeouts, the script exits immediately with an HTTP 500 error response to the client. This guarantees that no Telegram alert is ever dispatched for an unpersisted lead.

Here is the database schema and the ingestion script handling validation and PDO persistence:

-- Schema definition for lead tracking
CREATE TABLE IF NOT EXISTS `leads` (
    `id` VARCHAR(14) NOT NULL PRIMARY KEY,
    `customer_name` VARCHAR(255) NOT NULL,
    `customer_phone` VARCHAR(64) NOT NULL,
    `status` ENUM('pending', 'claimed') NOT NULL DEFAULT 'pending',
    `claimed_by_id` BIGINT UNSIGNED DEFAULT NULL,
    `claimed_by_name` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

<?php
// submit_lead.php
declare(strict_types=1);

header('Content-Type: application/json');

$dbHost = getenv('DB_HOST') ?: '127.0.0.1';
$dbName = getenv('DB_NAME') ?: 'app_db';
$dbUser = getenv('DB_USER') ?: 'db_user';
$dbPass = getenv('DB_PASS') ?: 'secret';

try {
    $pdo = new PDO(
        "mysql:host={$dbHost};dbname={$dbName};charset=utf8mb4",
        $dbUser,
        $dbPass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database connection failure']);
    exit;
}

$rawInput = file_get_contents('php://input');
$data = json_decode($rawInput, true);

$name = trim($data['name'] ?? '');
$phone = trim($data['phone'] ?? '');

if ($name === '' || $phone === '') {
    http_response_code(400);
    echo json_encode(['error' => 'Name and phone are required fields']);
    exit;
}

// Generate a 14-character hex ID (7 bytes = 14 hex chars)
$leadId = bin2hex(random_bytes(7));

try {
    $stmt = $pdo->prepare(
        'INSERT INTO leads (id, customer_name, customer_phone, status, created_at) VALUES (:id, :name, :phone, "pending", NOW())'
    );
    $stmt->execute([
        ':id' => $leadId,
        ':name' => $name,
        ':phone' => $phone,
    ]);
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Failed to persist lead record']);
    exit;
}

// Lead is safely in the database. Proceed to notify Telegram.
$telegramSuccess = notifyTelegramManager($leadId, $name, $phone);

echo json_encode([
    'success' => true,
    'lead_id' => $leadId,
    'telegram_delivered' => $telegramSuccess
]);

Dispatching Telegram Alerts with Robust Error Checking and Escaping

Once the lead is committed to MySQL, the server dispatches an alert to the sales team's Telegram chat containing an inline keyboard button labeled "Take Lead". The button carries the callback payload take:<lead_id>.

Two major vulnerabilities occur at this stage in naive implementations:

1. Unescaped User Input: If a customer submits HTML syntax (such as <script> or <link>) inside their name field, Telegram's HTML parser will fail with 400 Bad Request: CANNOT_PARSE_ENTITIES, causing alert delivery to crash. All user-supplied strings must be processed through htmlspecialchars($input, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'). 2. Silent Outbound cURL Failures: Calling curl_exec() without verifying response payload fields is unsafe. A 200 OK HTTP transport status does not guarantee success if Telegram returns {"ok": false, "description": "..."}. The code must evaluate curl_getinfo($ch, CURLINFO_HTTP_CODE), verify json_last_error() === JSON_ERROR_NONE, and confirm $responseData['ok'] === true.

Here is the function that formats user variables safely and posts the payload to Telegram using cURL:

<?php
// telegram_notifier.php
declare(strict_types=1);

function notifyTelegramManager(string $leadId, string $name, string $phone): bool
{
    $botToken = getenv('TELEGRAM_BOT_TOKEN');
    $chatId = getenv('TELEGRAM_MANAGER_CHAT_ID');

    if (!$botToken || !$chatId) {
        error_log('Telegram bot configuration missing');
        return false;
    }

    // Sanitize values for HTML parse mode
    $safeId = htmlspecialchars($leadId, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    $safeName = htmlspecialchars($name, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    $safePhone = htmlspecialchars($phone, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');

    $text = "<b>New Lead Received</b>\n\n";
    $text .= "<b>ID:</b> <code>{$safeId}</code>\n";
    $text .= "<b>Name:</b> {$safeName}\n";
    $text .= "<b>Phone:</b> {$safePhone}";

    $callbackData = "take:" . $leadId; // 5 + 14 = 19 bytes (well under 64-byte limit)

    $payload = [
        'chat_id' => $chatId,
        'text' => $text,
        'parse_mode' => 'HTML',
        'reply_markup' => [
            'inline_keyboard' => [
                [
                    [
                        'text' => '✋ Take Lead',
                        'callback_data' => $callbackData,
                    ]
                ]
            ]
        ]
    ];

    $ch = curl_init("https://api.telegram.org/bot{$botToken}/sendMessage");
    curl_setopt_array($ch, [
        CURLOPT_POST => true,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
        CURLOPT_POSTFIELDS => json_encode($payload),
        CURLOPT_TIMEOUT => 5,
    ]);

    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $curlError = curl_error($ch);
    curl_close($ch);

    if ($response === false) {
        error_log("Telegram cURL transport error: {$curlError}");
        return false;
    }

    if ($httpCode !== 200) {
        error_log("Telegram returned non-200 HTTP code: {$httpCode}. Response: {$response}");
        return false;
    }

    $responseData = json_decode((string)$response, true);
    if (json_last_error() !== JSON_ERROR_NONE || !isset($responseData['ok']) || $responseData['ok'] !== true) {
        error_log("Telegram API error response: {$response}");
        return false;
    }

    return true;
}

Handling Webhook Callbacks and Preventing Race Conditions

When a manager clicks "Take Lead" in Telegram, Telegram sends a callback_query payload to your registered webhook endpoint. If multiple managers click the button simultaneously, a race condition occurs unless the backend executes atomic updates.

To safely handle the callback update:

1. Verify Webhook Secret Token: Validate the X-Telegram-Bot-Api-Secret-Token header supplied by Telegram against your configured secret. Reject unauthorized requests with HTTP 403. 2. Acknowledge Immediately via answerCallbackQuery: Telegram displays a loading indicator on the inline button until answerCallbackQuery is called. The backend MUST make this API call within 10 seconds to dismiss the spinner, regardless of whether the claim succeeds or fails. 3. Atomic Lead Assignment: Execute an atomic SQL query: UPDATE leads SET claimed_by_id = ?, claimed_by_name = ?, status = 'claimed' WHERE id = ? AND status = 'pending'. Checking $stmt->rowCount() reveals whether this specific manager successfully claimed the lead or if another manager beat them to it. 4. Update Message Text: Call editMessageText to visually update the original message in Telegram, displaying who claimed the lead and removing the action button to block double claims.

Here is the complete webhook handler endpoint in PHP:

<?php
// webhook.php
declare(strict_types=1);

header('Content-Type: application/json');

$secretHeader = $_SERVER['HTTP_X_TELEGRAM_BOT_API_SECRET_TOKEN'] ?? '';
$expectedSecret = getenv('TELEGRAM_WEBHOOK_SECRET');

if ($expectedSecret !== '' && !hash_equals($expectedSecret, $secretHeader)) {
    http_response_code(403);
    echo json_encode(['error' => 'Unauthorized webhook source']);
    exit;
}

$rawInput = file_get_contents('php://input');
$update = json_decode($rawInput, true);

if (!isset($update['callback_query'])) {
    // Acknowledge standard updates gracefully
    echo json_encode(['status' => 'ignored']);
    exit;
}

$callbackQuery = $update['callback_query'];
$callbackId = $callbackQuery['id'];
$callbackData = $callbackQuery['data'] ?? '';
$fromUser = $callbackQuery['from'];
$userId = $fromUser['id'];
$userName = trim(($fromUser['first_name'] ?? '') . ' ' . ($fromUser['last_name'] ?? ''));
$message = $callbackQuery['message'];
$chatId = $message['chat']['id'];
$messageId = $message['message_id'];

$botToken = getenv('TELEGRAM_BOT_TOKEN');

// Check callback prefix
if (strpos($callbackData, 'take:') !== 0) {
    answerCallback($botToken, $callbackId, 'Unknown command key.');
    exit;
}

$leadId = substr($callbackData, 5);

$dbHost = getenv('DB_HOST') ?: '127.0.0.1';
$dbName = getenv('DB_NAME') ?: 'app_db';
$dbUser = getenv('DB_USER') ?: 'db_user';
$dbPass = getenv('DB_PASS') ?: 'secret';

$pdo = new PDO("mysql:host={$dbHost};dbname={$dbName};charset=utf8mb4", $dbUser, $dbPass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

// Atomic update query
$stmt = $pdo->prepare(
    'UPDATE leads SET claimed_by_id = :userId, claimed_by_name = :userName, status = "claimed" WHERE id = :id AND status = "pending"'
);
$stmt->execute([
    ':userId' => $userId,
    ':userName' => $userName,
    ':id' => $leadId,
]);

if ($stmt->rowCount() === 1) {
    // Success: this manager won the race condition
    answerCallback($botToken, $callbackId, 'You have successfully taken this lead!');
    
    $safeManager = htmlspecialchars($userName, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    $updatedText = $message['text'] . "\n\n✅ <b>Claimed by:</b> {$safeManager}";
    
    editMessageText($botToken, $chatId, $messageId, $updatedText);
} else {
    // Failure: lead was already claimed or does not exist
    answerCallback($botToken, $callbackId, 'This lead has already been claimed by another manager.', true);
}

echo json_encode(['status' => 'ok']);

function answerCallback(string $token, string $callbackId, string $text, bool $showAlert = false): void
{
    $ch = curl_init("https://api.telegram.org/bot{$token}/answerCallbackQuery");
    curl_setopt_array($ch, [
        CURLOPT_POST => true,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
        CURLOPT_POSTFIELDS => json_encode([
            'callback_query_id' => $callbackId,
            'text' => $text,
            'show_alert' => $showAlert,
        ]),
        CURLOPT_TIMEOUT => 3,
    ]);
    curl_exec($ch);
    curl_close($ch);
}

function editMessageText(string $token, int $chatId, int $messageId, string $text): void
{
    $ch = curl_init("https://api.telegram.org/bot{$token}/editMessageText");
    curl_setopt_array($ch, [
        CURLOPT_POST => true,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
        CURLOPT_POSTFIELDS => json_encode([
            'chat_id' => $chatId,
            'message_id' => $messageId,
            'text' => $text,
            'parse_mode' => 'HTML',
            'reply_markup' => ['inline_keyboard' => []] // Remove buttons
        ]),
        CURLOPT_TIMEOUT => 3,
    ]);
    curl_exec($ch);
    curl_close($ch);
}

Production Notes

Deploying this workflow to production requires accounting for edge cases in distributed networks and high-concurrency environments:

1. Strict Payload Limits: The callback_data string in Telegram inline keyboards cannot exceed 64 bytes. Avoid embedding raw JSON strings, email addresses, or compound flags into callback_data. Using take:<14-char-hex> consumes 19 bytes, leaving ample space if you need to add auxiliary prefixes later (e.g., reject:a1b2c3d4e5f6g7). 2. Database Isolation Levels: When updating lead status upon callback execution, ensure your database engine uses standard READ COMMITTED or REPEATABLE READ transaction isolation. The atomic WHERE status = 'pending' clause prevents double-assignment without requiring table-level locks. 3. Idempotency of Webhook Updates: Telegram re-delivers webhook payloads if your server returns a non-2xx status or takes longer than 5 seconds to respond. Store processed update_id values in Redis or an InnoDB table. If an incoming update_id has already been recorded, acknowledge the update via answerCallbackQuery and return HTTP 200 immediately without executing redundant database queries. 4. Webhook Secret Validation: Always pass a secret_token when invoking setWebhook. Verify the header $_SERVER['HTTP_X_TELEGRAM_BOT_API_SECRET_TOKEN'] on every incoming POST request to block unauthorized traffic from targeting your webhook handler.

Building enterprise lead-routing pipelines and resilient custom integrations requires careful architectural design. If you need expert support building robust infrastructure for Telegram, consider partnering with BotCreator, a specialized studio that ships production-ready Telegram bots and Mini Apps. You can also refer to the complete API methods reference at when implementing complex message handling patterns.