php - 从 Web 表单插入 PDO

Question

我尝试使用 PHP 和 MySQL 进行 PDO 插入的方式有什么问题？

MySQL 数据库使用与 ($_POST) 变量相同的名称。

<?php
if (!empty($_POST)) {

//Declare Database Variables Here

$dblist = ($_POST);
$keys = array_keys($data);

$dbcols = join(', ', array_values($keys));
$data = join(', ',array_values($dblist));

$dbtype = "mysql";
$dbhost = '127.0.0.1';
$dbname = 'bpstalent';
$dbuser = 'root';
$psword = 'root';
$portno = 3306;

// if table_name is submitted, display dynamic table with another form request for table name

$pdo = new PDO('mysql:host=' . $dbhost . ';port=' . $portno . 'dbname=' . $dbname . ';' . $dbuser . ';' . $psword . ';' );

    echo "form submitted";

$sql = "INSERT INTO 'applicants'($dbcols) VALUES ($data)";

$stmt = $pdo->prepare($sql);

$stmt->execute();

} 

else {

?>

此处为 html 表单

<?
    ;}
?>

Answer 1

此外，我认为您很容易成为 SQL 注入(inject)的目标。我会改变

$sql = "INSERT INTO 'applicants'($dbcols) VALUES ($data)";

并将 $dbcols 的值替换为硬编码的列列表(它们也可以来自数组，只是不是由用户发送的)

对于$data，我将用bindValue() 处理的参数替换它。您可以通过用“,:”替换“,”来实现这一点，将名称转换为para占位符。如果回显您的最终查询将如下所示:

$sql = "INSERT INTO applicants (col1, col2, col3) VALUES (:col1, :col2, :col3)";

请参阅 PHP 文档中的此示例，值得您花时间来保护此表单:

http://www.php.net/manual/en/pdostatement.bindvalue.php

下面是重新编写的脚本示例，未经测试，但应该可以让您了解如何使事情变得更安全:

<?php
if ($_POST) {

    //HARDCODE COLUMNS, DO NOT RELY ON USER INPUT, BAD THINGS WILL HAPPEN IF YOU DO
    $keys = array("col1", "col2", "col3");

    $dbcols = '`'.join('`, `', $keys).'`';
    $placeholders = ':'.join(', :', $keys);

    //RUN THROUGH POST DATA LOOKING FOR YOUR KEYS, ONLY PASS TO DATABASE DATA YOU ARE EXPECTING TO SEE
    $data = array();
    foreach ($keys as $k)
    {
        $data[$k] =  $_POST[$k];
    }

    //CONSIDER REPLACING THIS WITH require_once() FILE
    $dbtype = "mysql";
    $dbhost = '127.0.0.1';
    $dbname = 'bpstalent';
    $dbuser = 'root';
    $psword = 'root';
    $portno = 3306;

    $pdo = new PDO("$dbtype:host=$dbhost;port=$portno;dbname=$dbname", $dbuser, $psword);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "INSERT INTO applicants ($dbcols) VALUES ($placeholders)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($data);
}