php - INSERT INTO 语句不起作用

Question

我对 PHP/SQL 编码还很陌生，我完全不知道为什么这种编码不起作用。数据库本身是由我的大学制作的，所以我只是想连接到它(我不确定我是否能够透露细节所以我把连接编码拿出来了。我处于基础水平所以繁重的技术语言会让我难以理解，但我将不胜感激任何建议!

我正在尝试将链接到 PHP 文件的表单的结果插入到数据库表中。我不确定是否需要在 PHP 文件中添加任何内容来说明这一点？但这是我的代码:

<?php
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
// Connect to database
$sql = "SELECT runnerid, position, eventid, date, finishtime, categoryid,  agegrade, pb FROM Results";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
    { 
        echo "<table>";
        echo '<table border=1><tr><th>Runner ID</th><th>Position</th><th>Event ID</th><th>Date</th><th>Finish Time</th><th>Category ID</th><th>Age Grade</th><th>Personal Best</th></tr>';
        echo "<tr><td>";
        echo $row['runnerid'];
        echo "</td><td>";
        echo $row['position'];
        echo "</td><td>";
        echo $row['eventid'];
        echo "</td><td>";
        echo $row['date'];
        echo "</td><td>";
        echo $row['finishtime'];
        echo "</td><td>";
        echo $row['categoryid'];
        echo "</td><td>";
        echo $row['agegrade'];
        echo "</td><td>";
        echo $row['pb'];
        echo "</td>
        </tr>";

    }
    echo "</table>";
}
} else {
 echo "0 results";
}

$sql = "INSERT INTO Results VALUES ('$_POST[runnerid]', '$_POST[position]' '$_POST[eventid]' '$_POST[date]' '$_POST[finishtime]' '$_POST[categoryid]' '$_POST[agegrade]' '$_POST[pb]')";

$result = $conn->query($sql);
if (!$result) {
    die('Could not insert data' . mysql_error()); 
}
$conn->close();
?>

我什至尝试过不使用 $_POST 编码来添加新数据的代码，但这也不起作用。

Answer 1

目前还没有人提到，根据您的代码，我可以通过一个请求轻松清除您的整个数据库。这是您需要采取的措施来防范 SQL injection attacks :

$sql = "INSERT INTO Results VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('iiississ', $_POST["runnerid"], $_POST["position"], $_POST["eventid"], $_POST["date"], $_POST["finishtime"], $_POST["categoryid"], $_POST["agegrade"], $_POST["pb"]);
$result = $stmt->execute();
if (!$result) {
    die('Could not insert data: ' . $conn->error); 
}

Read up on prepared statements :

Bound variables are sent to the server separately from the query and thus cannot interfere with it. The server uses these values directly at the point of execution, after the statement template is parsed. Bound parameters do not need to be escaped as they are never substituted into the query string directly. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion.