从mysqli_query到mysqli_prepare有问题

Question

我是PHP新手，我创建了一个简单的php站点，允许我提交表单并删除存储在数据库中的数据。有人告诉我，最好使用准备好的语句来避免SQL注入。

我更新了我的删除，它仍然有效，不确定它是否完全正确：

<?php

include("dbconnect.php");

$getid = $_GET["id"];
$delete = mysqli_prepare($database,"DELETE FROM contacts WHERE id IN ($getid)");
mysqli_stmt_execute($delete);

header("Location:http://localhost/address-book");
exit;

?>

但我似乎无法让add to数据库特性工作。我尝试过各种不同的方式来写它，但我确信我遗漏了一些简单的东西。下面是我最初编写的不安全代码：

<?php

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

include("inc/dbconnect.php");

// assigns form data to table columns
$assign = "INSERT INTO contacts(firstName,lastName,email,phone,birthday) VALUES ('$_POST[firstName]','$_POST[lastName]','$_POST[email]','$_POST[phone]','$_POST[birthday]')";

//execute query
if (mysqli_query($database,$assign)) {
    header("Location:http://localhost/address-book/");
    exit;
} else {
    exit;
}

?>

如果有人能引导我朝着正确的方向前进，我将心存感激。这一切我都是新手。

更新:我已经更新了我的原始代码，并为delete:提供了以下内容

<?php

include("dbconnect.php");

$getid = $_GET["id"];
$delete = mysqli_prepare($database,"DELETE FROM contacts WHERE id IN (?)");
mysqli_stmt_bind_param($delete, 's', $getid);
mysqli_stmt_execute($delete);

header("Location:http://localhost/address-book");
exit;

?>

和添加特性：

<?php

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

include("inc/dbconnect.php");

$firstName = "$_POST[firstName]";
$lastName = "$_POST[lastName]";
$email = "$_POST[email]";
$phone = "$_POST[phone]";

// assigns form data to table columns
$assign = mysqli_prepare($database,"INSERT INTO contacts(firstName,lastName,email,phone) VALUES (?,?,?,?)");
mysqli_stmt_bind_param($assign, 'ssss', $firstName, $lastName, $email, $phone);
mysqli_stmt_execute($assign);
exit;

}

?>

Answer 1

一个简单的准备语句是类似于

$query = $this->db->prepare("Query here WHERE something = ?") --注意这个例子取自我的站点，所以您可能有其他的东西而不是$this->->prepare.

关键是"= something "被表示为问号。

然后将该问号的值绑定到查询。

$query->bindValue(1, passed in parameter)

作为一个充分发挥作用的例子：

    //function to add 1 to downloads each time a file is downloaded
public function addToDownload($filename){

    $query = $this->db->prepare('UPDATE trainingMaterial SET downloads = downloads + 1 WHERE filename = ?');
    $query->bindValue(1, $filename);

    try{

        $query->execute();

    }catch(PDOException $e){
        die($e->getMessage());
    }

}


Your query `$assign = "INSERT INTO contacts(firstName,lastName,email,phone,birthday) VALUES ('$_POST[firstName]','$_POST[lastName]','$_POST[email]','$_POST[phone]','$_POST[birthday]')";

将会是

$assign = "INSERT INTO contacts(firstName,lastName,email,phone,birthday) VALUES ?,?,?,?,?)";
$assign->bindValue(1, '$_POST[firstName]')
$assign->bindValue(2, '$_POST[lastName]')

等