Lab PHP

华东师范大学软件工程学院实践报告

实验课程：数据库系统实践	姓名：刘佳奇	学号：10225101455
实验名称：Lab PHP	实验日期：2024/5/11	指导老师：姚俊杰

实验目的

使用PHP连接数据库并实现批量化插入删除查询等功能。

实验环境

• Vscode
• MySQL 8.3.0
• PHP 7.3.4

实验过程与分析

PHP与MySQL的连接主要使用MySQLi函数库，其中i代表improvement增强，是MySQL函数库的扩展库，增加了安全性，可以大幅减少SQL注入等的发生。

mysql与mysqli的联系： （1）mysql与mysqli都是php方面的函数集，与mysql数据库关联不大。 （2）在php5版本之前，一般是用php的mysql函数去驱动mysql数据库的，比如mysql_query()的函数，属于面向过程
（3）在php5版本以后，增加了mysqli的函数功能，某种意义上讲，它是mysql系统函数的增强版，更稳定更高效更安全，与mysql_query()对应的有mysqli_query()，属于面向对象，用对象的方式操作驱动mysql数据库

接下来我们将使用这个库进行实验操作的进行。

思路：

index.php
总体结构为if else语句来区分初次访问和表单提交
首先，根据用户请求的方法（GET或POST）来确定是显示查询表单还是处理用户的查询请求;
如果是POST请求，获取用户输入的学生姓名或ID，并通过预处理语句执行数据库查询操作，防止了SQL注入的发生;
如果查询到符合条件的学生信息，则将查询结果以表格的形式展示在页面上，并为每个学生提供了查看课程信息的按钮;
若未查询到学生信息或查询出错，则给出相应的提示或报错信息。

get_courses.php 当用户点击查看课程信息按钮时，通过POST方法将学生ID提交到get_courses.php文件中; 查询结果以表格的形式展示在页面上，并计算了学生的平均绩点;
若未查询到课程信息或查询出错，则给出相应的提示或报错信息

index.php

<?php

// 显示HTML表单供用户输入子串或ID
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // 获取用户输入的子串或ID
    $substring = $_POST['substring'];
    $studentId = $_POST['studentId'];

    // 连接到数据库
    $con = mysqli_connect("localhost", "root", "59413");

    // 判断是否连接成功
    if (!$con) {
        die('数据库连接失败: ' . mysqli_connect_error());
    } else {
        // 设置编码格式
        mysqli_set_charset($con, "utf8");

        // 选择数据库
        mysqli_select_db($con, "college");

        if (!empty($substring)) {
            // 使用预处理语句防止SQL注入
            $stmt = $con->prepare("SELECT ID, name, dept_name, tot_cred FROM student WHERE name LIKE ?");
            $like_substring = "%" . $substring . "%";
            $stmt->bind_param("s", $like_substring);
        } else if (!empty($studentId)) {
            // 使用预处理语句防止SQL注入
            $stmt = $con->prepare("SELECT ID, name, dept_name, tot_cred FROM student WHERE ID = ?");
            $stmt->bind_param("i", $studentId);
        } else {
            echo "请输入学生姓名或ID。";
            return;
        }

        $stmt->execute();
        $result = $stmt->get_result();

        if ($result) {
            if ($result->num_rows > 0) {
                // 输出表格及th
                echo "<table border='2' width='300'>
                        <tr>
                        <th>ID</th><th>name</th>
                        <th>dept_name</th><th>tot_cred</th><th>操作</th>
                        </tr>";

                // 遍历数据表中的内容
                while ($row = $result->fetch_assoc()) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($row['ID']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['name']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['dept_name']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['tot_cred']) . "</td>";
                    echo "<td><button onclick='getCourses(" . htmlspecialchars($row['ID']) . ")'>查看课程信息</button></td>";
                    echo "</tr>";
                }
                echo "</table>";
            } else {
                echo "<script>alert('无相关学生信息。');</script>";
            }
        } else {
            echo "查询失败: " . mysqli_error($con);
        }

        // 关闭预处理语句
        $stmt->close();
    }

    // 关闭数据库
    mysqli_close($con);
} else {
    // 显示输入表单
    echo '<form method="post" action="">
            请输入学生姓名或其子串: <input type="text" name="substring">
            请输入学生ID: <input type="text" name="studentId">
            <input type="submit" value="查询">
          </form>';
}

?>

<script>
function getCourses(studentId) {
    // 创建一个隐藏的表单并提交
    var form = document.createElement("form");
    form.method = "POST";
    form.action = "get_courses.php";

    var input = document.createElement("input");
    input.type = "hidden";
    input.name = "studentId";
    input.value = studentId;

    form.appendChild(input);
    document.body.appendChild(form);
    form.submit();
}
</script>

get_courses.php

<?php

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // 获取学生ID
    $studentId = $_POST['studentId'];

    // 连接到数据库
    $con = mysqli_connect("localhost", "root", "59413");

    // 判断是否连接成功
    if (!$con) {
        die('数据库连接失败: ' . mysqli_connect_error());
    } else {
        // 设置编码格式
        mysqli_set_charset($con, "utf8");

        // 选择数据库
        mysqli_select_db($con, "college");

        // 查询学生的课程信息
        $stmt = $con->prepare("SELECT 
                                takes.course_id, 
                                section.year, 
                                section.semester, 
                                course.title, 
                                course.dept_name, 
                                takes.grade, 
                                course.credits 
                              FROM takes 
                              JOIN section ON takes.course_id = section.course_id 
                              JOIN course ON takes.course_id = course.course_id 
                              WHERE takes.ID = ?");
        $stmt->bind_param("i", $studentId);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result) {
            if ($result->num_rows > 0) {
                // 输出表格及th
                echo "<table border='2' width='500'>
                        <tr>
                        <th>课程ID</th><th>上课年份</th>
                        <th>上课学期</th><th>课程名称</th>
                        <th>开课院系</th><th>成绩等级</th>
                        <th>课程学分数</th>
                        </tr>";

                // 计算GPA
                $totalGradePoints = 0;
                $totalCredits = 0;

                // 遍历数据表中的内容
                while ($row = $result->fetch_assoc()) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($row['course_id']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['year']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['semester']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['title']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['dept_name']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['grade']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['credits']) . "</td>";
                    echo "</tr>";

                    // 计算总成绩点和总学分
                    $grade = $row['grade'];
                    $credits = $row['credits'];

                    // 获取成绩点
                    $gradeStmt = $con->prepare("SELECT grade_point FROM gp_mapping WHERE grade = ?");
                    $gradeStmt->bind_param("s", $grade);
                    $gradeStmt->execute();
                    $gradeResult = $gradeStmt->get_result();

                    if ($gradeResult && $gradeResult->num_rows > 0) {
                        $gradeRow = $gradeResult->fetch_assoc();
                        $gradePoint = $gradeRow['grade_point'];
                        $totalGradePoints += $gradePoint * $credits;
                        $totalCredits += $credits;
                    }

                    // 关闭预处理语句
                    $gradeStmt->close();
                }
                echo "</table>";

                // 显示GPA
                if ($totalCredits > 0) {
                    $gpa = $totalGradePoints / $totalCredits;
                    echo "<h3>平均绩点 (GPA): " . number_format($gpa, 2) . "</h3>";
                } else {
                    echo "<h3>无课程信息，无法计算GPA。</h3>";
                }
            } else {
                echo "<script>alert('无相关课程信息。');</script>";
            }
        } else {
            echo "查询失败: " . mysqli_error($con);
        }

        // 关闭预处理语句
        $stmt->close();
    }

    // 关闭数据库
    mysqli_close($con);
}

运行结果：

输入roses，查看名字为roses的学生信息：
点击查询课程信息进行查询：
或查询序号为1000的同学：

另外，让我们验证一下输入不存在的学生名字，我们已经用js写好了弹窗提示：

实验结果总结

结果归纳

本实验通过使用PHP连接MySQL数据库，实现了批量化的学生信息查询和课程信息查询功能。通过预处理语句，有效地防止了SQL注入等安全问题的发生。此外，通过计算学生的平均绩点，进一步丰富了查询结果的内容，提升了系统的实用性和用户体验。

问题辨析

1. PHP连接MySQL时报错：The server requested authentication method unknown to the client

（1）错误信息：服务器请求使用的是客户端未知的身份验证方法。
（2）问题分析：

MySQL 目前使用的身份验证插件有三种：

• mysql_native_password：使用新的SHA1哈希算法进行认证校验。通过SHA1(SHA1(password))两次哈希计算用户的原始密码然后保存在表mysql.user的authentication_string列中。（SHA1哈希算法现在已变得比较容易破解、相同的密码拥有相同的哈希值）
  
• sha256_password：实现基本的SHA-256身份验证。（加密方式比 mysql_native_password更安全，但认证过程中的时间更长 ）
• caching_sha2_password：哈希算法升级为了更为安全SHA256算法，并在服务器端使用缓存以获得更好的性能，并具有其他功能以提高适用性。（更安全的密码加密和更好的性能）

在 MySQL 8.0 版本中，默认的身份验证插件是 caching_sha2_password；而在 MySQL 5.7 版本则为mysql_native_password。我们的是8.3.0版本，由于不确定可以在MySQL命令行查看默认值。

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

或者查看用户与身份验证插件：

mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

上述的三种验证方法应该用哪一种呢？这是由PHP版本决定的，我们同样可以用phpinfo（）函数查看PHP连接MySQL时支持哪种身份验证插件

<?php

phpinfo();

在web界面中找到mysqlnd 下面的 Loaded plugins 的那一行，名字以auth_plugin_开头的就是 当前 PHP 可用的 MySQL 身份验证插件。

当前可用

可以看到，我当前使用的 PHP 并不支持 caching_sha2_password，这就是我连接 MySQL 会报错的原因。我们从中选一个 PHP 和 Mysql 都支持的身份验证插件就行，这里我选择使用 sha256_password，所以只要我把 MySQL 的也改成 sha256_password 就行了。

（3）解决方法：修改 MySQL 的身份认证插件

ALTER USER '用户名'@'%' IDENTIFIED WITH sha256_password BY '密码';