使用正则表达式解析sql

使用正则表达式解析sql

背景

java后端开发有时候要对接口进行权限控制，接口下所有的sql进行拦截修改，比如原来的sql 是 select * from user where name = “肥仔机器人” 要解析sql 进行修改后 select * from user where father = “橘猫博士” and name = “肥仔机器人”

代码

import org.apache.commons.lang3.StringUtils;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SQLParse {
	private String selectPart ;
	private String fromPart ;
	private String wherePart ;
	private String groupByPart ;
	private String orderByPart ;
	private String limitPart ;

	public SQLParse(String sql) {
	   //正则表示，大小通配
		Pattern pattern = Pattern.compile(
				"SELECT (.+?) FROM (.+?)(?: WHERE (.*?))?(?: GROUP BY (.*?))?(?: ORDER BY (.*?))?(?: LIMIT (.*?))?$",
				Pattern.CASE_INSENSITIVE
		);
		sql = sql.replace("\n", " ");
		Matcher matcher = pattern.matcher(sql);

		if (matcher.find()) {
			 selectPart = matcher.group(1);
			 fromPart = matcher.group(2);
			 wherePart = matcher.group(3);
			 groupByPart = matcher.group(4);
			 orderByPart = matcher.group(5);
			 limitPart = matcher.group(6);
		}
	}

	public String getSelect() {
		//解析select项
		return "SELECT " + selectPart;
	}

	public String getFrom() {
		//解析from
		return " FROM " + fromPart;
	}

	public String getWhere() {
		//解析where  

		if (StringUtils.isNotBlank(wherePart) ) {
			return " WHERE (" + wherePart + ")";
		} else {
			return "";
		}
	}

	//group by ... having 
	public String getGroupBy() {
		if (StringUtils.isNotBlank(groupByPart) ) {
			return " GROUP BY " + groupByPart + " ";
		} else {
			return "";
		}
	}

	//解析order by
	public String getOrderBy() {
		if (StringUtils.isNotBlank(orderByPart) ) {
			return " ORDER BY " + orderByPart + " ";
		} else {
			return "";
		}
	}

	//limit  
	public String getLimit() {
		if (StringUtils.isNotBlank(limitPart) ) {
			return " LIMIT  " + limitPart + " ";
		} else {
			return "";
		}
	}

	public static void main(String[] args) {
		String sql = " select * from user where name = \"肥仔机器人\" ";
		SQLParse2 sqlParse = new SQLParse(sql);
		System.out.println("Select:    "+   sqlParse2.getSelect());
		System.out.println("From:    "+   sqlParse2.getFrom());
		System.out.println("Where:     "+   sqlParse2.getWhere());
		System.out.println("GroupBy:     "+   sqlParse2.getGroupBy());
		System.out.println("OrderBy:     "+   sqlParse2.getOrderBy());
		System.out.println("Limit:     "+   sqlParse2.getLimit());
		System.out.println("sql:     "+   sqlParse2.getSelect()+sqlParse2.getFrom()+sqlParse2.getWhere()+" and father = \"橘猫博士\""+sqlParse2.getGroupBy()+sqlParse2.getOrderBy()+sqlParse2.getLimit());
	}

输出

Select:    SELECT *
From:     FROM user
Where:      WHERE (name = "肥仔机器人" )
GroupBy:     
OrderBy:     
Limit:     
sql:     SELECT * FROM user WHERE (name = "肥仔机器人" )and = "橘猫博士"

注意

该方法只是能解析简单的查询sql