GORM Best Practices: SQL injections and GORM Config

You’re already familiar with GORM and its role in simplifying database interactions in Go applications. In this topic, you’ll learn about DSN configuration, connection pooling, and best practices to optimize and customize GORM’s configuration. Additionally, you’ll learn the fundamentals of preventing SQL injections to safeguard your application’s security and integrity.

Database configuration

GORM can be used with different database management systems. The correct configuration usage is important for the seamless connection between the Go application and the database. it is done by Dialect configuration. It is the way of connecting the particular database correctly according to the management system. Let’s explore it using MySQL and PostgreSQL, two popular relational database management systems. Generally, the code snippet is almost the same; the keyword here is almost. The only difference is between the drivers and the data source name.

// MySQL Example

import (
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

func main() {
    dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"

	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }
    
    // Do something useful with db...
}

// PostgreSQL Example

import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

func main() {
    dsn := "host=localhost user=gorm password=gorm dbname=gorm port=5432 sslmode=disable TimeZone=Asia/Shanghai"

    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

	// Do something useful with db...
}

Here are some crucial differences:

Of course, the correct driver should be imported. By importing MySQL, we cannot use PostgreSQL or any other DBMS. In the case of MySQL, it is gorm.io/driver/mysql , and in the case of PostgreSQL, it is gorm.io/driver/postgres . So, the line is almost the same. Just the DBMS's name should be typed after the driver - gorm.io/driver/DBMS name

Now, let's compare the Data Source Names. When working with MySQL databases, the expected DSN is user:pass@tcp(localhost:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local; this string includes the username, password, server location (with port), database name, and additional parameters for the character set, time parsing, and location settings. It’s important to replace user, pass, and dbname with your actual database credentials and name.

In contrast, the PostgreSQL DSN takes a slightly different form: "host=localhost user=gorm password=gorm dbname=gorm port=5432 sslmode=disable TimeZone=Asia/Shanghai". Here, connection details are separated by spaces and include the host, port, user, password, database name, SSL mode, and time zone configuration. Similar to MySQL, you should replace the host, user, password, and dbname values accordingly. Here are the key differences in Data Source Names:

1. Connection String Structure: MySQL uses a more URL-like format, while PostgreSQL opts for a space-separated list of parameters.

2. SSL and Time Zone Settings: PostgreSQL DSN explicitly includes sslmode and TimeZone, which are not present in the MySQL DSN by default. These parameters allow for finer control over connection security and time-related queries.

3. Character Encoding and Parsing: The MySQL DSN specifies charset and parseTime options directly in the connection string, emphasizing the importance of character set compatibility and time parsing behavior.

Nevertheless, it does not mean that GORM supports only MySQL and PostgreSQL. There are other database management systems with which GORM can make seamless connections. Luckily, the snippet is the same; the only change is the driver and DSN. To learn more about connecting to other DBMS, you can take a look at GORM’s official docs.

Connection pooling

Another essential technique is connection pooling. It manages and reuses database connections efficiently, improving performance and resource utilization by keeping a pool of established connections ready for use rather than creating a new connection for each database operation.

In GORM, we can configure connection pooling to control the number of idle connections, maximum open connections, and maximum connection lifetime. These settings are managed through the database/sql package, which GORM uses for its underlying database connections:

// First, obtain the generic database interface for configuration
sqlDB, err := db.DB()
if err != nil {
    panic(err)
}

// Set connection pool configurations
sqlDB.SetMaxIdleConns(10)           // Set maximum number of idle connections
sqlDB.SetMaxOpenConns(100)          // Set maximum number of open connections
sqlDB.SetConnMaxLifetime(time.Hour) // Set maximum connection lifetime

Let’s break it down:

1. sqlDB().SetMaxIdleConns(10): Configures the pool to maintain up to 10 idle connections, which are open but unused, ensuring they're ready for immediate use; this optimization means less time spent opening new connections for each database operation.

2. sqlDB().SetMaxOpenConns(100): Limits the pool to 100 active connections to prevent overloading the database with too many concurrent connections; this cap helps manage the load on the database, ensuring efficient operation and preventing potential bottlenecks.

3. sqlDB().SetConnMaxLifetime(time.Hour): Sets connections to expire after one hour, forcing their renewal; this prevents issues from using stale connections, ensuring the pool is populated with fresh connections for reliable database interactions.

To wrap up, configuring limits for idle and open connections, along with defining connection lifetimes, can significantly enhance application performance and resource utilization; this approach not only optimizes database connectivity but also aids in achieving scalability and maintaining system stability.

GORM Config

The gorm.Config struct plays a crucial role when initializing a connection with the DB using GORM. It contains a series of fields that enable or disable various configuration options for the GORM connection, tailoring database interaction to your specific needs.

As you know, to set up the connection, the gorm.Open() function is used like this:

db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

In the above code line, the gorm.Open() method accepts the GORM configurations as a second argument, and since it is passed as an empty struct, it will use the default option. The gorm.Config struct is defined as follows:

type Config struct {
    SkipDefaultTransaction                   bool
    NamingStrategy                           schema.Namer
    Logger                                   logger.Interface
    NowFunc                                  func() time.Time
    DryRun                                   bool
    PrepareStmt                              bool
    DisableNestedTransaction                 bool
    AllowGlobalUpdate                        bool
    DisableAutomaticPing                     bool
    DisableForeignKeyConstraintWhenMigrating bool
}

These fields allow for extensive customization of how GORM behaves with your database. Below is a detailed explanation of each one of them:

1. SkipDefaultTransaction: By default, GORM executes write operations ((like creating, updating, or deleting records) within a transaction to maintain data integrity. However, disabling the default transaction feature can enhance database performance where data consistency across operations is not a primary concern.

2. NamingStrategy: GORM's default naming conventions use snake_case for table and column names while pluralizing table names. However, the NamingStrategy field in gorm.Config allows for customization, enabling developers to adapt naming schemes to fit specific organizational standards or preferences, such as adding prefixes or using singular table names.

3. Logger: Specifies the logging mechanism GORM should use, enabling detailed insight into the ORM's operation and interactions with the database. It allows you to choose between built-in loggers like logger.Default for general logging, logger.Silent for minimal output, or integrate custom/third-party loggers that comply with the logger.Interface, tailoring logging to your application needs.

4. NowFunc: GORM uses a function to get the current time when it needs to create a timestamp. Some applications might need to use a specific timestamp timezone or format. Customizing this function allows for using specific time zones or formats, ensuring that timestamps align with application requirements.

5. DryRun: This option lets GORM generate SQL commands without actually executing them. It is useful for testing or preparing SQL statements. Let's say we are building a complex query and want to ensure it's correct before actually executing it. By using the dry run feature, we can see the SQL commands that GORM would run without affecting your database.

6. PrepareStmt: GORM can create prepared statements to speed up future database calls. If your application frequently repeats the same database queries, you can speed things up by preparing these queries in advance. GORM can cache prepared statements, reducing the time it takes to execute them later.

7. DisableNestedTransaction: While GORM supports nested transactions for complex operations, they can introduce unnecessary complexity. Disabling this feature simplifies transaction management, making the codebase easier to understand and maintain.

8. AllowGlobalUpdate: This option enables or disables the ability to perform global update/delete operations. While it can be efficient for batch updating or deleting many records across multiple tables, remember to use it cautiously to prevent accidental mass data alterations!

9. DisableAutomaticPing: GORM checks if the database is available automatically when it starts. If you work in an environment where database availability is always guaranteed, you can disable the automatic ping feature to speed up GORM's initialization process.

10. DisableForeignKeyConstraintWhenMigrating: GORM automatically adds foreign key constraints when creating tables. However, you might want to manage these constraints manually in certain situations. You can retain more control over the database schema by disabling automatic constraint creation.

Preventing SQL injections

SQL injection attacks are among the most common and severe security vulnerabilities in web applications. These attacks occur when untrusted data is inserted into SQL queries without proper validation or escaping, potentially allowing attackers to manipulate or destroy databases.

GORM mitigates the risk of SQL injections through the use of parameterized queries, input validation, and escaping special characters.

Parameterized queries: GORM strongly advocates using parameterized queries over string concatenation for building SQL queries; this approach automatically prevents the insertion of malicious SQL code. Consider the example below:

// Potentially unsafe due to SQL injection risk
userInput := "user; DROP TABLE users;"
passwordInput := "password"
db.Where("username = '" + userInput + "' AND password = '" + passwordInput + "'").First(&User{})

The above approach is unsafe because userInput includes a harmful SQL statement DROP TABLE users; that could lead to the users table's deletion if executed.

A safer approach uses parameterized queries:

// Safe against SQL injection
db.Where("username = ? AND password = ?", userInput, passwordInput).First(&User{})

The above approach is secure because:

1. The ? placeholders like name = ? and password = ? ensure that userInput and passwordInput are treated as parameters, not part of the SQL command.

2. The database driver automatically escapes the user input before sending it to the DB server. Escaping means that any special characters that could alter the SQL query structure or execute unintended commands are properly encoded or sanitized.

Mitigating SQL injection in methods: While GORM provides a robust framework for preventing SQL injections, certain methods like Select, Distinct, Model, Group, Having, Raw, Exec, and Order require careful use. Directly incorporating user-submitted data without validation can introduce vulnerabilities. Always validate input against a list of allowed characters and use parameterized queries to ensure security.

Below are some examples of vulnerable and secure approaches using the above methods:

// Insecure: Exposes potential SQL injection vulnerability
db.Distinct("name; DROP TABLE users;").First(&user)

// Secure: Utilizes parameterized queries to prevent SQL injection
db.Select("DISTINCT(name)").Where("name = ?", "desired_name").First(&user)

As you can see in the above example, the secure approach uses parameterization to safeguard against SQL injection effectively, contrasting with the vulnerability exposed by direct string insertion in the insecure method. Remember that adopting parameterized queries and stringent validation practices ensures the robust security of GORM-based applications against SQL injection, safeguarding data integrity and application functionality.

Conclusion

In this topic, you learned how to connect to a specific database management system correctly and set connection pooling to improve performance and resource utilization. Additionally, you are aware that you can customize the gorm.Config struct during the DB initialization according to your specific application needs.

You also learned how to prevent SQL injection during database interaction. By following these best practices, we can leverage GORM effectively in our Go applications while minimizing the risk of SQL injection vulnerabilities, optimizing database interactions, and maintaining clean and readable code.