PHP与MySQL：高效安全地从数据库动态生成HTML下拉菜单

<?php
// 数据库连接信息
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'mydatabase';

// 建立数据库连接
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) or die("数据库连接失败");

// 假设已通过会话获取管理员ID
session_start();
$_SESSION["adminusername"] = 'some_admin_id'; // 示例值

// 1. 从ADMIN表获取逗号分隔的FileID字符串
$sql_admin = "SELECT Files FROM ADMIN WHERE id='" . $_SESSION["adminusername"] . "'";
$result_admin = mysqli_query($conn, $sql_admin);
$admin_data = mysqli_fetch_array($result_admin);
$arraydata = $admin_data["Files"]; // 示例: "26,27,28,29"

// 将逗号分隔字符串转换为数组
$file_ids = explode(',', $arraydata);

// 2. 遍历FileID数组，查询Servers表并构建HTML下拉菜单
$select_html = '<select class="smallInput" name="serverfile" tabindex="6">';

foreach ($file_ids as $singleID) {
    // 为每个ID执行一次查询
    $sql_servers = "SELECT FileID, FileTitle FROM Servers WHERE FileType='CFG' AND FileID='" . $singleID . "'";
    $result_servers = mysqli_query($conn, $sql_servers);

    // 检查是否有结果，然后添加选项
    if (mysqli_num_rows($result_servers) > 0) {
        while ($row = mysqli_fetch_array($result_servers)) {
            $select_html .= '<option value="' . htmlspecialchars($row['FileID']) . '">' . htmlspecialchars($row['FileTitle']) . '</option>';
        }
    }
}
$select_html .= '</select>';

echo $select_html;

mysqli_close($conn);
?>

SELECT s.FileID, s.FileTitle
FROM Servers AS s
JOIN ADMIN AS a ON FIND_IN_SET(s.FileID, a.Files)
WHERE s.FileType = 'CFG'
AND a.id = ?; -- 使用占位符防止SQL注入

<?php
// 数据库连接信息
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'mydatabase';

// 建立数据库连接
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) or die("数据库连接失败");

// 假设已通过会话获取管理员ID
session_start();
$_SESSION["adminusername"] = 'some_admin_id'; // 示例值

// 1. 准备SQL语句，使用占位符 '?'
$sql_optimized = "
    SELECT s.FileID, s.FileTitle
    FROM Servers AS s
    JOIN ADMIN AS a ON FIND_IN_SET(s.FileID, a.Files)
    WHERE s.FileType = 'CFG'
    AND a.id = ?
";

// 2. 预处理语句
$stmt = mysqli_prepare($conn, $sql_optimized);

if ($stmt === false) {
    die("SQL语句预处理失败: " . mysqli_error($conn));
}

// 3. 绑定参数
// "s" 表示参数类型为字符串 (string)
mysqli_stmt_bind_param($stmt, "s", $_SESSION["adminusername"]);

// 4. 执行预处理语句
mysqli_stmt_execute($stmt);

// 5. 获取结果集
$result = mysqli_stmt_get_result($stmt);

// 6. 构建HTML下拉菜单
$select_html = '<select class="smallInput" name="serverfile" tabindex="6">';

if ($result) {
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        $select_html .= '<option value="' . htmlspecialchars($row['FileID']) . '">' . htmlspecialchars($row['FileTitle']) . '</option>';
    }
} else {
    // 处理无结果或错误情况
    $select_html .= '<option value="">无可用文件</option>';
}

$select_html .= '</select>';

echo $select_html;

// 7. 关闭语句和连接
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>