DVWA数据库初始化踩坑实录：MySQL 8.0.12外键约束与语法兼容性完美解决方案- 惊觉

摘要

问题现象

访问DVWA的setup.php，点击"Create / Reset Database"后报错：
✅ Database has been created.
✅ 'users' table was created.
✅ Data inserted into 'users' table.
❌ Could not create access_log table
SQL: Cannot add foreign key constraint
❌ Could not add role column to users table
SQL: You have an error in your SQL syntax...

浏览器显示：
Table could not be createdSQL: Cannot add foreign key constraint

根本原因深度剖析

问题一：外键约束创建失败（核心痛点！）

错误表现：Cannot add foreign key constraint
真实原因：
1. users表未指定InnoDB引擎 → 可能默认MyISAM（不支持外键）
2. 字段类型不严格匹配：
  users.user_id = INT(6)
  access_log.user_id = INT
3. 字符集不统一导致隐式转换失败
验证命令：
SHOW CREATE TABLE users;
DESC users; DESC access_log;
SHOW TABLE STATUS LIKE 'users';

问题二：SQL语法错误（隐藏陷阱！）

原代码：
ALTER TABLE users ADD COLUMN IF NOT EXISTS role VARCHAR(20) DEFAULT 'user';

❌ MySQL 8.0.12不支持该语法！
✅ 官方文档明确：ADD COLUMN IF NOT EXISTS 仅在 MySQL 8.0.17+ 支持
（参考：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

）

💡 关键认知：
DVWA的数据库操作逻辑实际在 dvwa/includes/DBMS/MySQL.php 中，
setup.php 仅是前端入口！修改此处才能根治问题。

终极解决方案（无需升级MySQL！）

操作前必读

1. 备份原文件（重要！）

cp dvwa/includes/DBMS/MySQL.php dvwa/includes/DBMS/MySQL.php.bak

2. 确认MySQL版本

mysql -V # 若显示 8.0.12 或更低，本文方案适用

五大关键修复点（修改文件：dvwa/includes/DBMS/MySQL.php）

修复1：users表显式指定InnoDB引擎 + 字符集
// 原代码（第48行附近）
$ create_tb = "CREATE TABLE users (user_id int(6), ... , PRIMARY KEY (user_id));";

// 修改为 ↓
$ create_tb = "CREATE TABLE users (
user_id INT(6) NOT NULL,
first_name VARCHAR(15),
last_name VARCHAR(15),
user VARCHAR(15),
password VARCHAR(32),
avatar VARCHAR(70),
last_login TIMESTAMP,
failed_login INT(3),
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"; // ✅ 强制InnoDB+统一字符集

修复2：安全添加role列（绕过IF NOT EXISTS语法）
// 原代码（第85行附近）
$ alter_users = "ALTER TABLE users ADD COLUMN IF NOT EXISTS role VARCHAR(20) DEFAULT 'user';";

// 替换为 ↓
$ check_role = "SELECT COUNT(*) AS cnt FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '{ $ _DVWA['db_database']}'
AND TABLE_NAME = 'users' AND COLUMN_NAME = 'role'";
��=��(result=mysqliquery( GLOBALS["___mysqli_ston"], $ check_role);
if (mysqli_fetch_assoc( $ result)['cnt'] == 0) {
mysqli_query( $ GLOBALS["___mysqli_ston"], "ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';");
dvwaMessagePush("Added role column to users table.");
} else {
dvwaMessagePush("role column already exists in users table.");
}

修复3：access_log表字段类型严格对齐
// 原代码（第98行附近）
user_id INT NOT NULL,
target_id INT NOT NULL,

// 修改为 ↓
user_id INT(6) NOT NULL, // ✅ 与users.user_id完全一致
target_id INT(6) NOT NULL, // ✅

修复4：security_log表同步修改
// 同样将 user_id 和 target_id 改为 INT(6)
user_id INT(6) NOT NULL,
target_id INT(6) NOT NULL,

修复5：account_enabled列安全添加（同role列逻辑）
// 原代码（第185行附近）
$ alter_users_dept = "ALTER TABLE users ADD COLUMN IF NOT EXISTS account_enabled TINYINT(1) DEFAULT 1;";

// 替换为 ↓（逻辑同修复2）
$ check_enabled = "SELECT COUNT(*) AS cnt FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '{ $ _DVWA['db_database']}'
AND TABLE_NAME = 'users' AND COLUMN_NAME = 'account_enabled'";
��=��(resultenabled=mysqliquery( GLOBALS["___mysqli_ston"], $ check_enabled);
if (mysqli_fetch_assoc( $ result_enabled)['cnt'] == 0) {
mysqli_query( $ GLOBALS["___mysqli_ston"], "ALTER TABLE users ADD COLUMN account_enabled TINYINT(1) DEFAULT 1;");
dvwaMessagePush("Added account_enabled column to users table.");
} else {
dvwaMessagePush("account_enabled column already exists in users table.");
}

验证效果（实测成功日志）

✅ Database has been created.
✅ 'users' table was created.
✅ Data inserted into 'users' table.
✅ Added role column to users table.
✅ Updated admin user role.
✅ 'access_log' table was created. ← 外键成功！
✅ 'security_log' table was created. ← 外键成功！
✅ Backup file /config/config.inc.php.bak automatically created
✅ Added account_enabled column to users table.
✅ Setup successful!

功能验证：

登录DVWA后台，用户管理页正常显示role字段
操作日志功能可记录访问行为（access_log表有数据）
无任何SQL错误日志
中文用户名/评论无乱码（utf8mb4生效）

经验总结（避坑指南）

表格

问题类型	关键认知	实用技巧
MySQL版本陷阱	8.0.12 ≠ 8.0.17+！语法支持有差异	部署前执行：SELECT VERSION();
外键约束铁律	1. 引擎必须InnoDB 2. 字段类型严格一致 3. 被引用列必须有索引	创建表前检查： SHOW ENGINES; DESC users;
DVWA架构认知	setup.php ≠ 数据库逻辑文件！	核心文件路径： dvwa/includes/DBMS/MySQL.php
安全实践	allow_url_include=On仅用于实验环境	生产环境务必关闭： php.ini中设为Off

外键失败快速排查命令

-- 查看外键失败具体原因（关键！）
SHOW ENGINE INNODB STATUS\G

-- 检查表引擎和字符集
SELECT TABLE_NAME, ENGINE, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dvwa';

-- 验证字段类型是否匹配
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'dvwa' AND TABLE_NAME IN ('users', 'access_log');

完整修复版MySQL.php代码

<?php /* This file contains all of the code to setup the initial MySQL database. (setup.php) */ if( !defined( 'DVWA_WEB_PAGE_TO_ROOT' ) ) { define( 'DVWA_WEB_PAGE_TO_ROOT', '../../../' ); } if( !@($GLOBALS["___mysqli_ston"] = mysqli_connect( $_DVWA[ 'db_server' ], $_DVWA[ 'db_user' ], $_DVWA[ 'db_password' ], "", $_DVWA[ 'db_port' ] )) ) { dvwaMessagePush( "Could not connect to the database service. Please check the config file. Database Error #" . mysqli_connect_errno() . ": " . mysqli_connect_error() . "." ); if ($_DVWA[ 'db_user' ] == "root") { dvwaMessagePush( 'Your database user is root, if you are using MariaDB, this will not work, please read the README.md file.' ); } dvwaPageReload(); } // Create database $drop_db = "DROP DATABASE IF EXISTS {$_DVWA[ 'db_database' ]};"; if( !@mysqli_query($GLOBALS["___mysqli_ston"], $drop_db ) ) { dvwaMessagePush( "Could not drop existing database SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } $create_db = "CREATE DATABASE {$_DVWA[ 'db_database' ]} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"; if( !@mysqli_query($GLOBALS["___mysqli_ston"], $create_db ) ) { dvwaMessagePush( "Could not create database SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "Database has been created." ); // Create table 'users' - ✅ 关键修复1：显式指定 InnoDB 引擎 + 字符集 if( !@((bool)mysqli_query($GLOBALS["___mysqli_ston"], "USE " . $_DVWA[ 'db_database' ])) ) { dvwaMessagePush( 'Could not connect to database.' ); dvwaPageReload(); } $create_tb = "CREATE TABLE users ( user_id INT(6) NOT NULL, first_name VARCHAR(15), last_name VARCHAR(15), user VARCHAR(15), password VARCHAR(32), avatar VARCHAR(70), last_login TIMESTAMP, failed_login INT(3), PRIMARY KEY (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $create_tb ) ) { dvwaMessagePush( "Table could not be created SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "'users' table was created." ); // Insert some data into users $base_dir= str_replace ("setup.php", "", $_SERVER['SCRIPT_NAME']); $avatarUrl = $base_dir . 'hackable/users/'; $insert = "INSERT INTO users VALUES ('1','admin','admin','admin',MD5('password'),'{$avatarUrl}admin.jpg', NOW(), '0'), ('2','Gordon','Brown','gordonb',MD5('abc123'),'{$avatarUrl}gordonb.jpg', NOW(), '0'), ('3','Hack','Me','1337',MD5('charley'),'{$avatarUrl}1337.jpg', NOW(), '0'), ('4','Pablo','Picasso','pablo',MD5('letmein'),'{$avatarUrl}pablo.jpg', NOW(), '0'), ('5','Bob','Smith','smithy',MD5('password'),'{$avatarUrl}smithy.jpg', NOW(), '0');"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $insert ) ) { dvwaMessagePush( "Data could not be inserted into 'users' table SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "Data inserted into 'users' table." ); // ========== 修复2：安全添加 role 列（兼容 MySQL 8.0.12） ========== $check_role = "SELECT COUNT(*) AS cnt FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '{$_DVWA['db_database']}' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'role'"; $result = mysqli_query($GLOBALS["___mysqli_ston"], $check_role); $row = mysqli_fetch_assoc($result); if ($row['cnt'] == 0) { $alter_users = "ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $alter_users) ) { dvwaMessagePush( "Could not add role column to users table SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "Added role column to users table." ); } else { dvwaMessagePush( "role column already exists in users table." ); } // Set admin user role $update_admin = "UPDATE users SET role = 'admin' WHERE user = 'admin';"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $update_admin) ) { dvwaMessagePush( "Could not set admin role SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "Updated admin user role." ); // ========== 修复3：创建 access_log 表（字段类型与 users.user_id 严格对齐） ========== $create_access_log = "CREATE TABLE access_log ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT(6) NOT NULL, -- ✅ 与 users.user_id 类型完全一致 target_id INT(6) NOT NULL, -- ✅ action VARCHAR(50) NOT NULL, timestamp DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (target_id) REFERENCES users(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $create_access_log) ) { dvwaMessagePush( "Could not create access_log table SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "'access_log' table was created." ); // ========== 修复4：创建 security_log 表（同步修改字段类型） ========== $create_security_log = "CREATE TABLE security_log ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT(6) NOT NULL, -- ✅ 与 users.user_id 类型完全一致 target_id INT(6) NOT NULL, -- ✅ action VARCHAR(50) NOT NULL, timestamp DATETIME NOT NULL, ip_address VARCHAR(45) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (target_id) REFERENCES users(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $create_security_log) ) { dvwaMessagePush( "Could not create security_log table SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "'security_log' table was created." ); // Create guestbook table - ✅ 统一引擎和字符集 $create_tb_guestbook = "CREATE TABLE guestbook ( comment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, comment VARCHAR(300), name VARCHAR(100), PRIMARY KEY (comment_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $create_tb_guestbook) ) { dvwaMessagePush( "Table could not be created SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "'guestbook' table was created." ); // Insert data into 'guestbook' $insert = "INSERT INTO guestbook VALUES ('1','This is a test comment.','test');"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $insert) ) { dvwaMessagePush( "Data could not be inserted into 'guestbook' table SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "Data inserted into 'guestbook' table." ); // Copy .bak for a fun directory listing vuln $conf = DVWA_WEB_PAGE_TO_ROOT . 'config/config.inc.php'; $bakconf = DVWA_WEB_PAGE_TO_ROOT . 'config/config.inc.php.bak'; if (file_exists($conf)) { @copy($conf, $bakconf); } dvwaMessagePush( "Backup file /config/config.inc.php.bak automatically created" ); // ========== 修复5：安全添加 account_enabled 列（兼容 MySQL 8.0.12） ========== $check_enabled = "SELECT COUNT(*) AS cnt FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '{$_DVWA['db_database']}' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'account_enabled'"; $result_enabled = mysqli_query($GLOBALS["___mysqli_ston"], $check_enabled); $row_enabled = mysqli_fetch_assoc($result_enabled); if ($row_enabled['cnt'] == 0) { $alter_users_dept = "ALTER TABLE users ADD COLUMN account_enabled TINYINT(1) DEFAULT 1;"; if( !mysqli_query($GLOBALS["___mysqli_ston"], $alter_users_dept) ) { dvwaMessagePush( "Could not add account_enabled column to users table SQL: " . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) ); dvwaPageReload(); } dvwaMessagePush( "Added account_enabled column to users table." ); } else { dvwaMessagePush( "account_enabled column already exists in users table." ); } // Done dvwaMessagePush( "Setup successful!" ); if( !dvwaIsLoggedIn()) dvwaMessagePush( "Please <a href='login.php'>login</a>.<script>setTimeout(function(){window.location.href='login.php'},5000);</script>" ); dvwaPageReload(); ?>

附录：完整修复版MySQL.php代码

使用说明：

备份原文件：cp dvwa/includes/DBMS/MySQL.php dvwa/includes/DBMS/MySQL.php.bak
将下方代码完整覆盖到 dvwa/includes/DBMS/MySQL.php
访问 setup.php 重新初始化数据

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/2401_89711774/article/details/157619808

DVWA数据库初始化踩坑实录：MySQL 8.0.12外键约束与语法兼容性完美解决方案

摘要