Zum Inhalt

Adminer Cheatsheet

Adminer - Database Management Tool

Adminer (früher phpMinAdmin) ist ein umfassendes Datenbankverwaltungstool, das in PHP geschrieben wurde. Es besteht aus einer einzelnen PHP-Datei, die auf dem Zielserver bereitgestellt werden kann. Adminer ist für MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Elasticsearch, MongoDB und mehr verfügbar.

Inhaltsverzeichnis

Installation

Grundlegende 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

Webserver-Konfiguration

# 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-Anforderungen

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

Konfiguration

Grundlegende Konfiguration

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

Umgebungsspezifische Konfiguration

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

Datenbankverbindungen

Verbindungsparameter

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

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

Verbindungs-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();
        }
    }
}
?>

Datenbankoperationen

Datenbankmanagement

-- 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-Operationen

-- 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 und Wiederherstellung

# 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"

Tabellenverwaltung

Tabellenerstellung

-- 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');

Tabellenmodifikation

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

Indexverwaltung

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

Datenoperationen

Dateneinfügung

-- 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();

Datenaktualisierung

Note: I've translated the text content while preserving the markdown formatting, keeping technical terms in English, and maintaining the same structure and punctuation. Placeholders for sections 4-20 are left blank as no specific text was provided for translation.```sql -- 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; ```### Datenlöschung 

-- 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
);
```### Datenabfrage
```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');
```## SQL-Editor
```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;
```### Abfrageausführung
```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;
```### Abfrageoptimierung
```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);
```### Gespeicherte Prozeduren und Funktionen
```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 != '';
```## Import/Export
```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;
```### Datenimport
```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 ### Datenexportsql -- 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; ### Sicherung und Wiederherstellungsql -- 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'; ## Benutzerverwaltungsql -- 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(); ### Benutzererstellung und -verwaltungphp

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

### Berechtigungsverwaltungphp

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

### Sicherheitsrichtlinienapache

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>

## Sicherheitphp

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

### Zugangskontrollephp

'; 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 '
TimeQueryExecution TimeRows
' . 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(); ?>

### Datenschutzphp

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

### SSL/TLS-Konfigurationphp

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

## Anpassungbash

!/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)" ```### Themes und Styling