mysql - 插入多行PDO

Question

我遇到了一个问题。我不知道如何修复我的代码，让它使用输入检查器功能在 MySQL PDO 中插入多行。不幸的是，我尝试了很多代码，但没有成功。

我的代码如下:

<?php

$servername = "";
$username = "";
$password = "";
$dbname = "";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if ($_SERVER["REQUEST_METHOD"] == "POST") {

        $name = inputchecker_($_POST["name"][$info]);
        $number = input_checker($_POST["number"][$info]);
        $gender = input_checker($_POST["gender"][$info]);

        foreach ($info as $key=>$val) {

            $sql = "INSERT INTO memo (name, number, gender)
                    VALUES ('$name', '$number', '$gender')";
            // use exec() because no results are returned
            $conn->exec($sql);
            echo "New record created successfully";

        }    
    }    
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

function input_checker($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

?>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
Name 1: <input type="text" name="name[]"><br>
Name 2: <input type="text" name="name[]"><br>
Name 3: <input type="text" name="name[]"><br>
Number 1: <input type="text" name="number[]"><br>
Number 2: <input type="text" name="number[]"><br>
Gender 1: <input type="text" name="gender[]"><br>
Gender 2: <input type="text" name="gender[]"><br>
<input type="submit" name="submit" value="Submit">
</form>

Answer 1

注意:您应该使用prepare()方法并传递参数以防止SQL注入(inject)。

值 (?, ?, ?), (?, ?, ?), (?, ?, ?) 样式

<?php
function input_checker($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

$servername = '';
$username = '';
$password = '';
$dbname = '';

try {
    $conn = new PDO("mysql:host=${servername};dbname={$dbname}", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        $param_count = min(count($_POST['name']), count($_POST['number']), count($_POST['gender']));
        $all_values = array();
        $value_list = array();
        for ($i=0, $n=$param_count; $i<$n; ++$i) {
            $all_values[] = input_checker($_POST['name'][$i]);
            $all_values[] = input_checker($_POST['number'][$i]);
            $all_values[] = input_checker($_POST['gender'][$i]);
            $value_list[] = "(?, ?, ?)";
        }
        $sql_values = join(', ', $value_list);
        if ($sql_values) {
            $sql = "INSERT INTO `memo` (`name`, `number`, `gender`) VALUES {$sql_values}";
            $sth = $conn->prepare($sql);
            $sth->execute($all_values);
            echo 'New record created successfully';
        }
    }
} catch(PDOException $e) {
    echo $sql . '<br>' . $e->getMessage();
}

$conn = null;
?>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
Name 1: <input type="text" name="name[]"><br>
Name 2: <input type="text" name="name[]"><br>
Name 3: <input type="text" name="name[]"><br>
Number 1: <input type="text" name="number[]"><br>
Number 2: <input type="text" name="number[]"><br>
Number 3: <input type="text" name="number[]"><br>
Gender 1: <input type="text" name="gender[]"><br>
Gender 2: <input type="text" name="gender[]"><br>
Gender 3: <input type="text" name="gender[]"><br>
<input type="submit" name="submit" value="Submit">
</form>

或VALUES (:param1, :param2, :param3), (:param4, :param5, :param6), (:param7, :param8, :param9) 样式

<?php
function sql_value_pair(&$beg_i, array &$data_set)
{
    $sql_values = array();
    foreach ($data_set as $key => &$data) {
        $data['placeholder'] = ':param' . ++$beg_i;
        $sql_values[] = $data['placeholder'];
    }
    unset($data);
    return $sql_values ? '(' . join(', ', $sql_values) . ')' : NULL;
}

function input_checker($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

$servername = '';
$username = '';
$password = '';
$dbname = '';

try {
    $conn = new PDO("mysql:host=${servername};dbname={$dbname}", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        $param_count = min(count($_POST['name']), count($_POST['number']), count($_POST['gender']));
        $value_list = array();
        $data_sets  = array();
        for ($i=0, $j=0, $n=$param_count; $i<$n; ++$i) {
            $name = input_checker($_POST['name'][$i]);
            $number = input_checker($_POST['number'][$i]);
            $gender = input_checker($_POST['gender'][$i]);
            $data_set = array(array('value' => $name, 'type' => PDO::PARAM_STR),
                              array('value' => $number, 'type' => PDO::PARAM_STR),
                              array('value' => $gender, 'type' => PDO::PARAM_STR));
            $value_list[] = sql_value_pair($j, $data_set);
            $data_sets[] = $data_set;
        }
        $sql_values = join(', ', $value_list);
        if ($sql_values) {
            $sql = "INSERT INTO `memo` (`name`, `number`, `gender`) VALUES {$sql_values}";
            $sth = $conn->prepare($sql) or trigger_error(print_r($sth->errorInfo(), true));
            foreach ($data_sets as $data_set) {
                foreach ($data_set as $data) {
                    $sth->bindValue($data['placeholder'], $data['value'], $data['type']);
                }
            }
            $sth->execute() or trigger_error(print_r($sth->errorInfo(), true));
            echo 'New record created successfully';
        }
    }
} catch(PDOException $e) {
    echo $sql . '<br>' . $e->getMessage();
}

$conn = null;
?>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
Name 1: <input type="text" name="name[]"><br>
Name 2: <input type="text" name="name[]"><br>
Name 3: <input type="text" name="name[]"><br>
Number 1: <input type="text" name="number[]"><br>
Number 2: <input type="text" name="number[]"><br>
Number 3: <input type="text" name="number[]"><br>
Gender 1: <input type="text" name="gender[]"><br>
Gender 2: <input type="text" name="gender[]"><br>
Gender 3: <input type="text" name="gender[]"><br>
<input type="submit" name="submit" value="Submit">
</form>