SQL注入使用预处理语句[重复]

Question

This question already has an answer here:

• How can I prevent SQL injection in PHP? 28 answers

I'm doing security against SQL Injection in a specific page.

I'm using mysqli, for the db connection, and using prepared-statements.

Problem: PHP problem

Solution: have the working / displaying data

<p class="results-count">Records with the text: <b><?php echo $category = $_GET['target']; ?></b> and category: <b><?php echo $category = $_GET['category']; ?></b><span class="search-query"></span></p>

Action: Debug

[before] code:

$target = $_GET['target'];
$company = $_GET['company'];
$category = $_GET['category'];
// Make the query:
$sql = "select * 
from companies where ((Company_Name) LIKE ('%$target%') OR (Company_Subcategory) LIKE ('%$target%') OR (Keywords) LIKE ('%$target%') OR (Description) LIKE ('%$target%')) AND Company_Category = '$category' AND Featured = 'Y' order by Date_Created DESC";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while ($row = mysqli_fetch_assoc($result)) {
$company=$row['companyID'];
$name=$row['Company_Name'];
$image = $row['Company_Logo'];
$myArray = json_decode($image, true);
echo '<div class="item prem-biz-list featured-biz col-md-9 col-xs-9">
<div class="thumbnail">
<div class="place-img-list col-md-5 col-sm-5">
<img class="group list-group-image img-responsive" src="'.$myImage = $myArray[0]['name'].'" alt="" />
</div>
<div class="caption col-md-7 col-sm-7">
<div class=" row title-row">
<p class="featured-text caps">Featured</p>
</div>
<div class="biz-info-caption">
<h2 class="group inner place-title-list">'.$row['Company_Name'].'</h2>
<a href="tel:[phone]" class="phone-num">'.$row['Telephone_Number'].'</a>
<p class="address-list">'.$row['Address'].'</p>
<p class="group inner place-description">'.$row['Description'].'</p>
<p><a class="more-info-list" href="mini-about.php?company='.$company.'">More Information</a></p>
<p class="caps biz-cat-list">Category: <span class="biz-cat-name-list caps">'.$row['Company_Category'].'</span></p>   
<p class="caps biz-subcat-list">Subcategory: <span class="biz-subcat-name-list caps">'.$row['Company_Subcategory'].'</span></p>
</div>
</div>
</div>
</div>';   }}

[now] code:

<?php
// 1. Connect
$mysqli = new mysqli("localhost", "username", "password", "database");
if($mysqli->connect_errno) {
die("Connect failed: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
}       
// 2. Prepare
$sql = "select * 
from companies where (Company_Name) LIKE (?) OR (Company_Subcategory) LIKE (?) OR (Keywords) LIKE (?) OR (Description) LIKE (?) AND Company_Category = ? AND Featured = 'Y' order by Date_Created DESC";
$stmt = $mysqli->prepare($sql);
if(!$stmt) {
    die("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
}
//Variables
$target = $_GET['target'];
$company = $_GET['company'];
$category = $_GET['category'];
// 3. Bind params
// s = string
// i = integer
// d = double (float)
// b = blob (binary data)
$bind_result = $stmt->bind_param("sssss", '%$target%', '%$target%', '%$target%', '%$target%', '$category');
if(!$bind_result) {
    echo "Binding failed: (" . $stmt->errno . ") " . $stmt->error;
}
// 4. Execute
$execute_result = $stmt->execute();
if(!$execute_result) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
// $stmt->store_result();
// 5. Bind selected columns to variables
$stmt->bind_result($id, $username);
// 6. Use results
$row = $stmt->fetch();
while($row) {
$company=$row['companyID'];
$name=$row['Company_Name'];
$image = $row['Company_Logo'];
$myArray = json_decode($image, true);
echo '<div class="item prem-biz-list featured-biz col-md-9 col-xs-9">
<div class="thumbnail">
<div class="place-img-list col-md-5 col-sm-5">
<img class="group list-group-image img-responsive" src="'.$myImage = $myArray[0]['name'].'" alt="" />
</div>
<div class="caption col-md-7 col-sm-7">
<div class=" row title-row">
<p class="featured-text caps">Featured</p>
</div>
<div class="biz-info-caption">
<h2 class="group inner place-title-list">'.$row['Company_Name'].'</h2>
<a href="tel:[phone]" class="phone-num">'.$row['Telephone_Number'].'</a>
<p class="address-list">'.$row['Address'].'</p>
<p class="group inner place-description">'.$row['Description'].'</p>
<p><a class="more-info-list" href="mini-about.php?company='.$company.'">More Information</a></p>
<p class="caps biz-cat-list">Category: <span class="biz-cat-name-list caps">'.$row['Company_Category'].'</span></p>   
<p class="caps biz-subcat-list">Subcategory: <span class="biz-subcat-name-list caps">'.$row['Company_Subcategory'].'</span></p>
</div>
</div>
</div>
</div>';   }
// 7. Free results
$stmt->free_result();
// 8. Close statment
$stmt->close();
// 9. Close MySQL connection
$mysqli->close();

How can that be fixed? (apologies for this amount of code, tried to explain the best I could using the least amount of code possible and you would still understand the context / origin . It's all commented, specially in the new one

</div>

Answer 1

Add a semicolon.

$row = $stmt->fetch();

[Addendum. In answer to your comment question. ]

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo '<h2>' . $row['Company_Name'] . '</h2>';
}