php - 使用 PHP 中的表单值更新 MySQL 数据库行

Question

我有一个简单的 html 表单页面，需要更新 2 列值，其中 'Some_DB_Table'.id = 用户输入的 ID 号。然而，我对如何使用准备好的语句来避免 SQL 注入(inject)有点困惑。

我的代码:

HTML:

<form id="workorderMovement" name='workorderMovement_form' action="workordermovementGET.php" method="post">



<fieldset id="userid">

  <span>Welcome <?php echo $user ?> </span>

</fieldset> 




<fieldset id="sgnum">

<fieldset id="fieldset" style="text-align: center;"> 
  <span>Please enter the SG Number</span>
</fieldset>

<input type="text" name="sgnumber" id="sgnumber"> &nbsp;&nbsp;&nbsp; <input type="button" name="searchButton" id="searchButton" value="SEARCH">

</fieldset> 


<br/>
<br/>



<fieldset id="stageSelectField">

  <fieldset id="fieldset" style="text-align: center;"> 
    <span>Please select the Stage Completed</span>
  </fieldset>

<select name="stageSelect" id="stageSelect">
  <option value="Please Select">Please Select</option>
  <option value="Film Done">Film Done</option>
  <option value="Staged Done">Staged Done</option>
  <option value="Cleanroom Done">Cleanroom Done</option>
  <option value="GB2 Done">GB2 Done</option>
  <option value="Bagging Done">Bagging Done</option>
  <option value="Inspection Done">Inspection Done</option>
  <option value="LC Done">LC Inspection Done</option>
  <option value="IGU Done">IGU Done</option>
</select>

</fieldset> 


<br/>
<br/>


<fieldset id="floorNotesField">

  <fieldset id="fieldset" style="text-align: center;"> 
    <span>Please enter any new work order notes</span>
  </fieldset>

  <textarea type="text" name="floorNotes" id="floorNotes" class="floorNotesText"></textarea>

</fieldset>


<br/>
<br/>
<br/>

</form> <!-- End Work Order Movement Form -->

<fieldset id="doneButtonField">

  <input type="button" name="doneButton" id="doneButton" value="DONE">

</fieldset> 

我的 AJAX:

 j("#doneButton").click(function(){


 //send Workorder Movement Data values to php using ajax.

 var sgnumber = j('#sgnumber').val();
 var stageselect = j('#stageSelect').val();
 var floornotes = j('#floorNotes').val();
 j.ajax ({
    method: 'POST',
    url: "workordermovementUPDATE.php",
    data: {sgNumber: sgnumber, stageSelect: stageselect, floorNotes: floornotes},
    dataType: 'json',
    success: function( data ){
        alert(data);
    }
  });

});

我的 PHP:

    <?php 


include('inc.php');


//Get Table Options.
if (isset($_POST['sgNumber'])) {
    $sgNumber = $_POST['sgNumber'];

    if (isset($_POST['stageSelect'])) {
        $stageSelect=$_POST['stageSelect'];
    }
    if (isset($_POST['floorNotes'])) {
        $floorNotes=$_POST['floorNotes'];
    }

    //connect  to the database 
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if(mysqli_connect_errno() ) {
        printf('Could not connect: ' . mysqli_connect_error());
        exit();
    }

    $conn->select_db($dbname);

    if(! $conn->select_db($dbname) ) {
        echo 'Could not select database. '.'<BR>';
    }

    $sql= "UPDATE invoices SET productionstage = ".$stageSelect.", floornotes = ".$floorNotes." WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('i', $sgNumber);
    $stmt->execute();
    $stmt->store_result();     

    if(mysqli_query($conn, $stmt)){
        echo "".$sgnumber." Updated Successfully!";
    } else {
        echo "ERROR: Could not update ".$sgnumber."".mysqli_error($conn)."";
    }


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


//Free the result variable. 
 $result->free();


//Close the Database connection.
 $conn->close();

}//End If statement

?>

这是正确的/有什么建议吗？

谢谢!

Answer 1

如果您阅读the documentation on bind_param仔细地你会发现你需要指定每个参数的类型。通常这没什么大不了的:

 $stmt = $conn->prepare(
   "UPDATE invoices SET productionstage=?,floornotes=? WHERE id = ?"
 );
 $stmt->bind_param('ssi', $stageSelect, $floorNotes, $sgNumber);

尽量避免为语句创建中间变量。这通常会导致您无意中运行错误的查询。