Skip to content

Commit 856259c

Browse files
malwaregarrymalwaregarry
committed
[Postgres] Use common CERT oracle
1 parent 62a22d7 commit 856259c

3 files changed

Lines changed: 167 additions & 275 deletions

File tree

‎src/sqlancer/postgres/PostgresOracleFactory.java‎

Lines changed: 28 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,16 +3,18 @@
33
import java.sql.SQLException;
44
import java.util.ArrayList;
55
import java.util.List;
6+
import java.util.Optional;
67

78
import sqlancer.OracleFactory;
9+
import sqlancer.common.oracle.CERTOracle;
810
import sqlancer.common.oracle.CompositeTestOracle;
911
import sqlancer.common.oracle.NoRECOracle;
1012
import sqlancer.common.oracle.TLPWhereOracle;
1113
import sqlancer.common.oracle.TestOracle;
1214
import sqlancer.common.query.ExpectedErrors;
15+
import sqlancer.common.query.SQLancerResultSet;
1316
import sqlancer.postgres.gen.PostgresCommon;
1417
import sqlancer.postgres.gen.PostgresExpressionGenerator;
15-
import sqlancer.postgres.oracle.PostgresCERTOracle;
1618
import sqlancer.postgres.oracle.PostgresFuzzer;
1719
import sqlancer.postgres.oracle.PostgresPivotedQuerySynthesisOracle;
1820
import sqlancer.postgres.oracle.tlp.PostgresTLPAggregateOracle;
@@ -72,7 +74,31 @@ public TestOracle<PostgresGlobalState> create(PostgresGlobalState globalState) t
7274
CERT {
7375
@Override
7476
public TestOracle<PostgresGlobalState> create(PostgresGlobalState globalState) throws SQLException {
75-
return new PostgresCERTOracle(globalState);
77+
PostgresExpressionGenerator gen = new PostgresExpressionGenerator(globalState);
78+
ExpectedErrors errors = ExpectedErrors.newErrors().with(PostgresCommon.getCommonExpressionErrors())
79+
.withRegex(PostgresCommon.getCommonExpressionRegexErrors())
80+
.with(PostgresCommon.getCommonFetchErrors()).with(PostgresCommon.getCommonInsertUpdateErrors())
81+
.with(PostgresCommon.getGroupingErrors()).with(PostgresCommon.getCommonInsertUpdateErrors())
82+
.with(PostgresCommon.getCommonRangeExpressionErrors()).build();
83+
CERTOracle.CheckedFunction<SQLancerResultSet, Optional<Long>> rowCountParser = (rs) -> {
84+
String content = rs.getString(1).trim();
85+
if (content.contains("rows=")) {
86+
try {
87+
int ind = content.indexOf("rows=");
88+
long number = Long.parseLong(content.substring(ind + 5).split(" ")[0]);
89+
return Optional.of(number);
90+
} catch (Exception e) {
91+
}
92+
}
93+
return Optional.empty();
94+
};
95+
CERTOracle.CheckedFunction<SQLancerResultSet, Optional<String>> queryPlanParser = (rs) -> {
96+
String content = rs.getString(1).trim();
97+
String[] planPart = content.split("-> ");
98+
String plan = planPart[planPart.length - 1];
99+
return Optional.of(plan.split(" ")[0].trim());
100+
};
101+
return new CERTOracle<>(globalState, gen, errors, rowCountParser, queryPlanParser);
76102
}
77103

78104
@Override

‎src/sqlancer/postgres/gen/PostgresExpressionGenerator.java‎

Lines changed: 139 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,15 +5,18 @@
55
import java.util.Collections;
66
import java.util.List;
77
import java.util.Map;
8+
import java.util.function.Function;
89
import java.util.stream.Collectors;
910
import java.util.stream.Stream;
1011

1112
import sqlancer.IgnoreMeException;
1213
import sqlancer.Randomly;
14+
import sqlancer.common.gen.CERTGenerator;
1315
import sqlancer.common.gen.ExpressionGenerator;
1416
import sqlancer.common.gen.NoRECGenerator;
1517
import sqlancer.common.gen.TLPWhereGenerator;
1618
import sqlancer.common.schema.AbstractTables;
19+
import sqlancer.postgres.PostgresBugs;
1720
import sqlancer.postgres.PostgresCompoundDataType;
1821
import sqlancer.postgres.PostgresGlobalState;
1922
import sqlancer.postgres.PostgresProvider;
@@ -63,10 +66,12 @@
6366
import sqlancer.postgres.ast.PostgresSelect.PostgresSubquery;
6467
import sqlancer.postgres.ast.PostgresSelect.SelectType;
6568
import sqlancer.postgres.ast.PostgresSimilarTo;
69+
import sqlancer.postgres.ast.PostgresTableReference;
6670

6771
public class PostgresExpressionGenerator implements ExpressionGenerator<PostgresExpression>,
6872
NoRECGenerator<PostgresSelect, PostgresJoin, PostgresExpression, PostgresTable, PostgresColumn>,
69-
TLPWhereGenerator<PostgresSelect, PostgresJoin, PostgresExpression, PostgresTable, PostgresColumn> {
73+
TLPWhereGenerator<PostgresSelect, PostgresJoin, PostgresExpression, PostgresTable, PostgresColumn>,
74+
CERTGenerator<PostgresSelect, PostgresJoin, PostgresExpression, PostgresTable, PostgresColumn> {
7075

7176
private final int maxDepth;
7277

@@ -739,4 +744,137 @@ public String generateUnoptimizedQueryString(PostgresSelect select, PostgresExpr
739744

740745
return "SELECT SUM(count) FROM (" + select.asString() + ") as res";
741746
}
747+
748+
@Override
749+
public String generateExplainQuery(PostgresSelect select) {
750+
return "EXPLAIN " + select.asString();
751+
}
752+
753+
@Override
754+
public boolean mutate(PostgresSelect select) {
755+
List<Function<PostgresSelect, Boolean>> mutators = new ArrayList<>();
756+
757+
mutators.add(this::mutateJoin);
758+
mutators.add(this::mutateWhere);
759+
mutators.add(this::mutateGroupBy);
760+
mutators.add(this::mutateHaving);
761+
if (!PostgresBugs.bug18643) {
762+
mutators.add(this::mutateAnd);
763+
mutators.add(this::mutateOr);
764+
}
765+
// mutators.add(this::mutateLimit);
766+
mutators.add(this::mutateDistinct);
767+
768+
return Randomly.fromList(mutators).apply(select);
769+
}
770+
771+
boolean mutateJoin(PostgresSelect select) {
772+
if (select.getJoinList().isEmpty()) {
773+
return false;
774+
}
775+
PostgresJoin join = (PostgresJoin) Randomly.fromList(select.getJoinList());
776+
777+
// Exclude CROSS for on condition
778+
if (join.getType() == PostgresJoinType.CROSS) {
779+
List<PostgresColumn> columns = new ArrayList<>();
780+
columns.addAll(((PostgresTableReference) join.getLeftTable()).getTable().getColumns());
781+
columns.addAll(((PostgresTableReference) join.getRightTable()).getTable().getColumns());
782+
PostgresExpressionGenerator joinGen2 = new PostgresExpressionGenerator(globalState).setColumns(columns);
783+
join.setOnClause(joinGen2.generateExpression(0, PostgresDataType.BOOLEAN));
784+
}
785+
786+
PostgresJoinType newJoinType = PostgresJoinType.INNER;
787+
if (join.getType() == PostgresJoinType.LEFT || join.getType() == PostgresJoinType.RIGHT) {
788+
newJoinType = PostgresJoinType.getRandomExcept(PostgresJoinType.LEFT, PostgresJoinType.RIGHT);
789+
} else {
790+
newJoinType = PostgresJoinType.getRandomExcept(join.getType());
791+
}
792+
boolean increase = join.getType().ordinal() < newJoinType.ordinal();
793+
join.setType(newJoinType);
794+
if (newJoinType == PostgresJoinType.CROSS) {
795+
join.setOnClause(null);
796+
}
797+
return increase;
798+
}
799+
800+
boolean mutateDistinct(PostgresSelect select) {
801+
PostgresSelect.SelectType selectType = select.getSelectOption();
802+
if (selectType != PostgresSelect.SelectType.ALL) {
803+
select.setSelectType(PostgresSelect.SelectType.ALL);
804+
return true;
805+
} else {
806+
select.setSelectType(PostgresSelect.SelectType.DISTINCT);
807+
return false;
808+
}
809+
}
810+
811+
boolean mutateWhere(PostgresSelect select) {
812+
boolean increase = select.getWhereClause() != null;
813+
if (increase) {
814+
select.setWhereClause(null);
815+
} else {
816+
select.setWhereClause(generateExpression(0, PostgresDataType.BOOLEAN));
817+
}
818+
return increase;
819+
}
820+
821+
boolean mutateGroupBy(PostgresSelect select) {
822+
boolean increase = select.getGroupByExpressions().size() > 0;
823+
if (increase) {
824+
select.clearGroupByExpressions();
825+
} else {
826+
select.setGroupByExpressions(select.getFetchColumns());
827+
}
828+
return increase;
829+
}
830+
831+
boolean mutateHaving(PostgresSelect select) {
832+
if (select.getGroupByExpressions().size() == 0) {
833+
select.setGroupByExpressions(select.getFetchColumns());
834+
select.setHavingClause(generateExpression(0, PostgresDataType.BOOLEAN));
835+
return false;
836+
} else {
837+
if (select.getHavingClause() == null) {
838+
select.setHavingClause(generateExpression(0, PostgresDataType.BOOLEAN));
839+
return false;
840+
} else {
841+
select.setHavingClause(null);
842+
return true;
843+
}
844+
}
845+
}
846+
847+
boolean mutateAnd(PostgresSelect select) {
848+
if (select.getWhereClause() == null) {
849+
select.setWhereClause(generateExpression(0, PostgresDataType.BOOLEAN));
850+
} else {
851+
PostgresExpression newWhere = new PostgresBinaryLogicalOperation(select.getWhereClause(),
852+
generateExpression(0, PostgresDataType.BOOLEAN), BinaryLogicalOperator.AND);
853+
select.setWhereClause(newWhere);
854+
}
855+
return false;
856+
}
857+
858+
boolean mutateOr(PostgresSelect select) {
859+
if (select.getWhereClause() == null) {
860+
select.setWhereClause(generateExpression(0, PostgresDataType.BOOLEAN));
861+
return false;
862+
} else {
863+
PostgresExpression newWhere = new PostgresBinaryLogicalOperation(select.getWhereClause(),
864+
generateExpression(0, PostgresDataType.BOOLEAN), BinaryLogicalOperator.OR);
865+
select.setWhereClause(newWhere);
866+
return true;
867+
}
868+
}
869+
870+
boolean mutateLimit(PostgresSelect select) {
871+
boolean increase = select.getLimitClause() != null;
872+
if (increase) {
873+
select.setLimitClause(null);
874+
} else {
875+
Randomly r = new Randomly();
876+
select.setLimitClause(PostgresConstant.createIntConstant((int) Math.abs(r.getInteger())));
877+
}
878+
return increase;
879+
}
742880
}

0 commit comments

Comments
 (0)