Mysql安全性测试
程序员文章站
2022-05-17 14:31:52
...
一、没有进行预处理的SQL语句
<?php
// 1.连接数据库
$conn = mysql_connect('127.0.0.1:3306', 'root', '518666');
if (!$conn)
{
die("Could not connect:" . mysql_error());
}
// 2.选择数据库
mysql_select_db('mysql_safe', $conn);
// 3.设置编码,注意这里是utf8而不是utf-8,如果写后者，MySQL不会识别的，会出现乱码的。
mysql_query("SET NAMES utf8");
$title = "我们的爱情";
$content = '你是/谁啊，大几\都"老梁"做做&>women<a>没';
$add_time = date("Y-m-d H:i:s");
// 转义字符
$content = mysql_real_escape_string($content);
$content = htmlspecialchars($content, ENT_COMPAT);
// 你是/谁啊，大几都做做&>women<a>没 // 自动过滤反斜杠
/*
// 4.插入一条数据
$insert_sql = "insert into post_tbl (title, content, user_id, add_time) values ('{$title}', '{$content}', '4742551', '{$add_time}')";
if(mysql_query($insert_sql))
{
echo 'ok';
}
else
{
echo "Error : " . mysql_error();
}
$ret = mysql_affected_rows();
print_r($ret);
*/
// 5.PDO预处理插入
// PDO（PHP Data Object）则是提供了一个 Abstraction Layer 来操作数据库
// 查询
$user_id = 174742;
$password = "''or '1=1'" ;
$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 & 'Steve'";
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 & 'Steve'<br>
Bill & 'Steve'<br>
Bill & 'Steve'
</body>
</html>
以上代码的浏览器输出：
Bill & 'Steve'
Bill & 'Steve'
Bill & 'Steve'
*/
function mforum_html_tag_to_html_entity($content)
{
$content = (string)trim($content);
if(empty($content)) return '';
// $content = str_replace(' ', ' ', $content);
$content = htmlspecialchars($content, ENT_COMPAT, GB2312, false);
$content = str_replace(">", ">", $content);
$content = str_replace("<", "<", $content);
$content = str_replace("\"", """, $content);
$content = preg_replace("/\\\$/", "$", $content);
$content = preg_replace("/\r/", "", $content);
$content = str_replace("!", "!", $content);
$content = str_replace("'", "'", $content);
$content = preg_replace("/\\\/", "\", $content);
// 内容敏感词过滤
return $content;
}
二、PDO处理的SQL语句
<?php
// PDO的使用
// http://blog.csdn.net/qq635785620/article/details/11284591
$dbh = new PDO('mysql:host=127.0.0.1:3306;dbname=mysql_safe', 'root', '518666');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->exec('set names utf8');
$title = "我们的爱情";
$content = '你是/谁啊，大几\都"老梁"做做&>women<a>没' . " 测试打印号'我是单引号'哈哈";
$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('x_title'=>$title,':x_content'=> $content, ':x_user_id' => $user_id, ':x_add_time' => $add_time));
// 查询
$user_id = "17474#";
// $password = "''or '1=1'";
$password = 123456;
$sql = 'select * from post_tbl where user_id = :x_user_id and password = :x_password';
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':x_user_id'=>$user_id, ':x_password' => $password));
$rows = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
$rows[] = $row;
}
print_r($rows);
// echo $dbh->lastinsertid();