Adminer Cheatsheet¶
Adminer - Datenbank Management Tool
Adminer (früher phpMinAdmin) ist ein vollwertiges Datenbank-Management-Tool, das in PHP geschrieben wird. Es besteht aus einer einzigen PHP-Datei, die bereit ist, auf den Zielserver bereitzustellen. Adminer ist für MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Elasticsearch, MongoDB und vieles mehr verfügbar. < p>
Inhaltsverzeichnis¶
- [Installation](#installation
- [Konfiguration](LINK_1__
- Datenbankverbindungen
- [Datenbankoperationen](LINK_3__
- [Table Management](#table-management_
- [Dateneinsätze](LINK_5__
- [SQL Editor](#sql-editor_
- [Import/Export](#importexport
- (#user-management_)
- [Sicherheit](#security
- (#customization_)
- Beste Praktiken
Installation¶
Einfache Installation¶
```bash
Download latest version¶
wget https://www.adminer.org/latest.php -O adminer.php
Or download specific version¶
wget https://github.com/vrana/adminer/releases/download/v4.8.1/adminer-4.8.1.php -O adminer.php
Make it accessible via web server¶
sudo cp adminer.php /var/www/html/ sudo chown www-data:www-data /var/www/html/adminer.php sudo chmod 644 /var/www/html/adminer.php
Access via browser¶
http://your-server.com/adminer.php¶
```_
Docker Installation¶
```bash
Run Adminer in Docker¶
docker run --name adminer -p 8080:8080 adminer
With specific tag¶
docker run --name adminer -p 8080:8080 adminer:4.8.1
With custom configuration¶
docker run --name adminer \ -p 8080:8080 \ -e ADMINER_DEFAULT_SERVER=mysql \ -e ADMINER_DESIGN=pepa-linha \ adminer
Access at http://localhost:8080¶
Docker Compose setup¶
version: '3.8' services: adminer: image: adminer:4.8.1 restart: always ports: - 8080:8080 environment: ADMINER_DEFAULT_SERVER: mysql ADMINER_DESIGN: pepa-linha volumes: - ./adminer-plugins:/var/www/html/plugins-enabled ```_
Web Server Konfiguration¶
```apache
Apache configuration¶
<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>
```_
```nginx
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
```_
Konfiguration¶
Grundkonfiguration¶
```php
'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 ''; } } // Use custom configuration new AdminerConfig; ?>```_
Umwelt-spezifische Konfiguration¶
```php
'$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
```_
Datenbankverbindungen¶
Verbindungsparameter¶
```php
'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
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"; ?>```_
Anschluss Pooling¶
```php
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(); } } } ?>```_
Datenbanken¶
Datenbankverwaltung¶
```sql -- 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; ```_
Operationen¶
```sql -- 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¶
```bash
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" ```_
Tabelle Management¶
Tabelle Erstellung¶
```sql -- 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'); ```_
Tabelle Modifizierung¶
```sql -- Add column ALTER TABLE users ADD COLUMN phone VARCHAR(20); ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL;
-- Modify column ALTER TABLE users MODIFY COLUMN phone VARCHAR(25); -- PostgreSQL ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(25);
-- Drop column ALTER TABLE users DROP COLUMN phone;
-- Add constraint ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email); ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- Drop constraint ALTER TABLE users DROP CONSTRAINT uk_email; ALTER TABLE orders DROP CONSTRAINT fk_orders_user_id;
-- Rename table RENAME TABLE old_table TO new_table; -- PostgreSQL ALTER TABLE old_table RENAME TO new_table;
-- Rename column ALTER TABLE users CHANGE old_column new_column VARCHAR(50); -- PostgreSQL ALTER TABLE users RENAME COLUMN old_column TO new_column; ```_
Index Management¶
```sql -- 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; ```_
Datenverarbeitung¶
Dateneintrag¶
```sql -- 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(); ```_
Daten-Updates¶
```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¶
```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 ); ```_
Datensuche¶
```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¶
Querausführung¶
```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; ```_
Queroptimierung¶
```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; ```_
Gespeicherte Verfahren und Funktionen¶
```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); ```_
Import/Export¶
Datenimport¶
```php
'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); "; ?>```_
```sql -- 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 != ''; ```
Datenexport¶
```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; ```_
Backup und Wiederherstellung¶
```php
'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 ```_
Benutzermanagement¶
User Creation und Management¶
```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; ```_
Zulassungsmanagement¶
```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'; ```_
Sicherheitspolitik¶
```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(); ```_
Sicherheit¶
Zugriffskontrolle¶
```php
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; ?>```_
Datenschutz¶
```php
sensitive_fields)) { if ($field['field'] === 'email') { return $this->maskEmail($val); } elseif ($field['field'] === 'phone') { return $this->maskPhone($val); } else { return str_repeat('*', min(strlen($val), 8)); } } return parent::selectVal($val, $link, $field, $original); } private function maskEmail($email) { if (strpos($email, '@') === false) { return $email; } list($local, $domain) = explode('@', $email); $masked_local = substr($local, 0, 2) . str_repeat('*', max(0, strlen($local) - 2)); return $masked_local . '@' . $domain; } private function maskPhone($phone) { $cleaned = preg_replace('/[^0-9]/', '', $phone); if (strlen($cleaned) >= 4) { return str_repeat('*', strlen($cleaned) - 4) . substr($cleaned, -4); } return str_repeat('*', strlen($cleaned)); } // Prevent export of sensitive data function dumpTable($table, $style, $is_view = 0) { if ($style === 'INSERT' && $this->hasSensitiveFields($table)) { echo "-- Export of sensitive table '$table' is restricted\n"; return false; } return parent::dumpTable($table, $style, $is_view); } private function hasSensitiveFields($table) { $fields = fields($table); foreach ($fields as $field => $info) { if (in_array(strtolower($field), $this->sensitive_fields)) { return true; } } return false; } } ?>```_
SSL/TLS Konfiguration¶
```apache
Apache SSL configuration for 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>
```_
Anpassung¶
Themen und Stil¶
```php
/* 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 '```_
Plugin-Entwicklung¶
```php
'; 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 'Query Performance Statistics
'; echo '| Time | Query | Execution Time | Rows |
|---|---|---|---|
| ' . h($log['timestamp']) . ' | '; echo '' . h($query_preview) . ' | '; echo '' . number_format($log['execution_time'], 4) . 's | '; echo '' . number_format($log['rows_affected']) . ' | '; 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 '```_
Best Practices¶
Leistungsoptimierung¶
```php
$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 '```_
Sicherheitshärten¶
```php
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; ?>```_
Wartung und Überwachung¶
```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)" ```_
--
Zusammenfassung¶
Adminer ist ein leichtes, leistungsstarkes Datenbankmanagement-Tool, das umfassende Datenbankverwaltungsfunktionen über eine einzige PHP-Datei bietet. Dieses Cheatsheet umfasst wesentliche Funktionen, Sicherheitspraktiken und Anpassungsmöglichkeiten für eine effektive Datenbankverwaltung.
Key Strengths: - Lightweight: Bereitstellung von PHP-Datei - Multi-Database Support*: Funktioniert mit MySQL, PostgreSQL, SQLite und mehr - **User-Friendly Interface*: Intuitive webbasierte Schnittstelle - **Extensible*: Plugin-System für zusätzliche Funktionalität - **Secure: Eingebaute Sicherheitsfunktionen und Anpassungsmöglichkeiten
Best Use Cases: - Schnelle Datenbankverwaltungsaufgaben - Gemeinsame Hosting-Umgebungen - Entwicklung und Prüfung - Remote-Datenbankverwaltung - Leichte Alternative zu phpMyAdmin
Importierte Überlegungen: - Sicherheitskonfiguration ist entscheidend für den Produktionseinsatz - Leistungsoptimierung für große Datenbanken erforderlich - Regelmäßige Updates für Sicherheitspatches empfohlen - Zugangskontrolle und IP-Beschränkungen sollten umgesetzt werden - Audits, die für Compliance und Sicherheitsüberwachung unerlässlich sind
Durch die in diesem Cheatsheet skizzierten Praktiken und Techniken können Sie Adminer effektiv nutzen, um Datenbanken zu verwalten und gleichzeitig Sicherheit, Leistung und Zuverlässigkeit in Ihren Datenbank-Operationen zu gewährleisten.
<= <= <= <================================================================================= Funktion copyToClipboard() {\cHFFFF} const commands = document.querySelectorAll('code'); alle Befehle = ''; Befehle. Für jede(cmd) => alle Befehle += cmd.textContent + '\n'); navigator.clipboard.writeText (allCommands); Alarm ('Alle Befehle, die in die Zwischenablage kopiert werden!'); }
Funktion generierenPDF() { Fenster.print(); }