CVE-2022-4963: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability was found in Folio Spring Module Core up to 1.1.5. It has been rated as critical. Affected by this issue is the function dropSchema of the file tenant/src/main/java/org/folio/spring/tenant/hibernate/HibernateSchemaService.java of the component Schema Name Handler. The manipulation leads to sql injection. Upgrading to version 2.0.0 is able to address this issue. The name of the patch is d374a5f77e6b58e36f0e0e4419be18b95edcd7ff. It is recommended to upgrade the affected component. The identifier of this vulnerability is VDB-257516.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
Published: 2024-03-21T02:44Z
Repository: https://github.com/folio-org/spring-module-core
Language: Java
Files changed: tenant/src/main/java/org/folio/spring/tenant/hibernate/HibernateSchemaService.java, tenant/src/main/java/org/folio/spring/tenant/service/SchemaService.java
Commit date: 2022-11-21 18:46:32 +0100
Commit message: FOLIO-3645: schema name SQL injection

SELECT EXISTS now uses a prepared statement.

PostgreSQL doesn't support a prepared statement for CREATE SCHEMA and DROP SCHEMA.
Instead we validate the schema name before use to mitigate any SQL injection attack.

Diff: diff --git a/tenant/src/main/java/org/folio/spring/tenant/hibernate/HibernateSchemaService.java b/tenant/src/main/java/org/folio/spring/tenant/hibernate/HibernateSchemaService.java
index dbf4af3..ad52309 100644
--- a/tenant/src/main/java/org/folio/spring/tenant/hibernate/HibernateSchemaService.java
+++ b/tenant/src/main/java/org/folio/spring/tenant/hibernate/HibernateSchemaService.java
@@ -4,10 +4,11 @@ import java.io.File;
 import java.io.FileOutputStream;
 import java.io.IOException;
 import java.nio.charset.StandardCharsets;
 import java.sql.Connection;
 import java.sql.DriverManager;
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.EnumSet;
@@ -39,12 +40,10 @@ import org.springframework.context.annotation.ClassPathScanningCandidateComponen
 import org.springframework.core.io.Resource;
 import org.springframework.core.io.ResourceLoader;
 import org.springframework.core.type.filter.AnnotationTypeFilter;
 import org.springframework.stereotype.Service;
 
-import static org.hibernate.cfg.AvailableSettings.*;
-
 @Service("hibernateSchemaService")
 public class HibernateSchemaService implements InitializingBean {
 
   private final static String CONNECTION_DRIVER_CLASS = "connection.driver_class";
   private final static String DIALECT = "dialect";
@@ -175,13 +174,14 @@ public class HibernateSchemaService implements InitializingBean {
       statement.executeUpdate(String.format("DROP SCHEMA IF EXISTS %s CASCADE;", schema));
     }
   }
 
   private boolean schemaExists(Connection connection, String schema) throws SQLException {
-    try (Statement statement = connection.createStatement()) {
-      String queryTemplate = "SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name = '%s');";
-      ResultSet resultSet = statement.executeQuery(String.format(queryTemplate, schema));
+    String sql = "SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name = ?)";
+    try (PreparedStatement statement = connection.prepareStatement(sql)) {
+      statement.setString(1, schema);
+      ResultSet resultSet = statement.executeQuery();
       if (resultSet.next()) {
         return resultSet.getBoolean(1);
       }
       return false;
     }
diff --git a/tenant/src/main/java/org/folio/spring/tenant/service/SchemaService.java b/tenant/src/main/java/org/folio/spring/tenant/service/SchemaService.java
index 32eee78..682672f 100644
--- a/tenant/src/main/java/org/folio/spring/tenant/service/SchemaService.java
+++ b/tenant/src/main/java/org/folio/spring/tenant/service/SchemaService.java
@@ -1,17 +1,24 @@
 package org.folio.spring.tenant.service;
 
+import java.util.regex.Pattern;
 import org.folio.spring.tenant.properties.BuildInfoProperties;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Service;
 
 @Service
 public class SchemaService {
 
+  private static final Pattern SCHEMA_REGEXP = Pattern.compile("[a-zA-Z0-9_]+");
+
   @Autowired
   private BuildInfoProperties buildInfoProperties;
 
   public String getSchema(String tenant) {
-    return String.format("%s_%s", tenant, buildInfoProperties.getArtifact()).replace("-", "_");
+    var schema = String.format("%s_%s", tenant, buildInfoProperties.getArtifact()).replace("-", "_");
+    if (! SCHEMA_REGEXP.matcher(schema).matches()) {
+      throw new IllegalArgumentException("Illegal character in schema name: " + schema);
+    }
+    return schema;
   }
 
 }

Vulnerable code: import static org.hibernate.cfg.AvailableSettings.*;

    try (Statement statement = connection.createStatement()) {
      String queryTemplate = "SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name = '%s');";
      ResultSet resultSet = statement.executeQuery(String.format(queryTemplate, schema));
    return String.format("%s_%s", tenant, buildInfoProperties.getArtifact()).replace("-", "_");
Fixed code: import java.sql.PreparedStatement;
    String sql = "SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name = ?)";
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setString(1, schema);
      ResultSet resultSet = statement.executeQuery();
import java.util.regex.Pattern;
  private static final Pattern SCHEMA_REGEXP = Pattern.compile("[a-zA-Z0-9_]+");

    var schema = String.format("%s_%s", tenant, buildInfoProperties.getArtifact()).replace("-", "_");
    if (! SCHEMA_REGEXP.matcher(schema).matches()) {
      throw new IllegalArgumentException("Illegal character in schema name: " + schema);
    }
    return schema;