SONAR-13341 fix SSF-110

4 files changed, 235 insertions, 0 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java
index 490e434c402..040d8d14711 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java
@@ -780,6 +780,8 @@ public class DbVersion84 implements DbVersion {
       .add(3803, "Add 'need_issue_sync' column to 'project_branches' table", AddProjectBranchesNeedIssueSync.class)
       .add(3804, "Populate 'need_issue_sync' of 'project_branches'", PopulateProjectBranchesNeedIssueSync.class)
       .add(3805, "Make 'need_issue_sync' of 'project_branches' not null", MakeProjectBranchesNeedIssueSyncNonNull.class)
+
+      .add(3806, "Drop local webhooks", DropLocalWebhooks.class)
     ;
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooks.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooks.java
new file mode 100644
index 00000000000..6f6f1bc5cd8
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooks.java
@@ -0,0 +1,72 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program 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.
+ *
+ * This program 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.server.platform.db.migration.version.v84;
+
+import java.net.InetAddress;
+import java.net.MalformedURLException;
+import java.net.URL;
+import java.net.UnknownHostException;
+import java.sql.SQLException;
+import org.sonar.api.utils.log.Logger;
+import org.sonar.api.utils.log.Loggers;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
+import org.sonar.server.platform.db.migration.step.MassUpdate;
+
+public class DropLocalWebhooks extends DataChange {
+  private static final Logger LOG = Loggers.get(DropLocalWebhooks.class);
+
+  public DropLocalWebhooks(Database db) {
+    super(db);
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select w.uuid, w.name, w.url, w.project_uuid, p.name from webhooks w left join projects p on p.uuid = w.project_uuid");
+    massUpdate.update("delete from webhooks where uuid = ?");
+    massUpdate.execute((row, update) -> {
+      try {
+        String webhookName = row.getString(2);
+        String webhookUrl = row.getString(3);
+        URL url = new URL(webhookUrl);
+        InetAddress address = InetAddress.getByName(url.getHost());
+        if (address.isLoopbackAddress() || address.isAnyLocalAddress()) {
+          boolean projectLevel = row.getString(4) != null;
+          if (projectLevel) {
+            String projectName = row.getString(5);
+            LOG.warn("Webhook '{}' for project '{}' has been removed because it used an invalid, unsafe URL. Please recreate " +
+              "this webhook with a valid URL or ask a project administrator to do it if it is still needed.", webhookName, projectName);
+          } else {
+            LOG.warn("Global webhook '{}' has been removed because it used an invalid, unsafe URL. Please recreate this webhook with a valid URL" +
+              " if it is still needed.", webhookName);
+          }
+
+          update.setString(1, row.getString(1));
+          return true;
+        }
+      } catch (MalformedURLException | UnknownHostException e) {
+        return false;
+      }
+
+      return false;
+    });
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooksTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooksTest.java
new file mode 100644
index 00000000000..930518f22f3
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooksTest.java
@@ -0,0 +1,132 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2020 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program 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.
+ *
+ * This program 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.server.platform.db.migration.version.v84;
+
+import java.sql.SQLException;
+import java.util.List;
+import javax.annotation.Nullable;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.api.utils.System2;
+import org.sonar.api.utils.log.LogTester;
+import org.sonar.api.utils.log.LoggerLevel;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.step.DataChange;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class DropLocalWebhooksTest {
+
+  @Rule
+  public LogTester logTester = new LogTester();
+
+  private static final String TABLE_NAME = "webhooks";
+
+  @Rule
+  public CoreDbTester dbTester = CoreDbTester.createForSchema(DropLocalWebhooksTest.class, "schema.sql");
+
+  private final DataChange underTest = new DropLocalWebhooks(dbTester.database());
+
+  @Test
+  public void execute() throws SQLException {
+    prepareWebhooks();
+
+    underTest.execute();
+
+    verifyMigrationResult();
+  }
+
+  @Test
+  public void migrationIsReEntrant() throws SQLException {
+    prepareWebhooks();
+
+    underTest.execute();
+    underTest.execute();
+
+    verifyMigrationResult();
+  }
+
+  @Test
+  public void migrationIsSuccessfulWhenNoWebhooksDeleted() throws SQLException {
+    insertProject("p1", "pn1");
+    insertWebhook("uuid-1", "https://10.15.15.15:5555/some_webhook", "p1");
+    insertWebhook("uuid-5", "https://some.valid.address.com/random_webhook", null);
+
+    underTest.execute();
+
+    assertThat(dbTester.countRowsOfTable(TABLE_NAME)).isEqualTo(2);
+    assertThat(logTester.logs(LoggerLevel.WARN)).isEmpty();
+  }
+
+  @Test
+  public void migrationIsSuccessfulWhenNoWebhooksInDb() throws SQLException {
+    insertProject("p1", "pn1");
+
+    underTest.execute();
+
+    assertThat(dbTester.countRowsOfTable(TABLE_NAME)).isZero();
+    assertThat(logTester.logs(LoggerLevel.WARN)).isEmpty();
+  }
+
+  private void prepareWebhooks() {
+    insertProject("p1", "pn1");
+    insertProject("p2", "pn2");
+    insertWebhook("uuid-1", "https://10.15.15.15:5555/some_webhook", "p1");
+    insertWebhook("uuid-2", "https://0.0.0.0/some_webhook", "p1");
+    insertWebhook("uuid-3", "https://172.16.16.16:6666/some_webhook", "p2");
+    insertWebhook("uuid-4", "https://127.0.0.1/some_webhook", "p2");
+    insertWebhook("uuid-5", "https://some.valid.address.com/random_webhook", null);
+    insertWebhook("uuid-6", "https://248.235.76.254:7777/some_webhook", null);
+    insertWebhook("uuid-7", "https://localhost/some_webhook", null);
+  }
+
+  private void verifyMigrationResult() {
+    assertThat(dbTester.countRowsOfTable(TABLE_NAME)).isEqualTo(4);
+    assertThat(dbTester.select("select uuid from " + TABLE_NAME).stream().map(columns -> columns.get("UUID")))
+      .containsOnly("uuid-1", "uuid-3", "uuid-5", "uuid-6");
+
+    List<String> logs = logTester.logs(LoggerLevel.WARN);
+    assertThat(logs).hasSize(3);
+    assertThat(logs).containsExactlyInAnyOrder(
+      "Global webhook 'webhook-uuid-7' has been removed because it used an invalid, unsafe URL. Please recreate this webhook with a valid URL if it is still needed.",
+      "Webhook 'webhook-uuid-4' for project 'pn2' has been removed because it used an invalid, unsafe URL. Please recreate this webhook with a valid URL or ask a project administrator to do it if it is still needed.",
+      "Webhook 'webhook-uuid-2' for project 'pn1' has been removed because it used an invalid, unsafe URL. Please recreate this webhook with a valid URL or ask a project administrator to do it if it is still needed.");
+  }
+
+  private void insertProject(String uuid, String name) {
+    dbTester.executeInsert("PROJECTS",
+      "NAME", name,
+      "ORGANIZATION_UUID", "default",
+      "KEE", uuid + "-key",
+      "UUID", uuid,
+      "PRIVATE", Boolean.toString(false),
+      "QUALIFIER", "TRK",
+      "UPDATED_AT", System2.INSTANCE.now());
+  }
+
+  private void insertWebhook(String uuid, String url, @Nullable String projectUuid) {
+    dbTester.executeInsert(TABLE_NAME,
+      "UUID", uuid,
+      "NAME", "webhook-" + uuid,
+      "PROJECT_UUID", projectUuid,
+      "URL", url,
+      "CREATED_AT", System2.INSTANCE.now());
+  }
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooksTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooksTest/schema.sql
new file mode 100644
index 00000000000..de11e9e16ed
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/DropLocalWebhooksTest/schema.sql
@@ -0,0 +1,29 @@
+CREATE TABLE "PROJECTS"(
+    "UUID" VARCHAR(40) NOT NULL,
+    "KEE" VARCHAR(400) NOT NULL,
+    "QUALIFIER" VARCHAR(10) NOT NULL,
+    "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+    "NAME" VARCHAR(2000),
+    "DESCRIPTION" VARCHAR(2000),
+    "PRIVATE" BOOLEAN NOT NULL,
+    "TAGS" VARCHAR(500),
+    "CREATED_AT" BIGINT,
+    "UPDATED_AT" BIGINT NOT NULL
+);
+ALTER TABLE "PROJECTS" ADD CONSTRAINT "PK_NEW_PROJECTS" PRIMARY KEY("UUID");
+CREATE UNIQUE INDEX "UNIQ_PROJECTS_KEE" ON "PROJECTS"("KEE");
+CREATE INDEX "IDX_QUALIFIER" ON "PROJECTS"("QUALIFIER");
+
+CREATE TABLE "WEBHOOKS"(
+    "UUID" VARCHAR(40) NOT NULL,
+    "ORGANIZATION_UUID" VARCHAR(40),
+    "PROJECT_UUID" VARCHAR(40),
+    "NAME" VARCHAR(100) NOT NULL,
+    "URL" VARCHAR(2000) NOT NULL,
+    "SECRET" VARCHAR(200),
+    "CREATED_AT" BIGINT NOT NULL,
+    "UPDATED_AT" BIGINT
+);
+ALTER TABLE "WEBHOOKS" ADD CONSTRAINT "PK_WEBHOOKS" PRIMARY KEY("UUID");
+CREATE INDEX "ORGANIZATION_WEBHOOK" ON "WEBHOOKS"("ORGANIZATION_UUID");
+CREATE INDEX "PROJECT_WEBHOOK" ON "WEBHOOKS"("PROJECT_UUID");