Adminer Cheatsheet¶

Adminer - Database Management Tool

Adminer (formerly phpMinAdmin) is a full-featured database management tool written in PHP. It consists of a single PHP file ready to deploy to the target server. Adminer is available for MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Elasticsearch, MongoDB, and more.

Installation¶

Basic Installation¶

# Download latest version
wget https://www.adminer.org/latest.php -O adminer.php

# Or download specific version
wget https://github.com/vrana/adminer/releases/download/v4.8.1/adminer-4.8.1.php -O adminer.php

# Make it accessible via web server
sudo cp adminer.php /var/www/html/
sudo chown www-data:www-data /var/www/html/adminer.php
sudo chmod 644 /var/www/html/adminer.php

# Access via browser
# http://your-server.com/adminer.php

Docker Installation¶

# Run Adminer in Docker
docker run --name adminer -p 8080:8080 adminer

# With specific tag
docker run --name adminer -p 8080:8080 adminer:4.8.1

# With custom configuration
docker run --name adminer \
  -p 8080:8080 \
  -e ADMINER_DEFAULT_SERVER=mysql \
  -e ADMINER_DESIGN=pepa-linha \
  adminer

# Access at http://localhost:8080

# Docker Compose setup
version: '3.8'
services:
  adminer:
    image: adminer:4.8.1
    restart: always
    ports:
      - 8080:8080
    environment:
      ADMINER_DEFAULT_SERVER: mysql
      ADMINER_DESIGN: pepa-linha
    volumes:
      - ./adminer-plugins:/var/www/html/plugins-enabled

Web Server Configuration¶

# Apache configuration
<VirtualHost *:80>
    ServerName adminer.example.com
    DocumentRoot /var/www/adminer

    <Directory /var/www/adminer>
        Options -Indexes
        AllowOverride All
        Require all granted

        # Security headers
        Header always set X-Content-Type-Options nosniff
        Header always set X-Frame-Options DENY
        Header always set X-XSS-Protection "1; mode=block"
    </Directory>

    # Restrict access to specific IPs
    <Location />
        Require ip 192.168.1.0/24
        Require ip 10.0.0.0/8
    </Location>
</VirtualHost>

# Nginx configuration
server {
    listen 80;
    server_name adminer.example.com;
    root /var/www/adminer;
    index adminer.php;

    # Security headers
    add_header X-Content-Type-Options nosniff;
    add_header X-Frame-Options DENY;
    add_header X-XSS-Protection "1; mode=block";

    # Restrict access
    allow 192.168.1.0/24;
    allow 10.0.0.0/8;
    deny all;

    location ~ \.php$ {
        fastcgi_pass unix:/var/run/php/php8.1-fpm.sock;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include fastcgi_params;
    }

    # Deny access to sensitive files
    location ~ /\. {
        deny all;
    }
}

PHP Requirements¶

<?php
// Check PHP requirements
echo "PHP Version: " . PHP_VERSION . "\n";

// Required extensions
$required_extensions = [
    'pdo',
    'pdo_mysql',    // For MySQL/MariaDB
    'pdo_pgsql',    // For PostgreSQL
    'pdo_sqlite',   // For SQLite
    'pdo_sqlsrv',   // For SQL Server
    'pdo_oci',      // For Oracle
    'mongodb',      // For MongoDB
    'json',
    'session',
    'pcre'
];

foreach ($required_extensions as $ext) {
    if (extension_loaded($ext)) {
        echo "✓ $ext extension loaded\n";
    } else {
        echo "✗ $ext extension NOT loaded\n";
    }
}

// Memory and time limits
echo "Memory Limit: " . ini_get('memory_limit') . "\n";
echo "Max Execution Time: " . ini_get('max_execution_time') . "\n";
echo "Upload Max Filesize: " . ini_get('upload_max_filesize') . "\n";
echo "Post Max Size: " . ini_get('post_max_size') . "\n";
?>

Configuration¶

Basic Configuration¶

<?php
// adminer-config.php - Custom configuration file

// Include original Adminer
include './adminer.php';

// Custom configuration class
class AdminerConfig extends Adminer {

    // Default database server
    function database() {
        return 'myapp_db';
    }

    // Default username
    function username() {
        return 'admin';
    }

    // Hide certain databases
    function databases($flush = true) {
        return array('myapp_db', 'analytics_db');
    }

    // Custom login credentials
    function login($login, $password) {
        // Allow specific users
        $users = array(
            'admin' => 'secure_password_hash',
            'readonly' => 'readonly_password_hash'
        );

        return isset($users[$login]) && 
               password_verify($password, $users[$login]);
    }

    // Customize interface
    function name() {
        return 'MyApp Database Admin';
    }

    // Custom CSS
    function head() {
        echo '<style>
            body { font-family: Arial, sans-serif; }
            #menu { background: #2c3e50; }
            #menu a { color: white; }
        </style>';
    }
}

// Use custom configuration
new AdminerConfig;
?>

Environment-Specific Configuration¶

<?php
// config/development.php
class AdminerDevelopment extends Adminer {
    function login($login, $password) {
        // Allow any login in development
        return true;
    }

    function database() {
        return 'myapp_dev';
    }

    function name() {
        return 'Development Database';
    }
}

// config/production.php
class AdminerProduction extends Adminer {
    function login($login, $password) {
        // Strict authentication in production
        $allowed_users = [
            'dba' => '$2y$10$hash_for_dba_password',
            'readonly' => '$2y$10$hash_for_readonly_password'
        ];

        return isset($allowed_users[$login]) && 
               password_verify($password, $allowed_users[$login]);
    }

    function databases() {
        // Only show production databases
        return ['myapp_prod', 'analytics_prod'];
    }

    function name() {
        return 'Production Database - CAUTION';
    }

    // Disable dangerous operations
    function drop() {
        return false; // Disable DROP operations
    }

    function alter() {
        return false; // Disable ALTER operations
    }
}

// Load environment-specific config
$environment = $_ENV['APP_ENV'] ?? 'development';
include "config/{$environment}.php";
?>

Plugin System¶

<?php
// plugins-enabled/plugin.php
// Enable specific plugins

// Available plugins:
// - designs (themes)
// - dump-date (add date to exports)
// - dump-zip (compress exports)
// - edit-calendar (date picker)
// - edit-foreign (foreign key editor)
// - edit-textarea (larger text areas)
// - email-table (email table contents)
// - enum-option (enum field editor)
// - file-upload (file upload interface)
// - foreign-system (cross-database foreign keys)
// - frames (frame interface)
// - json-column (JSON column editor)
// - login-password-less (passwordless login)
// - login-servers (predefined servers)
// - login-ssl (SSL connection options)
// - master-password (master password protection)
// - readonly (read-only mode)
// - slug (SEO-friendly URLs)
// - struct-comments (structure comments)
// - table-structure (enhanced table structure)
// - tinymce (rich text editor)
// - translation (custom translations)
// - version-noverify (skip version verification)

// Enable plugins
$plugins = [
    'designs',
    'edit-calendar',
    'edit-textarea',
    'dump-zip',
    'json-column'
];

foreach ($plugins as $plugin) {
    if (file_exists("plugins/$plugin.php")) {
        include "plugins/$plugin.php";
    }
}
?>

Database Connections¶

Connection Parameters¶

<?php
// Connection configuration for different databases

// MySQL/MariaDB connection
$mysql_config = [
    'server' => 'localhost:3306',
    'username' => 'dbuser',
    'password' => 'password',
    'database' => 'myapp',
    'driver' => 'mysql'
];

// PostgreSQL connection
$pgsql_config = [
    'server' => 'localhost:5432',
    'username' => 'postgres',
    'password' => 'password',
    'database' => 'myapp',
    'driver' => 'pgsql'
];

// SQLite connection
$sqlite_config = [
    'server' => '',
    'username' => '',
    'password' => '',
    'database' => '/path/to/database.sqlite',
    'driver' => 'sqlite'
];

// SQL Server connection
$sqlsrv_config = [
    'server' => 'localhost:1433',
    'username' => 'sa',
    'password' => 'password',
    'database' => 'myapp',
    'driver' => 'mssql'
];

// Oracle connection
$oracle_config = [
    'server' => 'localhost:1521/XE',
    'username' => 'hr',
    'password' => 'password',
    'database' => '',
    'driver' => 'oracle'
];
?>

SSL/TLS Configuration¶

<?php
// SSL connection configuration
class AdminerSSL extends Adminer {
    function connectSsl() {
        return [
            MYSQLI_CLIENT_SSL => true,
            MYSQLI_CLIENT_SSL_VERIFY_SERVER_CERT => true
        ];
    }

    function credentials() {
        return [
            'server' => 'secure-db.example.com:3306',
            'username' => 'ssl_user',
            'password' => 'ssl_password',
            'ssl' => [
                'key' => '/path/to/client-key.pem',
                'cert' => '/path/to/client-cert.pem',
                'ca' => '/path/to/ca-cert.pem',
                'capath' => '/path/to/ca-certs/',
                'cipher' => 'DHE-RSA-AES256-SHA'
            ]
        ];
    }
}

// PostgreSQL SSL configuration
$pgsql_ssl_dsn = "pgsql:host=secure-db.example.com;port=5432;dbname=myapp;sslmode=require;sslcert=/path/to/client.crt;sslkey=/path/to/client.key;sslrootcert=/path/to/ca.crt";
?>

Connection Pooling¶

<?php
// Connection management
class AdminerConnectionPool extends Adminer {
    private static $connections = [];

    function connect() {
        $server = $this->server();
        $username = $this->username();
        $password = $this->password();

        $key = md5($server . $username . $password);

        if (!isset(self::$connections[$key])) {
            self::$connections[$key] = new PDO(
                $this->dsn(),
                $username,
                $password,
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_PERSISTENT => true,
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
                ]
            );
        }

        return self::$connections[$key];
    }

    function dsn() {
        $server = $this->server();
        $database = $this->database();

        switch ($this->driver()) {
            case 'mysql':
                return "mysql:host=$server;dbname=$database;charset=utf8mb4";
            case 'pgsql':
                return "pgsql:host=$server;dbname=$database";
            case 'sqlite':
                return "sqlite:$database";
            default:
                return parent::dsn();
        }
    }
}
?>

Database Operations¶

Database Management¶

-- Create database
CREATE DATABASE myapp_new 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- PostgreSQL
CREATE DATABASE myapp_new 
WITH ENCODING 'UTF8' 
LC_COLLATE='en_US.UTF-8' 
LC_CTYPE='en_US.UTF-8';

-- Drop database
DROP DATABASE myapp_old;

-- Show databases
SHOW DATABASES;

-- PostgreSQL
SELECT datname FROM pg_database;

-- Database size information
SELECT 
    table_schema as database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb
FROM information_schema.tables
GROUP BY table_schema;

-- PostgreSQL database size
SELECT 
    datname as database_name,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
WHERE datistemplate = false;

Schema Operations¶

-- Create schema (PostgreSQL)
CREATE SCHEMA analytics;
CREATE SCHEMA reporting;

-- Set search path
SET search_path TO analytics, public;

-- Grant schema permissions
GRANT USAGE ON SCHEMA analytics TO app_user;
GRANT CREATE ON SCHEMA analytics TO app_user;

-- List schemas
SELECT schema_name 
FROM information_schema.schemata 
WHERE schema_name NOT IN ('information_schema', 'pg_catalog');

-- Schema size information
SELECT 
    schemaname,
    COUNT(*) as table_count,
    SUM(pg_total_relation_size(schemaname||'.'||tablename)) as total_size
FROM pg_tables 
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
GROUP BY schemaname;

Backup and Restore¶

# MySQL backup via Adminer
# Use Export functionality in web interface
# Or command line:
mysqldump -u username -p --single-transaction --routines --triggers myapp > backup.sql

# PostgreSQL backup
pg_dump -U username -h localhost -d myapp -f backup.sql

# SQLite backup
sqlite3 myapp.db ".backup backup.db"

# Restore MySQL
mysql -u username -p myapp < backup.sql

# Restore PostgreSQL
psql -U username -h localhost -d myapp -f backup.sql

# Restore SQLite
sqlite3 myapp.db ".restore backup.db"

Table Management¶

Table Creation¶

-- Create table with various data types
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    is_active BOOLEAN DEFAULT TRUE,
    profile_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_email (email),
    INDEX idx_name (first_name, last_name),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PostgreSQL table with advanced features
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    order_number VARCHAR(20) UNIQUE NOT NULL,
    status order_status_enum DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
    order_data JSONB,
    shipping_address TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT chk_order_number CHECK (order_number ~ '^ORD-[0-9]{6}$')
);

-- Create indexes
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_data_gin ON orders USING GIN (order_data);

-- Create enum type (PostgreSQL)
CREATE TYPE order_status_enum AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

Table Modification¶

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL;

-- Modify column
ALTER TABLE users MODIFY COLUMN phone VARCHAR(25);
-- PostgreSQL
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(25);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT uk_email;
ALTER TABLE orders DROP CONSTRAINT fk_orders_user_id;

-- Rename table
RENAME TABLE old_table TO new_table;
-- PostgreSQL
ALTER TABLE old_table RENAME TO new_table;

-- Rename column
ALTER TABLE users CHANGE old_column new_column VARCHAR(50);
-- PostgreSQL
ALTER TABLE users RENAME COLUMN old_column TO new_column;

Index Management¶

-- Create indexes
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_name ON users(first_name, last_name);

-- Composite indexes
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_date_status ON orders(created_at, status);

-- Partial indexes (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
CREATE INDEX idx_recent_orders ON orders(created_at) 
    WHERE created_at >= NOW() - INTERVAL '30 days';

-- Functional indexes
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));

-- Full-text indexes (MySQL)
CREATE FULLTEXT INDEX idx_products_search ON products(name, description);

-- GIN indexes for JSON (PostgreSQL)
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile_data);

-- Show indexes
SHOW INDEXES FROM users;
-- PostgreSQL
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- Drop index
DROP INDEX idx_users_email ON users;
-- PostgreSQL
DROP INDEX idx_users_email;

-- Index usage statistics (PostgreSQL)
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Data Operations¶

Data Insertion¶

-- Single row insert
INSERT INTO users (username, email, password_hash, first_name, last_name) 
VALUES ('john_doe', 'john@example.com', 'hashed_password', 'John', 'Doe');

-- Multiple row insert
INSERT INTO users (username, email, password_hash, first_name, last_name) VALUES
('jane_smith', 'jane@example.com', 'hashed_password', 'Jane', 'Smith'),
('bob_wilson', 'bob@example.com', 'hashed_password', 'Bob', 'Wilson'),
('alice_brown', 'alice@example.com', 'hashed_password', 'Alice', 'Brown');

-- Insert with JSON data (MySQL 5.7+)
INSERT INTO users (username, email, profile_data) VALUES
('user1', 'user1@example.com', '{"preferences": {"theme": "dark", "language": "en"}, "settings": {"notifications": true}}');

-- Insert with JSONB (PostgreSQL)
INSERT INTO orders (user_id, order_number, order_data) VALUES
(1, 'ORD-000001', '{"items": [{"product_id": 1, "quantity": 2, "price": 29.99}], "shipping": {"method": "standard", "cost": 5.99}}');

-- Insert from SELECT
INSERT INTO archived_orders (user_id, order_number, total_amount, created_at)
SELECT user_id, order_number, total_amount, created_at
FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Insert with ON DUPLICATE KEY UPDATE (MySQL)
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1, 1, NOW())
ON DUPLICATE KEY UPDATE 
    login_count = login_count + 1,
    last_login = NOW();

-- Insert with UPSERT (PostgreSQL)
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1, 1, NOW())
ON CONFLICT (user_id) DO UPDATE SET
    login_count = user_stats.login_count + 1,
    last_login = NOW();

Data Updates¶

-- Simple update
UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- Update with conditions
UPDATE users 
SET is_active = FALSE, updated_at = NOW()
WHERE last_login < DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- Update with JOIN
UPDATE users u
JOIN user_profiles p ON u.id = p.user_id
SET u.first_name = p.display_name
WHERE p.display_name IS NOT NULL;

-- Update JSON data (MySQL)
UPDATE users 
SET profile_data = JSON_SET(profile_data, '$.preferences.theme', 'light')
WHERE id = 1;

-- Update JSONB data (PostgreSQL)
UPDATE orders 
SET order_data = order_data || '{"status": "updated"}'::jsonb
WHERE id = 1;

-- Conditional update
UPDATE products 
SET price = CASE 
    WHEN category = 'electronics' THEN price * 1.1
    WHEN category = 'books' THEN price * 1.05
    ELSE price
END
WHERE updated_at < DATE_SUB(NOW(), INTERVAL 1 MONTH);

-- Update with subquery
UPDATE users 
SET total_orders = (
    SELECT COUNT(*) 
    FROM orders 
    WHERE orders.user_id = users.id
);

-- Bulk update with temporary table
CREATE TEMPORARY TABLE temp_updates (
    user_id INT,
    new_email VARCHAR(100)
);

INSERT INTO temp_updates VALUES
(1, 'user1_new@example.com'),
(2, 'user2_new@example.com');

UPDATE users u
JOIN temp_updates t ON u.id = t.user_id
SET u.email = t.new_email;

Data Deletion¶

-- Simple delete
DELETE FROM users WHERE id = 1;

-- Delete with conditions
DELETE FROM logs 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Delete with JOIN
DELETE u FROM users u
JOIN user_profiles p ON u.id = p.user_id
WHERE p.status = 'deleted';

-- Delete with subquery
DELETE FROM orders 
WHERE user_id IN (
    SELECT id FROM users WHERE is_active = FALSE
);

-- Safe delete with LIMIT
DELETE FROM temp_data 
WHERE processed = TRUE 
ORDER BY created_at 
LIMIT 1000;

-- Soft delete (mark as deleted)
UPDATE users 
SET is_deleted = TRUE, deleted_at = NOW()
WHERE id = 1;

-- Cascade delete (with foreign key constraints)
DELETE FROM users WHERE id = 1;
-- This will also delete related orders if FK has ON DELETE CASCADE

-- Truncate table (fast delete all)
TRUNCATE TABLE temp_table;

-- Delete duplicates
DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id > u2.id 
  AND u1.email = u2.email;

-- PostgreSQL delete duplicates
DELETE FROM users 
WHERE id NOT IN (
    SELECT MIN(id) 
    FROM users 
    GROUP BY email
);

Data Querying¶

-- Basic SELECT
SELECT id, username, email, created_at 
FROM users 
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 10;

-- Complex WHERE conditions
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing')
  AND total_amount > 100
  AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- JOIN operations
SELECT 
    u.username,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.is_active = TRUE
GROUP BY u.id, u.username, u.email
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;

-- Subqueries
SELECT * FROM users 
WHERE id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total_amount > 500
);

-- EXISTS subquery
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
      AND o.status = 'completed'
);

-- Window functions (PostgreSQL/MySQL 8.0+)
SELECT 
    username,
    email,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as user_number,
    RANK() OVER (ORDER BY created_at) as user_rank,
    LAG(created_at) OVER (ORDER BY created_at) as previous_user_date
FROM users;

-- JSON queries (MySQL)
SELECT 
    id,
    username,
    JSON_EXTRACT(profile_data, '$.preferences.theme') as theme,
    JSON_EXTRACT(profile_data, '$.settings.notifications') as notifications
FROM users
WHERE JSON_EXTRACT(profile_data, '$.preferences.theme') = 'dark';

-- JSONB queries (PostgreSQL)
SELECT 
    id,
    order_number,
    order_data->>'status' as status,
    order_data->'items'->0->>'product_id' as first_product_id
FROM orders
WHERE order_data @> '{"status": "completed"}';

-- Full-text search (MySQL)
SELECT * FROM products 
WHERE MATCH(name, description) AGAINST('laptop gaming' IN NATURAL LANGUAGE MODE);

-- Full-text search (PostgreSQL)
SELECT * FROM products 
WHERE to_tsvector('english', name || ' ' || description) @@ to_tsquery('english', 'laptop & gaming');

SQL Editor¶

Query Execution¶

-- Adminer SQL editor features:
-- 1. Syntax highlighting
-- 2. Auto-completion
-- 3. Query history
-- 4. Multiple query execution
-- 5. Export results
-- 6. Query profiling

-- Execute multiple queries (separate with semicolons)
SELECT COUNT(*) as total_users FROM users;
SELECT COUNT(*) as total_orders FROM orders;
SELECT COUNT(*) as total_products FROM products;

-- Query with parameters (use Adminer's parameter feature)
SELECT * FROM users 
WHERE created_at >= ? 
  AND status = ?;
-- Parameters: ['2023-01-01', 'active']

-- Complex analytical query
WITH monthly_stats AS (
    SELECT 
        DATE_FORMAT(created_at, '%Y-%m') as month,
        COUNT(*) as order_count,
        SUM(total_amount) as revenue,
        AVG(total_amount) as avg_order_value
    FROM orders
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
),
growth_stats AS (
    SELECT 
        month,
        order_count,
        revenue,
        avg_order_value,
        LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
        (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100 as growth_rate
    FROM monthly_stats
)
SELECT 
    month,
    order_count,
    ROUND(revenue, 2) as revenue,
    ROUND(avg_order_value, 2) as avg_order_value,
    ROUND(growth_rate, 2) as growth_rate_percent
FROM growth_stats
ORDER BY month;

Query Optimization¶

-- Use EXPLAIN to analyze query performance
EXPLAIN SELECT 
    u.username,
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.username;

-- PostgreSQL EXPLAIN with detailed analysis
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT 
    u.username,
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.username;

-- Optimize with proper indexes
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Use LIMIT for large result sets
SELECT * FROM large_table 
ORDER BY created_at DESC 
LIMIT 100;

-- Use EXISTS instead of IN for better performance
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
) 
LIMIT 1000;

-- Optimize GROUP BY queries
SELECT 
    status,
    COUNT(*) as count
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY status
ORDER BY count DESC;

-- Use covering indexes
CREATE INDEX idx_orders_covering ON orders(status, created_at, total_amount);

-- Query with index hints (MySQL)
SELECT * FROM users 
USE INDEX (idx_users_email)
WHERE email = 'user@example.com';

-- Force index usage
SELECT * FROM users 
FORCE INDEX (idx_users_created_at)
WHERE created_at >= '2023-01-01'
ORDER BY created_at;

Stored Procedures and Functions¶

-- MySQL stored procedure
DELIMITER //
CREATE PROCEDURE GetUserStats(IN user_id INT)
BEGIN
    SELECT 
        u.username,
        u.email,
        COUNT(o.id) as total_orders,
        SUM(o.total_amount) as total_spent,
        AVG(o.total_amount) as avg_order_value,
        MAX(o.created_at) as last_order_date
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = user_id
    GROUP BY u.id, u.username, u.email;
END //
DELIMITER ;

-- Call stored procedure
CALL GetUserStats(1);

-- MySQL function
DELIMITER //
CREATE FUNCTION CalculateDiscount(order_total DECIMAL(10,2)) 
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE discount DECIMAL(10,2) DEFAULT 0;

    IF order_total >= 1000 THEN
        SET discount = order_total * 0.10;
    ELSEIF order_total >= 500 THEN
        SET discount = order_total * 0.05;
    ELSEIF order_total >= 100 THEN
        SET discount = order_total * 0.02;
    END IF;

    RETURN discount;
END //
DELIMITER ;

-- Use function
SELECT 
    id,
    total_amount,
    CalculateDiscount(total_amount) as discount,
    total_amount - CalculateDiscount(total_amount) as final_amount
FROM orders;

-- PostgreSQL function
CREATE OR REPLACE FUNCTION get_user_order_summary(p_user_id INTEGER)
RETURNS TABLE(
    username VARCHAR,
    email VARCHAR,
    total_orders BIGINT,
    total_spent NUMERIC,
    avg_order_value NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.username,
        u.email,
        COUNT(o.id)::BIGINT,
        COALESCE(SUM(o.total_amount), 0),
        COALESCE(AVG(o.total_amount), 0)
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = p_user_id
    GROUP BY u.username, u.email;
END;
$$ LANGUAGE plpgsql;

-- Call PostgreSQL function
SELECT * FROM get_user_order_summary(1);

Import/Export¶

Data Import¶

<?php
// CSV import configuration in Adminer
$import_config = [
    'format' => 'csv',
    'delimiter' => ',',
    'enclosure' => '"',
    'escape' => '\\',
    'charset' => 'utf-8',
    'skip_first_line' => true,
    'null_value' => '',
    'date_format' => 'Y-m-d',
    'datetime_format' => 'Y-m-d H:i:s'
];

// SQL import example
$sql_import = "
INSERT INTO users (username, email, first_name, last_name) VALUES
('user1', 'user1@example.com', 'John', 'Doe'),
('user2', 'user2@example.com', 'Jane', 'Smith'),
('user3', 'user3@example.com', 'Bob', 'Wilson');

INSERT INTO orders (user_id, order_number, total_amount) VALUES
(1, 'ORD-001', 99.99),
(2, 'ORD-002', 149.99),
(3, 'ORD-003', 79.99);
";
?>

-- Bulk import strategies

-- MySQL LOAD DATA INFILE
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(username, email, first_name, last_name, @created_at)
SET created_at = STR_TO_DATE(@created_at, '%m/%d/%Y %H:%i:%s');

-- PostgreSQL COPY
COPY users(username, email, first_name, last_name, created_at)
FROM '/path/to/users.csv'
DELIMITER ','
CSV HEADER;

-- Import with data transformation
LOAD DATA INFILE '/path/to/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(user_id, @order_date, @total_amount, status)
SET 
    order_date = STR_TO_DATE(@order_date, '%m/%d/%Y'),
    total_amount = CAST(@total_amount AS DECIMAL(10,2)),
    created_at = NOW();

-- Handle import errors
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE temp_import
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Validate imported data
SELECT COUNT(*) as total_imported FROM temp_import;
SELECT COUNT(*) as invalid_emails 
FROM temp_import 
WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- Transfer valid data
INSERT INTO users (username, email, first_name, last_name)
SELECT username, email, first_name, last_name
FROM temp_import
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
  AND username IS NOT NULL
  AND username != '';

Data Export¶

-- Export formats available in Adminer:
-- 1. SQL - INSERT statements
-- 2. CSV - Comma-separated values
-- 3. TSV - Tab-separated values
-- 4. XML - XML format
-- 5. JSON - JSON format

-- Export with custom query
SELECT 
    u.id,
    u.username,
    u.email,
    u.first_name,
    u.last_name,
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent,
    DATE_FORMAT(u.created_at, '%Y-%m-%d') as registration_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.is_active = TRUE
GROUP BY u.id
ORDER BY total_spent DESC;

-- Export specific date range
SELECT * FROM orders 
WHERE created_at >= '2023-01-01' 
  AND created_at < '2024-01-01'
ORDER BY created_at;

-- Export with data transformation
SELECT 
    id,
    username,
    CONCAT(first_name, ' ', last_name) as full_name,
    email,
    CASE 
        WHEN is_active = 1 THEN 'Active'
        ELSE 'Inactive'
    END as status,
    DATE_FORMAT(created_at, '%m/%d/%Y') as registration_date
FROM users;

-- Export JSON data (MySQL)
SELECT 
    id,
    username,
    email,
    JSON_PRETTY(profile_data) as profile_json
FROM users
WHERE profile_data IS NOT NULL;

-- Export aggregated data
SELECT 
    DATE_FORMAT(created_at, '%Y-%m') as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value,
    MIN(total_amount) as min_order,
    MAX(total_amount) as max_order
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

Backup and Restore¶

<?php
// Automated backup script using Adminer
class AdminerBackup extends Adminer {

    function dumpTable($table, $style, $is_view = 0) {
        if ($style == 'CREATE') {
            // Add backup timestamp
            echo "-- Backup created: " . date('Y-m-d H:i:s') . "\n";
            echo "-- Table: $table\n\n";
        }
        return parent::dumpTable($table, $style, $is_view);
    }

    function dumpData($table, $style, $query) {
        if ($style == 'INSERT') {
            echo "-- Data for table: $table\n";
        }
        return parent::dumpData($table, $style, $query);
    }
}

// Backup configuration
$backup_config = [
    'format' => 'sql',
    'include_structure' => true,
    'include_data' => true,
    'include_drop' => true,
    'include_create' => true,
    'add_locks' => true,
    'hex_blob' => true,
    'routines' => true,
    'events' => true,
    'triggers' => true
];
?>

# Command-line backup scripts

# MySQL backup
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"
DB_NAME="myapp"

mysqldump -u backup_user -p$BACKUP_PASSWORD \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --add-drop-table \
  --add-locks \
  $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql

# Compress backup
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql

# Keep only last 7 days
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

# PostgreSQL backup
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"
DB_NAME="myapp"

pg_dump -U backup_user -h localhost \
  --verbose \
  --clean \
  --create \
  --if-exists \
  --format=custom \
  --compress=9 \
  $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.backup

# Keep only last 7 days
find $BACKUP_DIR -name "${DB_NAME}_*.backup" -mtime +7 -delete

# Restore MySQL
mysql -u username -p myapp < backup_file.sql

# Restore PostgreSQL
pg_restore -U username -h localhost -d myapp backup_file.backup

User Management¶

User Creation and Management¶

-- MySQL user management
-- Create user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON myapp.* TO 'admin_user'@'localhost';

-- Create role (MySQL 8.0+)
CREATE ROLE 'app_role';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_role';
GRANT 'app_role' TO 'app_user'@'localhost';

-- Set default role
SET DEFAULT ROLE 'app_role' TO 'app_user'@'localhost';

-- Show user privileges
SHOW GRANTS FOR 'app_user'@'localhost';

-- Change password
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';

-- Account locking/unlocking
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;

-- Password expiration
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE NEVER;

-- Drop user
DROP USER 'app_user'@'localhost';

-- PostgreSQL user management
-- Create user
CREATE USER app_user WITH PASSWORD 'secure_password';
CREATE USER readonly_user WITH PASSWORD 'readonly_password';

-- Create role
CREATE ROLE app_role;
CREATE ROLE readonly_role;

-- Grant privileges to role
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- Grant role to user
GRANT app_role TO app_user;
GRANT readonly_role TO readonly_user;

-- Grant database privileges
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Change password
ALTER USER app_user WITH PASSWORD 'new_password';

-- Drop user
DROP USER app_user;

-- Show user information
SELECT 
    usename as username,
    usesuper as is_superuser,
    usecreatedb as can_create_db,
    userepl as can_replicate,
    valuntil as password_expiry
FROM pg_user;

Permission Management¶

-- Granular permission management

-- Table-level permissions
GRANT SELECT ON users TO 'readonly_user'@'localhost';
GRANT INSERT, UPDATE ON orders TO 'app_user'@'localhost';
GRANT DELETE ON logs TO 'admin_user'@'localhost';

-- Column-level permissions (MySQL 8.0+)
GRANT SELECT (id, username, email) ON users TO 'limited_user'@'localhost';
GRANT UPDATE (email, updated_at) ON users TO 'profile_editor'@'localhost';

-- PostgreSQL column-level permissions
GRANT SELECT (id, username, email) ON users TO limited_user;
GRANT UPDATE (email, updated_at) ON users TO profile_editor;

-- Stored procedure permissions
GRANT EXECUTE ON PROCEDURE GetUserStats TO 'app_user'@'localhost';
GRANT EXECUTE ON FUNCTION CalculateDiscount TO 'app_user'@'localhost';

-- View permissions
CREATE VIEW user_summary AS
SELECT id, username, email, created_at
FROM users
WHERE is_active = TRUE;

GRANT SELECT ON user_summary TO 'readonly_user'@'localhost';

-- Row-level security (PostgreSQL)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_orders_policy ON orders
FOR ALL TO app_user
USING (user_id = current_setting('app.current_user_id')::INTEGER);

-- Set user context
SET app.current_user_id = '123';

-- Revoke permissions
REVOKE INSERT, UPDATE ON orders FROM 'app_user'@'localhost';
REVOKE ALL PRIVILEGES ON myapp.* FROM 'old_user'@'localhost';

-- Show effective permissions
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE grantee = 'app_user@localhost';

Security Policies¶

-- Password policies (MySQL 8.0+)
-- Install password validation plugin
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Configure password policy
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- Account management policies
CREATE USER 'temp_user'@'localhost' 
IDENTIFIED BY 'TempPassword123!' 
PASSWORD EXPIRE INTERVAL 30 DAY
FAILED_LOGIN_ATTEMPTS 3 
PASSWORD_LOCK_TIME 2;

-- Connection limits
ALTER USER 'app_user'@'localhost' 
WITH MAX_CONNECTIONS_PER_HOUR 1000
MAX_QUERIES_PER_HOUR 10000
MAX_UPDATES_PER_HOUR 5000;

-- PostgreSQL security policies
-- Create security-focused role
CREATE ROLE secure_app_role 
NOLOGIN 
NOCREATEDB 
NOCREATEROLE 
NOREPLICATION;

-- Grant minimal required privileges
GRANT CONNECT ON DATABASE myapp TO secure_app_role;
GRANT USAGE ON SCHEMA public TO secure_app_role;
GRANT SELECT, INSERT, UPDATE ON users TO secure_app_role;
GRANT SELECT ON products TO secure_app_role;

-- Create application user with secure role
CREATE USER app_user 
WITH PASSWORD 'SecurePassword123!' 
IN ROLE secure_app_role
VALID UNTIL '2024-12-31';

-- Audit user activities
CREATE TABLE user_audit_log (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    action VARCHAR(50),
    table_name VARCHAR(50),
    record_id INTEGER,
    old_values JSONB,
    new_values JSONB,
    timestamp TIMESTAMP DEFAULT NOW(),
    ip_address INET
);

-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_audit_log (
        username, action, table_name, record_id, 
        old_values, new_values, ip_address
    ) VALUES (
        current_user,
        TG_OP,
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE NULL END,
        CASE WHEN TG_OP = 'INSERT' THEN to_jsonb(NEW) 
             WHEN TG_OP = 'UPDATE' THEN to_jsonb(NEW) 
             ELSE NULL END,
        inet_client_addr()
    );

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Apply audit trigger to sensitive tables
CREATE TRIGGER users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Security¶

Access Control¶

<?php
// Secure Adminer configuration
class SecureAdminer extends Adminer {

    // IP whitelist
    private $allowed_ips = [
        '192.168.1.0/24',
        '10.0.0.0/8',
        '172.16.0.0/12'
    ];

    // Check IP access
    function __construct() {
        if (!$this->isAllowedIP()) {
            http_response_code(403);
            die('Access denied');
        }
    }

    private function isAllowedIP() {
        $client_ip = $_SERVER['REMOTE_ADDR'];

        foreach ($this->allowed_ips as $allowed_range) {
            if ($this->ipInRange($client_ip, $allowed_range)) {
                return true;
            }
        }

        return false;
    }

    private function ipInRange($ip, $range) {
        if (strpos($range, '/') === false) {
            return $ip === $range;
        }

        list($subnet, $mask) = explode('/', $range);
        return (ip2long($ip) & ~((1 << (32 - $mask)) - 1)) === ip2long($subnet);
    }

    // Secure login
    function login($login, $password) {
        // Rate limiting
        session_start();
        $attempts_key = 'login_attempts_' . $_SERVER['REMOTE_ADDR'];
        $attempts = $_SESSION[$attempts_key] ?? 0;

        if ($attempts >= 5) {
            $last_attempt = $_SESSION['last_attempt_' . $_SERVER['REMOTE_ADDR']] ?? 0;
            if (time() - $last_attempt < 300) { // 5 minutes lockout
                return false;
            } else {
                $_SESSION[$attempts_key] = 0; // Reset attempts
            }
        }

        // Validate credentials
        $valid_users = [
            'admin' => '$2y$10$hash_for_admin_password',
            'readonly' => '$2y$10$hash_for_readonly_password'
        ];

        if (isset($valid_users[$login]) && password_verify($password, $valid_users[$login])) {
            $_SESSION[$attempts_key] = 0; // Reset on successful login
            $_SESSION['user_role'] = $this->getUserRole($login);
            return true;
        } else {
            $_SESSION[$attempts_key] = $attempts + 1;
            $_SESSION['last_attempt_' . $_SERVER['REMOTE_ADDR']] = time();
            return false;
        }
    }

    private function getUserRole($login) {
        $roles = [
            'admin' => 'admin',
            'readonly' => 'readonly'
        ];
        return $roles[$login] ?? 'readonly';
    }

    // Restrict operations based on role
    function drop() {
        return $_SESSION['user_role'] === 'admin';
    }

    function alter() {
        return $_SESSION['user_role'] === 'admin';
    }

    function create() {
        return $_SESSION['user_role'] === 'admin';
    }

    // Hide sensitive databases
    function databases($flush = true) {
        $all_databases = parent::databases($flush);
        $allowed_databases = [
            'myapp_prod',
            'myapp_staging',
            'analytics'
        ];

        return array_intersect($all_databases, $allowed_databases);
    }

    // Log all activities
    function query($query, $start = null) {
        $result = parent::query($query, $start);

        // Log query
        $log_entry = [
            'timestamp' => date('Y-m-d H:i:s'),
            'user' => $this->username(),
            'ip' => $_SERVER['REMOTE_ADDR'],
            'query' => $query,
            'execution_time' => $start ? (microtime(true) - $start) : null
        ];

        file_put_contents(
            '/var/log/adminer/queries.log',
            json_encode($log_entry) . "\n",
            FILE_APPEND | LOCK_EX
        );

        return $result;
    }
}

// Use secure configuration
new SecureAdminer;
?>

Data Protection¶

<?php
// Data masking for sensitive information
class DataMaskingAdminer extends Adminer {

    private $sensitive_fields = [
        'password',
        'password_hash',
        'ssn',
        'credit_card',
        'phone',
        'email'
    ];

    // Mask sensitive data in select results
    function selectVal($val, $link, $field, $original) {
        if (in_array(strtolower($field['field']), $this->sensitive_fields)) {
            if ($field['field'] === 'email') {
                return $this->maskEmail($val);
            } elseif ($field['field'] === 'phone') {
                return $this->maskPhone($val);
            } else {
                return str_repeat('*', min(strlen($val), 8));
            }
        }

        return parent::selectVal($val, $link, $field, $original);
    }

    private function maskEmail($email) {
        if (strpos($email, '@') === false) {
            return $email;
        }

        list($local, $domain) = explode('@', $email);
        $masked_local = substr($local, 0, 2) . str_repeat('*', max(0, strlen($local) - 2));
        return $masked_local . '@' . $domain;
    }

    private function maskPhone($phone) {
        $cleaned = preg_replace('/[^0-9]/', '', $phone);
        if (strlen($cleaned) >= 4) {
            return str_repeat('*', strlen($cleaned) - 4) . substr($cleaned, -4);
        }
        return str_repeat('*', strlen($cleaned));
    }

    // Prevent export of sensitive data
    function dumpTable($table, $style, $is_view = 0) {
        if ($style === 'INSERT' && $this->hasSensitiveFields($table)) {
            echo "-- Export of sensitive table '$table' is restricted\n";
            return false;
        }

        return parent::dumpTable($table, $style, $is_view);
    }

    private function hasSensitiveFields($table) {
        $fields = fields($table);
        foreach ($fields as $field => $info) {
            if (in_array(strtolower($field), $this->sensitive_fields)) {
                return true;
            }
        }
        return false;
    }
}
?>

SSL/TLS Configuration¶

# Apache SSL configuration for Adminer
<VirtualHost *:443>
    ServerName adminer.example.com
    DocumentRoot /var/www/adminer

    SSLEngine on
    SSLCertificateFile /etc/ssl/certs/adminer.crt
    SSLCertificateKeyFile /etc/ssl/private/adminer.key
    SSLCertificateChainFile /etc/ssl/certs/ca-bundle.crt

    # Strong SSL configuration
    SSLProtocol all -SSLv2 -SSLv3 -TLSv1 -TLSv1.1
    SSLCipherSuite ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384
    SSLHonorCipherOrder on

    # Security headers
    Header always set Strict-Transport-Security "max-age=31536000; includeSubDomains"
    Header always set X-Content-Type-Options nosniff
    Header always set X-Frame-Options DENY
    Header always set X-XSS-Protection "1; mode=block"
    Header always set Content-Security-Policy "default-src 'self'; script-src 'self' 'unsafe-inline'; style-src 'self' 'unsafe-inline'"

    # Client certificate authentication (optional)
    SSLVerifyClient require
    SSLVerifyDepth 2
    SSLCACertificateFile /etc/ssl/certs/client-ca.crt

    <Directory /var/www/adminer>
        Options -Indexes
        AllowOverride All
        Require all granted

        # Additional security
        php_admin_value open_basedir "/var/www/adminer:/tmp"
        php_admin_flag allow_url_fopen off
        php_admin_flag allow_url_include off
    </Directory>
</VirtualHost>

Customization¶

Themes and Styling¶

<?php
// Custom theme for Adminer
class AdminerCustomTheme extends Adminer {

    function head() {
        echo '<style>
            /* Custom color scheme */
            :root {
                --primary-color: #2c3e50;
                --secondary-color: #3498db;
                --success-color: #27ae60;
                --warning-color: #f39c12;
                --danger-color: #e74c3c;
                --light-bg: #ecf0f1;
                --dark-text: #2c3e50;
            }

            /* Header styling */
            #menu {
                background: var(--primary-color);
                border-bottom: 3px solid var(--secondary-color);
            }

            #menu a {
                color: white;
                text-decoration: none;
                padding: 10px 15px;
                display: inline-block;
                transition: background 0.3s;
            }

            #menu a:hover {
                background: var(--secondary-color);
            }

            /* Table styling */
            table {
                border-collapse: collapse;
                width: 100%;
                margin: 20px 0;
                box-shadow: 0 2px 5px rgba(0,0,0,0.1);
            }

            th {
                background: var(--primary-color);
                color: white;
                padding: 12px;
                text-align: left;
                font-weight: bold;
            }

            td {
                padding: 10px 12px;
                border-bottom: 1px solid #ddd;
            }

            tr:nth-child(even) {
                background: var(--light-bg);
            }

            tr:hover {
                background: #d5dbdb;
            }

            /* Form styling */
            input[type="text"], 
            input[type="password"], 
            input[type="email"], 
            select, 
            textarea {
                padding: 8px 12px;
                border: 2px solid #bdc3c7;
                border-radius: 4px;
                font-size: 14px;
                transition: border-color 0.3s;
            }

            input:focus, select:focus, textarea:focus {
                outline: none;
                border-color: var(--secondary-color);
            }

            /* Button styling */
            input[type="submit"], 
            button {
                background: var(--secondary-color);
                color: white;
                padding: 10px 20px;
                border: none;
                border-radius: 4px;
                cursor: pointer;
                font-size: 14px;
                transition: background 0.3s;
            }

            input[type="submit"]:hover, 
            button:hover {
                background: #2980b9;
            }

            /* Success/Error messages */
            .message {
                padding: 15px;
                margin: 10px 0;
                border-radius: 4px;
                font-weight: bold;
            }

            .success {
                background: #d4edda;
                color: #155724;
                border: 1px solid #c3e6cb;
            }

            .error {
                background: #f8d7da;
                color: #721c24;
                border: 1px solid #f5c6cb;
            }

            /* SQL editor styling */
            #sql {
                font-family: "Monaco", "Menlo", "Ubuntu Mono", monospace;
                font-size: 14px;
                line-height: 1.5;
                border: 2px solid #bdc3c7;
                border-radius: 4px;
                padding: 15px;
                background: #f8f9fa;
            }

            /* Responsive design */
            @media (max-width: 768px) {
                table {
                    font-size: 12px;
                }

                th, td {
                    padding: 8px;
                }

                #menu a {
                    padding: 8px 10px;
                    font-size: 14px;
                }
            }
        </style>';

        // Add custom JavaScript
        echo '<script>
            // Auto-refresh for long-running queries
            function autoRefresh() {
                if (document.querySelector(".loading")) {
                    setTimeout(function() {
                        location.reload();
                    }, 5000);
                }
            }

            // Confirm dangerous operations
            function confirmDangerous(action) {
                return confirm("Are you sure you want to " + action + "? This action cannot be undone.");
            }

            // Add confirmation to drop/truncate buttons
            document.addEventListener("DOMContentLoaded", function() {
                var dangerousButtons = document.querySelectorAll("input[value*=\"Drop\"], input[value*=\"Truncate\"]");
                dangerousButtons.forEach(function(button) {
                    button.onclick = function() {
                        return confirmDangerous(this.value.toLowerCase());
                    };
                });

                autoRefresh();
            });
        </script>';
    }

    // Custom page title
    function name() {
        return 'MyApp Database Administration';
    }

    // Custom navigation
    function navigation($missing) {
        echo '<div style="padding: 10px; background: #ecf0f1; margin-bottom: 20px;">';
        echo '<strong>Database:</strong> ' . h(DB);
        echo ' | <strong>Server:</strong> ' . h(SERVER);
        echo ' | <strong>User:</strong> ' . h($this->username());
        echo '</div>';

        return parent::navigation($missing);
    }
}
?>

Plugin Development¶

<?php
// Custom plugin example: Enhanced JSON editor
class JsonEditorPlugin {

    function __construct() {
        // Register plugin hooks
        add_action('adminer_head', array($this, 'addJsonEditorAssets'));
        add_filter('adminer_edit_input', array($this, 'enhanceJsonFields'));
    }

    function addJsonEditorAssets() {
        echo '<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jsoneditor/9.10.2/jsoneditor.min.css">';
        echo '<script src="https://cdnjs.cloudflare.com/ajax/libs/jsoneditor/9.10.2/jsoneditor.min.js"></script>';

        echo '<script>
            function initJsonEditor(textarea) {
                var container = document.createElement("div");
                container.style.height = "300px";
                container.style.width = "100%";

                textarea.parentNode.insertBefore(container, textarea);
                textarea.style.display = "none";

                var options = {
                    mode: "tree",
                    modes: ["code", "tree", "view"],
                    onChange: function() {
                        try {
                            textarea.value = JSON.stringify(editor.get());
                        } catch (e) {
                            // Invalid JSON, keep original value
                        }
                    }
                };

                var editor = new JSONEditor(container, options);

                try {
                    var json = JSON.parse(textarea.value || "{}");
                    editor.set(json);
                } catch (e) {
                    editor.set({});
                }

                return editor;
            }

            document.addEventListener("DOMContentLoaded", function() {
                var jsonFields = document.querySelectorAll("textarea[data-type=\"json\"]");
                jsonFields.forEach(function(field) {
                    initJsonEditor(field);
                });
            });
        </script>';
    }

    function enhanceJsonFields($input, $field, $value) {
        if (in_array(strtolower($field['type']), ['json', 'jsonb'])) {
            return '<textarea name="' . h($field['field']) . '" data-type="json" rows="10" cols="50">' . h($value) . '</textarea>';
        }

        return $input;
    }
}

// Custom plugin: Query performance analyzer
class QueryPerformancePlugin {

    private $query_start_time;
    private $query_log = [];

    function __construct() {
        add_action('adminer_query_start', array($this, 'startQueryTimer'));
        add_action('adminer_query_end', array($this, 'endQueryTimer'));
        add_action('adminer_footer', array($this, 'displayPerformanceStats'));
    }

    function startQueryTimer($query) {
        $this->query_start_time = microtime(true);
    }

    function endQueryTimer($query, $result) {
        $execution_time = microtime(true) - $this->query_start_time;

        $this->query_log[] = [
            'query' => $query,
            'execution_time' => $execution_time,
            'rows_affected' => is_object($result) ? $result->rowCount() : 0,
            'timestamp' => date('H:i:s')
        ];
    }

    function displayPerformanceStats() {
        if (empty($this->query_log)) {
            return;
        }

        echo '<div style="margin-top: 20px; padding: 15px; background: #f8f9fa; border: 1px solid #dee2e6; border-radius: 4px;">';
        echo '<h3>Query Performance Statistics</h3>';
        echo '<table style="width: 100%; font-size: 12px;">';
        echo '<tr><th>Time</th><th>Query</th><th>Execution Time</th><th>Rows</th></tr>';

        foreach ($this->query_log as $log) {
            $query_preview = strlen($log['query']) > 100 ? 
                substr($log['query'], 0, 100) . '...' : 
                $log['query'];

            $time_class = $log['execution_time'] > 1 ? 'style="color: red; font-weight: bold;"' : '';

            echo '<tr>';
            echo '<td>' . h($log['timestamp']) . '</td>';
            echo '<td title="' . h($log['query']) . '">' . h($query_preview) . '</td>';
            echo '<td ' . $time_class . '>' . number_format($log['execution_time'], 4) . 's</td>';
            echo '<td>' . number_format($log['rows_affected']) . '</td>';
            echo '</tr>';
        }

        echo '</table>';

        $total_time = array_sum(array_column($this->query_log, 'execution_time'));
        $avg_time = $total_time / count($this->query_log);

        echo '<p><strong>Total Queries:</strong> ' . count($this->query_log) . ' | ';
        echo '<strong>Total Time:</strong> ' . number_format($total_time, 4) . 's | ';
        echo '<strong>Average Time:</strong> ' . number_format($avg_time, 4) . 's</p>';
        echo '</div>';
    }
}

// Initialize plugins
new JsonEditorPlugin();
new QueryPerformancePlugin();
?>

Best Practices¶

Performance Optimization¶

<?php
// Performance-optimized Adminer configuration
class PerformanceAdminer extends Adminer {

    // Limit result sets to prevent memory issues
    function selectLimit() {
        return 1000; // Default limit for SELECT queries
    }

    // Optimize table listing
    function tablesPrint($tables) {
        // Cache table information
        static $table_cache = [];

        if (empty($table_cache)) {
            foreach ($tables as $table => $type) {
                $table_cache[$table] = [
                    'type' => $type,
                    'rows' => $this->getTableRowCount($table),
                    'size' => $this->getTableSize($table)
                ];
            }
        }

        return parent::tablesPrint($tables);
    }

    private function getTableRowCount($table) {
        static $row_counts = [];

        if (!isset($row_counts[$table])) {
            try {
                $result = $this->connection->query("SELECT COUNT(*) FROM " . idf_escape($table));
                $row_counts[$table] = $result->fetchColumn();
            } catch (Exception $e) {
                $row_counts[$table] = 'N/A';
            }
        }

        return $row_counts[$table];
    }

    private function getTableSize($table) {
        static $table_sizes = [];

        if (!isset($table_sizes[$table])) {
            try {
                $query = "SELECT 
                    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
                    FROM information_schema.tables 
                    WHERE table_schema = DATABASE() 
                    AND table_name = " . q($table);

                $result = $this->connection->query($query);
                $table_sizes[$table] = $result->fetchColumn() . ' MB';
            } catch (Exception $e) {
                $table_sizes[$table] = 'N/A';
            }
        }

        return $table_sizes[$table];
    }

    // Optimize query execution
    function query($query, $start = null) {
        // Add query timeout
        if ($this->connection instanceof PDO) {
            $this->connection->setAttribute(PDO::ATTR_TIMEOUT, 30);
        }

        // Log slow queries
        $start_time = microtime(true);
        $result = parent::query($query, $start);
        $execution_time = microtime(true) - $start_time;

        if ($execution_time > 5) { // Log queries taking more than 5 seconds
            $this->logSlowQuery($query, $execution_time);
        }

        return $result;
    }

    private function logSlowQuery($query, $execution_time) {
        $log_entry = [
            'timestamp' => date('Y-m-d H:i:s'),
            'execution_time' => $execution_time,
            'query' => $query,
            'user' => $this->username(),
            'database' => DB
        ];

        file_put_contents(
            '/var/log/adminer/slow_queries.log',
            json_encode($log_entry) . "\n",
            FILE_APPEND | LOCK_EX
        );
    }

    // Memory optimization
    function selectOrderPrint($order, $columns, $indexes) {
        // Limit memory usage for large result sets
        if (ini_get('memory_limit') !== '-1') {
            $memory_limit = $this->parseMemoryLimit(ini_get('memory_limit'));
            $current_usage = memory_get_usage(true);

            if ($current_usage > $memory_limit * 0.8) {
                echo '<div class="message error">Warning: High memory usage detected. Consider limiting your query results.</div>';
            }
        }

        return parent::selectOrderPrint($order, $columns, $indexes);
    }

    private function parseMemoryLimit($limit) {
        $limit = trim($limit);
        $last = strtolower($limit[strlen($limit)-1]);
        $limit = (int) $limit;

        switch($last) {
            case 'g': $limit *= 1024;
            case 'm': $limit *= 1024;
            case 'k': $limit *= 1024;
        }

        return $limit;
    }
}
?>

Security Hardening¶

<?php
// Security-hardened Adminer configuration
class SecureAdminer extends Adminer {

    // CSRF protection
    private $csrf_token;

    function __construct() {
        session_start();

        // Generate CSRF token
        if (!isset($_SESSION['csrf_token'])) {
            $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
        }
        $this->csrf_token = $_SESSION['csrf_token'];

        // Validate CSRF token for POST requests
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $submitted_token = $_POST['csrf_token'] ?? '';
            if (!hash_equals($this->csrf_token, $submitted_token)) {
                http_response_code(403);
                die('CSRF token validation failed');
            }
        }
    }

    // Add CSRF token to forms
    function head() {
        parent::head();

        echo '<script>
            document.addEventListener("DOMContentLoaded", function() {
                var forms = document.querySelectorAll("form");
                forms.forEach(function(form) {
                    if (form.method.toLowerCase() === "post") {
                        var csrfInput = document.createElement("input");
                        csrfInput.type = "hidden";
                        csrfInput.name = "csrf_token";
                        csrfInput.value = "' . $this->csrf_token . '";
                        form.appendChild(csrfInput);
                    }
                });
            });
        </script>';
    }

    // Input sanitization
    function editInput($table, $field, $attrs, $value) {
        // Sanitize input values
        if (is_string($value)) {
            $value = htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
        }

        return parent::editInput($table, $field, $attrs, $value);
    }

    // SQL injection prevention
    function query($query, $start = null) {
        // Block potentially dangerous queries
        $dangerous_patterns = [
            '/\b(LOAD_FILE|INTO\s+OUTFILE|INTO\s+DUMPFILE)\b/i',
            '/\b(UNION\s+SELECT.*FROM\s+information_schema)\b/i',
            '/\b(SELECT.*FROM\s+mysql\.user)\b/i',
            '/\b(BENCHMARK|SLEEP)\s*\(/i'
        ];

        foreach ($dangerous_patterns as $pattern) {
            if (preg_match($pattern, $query)) {
                throw new Exception('Potentially dangerous query blocked');
            }
        }

        return parent::query($query, $start);
    }

    // Session security
    function loginForm() {
        // Regenerate session ID on login form display
        session_regenerate_id(true);

        return parent::loginForm();
    }

    // Secure headers
    function headers() {
        // Security headers
        header('X-Content-Type-Options: nosniff');
        header('X-Frame-Options: DENY');
        header('X-XSS-Protection: 1; mode=block');
        header('Referrer-Policy: strict-origin-when-cross-origin');
        header('Content-Security-Policy: default-src \'self\'; script-src \'self\' \'unsafe-inline\'; style-src \'self\' \'unsafe-inline\'');

        // Prevent caching of sensitive pages
        header('Cache-Control: no-cache, no-store, must-revalidate');
        header('Pragma: no-cache');
        header('Expires: 0');

        return parent::headers();
    }

    // Audit logging
    function query($query, $start = null) {
        $result = parent::query($query, $start);

        // Log all database operations
        $this->auditLog([
            'action' => 'query',
            'query' => $query,
            'user' => $this->username(),
            'ip' => $_SERVER['REMOTE_ADDR'],
            'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? '',
            'timestamp' => date('Y-m-d H:i:s'),
            'database' => DB,
            'success' => $result !== false
        ]);

        return $result;
    }

    private function auditLog($data) {
        $log_file = '/var/log/adminer/audit.log';
        $log_entry = json_encode($data) . "\n";

        file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);

        // Also log to syslog for centralized logging
        syslog(LOG_INFO, "Adminer: " . json_encode($data));
    }
}

// Use secure configuration
new SecureAdminer;
?>

Maintenance and Monitoring¶

#!/bin/bash
# Adminer maintenance script

# Configuration
ADMINER_DIR="/var/www/adminer"
LOG_DIR="/var/log/adminer"
BACKUP_DIR="/backups/adminer"
DATE=$(date +%Y%m%d_%H%M%S)

# Create necessary directories
mkdir -p $LOG_DIR $BACKUP_DIR

# Log rotation
find $LOG_DIR -name "*.log" -size +100M -exec gzip {} \;
find $LOG_DIR -name "*.log.gz" -mtime +30 -delete

# Security scan
echo "Running security scan..."
grep -i "error\|warning\|failed" $LOG_DIR/audit.log | tail -20

# Check for suspicious activity
echo "Checking for suspicious activity..."
awk '{print $1}' $LOG_DIR/audit.log | sort | uniq -c | sort -nr | head -10

# Performance monitoring
echo "Performance statistics:"
awk '/execution_time/ {sum+=$2; count++} END {print "Average query time:", sum/count "s"}' $LOG_DIR/slow_queries.log

# Disk space check
echo "Disk space usage:"
df -h $ADMINER_DIR
du -sh $LOG_DIR

# Update check
echo "Checking for Adminer updates..."
CURRENT_VERSION=$(grep -o 'version.*[0-9]\+\.[0-9]\+\.[0-9]\+' $ADMINER_DIR/adminer.php | head -1)
echo "Current version: $CURRENT_VERSION"

# Backup configuration
echo "Creating configuration backup..."
tar -czf $BACKUP_DIR/adminer_config_$DATE.tar.gz $ADMINER_DIR/*.php

# Clean old backups
find $BACKUP_DIR -name "adminer_config_*.tar.gz" -mtime +7 -delete

echo "Maintenance completed at $(date)"

Summary¶

Adminer is a lightweight, powerful database management tool that provides comprehensive database administration capabilities through a single PHP file. This cheatsheet covers essential features, security practices, and customization options for effective database management.

Key Strengths: - Lightweight: Single PHP file deployment - Multi-Database Support: Works with MySQL, PostgreSQL, SQLite, and more - User-Friendly Interface: Intuitive web-based interface - Extensible: Plugin system for additional functionality - Secure: Built-in security features and customization options

Best Use Cases: - Quick database administration tasks - Shared hosting environments - Development and testing - Remote database management - Lightweight alternative to phpMyAdmin

Important Considerations: - Security configuration is crucial for production use - Performance optimization needed for large databases - Regular updates recommended for security patches - Access control and IP restrictions should be implemented - Audit logging essential for compliance and security monitoring

By following the practices and techniques outlined in this cheatsheet, you can effectively use Adminer to manage databases while maintaining security, performance, and reliability in your database operations.