Home > Article > Database > Mysql security testing

Mysql security testing

高洛峰Original: 2016-11-21 16:30:361044browse

一、没有进行预处理的SQL语句

<?php
    
    // 1.连接数据库
  $conn = mysql_connect(&#39;127.0.0.1:3306&#39;, &#39;root&#39;, &#39;518666&#39;);
  if (!$conn)
  {
    die("Could not connect:" . mysql_error());
  }

  // 2.选择数据库
  mysql_select_db(&#39;mysql_safe&#39;, $conn);


  // 3.设置编码,注意这里是utf8而不是utf-8,如果写后者，MySQL不会识别的，会出现乱码的。
  mysql_query("SET NAMES utf8");

  $title    = "我们的爱情";
  $content  = &#39;你是/谁啊，大几\都"老梁"做做&>women<a>没&#39;;
  $add_time = date("Y-m-d H:i:s");

  // 转义字符
  $content = mysql_real_escape_string($content);
  $content = htmlspecialchars($content, ENT_COMPAT);
  // 你是/谁啊，大几都做做&amp;&gt;women&lt;a&gt;没   // 自动过滤反斜杠
/*
  // 4.插入一条数据
  $insert_sql = "insert into post_tbl (title, content, user_id, add_time) values (&#39;{$title}&#39;, &#39;{$content}&#39;, &#39;4742551&#39;, &#39;{$add_time}&#39;)";
 if(mysql_query($insert_sql))
  {
    echo &#39;ok&#39;;

  }
  else
  {
    echo "Error : " . mysql_error();
  }
   $ret = mysql_affected_rows();
  print_r($ret);
  */
   // 5.PDO预处理插入
   // PDO（PHP Data Object）则是提供了一个 Abstraction Layer 来操作数据库
    // 查询
    $user_id  = 174742;
    $password = "&#39;&#39;or &#39;1=1&#39;" ;
    $sql = "select * from post_tbl where user_id = {$user_id} and password = {$password}";

    print_r($sql);
    $query  = mysql_query($sql);
    // $result = mysql_fetch_array($query);

    $rows = array();
    while($row=mysql_fetch_array($query))
    {
         $rows[] = $row;
    }

   
    print_r( $rows);




  // 关闭数据库连接
  mysql_close($conn);

/*

$str = "Bill & &#39;Steve&#39;";
echo htmlspecialchars($str, ENT_COMPAT); // 只转换双引号
echo "<br>";
echo htmlspecialchars($str, ENT_QUOTES); // 转换双引号和单引号
echo "<br>";
echo htmlspecialchars($str, ENT_NOQUOTES); // 不转换任何引号
*/

/*
以上代码的 HTML 输出如下（查看源代码）：
<!DOCTYPE html>
<html>
<body>
Bill &amp; &#39;Steve&#39;<br>
Bill &amp; &#039;Steve&#039;<br>
Bill &amp; &#39;Steve&#39;
</body>
</html>
以上代码的浏览器输出：
Bill & &#39;Steve&#39;
Bill & &#39;Steve&#39;
Bill & &#39;Steve&#39;
*/

  function mforum_html_tag_to_html_entity($content)
{
  $content = (string)trim($content);
  if(empty($content)) return &#39;&#39;;
  // $content = str_replace(&#39; &#39;, &#39; &#39;, $content);
  $content = htmlspecialchars($content, ENT_COMPAT, GB2312, false);
  $content = str_replace("&gt;", "&#62;", $content);
  $content = str_replace("&lt;", "&#60;", $content);
  $content = str_replace("\"", "&quot;", $content);
  $content = preg_replace("/\\\$/", "&#036;", $content);
  $content = preg_replace("/\r/", "", $content);
  $content = str_replace("!", "&#33;", $content);
  $content = str_replace("&#39;", "&#39;", $content);
  $content = preg_replace("/\\\/", "&#092;", $content);

  // 内容敏感词过滤
  return $content;
}

二、PDO处理的SQL语句

<?php  

// PDO的使用
// http://blog.csdn.net/qq635785620/article/details/11284591
$dbh = new PDO(&#39;mysql:host=127.0.0.1:3306;dbname=mysql_safe&#39;, &#39;root&#39;, &#39;518666&#39;); 
 
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
$dbh->exec(&#39;set names utf8&#39;);   

$title    = "我们的爱情";
$content  = &#39;你是/谁啊，大几\都"老梁"做做&>women<a>没&#39; . " 测试打印号&#39;我是单引号&#39;哈哈";
$user_id  = 174742;
$add_time = date("Y-m-d H:i:s");

// $insert_sql = "insert into post_tbl (title, content, user_id, add_time) values (:x_title, :x_content, :x_user_id, :x_add_time)";

// $stmt = $dbh->prepare($insert_sql); 
// $stmt->execute(array(&#39;x_title&#39;=>$title,&#39;:x_content&#39;=> $content, &#39;:x_user_id&#39; => $user_id, &#39;:x_add_time&#39; => $add_time));    

// 查询
$user_id  = "17474#";
// $password = "&#39;&#39;or &#39;1=1&#39;";
 $password = 123456;
$sql = &#39;select * from post_tbl where user_id = :x_user_id and password = :x_password&#39;;
$stmt = $dbh->prepare($sql);    
$stmt->execute(array(&#39;:x_user_id&#39;=>$user_id, &#39;:x_password&#39; => $password));    

$rows = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{   
   $rows[] = $row;    
    
}   
print_r($rows);    

// echo $dbh->lastinsertid();

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：ORACLE adds HINT to SQL statements through SPMNext article：ORACLE adds HINT to SQL statements through SPM

See more

Mysql security testing

Related articles