Tool Database MySQL

Terakhir diperbarui: 28 Jul 2026

DatabaseTool Class

<?php

namespace App\Tools;

class DatabaseTool implements ToolInterface
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function getDefinition(): array
    {
        return [
            'type'     => 'function',
            'function' => [
                'name'        => 'database_query',
                'description' => 'Mengakses database untuk mencari atau mengambil data. Tersedia operasi: searchUser, getProduct, searchArticle, insertData.',
                'parameters'  => [
                    'type'       => 'object',
                    'properties' => [
                        'operation' => [
                            'type'        => 'string',
                            'enum'        => ['searchUser', 'getProduct', 'searchArticle', 'insertData'],
                            'description' => 'Operasi database yang akan dieksekusi',
                        ],
                        'params' => [
                            'type'        => 'object',
                            'description' => 'Parameter untuk operasi yang dipilih',
                        ],
                    ],
                    'required' => ['operation'],
                ],
            ],
        ];
    }

    public function execute(array $args): mixed
    {
        $operation = $args['operation'] ?? '';
        $params    = $args['params'] ?? [];

        return match ($operation) {
            'searchUser'    => $this->searchUser($params),
            'getProduct'    => $this->getProduct($params),
            'searchArticle' => $this->searchArticle($params),
            'insertData'    => $this->insertData($params),
            default         => ['error' => "Operasi '$operation' tidak dikenal"],
        };
    }

    private function searchUser(array $params): array
    {
        $keyword = trim($params['keyword'] ?? '');
        if (empty($keyword) || strlen($keyword) < 2) {
            return ['error' => 'Keyword pencarian minimal 2 karakter'];
        }

        // Prepared statement aman dari SQL Injection
        $stmt = $this->pdo->prepare(
            'SELECT id, name, email, role, created_at
             FROM users
             WHERE name LIKE ? OR email LIKE ?
             ORDER BY name ASC
             LIMIT 10'
        );
        $like = '%' . $keyword . '%';
        $stmt->execute([$like, $like]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return [
            'count'  => count($rows),
            'users'  => $rows,
            'keyword' => $keyword,
        ];
    }

    private function getProduct(array $params): array
    {
        $productId = (int) ($params['id'] ?? 0);
        if ($productId <= 0) {
            return ['error' => 'ID produk tidak valid'];
        }

        $stmt = $this->pdo->prepare(
            'SELECT id, name, sku, price, stock, category, description
             FROM products
             WHERE id = ? AND is_active = 1
             LIMIT 1'
        );
        $stmt->execute([$productId]);
        $product = $stmt->fetch(PDO::FETCH_ASSOC);

        return $product ?: ['error' => "Produk dengan ID $productId tidak ditemukan"];
    }

    private function searchArticle(array $params): array
    {
        $keyword  = trim($params['keyword'] ?? '');
        $category = trim($params['category'] ?? '');

        $sql    = 'SELECT id, title, slug, category, excerpt, created_at FROM articles WHERE status = "published"';
        $values = [];

        if (!empty($keyword)) {
            $sql     .= ' AND (title LIKE ? OR content LIKE ?)';
            $values[] = '%' . $keyword . '%';
            $values[] = '%' . $keyword . '%';
        }

        if (!empty($category)) {
            $sql     .= ' AND category = ?';
            $values[] = $category;
        }

        $sql .= ' ORDER BY created_at DESC LIMIT 5';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($values);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return ['count' => count($rows), 'articles' => $rows];
    }

    private function insertData(array $params): array
    {
        $table   = $params['table'] ?? '';
        $data    = $params['data'] ?? [];
        $allowed = ['feedback', 'contact_messages']; // Whitelist tabel yang boleh diisi AI

        if (!in_array($table, $allowed, true)) {
            return ['error' => "Tabel '$table' tidak diizinkan untuk operasi insert"];
        }

        if (empty($data)) {
            return ['error' => 'Data untuk insert tidak boleh kosong'];
        }

        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));

        $stmt = $this->pdo->prepare("INSERT INTO $table ($columns) VALUES ($placeholders)");
        $stmt->execute(array_values($data));

        return ['success' => true, 'id' => $this->pdo->lastInsertId()];
    }
}