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
- Konfiguration
- Datenbankverbindungen
- Datenbankoperationen
- Tabellenverwaltung
- Datenoperationen
- SQL-Editor
- Import/Export
- Benutzerverwaltung
- Sicherheit
- Anpassung
- Best Practices
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.phpDocker-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-enabledWebserver-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;
```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
);
```### 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
];
?># Command-line backup scripts
# MySQL backup
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"
DB_NAME="myapp"
mysqldump -u backup_user -p$BACKUP_PASSWORD \
--single-transaction \
--routines \
--triggers \
--events \
--hex-blob \
--add-drop-table \
--add-locks \
$DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql
# Compress backup
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql
# Keep only last 7 days
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete
# PostgreSQL backup
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"
DB_NAME="myapp"
pg_dump -U backup_user -h localhost \
--verbose \
--clean \
--create \
--if-exists \
--format=custom \
--compress=9 \
$DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.backup
# Keep only last 7 days
find $BACKUP_DIR -name "${DB_NAME}_*.backup" -mtime +7 -delete
# Restore MySQL
mysql -u username -p myapp < backup_file.sql
# Restore PostgreSQL
pg_restore -U username -h localhost -d myapp backup_file.backup
```### Datenexport
```sql
-- 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 Wiederherstellung
```sql
-- 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';
```## Benutzerverwaltung
```sql
-- 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 -verwaltung
```php
<?php
// Secure Adminer configuration
class SecureAdminer extends Adminer {
// IP whitelist
private $allowed_ips = [
'192.168.1.0/24',
'10.0.0.0/8',
'172.16.0.0/12'
];
// Check IP access
function __construct() {
if (!$this->isAllowedIP()) {
http_response_code(403);
die('Access denied');
}
}
private function isAllowedIP() {
$client_ip = $_SERVER['REMOTE_ADDR'];
foreach ($this->allowed_ips as $allowed_range) {
if ($this->ipInRange($client_ip, $allowed_range)) {
return true;
}
}
return false;
}
private function ipInRange($ip, $range) {
if (strpos($range, '/') === false) {
return $ip === $range;
}
list($subnet, $mask) = explode('/', $range);
return (ip2long($ip) & ~((1 << (32 - $mask)) - 1)) === ip2long($subnet);
}
// Secure login
function login($login, $password) {
// Rate limiting
session_start();
$attempts_key = 'login_attempts_' . $_SERVER['REMOTE_ADDR'];
$attempts = $_SESSION[$attempts_key] ?? 0;
if ($attempts >= 5) {
$last_attempt = $_SESSION['last_attempt_' . $_SERVER['REMOTE_ADDR']] ?? 0;
if (time() - $last_attempt < 300) { // 5 minutes lockout
return false;
} else {
$_SESSION[$attempts_key] = 0; // Reset attempts
}
}
// Validate credentials
$valid_users = [
'admin' => '$2y$10$hash_for_admin_password',
'readonly' => '$2y$10$hash_for_readonly_password'
];
if (isset($valid_users[$login]) && password_verify($password, $valid_users[$login])) {
$_SESSION[$attempts_key] = 0; // Reset on successful login
$_SESSION['user_role'] = $this->getUserRole($login);
return true;
} else {
$_SESSION[$attempts_key] = $attempts + 1;
$_SESSION['last_attempt_' . $_SERVER['REMOTE_ADDR']] = time();
return false;
}
}
private function getUserRole($login) {
$roles = [
'admin' => 'admin',
'readonly' => 'readonly'
];
return $roles[$login] ?? 'readonly';
}
// Restrict operations based on role
function drop() {
return $_SESSION['user_role'] === 'admin';
}
function alter() {
return $_SESSION['user_role'] === 'admin';
}
function create() {
return $_SESSION['user_role'] === 'admin';
}
// Hide sensitive databases
function databases($flush = true) {
$all_databases = parent::databases($flush);
$allowed_databases = [
'myapp_prod',
'myapp_staging',
'analytics'
];
return array_intersect($all_databases, $allowed_databases);
}
// Log all activities
function query($query, $start = null) {
$result = parent::query($query, $start);
// Log query
$log_entry = [
'timestamp' => date('Y-m-d H:i:s'),
'user' => $this->username(),
'ip' => $_SERVER['REMOTE_ADDR'],
'query' => $query,
'execution_time' => $start ? (microtime(true) - $start) : null
];
file_put_contents(
'/var/log/adminer/queries.log',
json_encode($log_entry) . "\n",
FILE_APPEND | LOCK_EX
);
return $result;
}
}
// Use secure configuration
new SecureAdminer;
?>
```### Berechtigungsverwaltung
```php
<?php
// Data masking for sensitive information
class DataMaskingAdminer extends Adminer {
private $sensitive_fields = [
'password',
'password_hash',
'ssn',
'credit_card',
'phone',
'email'
];
// Mask sensitive data in select results
function selectVal($val, $link, $field, $original) {
if (in_array(strtolower($field['field']), $this->sensitive_fields)) {
if ($field['field'] === 'email') {
return $this->maskEmail($val);
} elseif ($field['field'] === 'phone') {
return $this->maskPhone($val);
} else {
return str_repeat('*', min(strlen($val), 8));
}
}
return parent::selectVal($val, $link, $field, $original);
}
private function maskEmail($email) {
if (strpos($email, '@') === false) {
return $email;
}
list($local, $domain) = explode('@', $email);
$masked_local = substr($local, 0, 2) . str_repeat('*', max(0, strlen($local) - 2));
return $masked_local . '@' . $domain;
}
private function maskPhone($phone) {
$cleaned = preg_replace('/[^0-9]/', '', $phone);
if (strlen($cleaned) >= 4) {
return str_repeat('*', strlen($cleaned) - 4) . substr($cleaned, -4);
}
return str_repeat('*', strlen($cleaned));
}
// Prevent export of sensitive data
function dumpTable($table, $style, $is_view = 0) {
if ($style === 'INSERT' && $this->hasSensitiveFields($table)) {
echo "-- Export of sensitive table '$table' is restricted\n";
return false;
}
return parent::dumpTable($table, $style, $is_view);
}
private function hasSensitiveFields($table) {
$fields = fields($table);
foreach ($fields as $field => $info) {
if (in_array(strtolower($field), $this->sensitive_fields)) {
return true;
}
}
return false;
}
}
?>
```### Sicherheitsrichtlinien
```apache
# Apache SSL configuration for Adminer
<VirtualHost *:443>
ServerName adminer.example.com
DocumentRoot /var/www/adminer
SSLEngine on
SSLCertificateFile /etc/ssl/certs/adminer.crt
SSLCertificateKeyFile /etc/ssl/private/adminer.key
SSLCertificateChainFile /etc/ssl/certs/ca-bundle.crt
# Strong SSL configuration
SSLProtocol all -SSLv2 -SSLv3 -TLSv1 -TLSv1.1
SSLCipherSuite ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384
SSLHonorCipherOrder on
# Security headers
Header always set Strict-Transport-Security "max-age=31536000; includeSubDomains"
Header always set X-Content-Type-Options nosniff
Header always set X-Frame-Options DENY
Header always set X-XSS-Protection "1; mode=block"
Header always set Content-Security-Policy "default-src 'self'; script-src 'self' 'unsafe-inline'; style-src 'self' 'unsafe-inline'"
# Client certificate authentication (optional)
SSLVerifyClient require
SSLVerifyDepth 2
SSLCACertificateFile /etc/ssl/certs/client-ca.crt
<Directory /var/www/adminer>
Options -Indexes
AllowOverride All
Require all granted
# Additional security
php_admin_value open_basedir "/var/www/adminer:/tmp"
php_admin_flag allow_url_fopen off
php_admin_flag allow_url_include off
</Directory>
</VirtualHost>
```## Sicherheit
```php
<?php
// Custom theme for Adminer
class AdminerCustomTheme extends Adminer {
function head() {
echo '<style>
/* Custom color scheme */
:root {
--primary-color: #2c3e50;
--secondary-color: #3498db;
--success-color: #27ae60;
--warning-color: #f39c12;
--danger-color: #e74c3c;
--light-bg: #ecf0f1;
--dark-text: #2c3e50;
}
/* Header styling */
#menu {
background: var(--primary-color);
border-bottom: 3px solid var(--secondary-color);
}
#menu a {
color: white;
text-decoration: none;
padding: 10px 15px;
display: inline-block;
transition: background 0.3s;
}
#menu a:hover {
background: var(--secondary-color);
}
/* Table styling */
table {
border-collapse: collapse;
width: 100%;
margin: 20px 0;
box-shadow: 0 2px 5px rgba(0,0,0,0.1);
}
th {
background: var(--primary-color);
color: white;
padding: 12px;
text-align: left;
font-weight: bold;
}
td {
padding: 10px 12px;
border-bottom: 1px solid #ddd;
}
tr:nth-child(even) {
background: var(--light-bg);
}
tr:hover {
background: #d5dbdb;
}
/* Form styling */
input[type="text"],
input[type="password"],
input[type="email"],
select,
textarea {
padding: 8px 12px;
border: 2px solid #bdc3c7;
border-radius: 4px;
font-size: 14px;
transition: border-color 0.3s;
}
input:focus, select:focus, textarea:focus {
outline: none;
border-color: var(--secondary-color);
}
/* Button styling */
input[type="submit"],
button {
background: var(--secondary-color);
color: white;
padding: 10px 20px;
border: none;
border-radius: 4px;
cursor: pointer;
font-size: 14px;
transition: background 0.3s;
}
input[type="submit"]:hover,
button:hover {
background: #2980b9;
}
/* Success/Error messages */
.message {
padding: 15px;
margin: 10px 0;
border-radius: 4px;
font-weight: bold;
}
.success {
background: #d4edda;
color: #155724;
border: 1px solid #c3e6cb;
}
.error {
background: #f8d7da;
color: #721c24;
border: 1px solid #f5c6cb;
}
/* SQL editor styling */
#sql {
font-family: "Monaco", "Menlo", "Ubuntu Mono", monospace;
font-size: 14px;
line-height: 1.5;
border: 2px solid #bdc3c7;
border-radius: 4px;
padding: 15px;
background: #f8f9fa;
}
/* Responsive design */
@media (max-width: 768px) {
table {
font-size: 12px;
}
th, td {
padding: 8px;
}
#menu a {
padding: 8px 10px;
font-size: 14px;
}
}
</style>';
// Add custom JavaScript
echo '<script>
// Auto-refresh for long-running queries
function autoRefresh() {
if (document.querySelector(".loading")) {
setTimeout(function() {
location.reload();
}, 5000);
}
}
// Confirm dangerous operations
function confirmDangerous(action) {
return confirm("Are you sure you want to " + action + "? This action cannot be undone.");
}
// Add confirmation to drop/truncate buttons
document.addEventListener("DOMContentLoaded", function() {
var dangerousButtons = document.querySelectorAll("input[value*=\"Drop\"], input[value*=\"Truncate\"]");
dangerousButtons.forEach(function(button) {
button.onclick = function() {
return confirmDangerous(this.value.toLowerCase());
};
});
autoRefresh();
});
</script>';
}
// Custom page title
function name() {
return 'MyApp Database Administration';
}
// Custom navigation
function navigation($missing) {
echo '<div style="padding: 10px; background: #ecf0f1; margin-bottom: 20px;">';
echo '<strong>Database:</strong> ' . h(DB);
echo ' | <strong>Server:</strong> ' . h(SERVER);
echo ' | <strong>User:</strong> ' . h($this->username());
echo '</div>';
return parent::navigation($missing);
}
}
?>
```### Zugangskontrolle
```php
<?php
// Custom plugin example: Enhanced JSON editor
class JsonEditorPlugin {
function __construct() {
// Register plugin hooks
add_action('adminer_head', array($this, 'addJsonEditorAssets'));
add_filter('adminer_edit_input', array($this, 'enhanceJsonFields'));
}
function addJsonEditorAssets() {
echo '<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jsoneditor/9.10.2/jsoneditor.min.css">';
echo '<script src="https://cdnjs.cloudflare.com/ajax/libs/jsoneditor/9.10.2/jsoneditor.min.js"></script>';
echo '<script>
function initJsonEditor(textarea) {
var container = document.createElement("div");
container.style.height = "300px";
container.style.width = "100%";
textarea.parentNode.insertBefore(container, textarea);
textarea.style.display = "none";
var options = {
mode: "tree",
modes: ["code", "tree", "view"],
onChange: function() {
try {
textarea.value = JSON.stringify(editor.get());
} catch (e) {
// Invalid JSON, keep original value
}
}
};
var editor = new JSONEditor(container, options);
try {
var json = JSON.parse(textarea.value || "{}");
editor.set(json);
} catch (e) {
editor.set({});
}
return editor;
}
document.addEventListener("DOMContentLoaded", function() {
var jsonFields = document.querySelectorAll("textarea[data-type=\"json\"]");
jsonFields.forEach(function(field) {
initJsonEditor(field);
});
});
</script>';
}
function enhanceJsonFields($input, $field, $value) {
if (in_array(strtolower($field['type']), ['json', 'jsonb'])) {
return '<textarea name="' . h($field['field']) . '" data-type="json" rows="10" cols="50">' . h($value) . '</textarea>';
}
return $input;
}
}
// Custom plugin: Query performance analyzer
class QueryPerformancePlugin {
private $query_start_time;
private $query_log = [];
function __construct() {
add_action('adminer_query_start', array($this, 'startQueryTimer'));
add_action('adminer_query_end', array($this, 'endQueryTimer'));
add_action('adminer_footer', array($this, 'displayPerformanceStats'));
}
function startQueryTimer($query) {
$this->query_start_time = microtime(true);
}
function endQueryTimer($query, $result) {
$execution_time = microtime(true) - $this->query_start_time;
$this->query_log[] = [
'query' => $query,
'execution_time' => $execution_time,
'rows_affected' => is_object($result) ? $result->rowCount() : 0,
'timestamp' => date('H:i:s')
];
}
function displayPerformanceStats() {
if (empty($this->query_log)) {
return;
}
echo '<div style="margin-top: 20px; padding: 15px; background: #f8f9fa; border: 1px solid #dee2e6; border-radius: 4px;">';
echo '<h3>Query Performance Statistics</h3>';
echo '<table style="width: 100%; font-size: 12px;">';
echo '<tr><th>Time</th><th>Query</th><th>Execution Time</th><th>Rows</th></tr>';
foreach ($this->query_log as $log) {
$query_preview = strlen($log['query']) > 100 ?
substr($log['query'], 0, 100) . '...' :
$log['query'];
$time_class = $log['execution_time'] > 1 ? 'style="color: red; font-weight: bold;"' : '';
echo '<tr>';
echo '<td>' . h($log['timestamp']) . '</td>';
echo '<td title="' . h($log['query']) . '">' . h($query_preview) . '</td>';
echo '<td ' . $time_class . '>' . number_format($log['execution_time'], 4) . 's</td>';
echo '<td>' . number_format($log['rows_affected']) . '</td>';
echo '</tr>';
}
echo '</table>';
$total_time = array_sum(array_column($this->query_log, 'execution_time'));
$avg_time = $total_time / count($this->query_log);
echo '<p><strong>Total Queries:</strong> ' . count($this->query_log) . ' | ';
echo '<strong>Total Time:</strong> ' . number_format($total_time, 4) . 's | ';
echo '<strong>Average Time:</strong> ' . number_format($avg_time, 4) . 's</p>';
echo '</div>';
}
}
// Initialize plugins
new JsonEditorPlugin();
new QueryPerformancePlugin();
?>
```### Datenschutz
```php
<?php
// Performance-optimized Adminer configuration
class PerformanceAdminer extends Adminer {
// Limit result sets to prevent memory issues
function selectLimit() {
return 1000; // Default limit for SELECT queries
}
// Optimize table listing
function tablesPrint($tables) {
// Cache table information
static $table_cache = [];
if (empty($table_cache)) {
foreach ($tables as $table => $type) {
$table_cache[$table] = [
'type' => $type,
'rows' => $this->getTableRowCount($table),
'size' => $this->getTableSize($table)
];
}
}
return parent::tablesPrint($tables);
}
private function getTableRowCount($table) {
static $row_counts = [];
if (!isset($row_counts[$table])) {
try {
$result = $this->connection->query("SELECT COUNT(*) FROM " . idf_escape($table));
$row_counts[$table] = $result->fetchColumn();
} catch (Exception $e) {
$row_counts[$table] = 'N/A';
}
}
return $row_counts[$table];
}
private function getTableSize($table) {
static $table_sizes = [];
if (!isset($table_sizes[$table])) {
try {
$query = "SELECT
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name = " . q($table);
$result = $this->connection->query($query);
$table_sizes[$table] = $result->fetchColumn() . ' MB';
} catch (Exception $e) {
$table_sizes[$table] = 'N/A';
}
}
return $table_sizes[$table];
}
// Optimize query execution
function query($query, $start = null) {
// Add query timeout
if ($this->connection instanceof PDO) {
$this->connection->setAttribute(PDO::ATTR_TIMEOUT, 30);
}
// Log slow queries
$start_time = microtime(true);
$result = parent::query($query, $start);
$execution_time = microtime(true) - $start_time;
if ($execution_time > 5) { // Log queries taking more than 5 seconds
$this->logSlowQuery($query, $execution_time);
}
return $result;
}
private function logSlowQuery($query, $execution_time) {
$log_entry = [
'timestamp' => date('Y-m-d H:i:s'),
'execution_time' => $execution_time,
'query' => $query,
'user' => $this->username(),
'database' => DB
];
file_put_contents(
'/var/log/adminer/slow_queries.log',
json_encode($log_entry) . "\n",
FILE_APPEND | LOCK_EX
);
}
// Memory optimization
function selectOrderPrint($order, $columns, $indexes) {
// Limit memory usage for large result sets
if (ini_get('memory_limit') !== '-1') {
$memory_limit = $this->parseMemoryLimit(ini_get('memory_limit'));
$current_usage = memory_get_usage(true);
if ($current_usage > $memory_limit * 0.8) {
echo '<div class="message error">Warning: High memory usage detected. Consider limiting your query results.</div>';
}
}
return parent::selectOrderPrint($order, $columns, $indexes);
}
private function parseMemoryLimit($limit) {
$limit = trim($limit);
$last = strtolower($limit[strlen($limit)-1]);
$limit = (int) $limit;
switch($last) {
case 'g': $limit *= 1024;
case 'm': $limit *= 1024;
case 'k': $limit *= 1024;
}
return $limit;
}
}
?>
```### SSL/TLS-Konfiguration
```php
<?php
// Security-hardened Adminer configuration
class SecureAdminer extends Adminer {
// CSRF protection
private $csrf_token;
function __construct() {
session_start();
// Generate CSRF token
if (!isset($_SESSION['csrf_token'])) {
$_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}
$this->csrf_token = $_SESSION['csrf_token'];
// Validate CSRF token for POST requests
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$submitted_token = $_POST['csrf_token'] ?? '';
if (!hash_equals($this->csrf_token, $submitted_token)) {
http_response_code(403);
die('CSRF token validation failed');
}
}
}
// Add CSRF token to forms
function head() {
parent::head();
echo '<script>
document.addEventListener("DOMContentLoaded", function() {
var forms = document.querySelectorAll("form");
forms.forEach(function(form) {
if (form.method.toLowerCase() === "post") {
var csrfInput = document.createElement("input");
csrfInput.type = "hidden";
csrfInput.name = "csrf_token";
csrfInput.value = "' . $this->csrf_token . '";
form.appendChild(csrfInput);
}
});
});
</script>';
}
// Input sanitization
function editInput($table, $field, $attrs, $value) {
// Sanitize input values
if (is_string($value)) {
$value = htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
}
return parent::editInput($table, $field, $attrs, $value);
}
// SQL injection prevention
function query($query, $start = null) {
// Block potentially dangerous queries
$dangerous_patterns = [
'/\b(LOAD_FILE|INTO\s+OUTFILE|INTO\s+DUMPFILE)\b/i',
'/\b(UNION\s+SELECT.*FROM\s+information_schema)\b/i',
'/\b(SELECT.*FROM\s+mysql\.user)\b/i',
'/\b(BENCHMARK|SLEEP)\s*\(/i'
];
foreach ($dangerous_patterns as $pattern) {
if (preg_match($pattern, $query)) {
throw new Exception('Potentially dangerous query blocked');
}
}
return parent::query($query, $start);
}
// Session security
function loginForm() {
// Regenerate session ID on login form display
session_regenerate_id(true);
return parent::loginForm();
}
// Secure headers
function headers() {
// Security headers
header('X-Content-Type-Options: nosniff');
header('X-Frame-Options: DENY');
header('X-XSS-Protection: 1; mode=block');
header('Referrer-Policy: strict-origin-when-cross-origin');
header('Content-Security-Policy: default-src \'self\'; script-src \'self\' \'unsafe-inline\'; style-src \'self\' \'unsafe-inline\'');
// Prevent caching of sensitive pages
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');
return parent::headers();
}
// Audit logging
function query($query, $start = null) {
$result = parent::query($query, $start);
// Log all database operations
$this->auditLog([
'action' => 'query',
'query' => $query,
'user' => $this->username(),
'ip' => $_SERVER['REMOTE_ADDR'],
'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? '',
'timestamp' => date('Y-m-d H:i:s'),
'database' => DB,
'success' => $result !== false
]);
return $result;
}
private function auditLog($data) {
$log_file = '/var/log/adminer/audit.log';
$log_entry = json_encode($data) . "\n";
file_put_contents($log_file, $log_entry, FILE_APPEND | LOCK_EX);
// Also log to syslog for centralized logging
syslog(LOG_INFO, "Adminer: " . json_encode($data));
}
}
// Use secure configuration
new SecureAdminer;
?>
```## Anpassung
```bash
#!/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