UUID support in various databases

Jmix
#1

The Jmix project template currently defines the following Liquibase properties:

<property name="uuid.type" dbms="oracle" value="varchar2(32)"/>
<property name="uuid.type" dbms="!oracle" value="uuid"/>

And then duplicates each insert, specifying uuid values with and without dashes depending on the dbms:

<insert tableName="APP_USER" dbms="postgresql, mssql, hsqldb">
    <column name="ID" value="60885987-1b61-4247-94c7-dff348347f93"/>
    ...
</insert>

<insert tableName="APP_USER" dbms="oracle, mysql, mariadb">
    <column name="ID" value="608859871b61424794c7dff348347f93"/>
    ...
</insert>

First of all, varchar2(32) is not the best way to store an uuid in Oracle. The proper column type should be raw(16):

<property name="uuid.type" dbms="oracle" value="raw(16)"/>
<property name="uuid.type" dbms="!oracle" value="uuid"/>

Then, I did a few tests on various dbms (not all of them, admittedly) and the version without dashes seems to work on all those I tried it on, which would get rid of the need to duplicate inserts:

<insert tableName="APP_USER">
    <column name="ID" value="608859871b61424794c7dff348347f93"/>
    ...
</insert>

I tested the following on HSQLDB 2.5.0, Oracle 11g, and Postgresql 10.0, and they all worked (without dashes in the string):

create table test_uuid (id uuid); -- raw(16) on Oracle
insert into test_uuid values ('608859871b61424794c7dff348347f93');

Last but not least, the project template seems to reuse the same uuid values for the admin user in every Jmix project that is created. If so, then it’s a potential security issue. One of the benefits of uuid is that they cannot be guessed and used in sql injection or other tricks. To do it properly, the Jmix intellij plugin should generate a random uuid for every new project that is created.

#3

Hi,

  1. Jmix should support migration from existing CUBA projects. The migration includes database compatibility between CUBA and Jmix applications. By default, CUBA uses varchar2(32) for UUID properties in the ORACLE database. So we keep the same type varchar2(32) in Jmix. This allows us to migrate existing ORACLE databases without writing additional data update statements for each UUID column. Automatic statements generation by Jmix/Jmix Studio is a complex task and errors may occur while performing data migration from varchar2(32) to raw type.

  2. Liquibase update script without dashes in UUID value doesn’t work on MSSQL database. So separate insert scripts are needed to support different database types.

<insert tableName="APP_USER">
    <column name="ID" value="608859871b61424794c7dff348347f93"/>
    ...
</insert>
  1. Potential security issue.
    3.1 JDBC prepared statements have no problems with SQL injections. Jmix/CUBA uses prepared statements for all database modifications.
    3.2 Hardcoded user ids. We’ll consider UUID values generation for default admin user and role. As a workaround, you can change UUID values in the init-user.xml script.

Thank you,
Andrey Subbotin

1 Like
#5

Ok thank you, that makes sense.