依赖
<!-- druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<!-- 数据库操作工具类依赖 -->
<dependency>
<groupId>com.jfinal</groupId>
<artifactId>activerecord</artifactId>
<version>4.9.16</version>
</dependency>
配置类
package cn.window.debt.infrastructure.config;
import cn.hutool.core.util.StrUtil;
import cn.window.boot.base.common.util.Func;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import java.util.List;
import java.util.stream.Collectors;
/**
* backdoor配置
*/
@Slf4j
@Order
@Configuration("backdoorConfig")
@Data
@RefreshScope
public class BackdoorConfig implements InitializingBean {
// 是否启用后门
private boolean enable;
// 是否启用表名检查
private boolean enableTableCheck;
// 数据库url
private String debtDbUrl;
// 数据库用户
private String debtDbUser;
// 数据库密码
private String debtDbPwd;
// 数据库可操作性表名
private String workTables;
private List<String> workTableList;
@Value("${debt.backdoor.enable:false}")
public void setEnable(boolean enable) {
this.enable = enable;
}
@Value("${debt.backdoor.enableTableCheck:true}")
public void setEnableTableCheck(boolean enableTableCheck) {
this.enableTableCheck = enableTableCheck;
}
@Value("${debt.backdoor.debtDbUrl:}")
public void setDebtDbUrl(String debtDbUrl) {
this.debtDbUrl = debtDbUrl;
}
@Value("${debt.backdoor.debtDbUser:}")
public void setDebtDbUser(String debtDbUser) {
this.debtDbUser = debtDbUser;
}
@Value("${debt.backdoor.debtDbPwd:}")
public void setDebtDbPwd(String debtDbPwd) {
this.debtDbPwd = debtDbPwd;
}
@Value("${debt.backdoor.workTables:}")
public void setWorkTable(String workTables) {
this.workTables = workTables;
}
@Override
public void afterPropertiesSet() {
log.info("backdoor配置, enable -> {}, enableTableCheck -> {}, debtDbUrl -> {}, debtDbUser -> {}, debtDbPwd -> {}, workTables -> {}",
enable, enableTableCheck, debtDbUrl, debtDbUser, debtDbPwd, workTables);
if (StrUtil.isNotBlank(workTables)) {
this.workTableList = Func.toStrList(workTables).stream().map(tableName -> tableName.trim().toUpperCase()).collect(Collectors.toList());
}
}
}
执行SQL的工作类
package cn.window.debt.interfaces.back.actuator;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.CaseInsensitiveContainerFactory;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.dialect.OracleDialect;
import com.jfinal.plugin.druid.DruidPlugin;
import cn.window.boot.base.api.enums.BaseCode;
import cn.window.boot.base.api.exception.BizException;
import cn.window.debt.infrastructure.config.BackdoorConfig;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import java.io.*;
import java.util.*;
@Slf4j
@Component
public class SqlActuator {
@Autowired
private BackdoorConfig backdoorConfig;
private DruidPlugin debtDruidPlugin;
private ActiveRecordPlugin debtArp;
@Transactional(rollbackFor = Exception.class)
public Object execSql(File sqlFile) {
if (debtArp == null) {
debtDruidPlugin = new DruidPlugin(backdoorConfig.getDebtDbUrl(), backdoorConfig.getDebtDbUser(),
backdoorConfig.getDebtDbPwd(), "oracle.jdbc.driver.OracleDriver");
debtArp = new ActiveRecordPlugin("sqlActuator", debtDruidPlugin);
debtArp.setDialect(new OracleDialect());
debtArp.setContainerFactory(new CaseInsensitiveContainerFactory());
debtDruidPlugin.start();
debtArp.start();
}
String sqls = readFile(sqlFile);
if (StrUtil.isBlank(sqls)) {
throw new BizException(BaseCode.ERROR, "sql不能为空");
}
String[] sqlArr = sqls.split(";");
if (backdoorConfig.isEnableTableCheck()) {
checkTable(sqlArr);
}
Map<String, Object> resultMap = new LinkedHashMap<String, Object>();
s:
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i];
if (StrKit.notBlank(sql.trim())) {
if (sql.indexOf("--") == 0) {
continue s;
}
String[] wordArr = sql.split(" ");
for (String word : wordArr) {
word = word.toUpperCase();
if ("SELECT".equals(word)) {
List<Record> findList = Db.use("sqlActuator").find(sql);
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
for (Record record : findList) {
resultList.add(record.getColumns());
}
// log.info("SQL [{}] select result [{}]", sql, resultList);
resultMap.put("[SQL" + (i + 1) + "] select result", resultList);
continue s;
} else {
int update = Db.use("sqlActuator").update(sql);
// log.info("SQL [{}] rows [{}]", sql, update);
resultMap.put("[SQL" + (i + 1) + "] " + word.toLowerCase() + " rows", update);
continue s;
}
}
}
}
return resultMap;
}
@Transactional(rollbackFor = Exception.class)
public Object execSqlStr(String sqls) {
if (debtArp == null) {
debtDruidPlugin = new DruidPlugin(backdoorConfig.getDebtDbUrl(), backdoorConfig.getDebtDbUser(),
backdoorConfig.getDebtDbPwd(), "oracle.jdbc.driver.OracleDriver");
debtArp = new ActiveRecordPlugin("sqlActuator", debtDruidPlugin);
debtArp.setDialect(new OracleDialect());
debtArp.setContainerFactory(new CaseInsensitiveContainerFactory());
debtDruidPlugin.start();
debtArp.start();
}
if (StrUtil.isBlank(sqls)) {
throw new BizException(BaseCode.ERROR, "sql不能为空");
}
String[] sqlArr = sqls.split(";");
if (backdoorConfig.isEnableTableCheck()) {
checkTable(sqlArr);
}
Map<String, Object> resultMap = new LinkedHashMap<String, Object>();
s:
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i];
if (StrKit.notBlank(sql.trim())) {
if (sql.indexOf("--") == 0) {
continue s;
}
String[] wordArr = sql.split(" ");
for (String word : wordArr) {
word = word.toUpperCase();
if ("SELECT".equals(word)) {
List<Record> findList = Db.use("sqlActuator").find(sql);
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
for (Record record : findList) {
resultList.add(record.getColumns());
}
// log.info("SQL [{}] select result [{}]", sql, resultList);
resultMap.put("[SQL" + (i + 1) + "] select result", resultList);
continue s;
} else {
int update = Db.use("sqlActuator").update(sql);
// log.info("SQL [{}] rows [{}]", sql, update);
resultMap.put("[SQL" + (i + 1) + "] " + word.toLowerCase() + " rows", update);
continue s;
}
}
}
}
return resultMap;
}
/**
* 检查sql操作的表是否存在权限
*
* @param sqlArr
*/
private void checkTable(String[] sqlArr) {
log.info("检查sql操作的表是否存在权限");
Set<String> deniedTableNameSet = Sets.newHashSet();
for (String sql : sqlArr) {
if (StrUtil.isNotBlank(sql.trim())) {
if (sql.indexOf("--") == 0) {
continue;
}
}
List<String> tableNameList = getAllTableNameBySQL(sql);
for (String tableName : tableNameList) {
if (!backdoorConfig.getWorkTableList().contains(tableName.toUpperCase())) {
deniedTableNameSet.add(tableName);
}
}
}
if (ObjectUtil.isNotEmpty(deniedTableNameSet)) {
throw new BizException(BaseCode.ERROR, "表没有操作权限=>" + deniedTableNameSet);
}
}
private static List<String> getAllTableNameBySQL(String sql) {
// 数据库为ORACLE
String dbType = JdbcConstants.ORACLE;
try {
List<String> tableNameList = new ArrayList<>();
//格式化输出
String sqlResult = SQLUtils.format(sql, dbType);
log.info("格式化后的sql:[{}]", sqlResult);
List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
if (ObjectUtil.isEmpty(stmtList)) {
log.info("stmtList为空无需获取");
return Collections.emptyList();
}
for (SQLStatement sqlStatement : stmtList) {
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
sqlStatement.accept(visitor);
Map<TableStat.Name, TableStat> tables = visitor.getTables();
log.info("druid解析sql的结果集:[{}]", tables);
Set<TableStat.Name> tableNameSet = tables.keySet();
for (TableStat.Name name : tableNameSet) {
String tableName = name.getName();
if (StrUtil.isNotBlank(tableName)) {
tableNameList.add(tableName);
}
}
}
log.info("解析sql后的表名:[{}]", tableNameList);
return tableNameList;
} catch (Exception e) {
log.error("**************异常SQL:[{}]*****************\\n", sql);
throw new BizException("SQL异常=>" + sql);
}
}
private String readFile(File sqlFile) {
InputStreamReader reader = null;
BufferedReader bufReader = null;
try {
reader = new InputStreamReader(new FileInputStream(sqlFile));
bufReader = new BufferedReader(reader);
String line = "";
String result = "";
while ((line = bufReader.readLine()) != null) {
result += line;
}
return result;
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (bufReader != null) {
bufReader.close();
}
if (bufReader != null) {
bufReader.close();
}
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
}
接口
package cn.window.debt.interfaces.back.controller;
import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import com.jfinal.kit.StrKit;
import cn.window.boot.base.api.enums.BaseCode;
import cn.window.boot.base.api.exception.BizException;
import cn.window.boot.base.api.response.R;
import cn.window.debt.infrastructure.config.BackdoorConfig;
import cn.window.debt.interfaces.back.actuator.SqlActuator;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.util.Arrays;
/**
* backdoor接口
*/
@RestController
@Validated
@RequestMapping("/backdoor")
@Api(value = "backdoor", tags = "backdoor接口")
@Slf4j
public class BackdoorCtl {
@Autowired
private BackdoorConfig backdoorConfig;
@Autowired
private SqlActuator sqlActuator;
@PostMapping("/v1/execSql")
@ApiOperationSupport(order = 1)
@ApiOperation(value = "执行sql", notes = "", httpMethod = "POST")
public R<Object> execSql(MultipartFile file) {
if (!backdoorConfig.isEnable()) {
throw new BizException(BaseCode.ERROR, "backdoor未开启");
}
File tempFile = null;
Object execSqlResult = null;
try {
log.info("**************** sql执行start ****************");
String excelName = file.getOriginalFilename();
String[] excelNameArr = excelName.split("\\.");
if (excelNameArr.length == 2 && Arrays.asList("sql").contains(excelNameArr[1])) {
tempFile = multipartFileToFile(file);
execSqlResult = sqlActuator.execSql(tempFile);
} else {
log.error("不支持的数据导入文件");
}
} catch (Exception e) {
e.printStackTrace();
log.error("执行sql异常", e);
throw new BizException(BaseCode.SYSTEM_ERROR, e.getMessage());
} finally {
if (tempFile != null) {
tempFile.delete();
}
}
log.info("**************** sql执行end ****************");
return R.success(execSqlResult);
}
@GetMapping("/v1/execSqlStr")
@ApiOperationSupport(order = 1)
@ApiOperation(value = "执行sql", notes = "", httpMethod = "GET")
public R<Object> execSqlStr(String sqlStr) {
if (!backdoorConfig.isEnable()) {
throw new BizException(BaseCode.ERROR, "backdoor未开启");
}
Object execSqlResult = null;
try {
log.info("**************** sql执行start ****************");
execSqlResult = sqlActuator.execSqlStr(sqlStr);
} catch (Exception e) {
e.printStackTrace();
log.error("执行sql异常", e);
throw new BizException(BaseCode.SYSTEM_ERROR, e.getMessage());
}
log.info("**************** sql执行end ****************");
return R.success(execSqlResult);
}
private File multipartFileToFile(MultipartFile file) {
File toFile = null;
try {
if (file != null && file.getSize() > 0) {
InputStream ins = null;
ins = file.getInputStream();
toFile = new File(StrKit.getRandomUUID() + "-" + file.getOriginalFilename());
inputStreamToFile(ins, toFile);
ins.close();
}
} catch (IOException e) {
e.printStackTrace();
}
return toFile;
}
private static void inputStreamToFile(InputStream ins, File file) {
try {
OutputStream os = new FileOutputStream(file);
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytesRead);
}
os.close();
ins.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Nacos配置
debt.backdoor.enable=true
debt.backdoor.enableTableCheck=false
debt.backdoor.debtDbUrl=
debt.backdoor.debtDbUser=
debt.backdoor.debtDbPwd=
debt.backdoor.workTables=
测试