Handling Reserved Keywords in SQL Statements

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 i

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! Post, Code and Quiet Time. © 2024 – Unsubscribe

How to Handle Reserved Keywords in H2 Database

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), -- Er

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. Post, Code and Quiet Time. © 2024 – Unsubscribe

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

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 co

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. Post, Code and Quiet Time. © 2024 – Unsubscribe

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

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 messag

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! 🔒✨ Post, Code and Quiet Time. © 2024 – Unsubscribe

🍪 Custom React Hook to Get Cookie Value by Name 🍪

Do you ever find yourself needing to access cookie values in your React applications? I've got you covered with a simple and reusable custom React hook! useCookie.ts import { useEffect, useState } from 'react'; // Hook to get the public key from cookie export function useCookie(cookieName: string): string | null { const [cookieValue, setCookieValue] = useState<string | null>(null); useEffect(() => { const getCookie = (): string | null => { const cookies = documen

Post, Code and Quiet Time. 🍪 Custom React Hook to Get Cookie Value by Name 🍪 By Errong Leng – 01 May 2024 – View online → Photo by Jonny Gios / Unsplash Do you ever find yourself needing to access cookie values in your React applications? I've got you covered with a simple and reusable custom React hook! useCookie.ts import { useEffect, useState } from 'react'; // Hook to get the public key from cookie export function useCookie(cookieName: string): string | null { const [cookieValue, setCookieValue] = useState<string | null>(null); useEffect(() => { const getCookie = (): string | null => { const cookies = document.cookie.split(';').map(cookie => cookie.trim()); for (const cookie of cookies) { const [name, value] = cookie.split('='); if (name === cookieName) { return decodeURIComponent(value); } } return null; }; setCookieValue(getCookie()); }, [cookieName]); return cookieValue; } Sample usage // Usage example: const MyComponent = () => { const userId = useCookie('user_id'); return ( <div> User ID: {userId} </div> ); }; export default MyComponent; Conclusion With this hook, you can easily retrieve the value of a cookie by its name in your React components. Let me know what you think! And feel free to share your thoughts or suggestions in the comments below. Happy coding! 😊 Post, Code and Quiet Time. © 2024 – Unsubscribe