prefix . self::TABLE; } public function maybe_install_schema() { $current = (int) get_option(self::SCHEMA_OPT, 0); if ($current >= self::SCHEMA_VERSION) { return; } if (!AIEO_Schema_Lock::acquire(self::SCHEMA_OPT)) { return; } $this->install_schema(); update_option(self::SCHEMA_OPT, self::SCHEMA_VERSION, false); } private function install_schema() { global $wpdb; $table = self::table(); $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(80) NOT NULL, `host` varchar(255) NOT NULL, `port` smallint unsigned NOT NULL DEFAULT 3306, `db_name` varchar(64) NOT NULL, `db_user` varchar(64) NOT NULL, `db_pass_enc` varbinary(512) DEFAULT NULL, `ssl_mode` varchar(16) NOT NULL DEFAULT 'prefer', `is_default` tinyint(1) NOT NULL DEFAULT 0, `table_prefix` varchar(20) NOT NULL DEFAULT 'wp_', `erp_orders_table` varchar(64) NOT NULL DEFAULT '', `erp_customers_table` varchar(64) NOT NULL DEFAULT '', `erp_products_table` varchar(64) NOT NULL DEFAULT '', `predictor_table` varchar(64) NOT NULL DEFAULT '', `notes` text DEFAULT NULL, `last_tested_at` datetime DEFAULT NULL, `last_test_ok` tinyint(1) NOT NULL DEFAULT 0, `last_test_error` varchar(500) NOT NULL DEFAULT '', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `is_default` (`is_default`) ) {$charset_collate};"; $wpdb->query($sql); } public static function key_configured() { return defined('AIEO_ERP_ENCRYPTION_KEY') && is_string(AIEO_ERP_ENCRYPTION_KEY) && strlen(AIEO_ERP_ENCRYPTION_KEY) >= 32; } public static function suggest_key() { if (function_exists('random_bytes')) { return base64_encode(random_bytes(32)); } return base64_encode(openssl_random_pseudo_bytes(32)); } private static function key_bytes() { $raw = (string) AIEO_ERP_ENCRYPTION_KEY; if (strlen($raw) === 32) { return $raw; } $decoded = base64_decode($raw, true); if ($decoded !== false && strlen($decoded) === 32) { return $decoded; } return substr(hash('sha256', $raw, true), 0, 32); } private static function encrypt($plaintext) { if (!self::key_configured()) { return null; } if (!function_exists('sodium_crypto_secretbox')) { return null; } $nonce = random_bytes(SODIUM_CRYPTO_SECRETBOX_NONCEBYTES); $cipher = sodium_crypto_secretbox((string) $plaintext, $nonce, self::key_bytes()); return $nonce . $cipher; } private static function decrypt($blob) { if (!self::key_configured() || $blob === null || $blob === '') { return null; } if (!function_exists('sodium_crypto_secretbox_open')) { return null; } $blob = (string) $blob; $nonce_len = SODIUM_CRYPTO_SECRETBOX_NONCEBYTES; if (strlen($blob) <= $nonce_len) { return null; } $nonce = substr($blob, 0, $nonce_len); $cipher = substr($blob, $nonce_len); $plain = sodium_crypto_secretbox_open($cipher, $nonce, self::key_bytes()); return $plain === false ? null : $plain; } public static function get($id) { global $wpdb; $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE id=%d', (int) $id), ARRAY_A); return is_array($row) ? $row : null; } public static function get_by_name($name) { global $wpdb; $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE name=%s', (string) $name), ARRAY_A); return is_array($row) ? $row : null; } public static function all() { global $wpdb; $rows = $wpdb->get_results('SELECT * FROM ' . self::table() . ' ORDER BY is_default DESC, name ASC', ARRAY_A); return is_array($rows) ? $rows : array(); } public static function password_for($connection_id) { $row = self::get($connection_id); if (!$row || empty($row['db_pass_enc'])) { return null; } return self::decrypt($row['db_pass_enc']); } public static function for_connection_id($connection_id) { $row = self::get($connection_id); if (!$row) { return new WP_Error('aieo_erp_no_connection', __('Connection not found.', 'ai-eshop-optimizer')); } if (!self::key_configured()) { return new WP_Error('aieo_erp_no_key', __('AIEO_ERP_ENCRYPTION_KEY constant is not configured in wp-config.php.', 'ai-eshop-optimizer')); } $pass = self::decrypt($row['db_pass_enc']); if ($pass === null) { return new WP_Error('aieo_erp_decrypt_failed', __('Could not decrypt password โ€” encryption key may have been rotated.', 'ai-eshop-optimizer')); } $previous_die = $GLOBALS['EZSQL_ERROR'] ?? null; $remote = new wpdb( (string) $row['db_user'], (string) $pass, (string) $row['db_name'], (string) $row['host'] . ':' . (int) $row['port'] ); $remote->show_errors(false); $remote->suppress_errors(true); if (!empty($remote->error) || empty($remote->dbh)) { $err = is_object($remote->error) ? $remote->error->get_error_message() : (string) $remote->error; return new WP_Error('aieo_erp_connect_failed', sprintf( __( 'Connection failed: %s', 'ai-eshop-optimizer' ), $err )); } $remote->prefix = (string) ($row['table_prefix'] ?: 'wp_'); return $remote; } public static function table_for($connection_id, $logical) { $row = self::get($connection_id); if (!$row) { return ''; } $key = $logical . '_table'; if (!isset($row[$key])) { return ''; } $name = trim((string) $row[$key]); if ($name === '') { return ''; } if (!preg_match('/^[A-Za-z0-9_]+$/', $name)) { return ''; } return $name; } public static function tables_for($connection_id) { return array( 'erp_orders' => self::table_for($connection_id, 'erp_orders'), 'erp_customers' => self::table_for($connection_id, 'erp_customers'), 'erp_products' => self::table_for($connection_id, 'erp_products'), 'predictor' => self::table_for($connection_id, 'predictor'), ); } public static function query_table($connection_id, $logical, $sql, array $params = array()) { $table = self::table_for($connection_id, $logical); if ($table === '') { return array('ok' => false, 'error' => sprintf( __('Connection %1$d has no %2$s_table configured.', 'ai-eshop-optimizer'), (int) $connection_id, $logical)); } $remote = self::for_connection_id($connection_id); if (is_wp_error($remote)) { return array('ok' => false, 'error' => $remote->get_error_message()); } $sql_with_table = str_replace('{table}', '`' . $table . '`', $sql); $rows = empty($params) ? $remote->get_results($sql_with_table, ARRAY_A) : $remote->get_results($remote->prepare($sql_with_table, $params), ARRAY_A); if ($remote->last_error) { return array('ok' => false, 'error' => $remote->last_error); } return array('ok' => true, 'rows' => is_array($rows) ? $rows : array()); } public static function test_credentials(array $creds) { $required = array('host', 'port', 'db_name', 'db_user'); foreach ($required as $k) { if (!isset($creds[$k]) || $creds[$k] === '') { return array('ok' => false, 'error' => sprintf( __('Missing %s.', 'ai-eshop-optimizer'), $k)); } } $pass = isset($creds['db_pass']) ? (string) $creds['db_pass'] : ''; $remote = new wpdb( (string) $creds['db_user'], $pass, (string) $creds['db_name'], (string) $creds['host'] . ':' . (int) $creds['port'] ); $remote->show_errors(false); $remote->suppress_errors(true); if (!empty($remote->error) || empty($remote->dbh)) { $err = is_object($remote->error) ? $remote->error->get_error_message() : (string) $remote->error; return array('ok' => false, 'error' => 'Connection failed: ' . ($err ?: 'unknown error')); } $version = $remote->get_var('SELECT VERSION()'); $now = $remote->get_var('SELECT UTC_TIMESTAMP()'); if ($version === null) { return array('ok' => false, 'error' => 'Connected but query failed: ' . $remote->last_error); } return array( 'ok' => true, 'version' => (string) $version, 'now' => (string) $now, ); } public function build_page_context() { $this->maybe_install_schema(); return array( 'connections' => self::all(), 'key_ok' => self::key_configured(), 'suggested' => self::key_configured() ? '' : self::suggest_key(), 'nonce' => wp_create_nonce(self::NONCE_KEY), ); } private function verify_ajax() { if (!current_user_can(self::CAPABILITY)) { wp_send_json_error(array('message' => 'forbidden'), 403); } check_ajax_referer(self::NONCE_KEY, 'nonce'); } public function ajax_test_connection() { $this->verify_ajax(); $creds = array( 'host' => isset($_POST['host']) ? sanitize_text_field((string) wp_unslash($_POST['host'])) : '', 'port' => isset($_POST['port']) ? (int) $_POST['port'] : 3306, 'db_name' => isset($_POST['db_name']) ? sanitize_text_field((string) wp_unslash($_POST['db_name'])) : '', 'db_user' => isset($_POST['db_user']) ? sanitize_text_field((string) wp_unslash($_POST['db_user'])) : '', 'db_pass' => isset($_POST['db_pass']) ? (string) wp_unslash($_POST['db_pass']) : '', ); $existing_id = isset($_POST['id']) ? (int) $_POST['id'] : 0; if ($creds['db_pass'] === '' && $existing_id > 0) { $stored = self::password_for($existing_id); if ($stored !== null) { $creds['db_pass'] = $stored; } } $r = self::test_credentials($creds); if (empty($r['ok'])) { wp_send_json_error(array('message' => $r['error'])); } wp_send_json_success(array( 'message' => sprintf(__('Connected. MariaDB %1$s ยท server time %2$s', 'ai-eshop-optimizer'), $r['version'], $r['now']), 'version' => $r['version'], 'now' => $r['now'], )); } public function ajax_save_connection() { $this->verify_ajax(); if (!self::key_configured()) { wp_send_json_error(array('message' => __('AIEO_ERP_ENCRYPTION_KEY constant is missing in wp-config.php โ€” passwords cannot be stored safely. Add the suggested constant first, then save.', 'ai-eshop-optimizer'))); } $id = isset($_POST['id']) ? (int) $_POST['id'] : 0; $name = isset($_POST['name']) ? sanitize_text_field((string) wp_unslash($_POST['name'])) : ''; if ($name === '') { wp_send_json_error(array('message' => 'name required')); } $host = isset($_POST['host']) ? sanitize_text_field((string) wp_unslash($_POST['host'])) : ''; if ($host === '') { wp_send_json_error(array('message' => 'host required')); } $port = isset($_POST['port']) ? max(1, min(65535, (int) $_POST['port'])) : 3306; $db_name = isset($_POST['db_name']) ? sanitize_text_field((string) wp_unslash($_POST['db_name'])) : ''; if ($db_name === '') { wp_send_json_error(array('message' => 'db_name required')); } $db_user = isset($_POST['db_user']) ? sanitize_text_field((string) wp_unslash($_POST['db_user'])) : ''; if ($db_user === '') { wp_send_json_error(array('message' => 'db_user required')); } $ssl_mode = isset($_POST['ssl_mode']) ? sanitize_key((string) wp_unslash($_POST['ssl_mode'])) : 'prefer'; if (!in_array($ssl_mode, array('off', 'prefer', 'require'), true)) { $ssl_mode = 'prefer'; } $is_default = !empty($_POST['is_default']) ? 1 : 0; $table_prefix = isset($_POST['table_prefix']) ? sanitize_text_field((string) wp_unslash($_POST['table_prefix'])) : 'wp_'; if ($table_prefix === '') { $table_prefix = 'wp_'; } $notes = isset($_POST['notes']) ? sanitize_textarea_field((string) wp_unslash($_POST['notes'])) : ''; $sanitize_ident = static function ($v) { $v = trim((string) $v); return preg_match('/^[A-Za-z0-9_]+$/', $v) ? $v : ''; }; $erp_orders_table = isset($_POST['erp_orders_table']) ? $sanitize_ident(wp_unslash($_POST['erp_orders_table'])) : ''; $erp_customers_table = isset($_POST['erp_customers_table']) ? $sanitize_ident(wp_unslash($_POST['erp_customers_table'])) : ''; $erp_products_table = isset($_POST['erp_products_table']) ? $sanitize_ident(wp_unslash($_POST['erp_products_table'])) : ''; $predictor_table = isset($_POST['predictor_table']) ? $sanitize_ident(wp_unslash($_POST['predictor_table'])) : ''; $row = array( 'name' => $name, 'host' => $host, 'port' => $port, 'db_name' => $db_name, 'db_user' => $db_user, 'ssl_mode' => $ssl_mode, 'is_default' => $is_default, 'table_prefix' => $table_prefix, 'erp_orders_table' => $erp_orders_table, 'erp_customers_table' => $erp_customers_table, 'erp_products_table' => $erp_products_table, 'predictor_table' => $predictor_table, 'notes' => $notes, ); $formats = array('%s', '%s', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s'); $new_pass = isset($_POST['db_pass']) ? (string) wp_unslash($_POST['db_pass']) : ''; if ($new_pass !== '') { $enc = self::encrypt($new_pass); if ($enc === null) { wp_send_json_error(array('message' => __('Encryption failed โ€” the AIEO_ERP_ENCRYPTION_KEY constant looks malformed.', 'ai-eshop-optimizer'))); } $row['db_pass_enc'] = $enc; $formats[] = '%s'; } global $wpdb; $table = self::table(); if ($is_default) { $wpdb->query("UPDATE {$table} SET is_default = 0"); } if ($id > 0) { $existing = self::get($id); if (!$existing) { wp_send_json_error(array('message' => 'connection_not_found')); } $r = $wpdb->update($table, $row, array('id' => $id), $formats, array('%d')); if ($r === false) { wp_send_json_error(array('message' => 'UPDATE failed: ' . $wpdb->last_error)); } wp_send_json_success(array('id' => $id, 'message' => __('Connection updated.', 'ai-eshop-optimizer'))); } if (empty($row['db_pass_enc'])) { wp_send_json_error(array('message' => __('Password is required for a new connection.', 'ai-eshop-optimizer'))); } $r = $wpdb->insert($table, $row, $formats); if ($r === false) { wp_send_json_error(array('message' => 'INSERT failed: ' . $wpdb->last_error)); } wp_send_json_success(array('id' => (int) $wpdb->insert_id, 'message' => __('Connection created.', 'ai-eshop-optimizer'))); } public function ajax_delete_connection() { $this->verify_ajax(); $id = isset($_POST['id']) ? (int) $_POST['id'] : 0; if ($id <= 0) { wp_send_json_error(array('message' => 'invalid id')); } global $wpdb; $rows = $wpdb->delete(self::table(), array('id' => $id), array('%d')); if ($rows === false) { wp_send_json_error(array('message' => 'DELETE failed: ' . $wpdb->last_error)); } wp_send_json_success(array('deleted' => (int) $rows)); } } AIEO_ERP::instance();