mysql语句安全性_Mysql安全性测试

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

// 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没';

$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 "
";

echo htmlspecialchars($str, ENT_QUOTES); // 转换双引号和单引号

echo "
";

echo htmlspecialchars($str, ENT_NOQUOTES); // 不转换任何引号

*/

/*

以上代码的 HTML 输出如下(查看源代码)：

Bill & 'Steve'

Bill & 'Steve'

Bill & 'Steve'

以上代码的浏览器输出：

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语句

// 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没' . " 测试打印号'我是单引号'哈哈";

$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();