Hoja de Referencia de Adminer¶

Adminer - Database Management Tool

Adminer (anteriormente phpMinAdmin) es una herramienta completa de gestión de bases de datos escrita en PHP. Consiste en un único archivo PHP listo para implementar en el servidor de destino. Adminer está disponible para MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Elasticsearch, MongoDB y más.

The rest of the document would follow the same pattern of translation, maintaining the structure and technical terms in English. Would you like me to continue translating the remaining sections?```bash

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
```bash
# 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;
```### Eliminación de Datos
```sql
-- 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
);
```### Consulta de Datos
```sql
-- 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');
```## Editor SQL
```sql
-- 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;
```### Ejecución de Consultas
```sql
-- 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;
```### Optimización de Consultas
```sql
-- 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);
```### Procedimientos Almacenados y Funciones
```php
<?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 != '';
```## Importación/Exportación
```sql
-- 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;
```### Importación de Datos
```php
<?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
];
?>

```bash

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/$_\).sql

Compress backup¶

gzip $BACKUP_DIR/$.sql}_${DATE

Keep only last 7 days¶

find $BACKUP_DIR -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/$_\).backup

Keep only last 7 days¶

find $BACKUP_DIR -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 ### Exportación de Datossql -- 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; ### Copia de Seguridad y Restauraciónsql -- 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'; ## Gestión de Usuariossql -- 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(); ### Creación y Gestión de Usuariosphp

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; ?>

### Gestión de Permisosphp

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; } } ?>

### Políticas de Seguridadapache

Apache SSL configuration for Adminer¶

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>

## Seguridadphp

/* 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; } } '; // Add custom JavaScript echo ''; } // Custom page title function name() { return 'MyApp Database Administration'; } // Custom navigation function navigation($missing) { echo '

'; echo 'Database: ' . h(DB); echo ' | Server: ' . h(SERVER); echo ' | User: ' . h($this->username()); echo '

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

### Control de Accesophp

'; echo ''; echo ''; } function enhanceJsonFields($input, $field, $value) { if (in_array(strtolower($field['type']), ['json', 'jsonb'])) { return ''; } 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 '

'; echo '

Query Performance Statistics

'; echo ''; echo ''; 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 ''; echo ''; echo ''; echo ''; echo ''; echo ''; } echo '

Time	Query	Execution Time	Rows
' . h($log['timestamp']) . '	' . h($query_preview) . '	' . number_format($log['execution_time'], 4) . 's	' . number_format($log['rows_affected']) . '

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

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

'; echo '

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

### Protección de Datosphp

$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 '

Warning: High memory usage detected. Consider limiting your query results.

'; } } 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; } } ?>

### Configuración SSL/TLSphp

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 ''; } // 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; ?>

## Personalizaciónbash

!/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)" ```### Temas y Estilo

Hoja de Referencia de Adminer¶

Adminer - Database Management Tool

Tabla de Contenidos¶

Download latest version¶

Or download specific version¶

Make it accessible via web server¶

Access via browser¶

http://your-server.com/adminer.php¶

Web Server Configuration¶

PHP Requirements¶

Configuration¶

Basic Configuration¶

Environment-Specific Configuration¶

Plugin System¶

Database Connections¶

Connection Parameters¶

SSL/TLS Configuration¶

Connection Pooling¶

Database Operations¶

Database Management¶

Schema Operations¶

Backup and Restore¶

Table Management¶

Table Creation¶

Table Modification¶

Index Management¶

Data Operations¶

Data Insertion¶

Data Updates¶

Command-line backup scripts¶

MySQL backup¶

!/bin/bash¶

Compress backup¶

Keep only last 7 days¶

PostgreSQL backup¶

!/bin/bash¶

Keep only last 7 days¶

Restore MySQL¶

Restore PostgreSQL¶

Apache SSL configuration for Adminer¶

Query Performance Statistics

!/bin/bash¶

Adminer maintenance script¶

Configuration¶

Create necessary directories¶

Log rotation¶

Security scan¶

Check for suspicious activity¶

Performance monitoring¶

Disk space check¶

Update check¶

Backup configuration¶

Clean old backups¶

http://your-server.com/adminer.php ¶