Errong Leng

Using H2 Memory Database to Test Applications with Oracle's 'SELECT sysdate FROM DUAL'

By Errong Leng – 02 May 2024 – View online →

Introduction:

When developing applications that rely on Oracle databases, it's common to use the sysdate function along with the DUAL table to retrieve the current date and time. However, when writing tests for these applications, you may encounter challenges due to the Oracle-specific syntax. In this post, we'll discuss how to use H2 memory database to test applications that use SELECT sysdate FROM DUAL.

Using H2 Memory Database for Testing:

H2 is an in-memory database that provides compatibility with various SQL databases, including Oracle. By configuring H2 to emulate Oracle's behavior, you can seamlessly test applications without modifying the code.

1. Set Up H2 Memory Database:

First, ensure H2 is added as a dependency in your project. Then, configure H2 in your application's application.properties or application.yml file:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;INIT=RUNSCRIPT FROM 'classpath:init.sql'  spring.datasource.driverClassName=org.h2.Driver  spring.datasource.username=sa  spring.datasource.password=

In the above configuration:

spring.datasource.url: Specifies the H2 memory database URL with initialization script.
DB_CLOSE_DELAY=-1 and DB_CLOSE_ON_EXIT=FALSE: These properties ensure that the database remains open after the connection is closed, allowing access to the data during the entire test execution.
INIT=RUNSCRIPT FROM 'classpath:init.sql': This initializes the database by running the init.sql script located in the classpath.

2. Create DUAL-like Table and Initialize Data:

Create a init.sql file in the src/test/resources directory with the following content:

CREATE TABLE IF NOT EXISTS DUAL ( sysdate TIMESTAMP);

INSERT INTO DUAL (sysdate) VALUES (CURRENT_TIMESTAMP());

This script creates a DUAL-like table and inserts the current timestamp into it.

3. Modify the Test SQL Statement:

For testing, modify the SQL statement to insert or select the current date or timestamp from the DUAL-like table:

Inserting the current timestamp:

INSERT INTO DUAL (sysdate) VALUES (CURRENT_TIMESTAMP());

Selecting the current timestamp:

SELECT sysdate FROM DUAL;

Conclusion:

Using H2 memory database with a DUAL-like table allows you to test applications that rely on Oracle-specific syntax such as SELECT sysdate FROM DUAL. By configuring H2 to mimic Oracle's behavior, you can seamlessly test your applications without the need to modify the code.

Closing:

By following the steps outlined in this post, you can effectively use H2 memory database to test applications that use SELECT sysdate FROM DUAL, ensuring compatibility and reliability in your testing environment.

Handling Reserved Keywords in SQL Statements

Post, Code and Quiet Time.

Handling Reserved Keywords in SQL Statements

By Errong Leng – 02 May 2024 – View online →

Photo by Micke Lindström / Unsplash

Introduction:

When writing SQL statements, you may encounter situations where your identifiers clash with reserved keywords of the database system you're using. This can lead to errors when creating tables or executing queries. In this post, we'll explore how to handle reserved keywords in SQL statements for some of the most popular database systems: Oracle, MS SQL Server, MySQL, H2 and PostgreSQL.

1. Oracle:

Oracle handles reserved keywords by enclosing identifiers that clash with keywords in double quotation marks (""). For example:

CREATE TABLE my_table ( "VALUE" VARCHAR2(255) );

2. MS SQL Server:

MS SQL Server allows the use of square brackets ([]) to escape identifiers. For example:

CREATE TABLE my_table ( [VALUE] VARCHAR(255) );

3. MySQL:

MySQL uses backticks (`) to escape identifiers. For example:

CREATE TABLE my_table ( `VALUE` VARCHAR(255) );

4. H2:

H2 provides the NON_KEYWORDS parameter in the datasource URL to treat specific words as regular identifiers instead of reserved keywords. For example:

spring.datasource.url=jdbc:h2:mem:testdb;NON_KEYWORDS=VALUE

This tells H2 to treat "VALUE" as a regular identifier rather than a reserved keyword.

5. PostgreSQL:

PostgreSQL follows the SQL standard and allows identifiers to be enclosed in double quotes (") to handle reserved keywords. For example:

CREATE TABLE my_table ( "VALUE" VARCHAR(255) );

You can use double quotes to escape identifiers that are reserved keywords or contain special characters in PostgreSQL.

Conclusion:

Each database system has its own way of handling reserved keywords and identifiers. By understanding the conventions for your chosen database system, such as Oracle, MS SQL Server, MySQL, PostgreSQL, and H2, you can ensure that your SQL statements are free from errors and conflicts with reserved keywords.

Closing:

Handling reserved keywords in SQL statements is essential for smooth database operations. By following the conventions outlined in this post for popular database systems, you can avoid issues related to reserved keywords and develop robust database solutions.

Feel free to adjust the content according to your preferences or specific requirements!

How to Handle Reserved Keywords in H2 Database

Post, Code and Quiet Time.

How to Handle Reserved Keywords in H2 Database

By Errong Leng – 02 May 2024 – View online →

Photo by Thibault Mokuenko / Unsplash

Introduction:

When working with H2 databases, you may encounter situations where your SQL statements fail due to the use of reserved keywords as identifiers. One common example is using "VALUE" as a column name. In this post, we'll discuss how to resolve this issue and provide a solution for the fix.

The Problem:

Consider the following SQL statement for creating a table:

CREATE TABLE bby_param (      amended_on DATE,      created_on DATE,      syscode VARCHAR(255),      value VARCHAR(255), -- Error: "value" is a reserved keyword      ...  );

The issue arises because "value" is a reserved keyword in SQL, causing the SQL statement to fail with an error message indicating "expected identifier".

The Solution:

To resolve this issue, we need to configure H2 to treat "value" as a regular identifier rather than a reserved keyword. This can be achieved by adding "value" to the list of non-keywords in the H2 database URL.

Steps to Fix:

Update your H2 database URL to include the non-keyword list: spring.datasource.url=jdbc:h2:mem:testdb;NON_KEYWORDS=VALUE

Adding NON_KEYWORDS=VALUE instructs H2 to treat "VALUE" as a regular identifier.Restart your application to apply the changes.

Conclusion:

When working with H2 databases, it's essential to be aware of reserved keywords that may cause issues in your SQL statements. By configuring H2 to treat such keywords as regular identifiers, you can avoid errors and ensure smooth database operations.

Closing:

Handling reserved keywords in H2 databases is a common challenge, but with the right configuration, it's easily manageable. By following the steps outlined in this post, you can resolve issues related to reserved keywords and continue developing your applications smoothly.

How to Handle Spring Security in Spring Boot Integration Tests for @WebMvcTest

Post, Code and Quiet Time.

How to Handle Spring Security in Spring Boot Integration Tests for @WebMvcTest

By Errong Leng – 02 May 2024 – View online →

Photo by David Becker / Unsplash

Introduction:

When writing integration tests for Spring Boot applications that use Spring Security, you may encounter situations where you need to handle security configurations to properly test your endpoints. In this post, we'll explore different approaches to handle Spring Security in integration tests using the @WebMvcTest annotation.

1. Load Custom Security Configuration:

If your application has custom security configurations, you can selectively load them for testing by importing the configuration class using @Import annotation.

@WebMvcTest(YourTestController.class)  @Import(SecurityConfig.class) // Load your security configuration  public class YourTestControllerTest {      // Your tests...  }

2. Use MockUser for Authorization:

When your tests require authentication but not the full security setup, you can use @WithMockUser to provide a mock user.

@WebMvcTest(YourTestController.class)  @Import(SecurityConfig.class) // Load your security configuration  public class YourTestControllerTest {        @Autowired      private MockMvc mockMvc;        @Test      @WithMockUser      public void testEndpointWithMockUser() throws Exception {          // Test logic with mock user          mockMvc.perform(...      }  }

3. Difference between @SpringBootTest and @WebMvcTest:

@SpringBootTest loads the complete Spring application context and is used for integration testing the entire application, including all layers and components. On the other hand, @WebMvcTest focuses only on the web layer and loads only the components relevant for testing MVC controllers.

Conclusion:

Handling Spring Security in Spring Boot integration tests is crucial to ensure that your application behaves correctly under various security scenarios. By using the appropriate annotations and configurations, you can effectively test your endpoints while simulating different security conditions.

Closing:

Integration testing with Spring Boot and Spring Security ensures that your application behaves as expected under different security configurations. By understanding how to handle Spring Security in integration tests and the difference between @SpringBootTest and @WebMvcTest, you can write comprehensive tests for your Spring Boot applications.

🔐 Implementing AES-GCM Encryption and Decryption in TypeScript via native crypto

Post, Code and Quiet Time.

🔐 Implementing AES-GCM Encryption and Decryption in TypeScript via native crypto

By Errong Leng – 01 May 2024 – View online →

Photo by NASA Hubble Space Telescope / Unsplash

AES-GCM is a popular encryption algorithm used to secure data in transit. Today, I'll show you how to implement AES-GCM encryption and decryption using JavaScript, making it compatible with both browser and Node.js environments.

It's strongly recommended to use authenticated encryption, which includes checks that the ciphertext has not been modified by an attacker. Authentication helps protect against chosen-ciphertext attacks, in which an attacker can ask the system to decrypt arbitrary messages, and use the result to deduce information about the secret key. While it's possible to add authentication to CTR and CBC modes, they do not provide it by default and when implementing it manually one can easily make minor, but serious mistakes. GCM does provide built-in authentication, and for this reason it's often recommended over the other two AES modes.

SubtleCrypto: encrypt() method - Web APIs | MDN

The encrypt() method of the SubtleCrypto interface encrypts data.

MDN Web Docs

Utility Functions

Here are a few utility functions that you can use to encrypt and decrypt data with AES-GCM:

import { Buffer } from 'buffer';    let AES_GCM_KEY: CryptoKey;  let AES_GCM_IV: Uint8Array;  let CRYPTO: any;    const initCryptoKeyIV = async (key: string, iv: string) => {      CRYPTO = (typeof window !== undefined && (window.crypto?.subtle)) ? window.crypto : await import('crypto').then((crypto) => crypto.webcrypto);      AES_GCM_KEY = await CRYPTO.subtle.importKey(          "raw", // format          Buffer.from(key, "base64"), // key data          { name: "AES-GCM" }, // algorithm          true, // extractable          ["encrypt", "decrypt"] // key usages      );      AES_GCM_IV = Buffer.from(iv, "base64"); // iv data      console.log("errong", key, iv, AES_GCM_KEY, AES_GCM_IV, CRYPTO);  }    export const decrypt = async (encryptedText: string) => {        const buffer = Buffer.from(encryptedText, "base64");      const decrypted = await CRYPTO.subtle.decrypt(          {              name: "AES-GCM",              iv: AES_GCM_IV          },          AES_GCM_KEY,          buffer      );      return Buffer.from(decrypted).toString();  }    export const encrypt = async (plainText: string) => {        const buffer = Buffer.from(plainText);      const decrypted = await CRYPTO.subtle.encrypt(          {              name: "AES-GCM",              iv: AES_GCM_IV          },          AES_GCM_KEY,          buffer      );      return Buffer.from(decrypted).toString("base64");  }

Unit Tests

import { initCryptoKeyIV, encrypt, decrypt } from ".";    describe("AES-GCM", () => {      it("decrypted text should equal plain text", async () => {          await initCryptoKeyIV("8b/qGo5A82kW3ThhkyN3cyzKo98U54iFk+TKIjN0nfo=", "2K5/8S7fPvrVH0Mk");          const plain_text = "lengerrong";          const encrypted_text = await encrypt(plain_text);          console.log("encrypted_text", encrypted_text);          const decrypted_text = await decrypt(encrypted_text);          console.log("decrypted_text", decrypted_text);          expect(decrypted_text).toEqual(plain_text);      });  });

Browser and Node.js Compatibility

These functions are compatible with both browser and Node.js environments, allowing you to seamlessly encrypt and decrypt data across different platforms.

Give it a try and let me know what you think! If you have any questions or suggestions, feel free to leave a comment below. Happy coding! 🔒✨

Errong Leng

Using H2 Memory Database to Test Applications with Oracle's 'SELECT sysdate FROM DUAL'

Handling Reserved Keywords in SQL Statements

How to Handle Reserved Keywords in H2 Database

How to Handle Spring Security in Spring Boot Integration Tests for @WebMvcTest

🔐 Implementing AES-GCM Encryption and Decryption in TypeScript via native crypto

Utility Functions

Unit Tests

Browser and Node.js Compatibility

@qzl/typed-i18n — Our Open-Source Typed i18n Project is Gaining Traction

Report Abuse

Labels