From 2b702eaff6c12860cbfe38699affb206ae0c03a2 Mon Sep 17 00:00:00 2001 From: Teryk Bellahsene Date: Fri, 2 Oct 2015 14:56:02 +0200 Subject: [PATCH] SONAR-6857 Fix escaping in a like query --- .../server/component/ws/SearchAction.java | 1 - .../computation/ws/ActivityWsAction.java | 2 +- .../main/java/org/sonar/db/DatabaseUtils.java | 39 ++++++++++++++ .../db/{dialect => }/WildcardPosition.java | 2 +- .../sonar/db/component/ComponentQuery.java | 12 ++--- .../org/sonar/db/dialect/AbstractDialect.java | 40 -------------- .../java/org/sonar/db/dialect/Dialect.java | 6 --- .../sonar/db/component/ComponentMapper.xml | 4 +- .../java/org/sonar/db/DatabaseUtilsTest.java | 14 +++++ .../sonar/db/component/ComponentDaoTest.java | 10 ++-- .../org/sonar/db/dialect/DialectTest.java | 53 ------------------- 11 files changed, 67 insertions(+), 116 deletions(-) rename sonar-db/src/main/java/org/sonar/db/{dialect => }/WildcardPosition.java (96%) delete mode 100644 sonar-db/src/test/java/org/sonar/db/dialect/DialectTest.java diff --git a/server/sonar-server/src/main/java/org/sonar/server/component/ws/SearchAction.java b/server/sonar-server/src/main/java/org/sonar/server/component/ws/SearchAction.java index 5078bd381b6..503c99724cf 100644 --- a/server/sonar-server/src/main/java/org/sonar/server/component/ws/SearchAction.java +++ b/server/sonar-server/src/main/java/org/sonar/server/component/ws/SearchAction.java @@ -132,7 +132,6 @@ public class SearchAction implements ComponentsWsAction { private ComponentQuery buildQuery(Request wsRequest, List qualifiers) { return new ComponentQuery( - dbClient.getDatabase(), wsRequest.param(Param.TEXT_QUERY), qualifiers.toArray(new String[qualifiers.size()])); } diff --git a/server/sonar-server/src/main/java/org/sonar/server/computation/ws/ActivityWsAction.java b/server/sonar-server/src/main/java/org/sonar/server/computation/ws/ActivityWsAction.java index f34dfd063d3..d2122a37184 100644 --- a/server/sonar-server/src/main/java/org/sonar/server/computation/ws/ActivityWsAction.java +++ b/server/sonar-server/src/main/java/org/sonar/server/computation/ws/ActivityWsAction.java @@ -173,7 +173,7 @@ public class ActivityWsAction implements CeWsAction { query.setComponentUuid(componentUuid); } if (componentQuery != null) { - ComponentQuery componentDtoQuery = new ComponentQuery(dbClient.getDatabase(), componentQuery, Qualifiers.PROJECT, Qualifiers.VIEW); + ComponentQuery componentDtoQuery = new ComponentQuery(componentQuery, Qualifiers.PROJECT, Qualifiers.VIEW); List componentDtos = dbClient.componentDao().selectByQuery(dbSession, componentDtoQuery, 0, CeActivityQuery.MAX_COMPONENT_UUIDS); query.setComponentUuids(Lists.transform(componentDtos, ComponentDtoFunctions.toUuid())); } diff --git a/sonar-db/src/main/java/org/sonar/db/DatabaseUtils.java b/sonar-db/src/main/java/org/sonar/db/DatabaseUtils.java index 84360b3ff8d..13227b1781d 100644 --- a/sonar-db/src/main/java/org/sonar/db/DatabaseUtils.java +++ b/sonar-db/src/main/java/org/sonar/db/DatabaseUtils.java @@ -75,6 +75,45 @@ public class DatabaseUtils { } } + /** + * Returns an escaped value in parameter, with the desired wildcards. Suitable to be used in a like sql query
+ * Escapes the "/", "%" and "_" characters.
+ * + * You must add "ESCAPE '/'" after your like query. It defines '/' as the escape character. + */ + /** + * + */ + public static String buildLikeValue(String value, WildcardPosition wildcardPosition) { + String escapedValue = escapePercentAndUnderscore(value); + String wildcard = "%"; + switch (wildcardPosition) { + case BEFORE: + escapedValue = wildcard + escapedValue; + break; + case AFTER: + escapedValue += wildcard; + break; + case BEFORE_AND_AFTER: + escapedValue = wildcard + escapedValue + wildcard; + break; + default: + throw new UnsupportedOperationException("Unhandled WildcardPosition: " + wildcardPosition); + } + + return escapedValue; + } + + /** + * Replace escape percent and underscore by adding a slash just before + */ + private static String escapePercentAndUnderscore(String value) { + return value + .replaceAll("/", "//") + .replaceAll("%", "/%") + .replaceAll("_", "/_"); + } + /** * Partition by 1000 elements a list of input and execute a function on each part. * diff --git a/sonar-db/src/main/java/org/sonar/db/dialect/WildcardPosition.java b/sonar-db/src/main/java/org/sonar/db/WildcardPosition.java similarity index 96% rename from sonar-db/src/main/java/org/sonar/db/dialect/WildcardPosition.java rename to sonar-db/src/main/java/org/sonar/db/WildcardPosition.java index 0b8780b58e7..9c58d718490 100644 --- a/sonar-db/src/main/java/org/sonar/db/dialect/WildcardPosition.java +++ b/sonar-db/src/main/java/org/sonar/db/WildcardPosition.java @@ -18,7 +18,7 @@ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. */ -package org.sonar.db.dialect; +package org.sonar.db; public enum WildcardPosition { BEFORE, AFTER, BEFORE_AND_AFTER diff --git a/sonar-db/src/main/java/org/sonar/db/component/ComponentQuery.java b/sonar-db/src/main/java/org/sonar/db/component/ComponentQuery.java index 6c4e361ddfc..57d4536f968 100644 --- a/sonar-db/src/main/java/org/sonar/db/component/ComponentQuery.java +++ b/sonar-db/src/main/java/org/sonar/db/component/ComponentQuery.java @@ -22,20 +22,18 @@ package org.sonar.db.component; import javax.annotation.CheckForNull; import javax.annotation.Nullable; -import org.sonar.db.Database; import static com.google.common.base.Preconditions.checkArgument; -import static org.sonar.db.dialect.WildcardPosition.AFTER; +import static org.sonar.db.DatabaseUtils.buildLikeValue; +import static org.sonar.db.WildcardPosition.AFTER; public class ComponentQuery { - private final Database database; private final String nameOrKeyQuery; private final String[] qualifiers; - public ComponentQuery(Database database, @Nullable String nameOrKeyQuery, String... qualifiers) { + public ComponentQuery(@Nullable String nameOrKeyQuery, String... qualifiers) { checkArgument(qualifiers.length > 0, "At least one qualifier must be provided"); - this.database = database; this.nameOrKeyQuery = nameOrKeyQuery; this.qualifiers = qualifiers; } @@ -51,11 +49,11 @@ public class ComponentQuery { @CheckForNull public String getNameOrKeyQueryToSqlForResourceIndex() { - return database.getDialect().buildLikeValue(nameOrKeyQuery, AFTER).toLowerCase(); + return buildLikeValue(nameOrKeyQuery, AFTER).toLowerCase(); } @CheckForNull public String getNameOrKeyQueryToSqlForProjectKey() { - return database.getDialect().buildLikeValue(nameOrKeyQuery, AFTER); + return buildLikeValue(nameOrKeyQuery, AFTER); } } diff --git a/sonar-db/src/main/java/org/sonar/db/dialect/AbstractDialect.java b/sonar-db/src/main/java/org/sonar/db/dialect/AbstractDialect.java index 5aeb289f495..236c90acd1b 100644 --- a/sonar-db/src/main/java/org/sonar/db/dialect/AbstractDialect.java +++ b/sonar-db/src/main/java/org/sonar/db/dialect/AbstractDialect.java @@ -87,44 +87,4 @@ abstract class AbstractDialect implements Dialect { public int getScrollSingleRowFetchSize() { return 1; } - - @Override - public String buildLikeValue(String value, WildcardPosition wildcardPosition) { - String escapedValue = escapePercentAndUnderscore(value); - String wildcard = "%"; - switch (wildcardPosition) { - case BEFORE: - escapedValue = wildcard + escapedValue; - break; - case AFTER: - escapedValue += wildcard; - break; - case BEFORE_AND_AFTER: - escapedValue = wildcard + escapedValue + wildcard; - break; - default: - throw new UnsupportedOperationException("Unhandled WildcardPosition: " + wildcardPosition); - } - - return appendEscapeBackslachForSomeDb(escapedValue); - } - - /** - * Replace escape percent and underscore by adding a slash just before - */ - private static String escapePercentAndUnderscore(String value) { - return value - .replaceAll("%", "\\\\%") - .replaceAll("_", "\\\\_"); - } - - private String appendEscapeBackslachForSomeDb(String value) { - return isOracleOrMsSql() - ? (value + " ESCAPE '\\'") - : value; - } - - private boolean isOracleOrMsSql() { - return getId().equals(Oracle.ID) || getId().equals(MsSql.ID); - } } diff --git a/sonar-db/src/main/java/org/sonar/db/dialect/Dialect.java b/sonar-db/src/main/java/org/sonar/db/dialect/Dialect.java index a6fba169d91..3d0a0f6e730 100644 --- a/sonar-db/src/main/java/org/sonar/db/dialect/Dialect.java +++ b/sonar-db/src/main/java/org/sonar/db/dialect/Dialect.java @@ -89,10 +89,4 @@ public interface Dialect { * @return a boolean */ boolean supportsMigration(); - - /** - * Returns an escaped value in parameter, with the desired wildcards. - * Suitable to be used in a like sql query - */ - String buildLikeValue(String value, WildcardPosition wildcardPosition); } diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml index 8d846a61e39..9d295b93383 100644 --- a/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml +++ b/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml @@ -281,8 +281,8 @@ #{qualifier} - AND ri.kee like #{nameOrKeyQueryToSqlForResourceIndex}) - OR p.kee like #{nameOrKeyQueryToSqlForProjectKey}) + AND ri.kee like #{nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/') + OR p.kee like #{nameOrKeyQueryToSqlForProjectKey} ESCAPE '/') diff --git a/sonar-db/src/test/java/org/sonar/db/DatabaseUtilsTest.java b/sonar-db/src/test/java/org/sonar/db/DatabaseUtilsTest.java index fc850643379..823f425a044 100644 --- a/sonar-db/src/test/java/org/sonar/db/DatabaseUtilsTest.java +++ b/sonar-db/src/test/java/org/sonar/db/DatabaseUtilsTest.java @@ -44,6 +44,10 @@ import static org.assertj.core.api.Assertions.fail; import static org.mockito.Mockito.doThrow; import static org.mockito.Mockito.mock; import static org.mockito.Mockito.verify; +import static org.sonar.db.DatabaseUtils.buildLikeValue; +import static org.sonar.db.WildcardPosition.AFTER; +import static org.sonar.db.WildcardPosition.BEFORE; +import static org.sonar.db.WildcardPosition.BEFORE_AND_AFTER; @Category(DbTests.class) public class DatabaseUtilsTest { @@ -218,4 +222,14 @@ public class DatabaseUtilsTest { assertThat(logTester.logs(LoggerLevel.ERROR)).contains("SQL error: 456. Message: this is next"); } + + @Test + public void buildLikeValue_with_special_characters() { + String escapedValue = "like-\\/_/%//-value"; + String wildcard = "%"; + + assertThat(buildLikeValue("like-\\_%/-value", BEFORE)).isEqualTo(wildcard + escapedValue); + assertThat(buildLikeValue("like-\\_%/-value", AFTER)).isEqualTo(escapedValue + wildcard); + assertThat(buildLikeValue("like-\\_%/-value", BEFORE_AND_AFTER)).isEqualTo(wildcard + escapedValue + wildcard); + } } diff --git a/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java b/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java index 0ca8782056f..a282e4e25da 100644 --- a/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java +++ b/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java @@ -649,7 +649,7 @@ public class ComponentDaoTest { db.commit(); componentDb.indexProjects(); - ComponentQuery query = new ComponentQuery(db.database(), "oJect", Qualifiers.PROJECT); + ComponentQuery query = new ComponentQuery("oJect", Qualifiers.PROJECT); List result = underTest.selectByQuery(dbSession, query, 1, 3); assertThat(result).hasSize(3); @@ -659,15 +659,15 @@ public class ComponentDaoTest { @Test public void select_by_query_name_with_special_characters() { - componentDb.insertProjectAndSnapshot(dbSession, newProjectDto().setName("project-_%-name")); + componentDb.insertProjectAndSnapshot(dbSession, newProjectDto().setName("project-\\_%/-name")); db.commit(); componentDb.indexProjects(); - ComponentQuery query = new ComponentQuery(db.database(), "-_%-", Qualifiers.PROJECT); + ComponentQuery query = new ComponentQuery("-\\_%/-", Qualifiers.PROJECT); List result = underTest.selectByQuery(dbSession, query, 0, 10); assertThat(result).hasSize(1); - assertThat(result.get(0).name()).isEqualTo("project-_%-name"); + assertThat(result.get(0).name()).isEqualTo("project-\\_%/-name"); } @Test @@ -677,7 +677,7 @@ public class ComponentDaoTest { db.commit(); componentDb.indexProjects(); - ComponentQuery query = new ComponentQuery(db.database(), "project-_%-", Qualifiers.PROJECT); + ComponentQuery query = new ComponentQuery("project-_%-", Qualifiers.PROJECT); List result = underTest.selectByQuery(dbSession, query, 0, 10); assertThat(result).hasSize(1); diff --git a/sonar-db/src/test/java/org/sonar/db/dialect/DialectTest.java b/sonar-db/src/test/java/org/sonar/db/dialect/DialectTest.java deleted file mode 100644 index c20b8ccd9f4..00000000000 --- a/sonar-db/src/test/java/org/sonar/db/dialect/DialectTest.java +++ /dev/null @@ -1,53 +0,0 @@ -/* - * SonarQube, open source software quality management tool. - * Copyright (C) 2008-2014 SonarSource - * mailto:contact AT sonarsource DOT com - * - * SonarQube is free software; you can redistribute it and/or - * modify it under the terms of the GNU Lesser General Public - * License as published by the Free Software Foundation; either - * version 3 of the License, or (at your option) any later version. - * - * SonarQube is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - * Lesser General Public License for more details. - * - * You should have received a copy of the GNU Lesser General Public License - * along with this program; if not, write to the Free Software Foundation, - * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. - */ - -package org.sonar.db.dialect; - -import org.junit.Test; - -import static org.assertj.core.api.Assertions.assertThat; -import static org.sonar.db.dialect.WildcardPosition.AFTER; -import static org.sonar.db.dialect.WildcardPosition.BEFORE; -import static org.sonar.db.dialect.WildcardPosition.BEFORE_AND_AFTER; - -public class DialectTest { - - Dialect underTest = new H2(); - - @Test - public void buildLikeValue_with_H2() { - String escapedValue = "like-\\_\\%-value"; - String wildcard = "%"; - - assertThat(underTest.buildLikeValue("like-_%-value", BEFORE)).isEqualTo(wildcard + escapedValue); - assertThat(underTest.buildLikeValue("like-_%-value", AFTER)).isEqualTo(escapedValue + wildcard); - assertThat(underTest.buildLikeValue("like-_%-value", BEFORE_AND_AFTER)).isEqualTo(wildcard + escapedValue + wildcard); - } - - @Test - public void buildLikeValue_with_Oracle() { - underTest = new Oracle(); - String escapedValue = "like-\\_\\%-value"; - String wildcard = "%"; - - assertThat(underTest.buildLikeValue("like-_%-value", BEFORE_AND_AFTER)).isEqualTo(wildcard + escapedValue + wildcard + " ESCAPE '\\'"); - - } -} -- 2.39.5