我写了下面的脚本作为我的第一个php-mysql应用程序。我自学成才,代码按预期工作。我的主机认为它可能容易受到sql注入攻击,但无法告诉我为什么或应该更改什么以使其变得更好。我确信它并没有那么干净,但如果有人有任何建议或见解,我一定会很感激。
<form method="post" action="search.php?go" id="searchform">
<?php
$db=mysql_connect ("server", "*", "*") or die ('I cannot connect to the database because: ' . mysql_error());
$mydb=mysql_select_db("*");
$category_sql="SELECT distinct category FROM Members";
$category_Options="";
$category_result=mysql_query($category_sql) or die ('Error: '.mysql_error ());
while ($row=mysql_fetch_array($category_result)) {
$category=$row["category"];
$category_Options.="<OPTION VALUE='"$category'">".$category.'</option>';
}
?>
<p>
<SELECT NAME="category"><OPTION VALUE=0>Choose<?=$category_Options?></SELECT>
</p>
<input name="submit" "id="submit" type="submit" value="submit" />
</form>
<?php
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
$category=$_POST['category'];
$category=mysql_real_escape_string($category);
$sql="SELECT category, company, address, city, state, zip, phone, web, addescription, image
FROM Members
WHERE category LIKE '$category'";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
$category2=$row["category"];
$company=$row["company"];
$address=$row["address"];
$city=$row["city"];
$state=$row["state"];
$zip=$row["zip"];
$phone=$row["phone"];
$web = $row["web"];
$addescription = $row["addescription"];
$image = $row["image"];
echo "<blockquote>";
if(@file_get_contents($image))
{
echo "<img src='".$image ."' class='image'/>'n";
}
else
{
}
echo "<p>'n";
echo "</br>".$category2 . "'n";
echo "</br><b>".$company . "</b>'n";
echo "</br>".$address . "'n";
echo "</br>".$city . ", ".$state. " ".$zip . "'n";
echo "</br>".$phone . "'n";
echo "</br><a href=http://".$web .">".$web ."</a>'n";
echo "</br>".$addescription . "'n";
echo "</br><a href=http://www.printfriendly.com style=color:#6D9F00;text-decoration:none; class=printfriendly onclick=window.print();return false; title=Printer Friendly and PDF><img style=border:none; src=http://cdn.printfriendly.com/pf-button.gif alt=Print Friendly and PDF/></a>'n";
echo "</p>";
echo "</blockquote>"
;
}
}
else{
echo "<p>Please select a Category</p>";
}
}
mysql_close($db)
?>
MySQL函数已被弃用。使用MySQLi函数和准备好的语句是抵御sql注入攻击的更好方法。
$stmt = $mysqli->prepare('SELECT category, company, address, city, state, zip, phone, web, addescription, image FROM Members WHERE category LIKE ?');
$stmt->bind_param('s', $category);
我将展示PDO连接的实现以及如何使用它进行查询!首先,我们使用数据库凭据创建连接变量。我们将把这个连接存储在$db
中。
$username = "root";
$password = "";
$host = "localhost";
$dbname = "my_database";
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
try{
$db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8"; $username, $password, $options);
}catch(PDOException $ex){
die("Failed to connect: ".$ex->getMessage());
}
现在您有一个PDO连接存储在$db
中,您可以通过它进行查询。如果你不使用PHP 5.4,你可能需要考虑魔术引号,所以请记住这一点。
否则,创建这样的查询语句。.
$query = "SELECT category, company, address, city, state, zip, phone, web, addescription, image FROM Members WHERE category LIKE :category"
然后,您希望将$_POST['category']
变量的值(或创建后的$category
)绑定到参数:category
。这样做:
$query_params = array( ':category' => $category);
最后,现在您已经有了语句和参数,请使用之前创建的$db
变量来准备和执行该语句。
$statement = $db->prepare($query);
$result = $statement->execute($query_params);
由于我们是SELECT
,它可以返回多行(假设一个类别中有多行),因此我们需要考虑这一点。抓取语句返回的行,如下所示:
$rows = $statement->fetchAll();
现在,您可以通过使用foreach语句来引用数据库表的每个$row
中的列标题。
$citiesArray = array();
foreach($rows as $row){
if(isset($row['city'])){
$citiesArray[] = $row['city'];
}
}
希望能帮上忙!
记住永远不要信任用户的黄金法则。永远不要接受任何原始用户输入并将其插入数据库,因为您可能会对安全问题敞开大门。
您的代码似乎很好。但是,请注意,MySQL从PHP 5.5.0开始就被弃用了,您应该使用MySQLi或PDO扩展,它们提供了更多的安全性。
也许这就是你的主机说这样的话的原因,但从你的代码来看,我觉得很好
干杯。
问题在于代码中的以下部分
$sql = "SELECT category, company, address, city, state, zip,
phone, web, addescription, image
FROM Members
WHERE category LIKE '$category'";
$result=mysql_query($sql);
如果参数$category是从GET或POST参数中读取的,则应该对其进行转义:
$sql = "SELECT category, company, address, city, state, zip,
phone, web, addescription, image
FROM Members
WHERE category LIKE '" . mysql_real_escape_string($category) . "';";
如果采用这种方式,则该变量不能用于SQL注入
顺便说一句(就像Matthew Johnson所说的),从PHP 5.5开始,程序性mysql扩展就被弃用了。你最好使用Mysqli或PDO。
OOP方式(强烈推荐)如下:
$pdo = new PDO($dsn, $user, $password, $options);
$statement = $pdo->prepareStatement(
"SELECT category, company, address,
city, state, zip, phone, web,
addescription, image
FROM Members
WHERE category LIKE :category;");
$statement->bindParam(':category', $category, PDO::PARAM_STR);
$statement->execute();
$categories = $statement->fetchAll();