Column ‘tenant_id’ in where clause is ambiguous exception handling
Tip: If you don’t have enough time, please ignore my long-winded background and the process of analyzing the problem below, and skip directly to the step of solving the problem! ! !
Background
Recently, the company organized code vulnerability scanning, which exposed many problems, the most important of which is that the versions of springboot (2.1.10.RELEASE) and springcloud (Greenwich.SR4) are too low, resulting in many vulnerabilities in many corresponding components. Considering that the joint-stock company will conduct network attack and defense drills in the near future, the company leaders decided to upgrade the program version to solve many known vulnerabilities, which led to this problem.
On the premise of version upgrade, Mybatis-plus (3.2.0) needs to be upgraded to version 3.4.2. Due to the multi-tenant situation of the company’s business, the multi-tenant plug-in of Mybatis-plus is naturally used. The company introduced the concept of multi-tenancy in the early demand analysis, so this field was reserved when designing the database table structure. However, in the later stage of code writing, many developers did not strictly follow the development documents. The tenant ID (tenant_id) field was manually spliced in many insert SQLs, which resulted in duplication of the tenant_id field in the insert statement after the multi-tenant plug-in was enabled later.
Therefore, it is not only time-consuming and labor-intensive to delete the manually spliced tenant ID field in the code, but it may not be able to filter all of them. Therefore, in order to solve the problem of inserting the repeated tenant ID field in the SQL statement, we inherited the TenantSqlParser class, and the TenantSqlParser’s The processInsert method is rewritten. By parsing the columns field under the Insert class, it is judged whether the tenant ID field is already included. If it already exists, automatic splicing will not be performed through the multi-tenant plug-in. The implementation method will be mentioned below.
At the same time, in the query statement, because the built-in function of mybatis-plus will only splice the tables or subqueries behind left, from and where to add the tenant id, but when there is a subquery in the Select, the tenant ID will not be spliced automatically. Therefore, in order to realize the subquery automatic splicing of tenant IDs, we also rewrite the processPlainSelect method of TenantSqlParser to deal with the nested query splicing of tenant IDs in the Select part.
import com.baomidou.mybatisplus.core.toolkit.ExceptionUtils;
import com.baomidou.mybatisplus.extension.plugins.tenant.TenantSqlParser;
import net.sf.jsqlparser.expression.CaseExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.WhenClause;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.expression.operators.relational.MultiExpressionList;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SubSelect;
import java.util.List;
/***
* Multi-tenant sql parser extension
* @description: multi-tenant sql parser extension
* @author: nilong
* @date: 2022/5/16 11:10
* @return:
*/
public class TenantSqlParserExt extends TenantSqlParser {
private static final String IF_NULL = "IFNULL";
/**
* Whether to process inserted sql
*/
private boolean isHandlerInsert = true;
/**
* Whether to process the sql of the query
*/
private boolean isHandlerSelect = false;
@Override
public void processSelectBody(SelectBody selectBody) {
if(isHandlerSelect){
super.processSelectBody(selectBody);
}
}
@Override
public void processInsert(Insert insert) {
if (getTenantHandler(). doTableFilter(insert. getTable(). getName())) {
// filter exit execution
return;
}
boolean isExistTenantId =isExistTenantColumn(insert);
if (isExistTenantId) {
return;
}
insert.getColumns().add(new Column(getTenantHandler().getTenantIdColumn()));
if (insert. getSelect() != null) {
processPlainSelect((PlainSelect) insert. getSelect(). getSelectBody(), true);
} else if (insert. getItemsList() != null) {
// fixed github pull/295
ItemsList itemsList = insert. getItemsList();
if (itemsList instanceof MultiExpressionList) {
((MultiExpressionList) itemsList).getExprList().forEach(el -> el.getExpressions().add(getTenantHandler().getTenantId(false)));
} else {
((ExpressionList) insert. getItemsList()). getExpressions(). add(getTenantHandler(). getTenantId(false));
}
} else {
throw ExceptionUtils.mpe("Failed to process multiple-table update, please exclude the tableName or statementId");
}
}
/**
* Rewrite processPlainSelect method
* Process the subquery splicing tenant id in the select part
* @param plainSelect
*/
@Override
protected void process PlainSelect(PlainSelect plainSelect) {
// SELECT to nested query in FROM
List<SelectItem> selectItemList = plainSelect. getSelectItems();
for (SelectItem selectItem : selectItemList) {
if (selectItem instanceof SelectExpressionItem) {
SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
if (!(selectExpressionItem. getExpression() instanceof Column)) {
// handle column select nested part
operateExpression(selectExpressionItem. getExpression());
}
}
}
processPlainSelect(plainSelect, false);
}
/**
* Handle column select nested part
* @param expression
*/
private void operateExpression(Expression expression){
//For example: select (slect a from b where b.id=table.bid)a,table.* from table
if (expression instanceof SubSelect){
SubSelect subSelect = (SubSelect) expression;
PlainSelect plainSelect = (PlainSelect) subSelect. getSelectBody();
processSelectBody(plainSelect);
} else if (expression instance of Parenthesis){
Parenthesis parenthesis= (Parenthesis) expression;
operateExpression(parentthesis. getExpression());
}else if (expression instanceof CaseExpression) {
//handle case when
CaseExpression caseExpression= (CaseExpression) expression;
caseExpression. getWhenClauses();
List<WhenClause> whenClauses = caseExpression. getWhenClauses();
for (Expression e : whenClauses) {
if (e instanceof WhenClause){
WhenClause whenClause= (WhenClause) e;
operateExpression(whenClause. getThenExpression());
}
}
}else if (expression instanceof Function){
//handle the IFNULL problem
Function function= (Function) expression;
if (IF_NULL. equals(function. getName())){
ExpressionList expressionList = function. getParameters();
List<Expression> ifExpression=expressionList. getExpressions();
for (Expression e:ifExpression){
operateExpression(e);
}
}
}
}
/**
* Determine whether there is a tenant id column field
* @param insert
* @return If it already exists, the bypass does not execute
*/
private boolean isExistTenantColumn(Insert insert) {
List<Column> columns = insert. getColumns();
if(CollectionUtils.isEmpty(columns)){
return false;
}
String tenantIdColumn = getTenantHandler().getTenantIdColumn();
return columns.stream().map(Column::getColumnName).anyMatch(tenantId -> tenantId.equals(tenantIdColumn));
}
}
Alright, enough of the nonsense, let’s get to the point, hahahahaha
1. Analyze the problem
After upgrading the Mybatis-plus (3.4.2) version, the connection query (Join) table with the tenant ID field will report Column ‘tenant_id’ in where clause is ambiguous, this exception is obvious, the tenant ID field I don’t know which table field it is, like this:
SELECT
A.*
FROM
A
LEFT JOIN B ON B.a_id = A.id
AND tenant_id = 1
WHERE
rp_rent_in_info.company_name LIKE "%"
AND tenant_id = 1
The solution is very simple. Here you need to add the table name after the LEFT and WHERE conditions. Something like this:
SELECT
A.*
FROM
A
LEFT JOIN B ON B.a_id = A.id
AND B. tenant_id = 1
WHERE
rp_rent_in_info.company_name LIKE "%"
AND A. tenant_id = 1
However, it stands to reason that Mybatis-plus cannot handle such a simple problem, so I read the introduction of the multi-tenant plug-in on the official website of Mybatis-plus. Sure enough, the official website has given a clear explanation
At the same time, looking at the source code of Mybatis-plus can understand more clearly why you need to add an alias to the table. Here is how to paste the source code: ?
protected Column getAliasColumn(Table table) {
StringBuilder column = new StringBuilder();
if (table. getAlias() != null) {
column.append(table.getAlias().getName()).append(".");
}
?
column.append(this.tenantLineHandler.getTenantIdColumn());
return new Column(column. toString());
}
The second method means to obtain the alias field column. If the table has an alias, then the spliced SQL is alias.Tenant ID. If there is no alias, it is just the tenant ID, which is the same as mentioned above. SQL dime the same.
Ask a question:
Why is there no problem with version 3.2.0?
Let’s look at the source code of version 3.2.0 and find that mybatis-plus has already handled it for us. If there is no alias, the table name will be used for splicing.
protected Column getAliasColumn(Table table) {
StringBuilder column = new StringBuilder();
if (null == table. getAlias()) {
column.append(table.getName());
} else {
column.append(table.getAlias().getName());
}
?
column.append(".");
column.append(this.tenantHandler.getTenantIdColumn());
return new Column(column. toString());
}
Why is the higher version not processed?
It stands to reason that most of the implementations that do not affect the overall implementation, the higher version will be compatible with the lower version, why is it not processed here?
We also checked the source code of version 3.4.2 and found that this class has been deprecated
The official website has clearly stated that this class is no longer used and replaced by the TenantLineInnerInterceptor class. However, we also looked at the source code of version 3.4.2 before. The getAliasColumn under the TenantLineInnerInterceptor class still does not deal with the absence of aliases. I don’t know if Mybatis-plus is intentional for us to standardize the code. Hahahahahaha, the dog’s head saves my life!
Well, now that we know the reason for the error, there is a solution.
2. Solve the problem
We know that it is caused by non-standard SQL writing, so the solution is to add aliases to all SQL tables! However, since there are too many business tables, it is too much work to identify them one by one. Can we think of a simple and general method?
This is to mention the problem that mybatis-plus’s built-in function will only splice left, from and where to add tenant id to the table or subquery behind where we said above. Before we will query the method So, can we assign an alias to a table that does not have an alias before splicing the tenant ID in the query method? Just do it, we first find the place where the select nested subquery was processed before, that is, the processPlainSelect method, and add the modification of the table alias:
/**
* Rewrite processPlainSelect method
* Process the subquery splicing tenant id in the select part
*
* @param plainSelect
*/
@Override
protected void process PlainSelect(PlainSelect plainSelect) {
/ / Handle the alias problem of from table
FromItem fromItem = plainSelect. getFromItem();
if (fromItem instanceof Table) {
Table fromTable = (Table) fromItem;
if (null == fromTable. getAlias()) {
Alias alias = new Alias(fromTable.getName());
// fromTable.setAlias(alias);
plainSelect.getFromItem().setAlias(alias);
}
}
/ / Handle the alias problem of the left and right association tables
List<Join> selectJoins = plainSelect. getJoins();
if (CollectionUtils. isNotEmpty(selectJoins)) {
for (Join selectJoin : selectJoins) {
FromItem rightItem = selectJoin. getRightItem();
Table fromTable = (Table) rightItem;
if (null == fromTable. getAlias()) {
Alias alias = new Alias(fromTable.getName());
// fromTable.setAlias(alias);
rightItem. setAlias(alias);
}
}
}
// SELECT to nested query in FROM
List<SelectItem> selectItemList = plainSelect. getSelectItems();
for (SelectItem selectItem : selectItemList) {
if (selectItem instanceof SelectExpressionItem) {
SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
if (!(selectExpressionItem. getExpression() instanceof Column)) {
// handle column select nested part
operateExpression(selectExpressionItem. getExpression());
}
}
}
processPlainSelect(plainSelect, false);
}
So far, the problem is solved.
If you have other questions, welcome to discuss!