Repository: iluwatar/java-design-patterns
Issue: Security vulnerability in H2 database
Labels: epic: dependencies

The currently used H2 database version 1.4.190 has multiple security vulnerabilities. To mention a few:

1. - H2 Console before 2.1.210 allows remote attackers to execute arbitrary code via a jdbc:h2:mem JDBC URL containing the IGNORE_UNKNOWN_SETTINGS=TRUE;FORBID_CREATION=FALSE;INIT=RUNSCRIPT substring, a different vulnerability than https://github.com/advisories/GHSA-h376-j262-vhq6
2. - H2 Console in versions since 1.1.100 (2008-10-14) to 2.0.204 (2021-12-21) inclusive allows loading of custom classes from remote servers through JNDI.
3. - The package com.h2database:h2 from 0 and before 2.0.202 are vulnerable to XML External Entity (XXE) Injection via the org.h2.jdbc.JdbcSQLXML class object, when it receives parsed string data from org.h2.jdbc.JdbcResultSet.getSQLXML() method. If it executes the getSource() method when the parameter is DOMSource.class it will trigger the vulnerability.


Acceptance criteria

- H2 database has been upgraded to the latest version


Issue comments:
@iluwatar , is solution is to update the version of H2 database or something else .
Above issue is also mentioned here officially in H2 database repo [issue](https://github.com/h2database/h2database/issues/3195).
Yes, update the version (see the acceptance criteria). Would you like to try it @bhati00 ?
yes sure @iluwatar , i would like to try.
Sir , where i can find the details about this project , how is it developed and its architecture .
 Why we use the H2 database, is it only for the testing purpose . ?
Sir, i did updated the version of H2 database by changing the version of H2
in pom.xml . But it failed the test case.

On Mon, Oct 31, 2022, 23:57 Ilkka Seppälä ***@***.***> wrote:

> Yes, update the version (see the acceptance criteria). Would you like to
> try it @bhati00 <https://github.com/bhati00> ?
>
> —
> Reply to this email directly, view it on GitHub
> <https://github.com/iluwatar/java-design-patterns/issues/2297#issuecomment-1297497804>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AS5DWKN77YZSK3PD5GHLZ6LWGAFRNANCNFSM6AAAAAARSMVNKY>
> .
> You are receiving this because you were mentioned.Message ID:
> ***@***.***>
>

After changing the H2 version, you need to fix everything so that the CI build works again @bhati00 That's the difficulty in this task.
Hello @iluwatar  , can you please specify a bit that, what kind of changes should i made to in order to fix everything .I've read this article for  [migrate to hibernate 2.0](https://www.h2database.com/html/migration-to-v2.html#:~:text=your%20own%20risk.-,Upgrading,of%20this%20command%20for%20details.) , and one another thing , where we did specify the details about the h2 database , in rest api we use application.properties. 
Hmm I didn't get any errors after the upgrade. Could you just create the pull request with the updated dependency and we can check if it's just your workstation?
@iluwatar , i've made the [pull request ](https://github.com/iluwatar/java-design-patterns/pull/2298), it solves two issues , since previous one was'nt merged that's why i've commited to same branch. 
I'm not getting error in my workstation Sir, its circle.ci which shows the error , **maven build failed** .
I previous tried to upgrade the H2 database but failed with some strange error message that need non-trivial investigation.
@robertvolkmann , did you just simply added the dependency or also made some another changes for upgrading the database.
I simply pumped the version and it failed. Therefore I expect some additional changes.
wow , great @robertvolkmann , you've solved the issue .
i was seacrching in the project , where did the database created , @robertvolkmann  , why did we created it in domain-modal . And why did you  droped the table according to their dependency . 
@bhati00, the H2 database is automatically created if absent.
You have to drop the tables according to their dependency. In our case, we cannot drop the table CUSTOMERS before PURCHASES because the latter still has a reference to CUSTOMERS.

Pull request review comments:

Patch that resolved the issue:
diff --git a/domain-model/src/main/java/com/iluwatar/domainmodel/App.java b/domain-model/src/main/java/com/iluwatar/domainmodel/App.java
index 80058fb89a7a..681e7c4fc72a 100644
--- a/domain-model/src/main/java/com/iluwatar/domainmodel/App.java
+++ b/domain-model/src/main/java/com/iluwatar/domainmodel/App.java
@@ -60,8 +60,8 @@ public class App {
           + "customer_name varchar references CUSTOMERS(name));";
 
   public static final String DELETE_SCHEMA_SQL =
-      "DROP TABLE CUSTOMERS IF EXISTS;"
-          + "DROP TABLE PURCHASES IF EXISTS;"
+      "DROP TABLE PURCHASES IF EXISTS;"
+          + "DROP TABLE CUSTOMERS IF EXISTS;"
           + "DROP TABLE PRODUCTS IF EXISTS;";
 
   /**
diff --git a/metadata-mapping/README.md b/metadata-mapping/README.md
index 4cea6b10f84f..612188cbe267 100644
--- a/metadata-mapping/README.md
+++ b/metadata-mapping/README.md
@@ -26,14 +26,14 @@ Wikipedia says
 
 **Programmatic Example**
 
-We give an example about visiting the information of `USER` table in `h2` database. Firstly, we create `USER` table with `h2`:
+We give an example about visiting the information of `user_account` table in `h2` database. Firstly, we create `user_account` table with `h2`:
 
 ```java
 @Slf4j
 public class DatabaseUtil {
   private static final String DB_URL = "jdbc:h2:mem:metamapping";
-  private static final String CREATE_SCHEMA_SQL = "DROP TABLE IF EXISTS `user`;"
-      + "CREATE TABLE `user` (\n"
+  private static final String CREATE_SCHEMA_SQL = "DROP TABLE IF EXISTS `user_account`;"
+      + "CREATE TABLE `user_account` (\n"
       + "  `id` int(11) NOT NULL AUTO_INCREMENT,\n"
       + "  `username` varchar(255) NOT NULL,\n"
       + "  `password` varchar(255) NOT NULL,\n"
@@ -88,7 +88,7 @@ Then we write a `xml` file to show the mapping between the table and the object:
     "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
 
 <hibernate-mapping>
-  <class name="com.iluwatar.metamapping.model.User" table="user">
+  <class name="com.iluwatar.metamapping.model.User" table="user_account">
     <id name="id" type="java.lang.Integer" column="id">
       <generator class="native"/>
     </id>
diff --git a/metadata-mapping/src/main/java/com/iluwatar/metamapping/utils/DatabaseUtil.java b/metadata-mapping/src/main/java/com/iluwatar/metamapping/utils/DatabaseUtil.java
index 55f06ed9a794..048f470aebf4 100644
--- a/metadata-mapping/src/main/java/com/iluwatar/metamapping/utils/DatabaseUtil.java
+++ b/metadata-mapping/src/main/java/com/iluwatar/metamapping/utils/DatabaseUtil.java
@@ -34,8 +34,8 @@
 @Slf4j
 public class DatabaseUtil {
   private static final String DB_URL = "jdbc:h2:mem:metamapping";
-  private static final String CREATE_SCHEMA_SQL = "DROP TABLE IF EXISTS `user`;"
-      + "CREATE TABLE `user` (\n"
+  private static final String CREATE_SCHEMA_SQL = "DROP TABLE IF EXISTS `user_account`;"
+      + "CREATE TABLE `user_account` (\n"
       + "  `id` int(11) NOT NULL AUTO_INCREMENT,\n"
       + "  `username` varchar(255) NOT NULL,\n"
       + "  `password` varchar(255) NOT NULL,\n"
diff --git a/metadata-mapping/src/main/resources/com/iluwatar/metamapping/model/User.hbm.xml b/metadata-mapping/src/main/resources/com/iluwatar/metamapping/model/User.hbm.xml
index cd63c552d65d..38862c67a3f4 100644
--- a/metadata-mapping/src/main/resources/com/iluwatar/metamapping/model/User.hbm.xml
+++ b/metadata-mapping/src/main/resources/com/iluwatar/metamapping/model/User.hbm.xml
@@ -4,7 +4,7 @@
     "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
 
 <hibernate-mapping>
-  <class name="com.iluwatar.metamapping.model.User" table="user">
+  <class name="com.iluwatar.metamapping.model.User" table="user_account">
     <id name="id" type="java.lang.Integer" column="id">
       <generator class="native"/>
     </id>
diff --git a/pom.xml b/pom.xml
index fd1193bc15aa..ed94116b65cb 100644
--- a/pom.xml
+++ b/pom.xml
@@ -38,7 +38,6 @@
     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
     <sonar-maven-plugin.version>3.8.0.2131</sonar-maven-plugin.version>
     <spring-boot.version>2.7.5</spring-boot.version>
-    <h2.version>1.4.190</h2.version>
     <jacoco.version>0.8.8</jacoco.version>
     <commons-dbcp.version>1.4</commons-dbcp.version>
     <camel.version>2.25.1</camel.version>
@@ -246,11 +245,6 @@
         <type>pom</type>
         <scope>import</scope>
       </dependency>
-      <dependency>
-        <groupId>com.h2database</groupId>
-        <artifactId>h2</artifactId>
-        <version>${h2.version}</version>
-      </dependency>
       <dependency>
         <groupId>commons-dbcp</groupId>
         <artifactId>commons-dbcp</artifactId>
diff --git a/transaction-script/src/main/java/com/iluwatar/transactionscript/RoomSchemaSql.java b/transaction-script/src/main/java/com/iluwatar/transactionscript/RoomSchemaSql.java
index acb96930ebf8..29cad58620ef 100644
--- a/transaction-script/src/main/java/com/iluwatar/transactionscript/RoomSchemaSql.java
+++ b/transaction-script/src/main/java/com/iluwatar/transactionscript/RoomSchemaSql.java
@@ -30,7 +30,7 @@
 public final class RoomSchemaSql {
 
   public static final String CREATE_SCHEMA_SQL =
-      "CREATE TABLE ROOMS (ID NUMBER, ROOM_TYPE VARCHAR(100), PRICE INT(100), BOOKED VARCHAR(100))";
+      "CREATE TABLE ROOMS (ID NUMBER, ROOM_TYPE VARCHAR(100), PRICE INT, BOOKED VARCHAR(100))";
   public static final String DELETE_SCHEMA_SQL = "DROP TABLE ROOMS IF EXISTS";
 
   private RoomSchemaSql() {


Test patch: