Skip to main content

Time-based SQL Injection

Need​

Prevention of time-based SQL injection attacks

Context​

  • Usage of Go 1.15 for building high-performance and scalable applications
  • Usage of database/sql for interacting with SQL databases
  • Usage of gin-gonic/gin for building web applications and APIs in Go
  • Usage of go-sql-driver/mysql for connecting and interacting with MySQL database in Go programming language

Description​

Non compliant code​

package main

import (
    "database/sql"
    "github.com/gin-gonic/gin"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "user:password@/dbname")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    router := gin.Default()

    router.GET("/user/:id", func(c *gin.Context) {
        id := c.Param("id")
        var username string

        row := db.QueryRow("SELECT username FROM users WHERE id = " + id)
        row.Scan(&username)

        c.JSON(200, gin.H{
            "user": username,
        })
    })

    router.Run(":8080")
}

In the above code, the application is vulnerable to time-based SQL injection attacks. The vulnerability lies in the route handler for "/user/:id". The application takes a user ID from the URL parameter and directly concatenates it into a SQL query without any sanitization or parameterization.

An attacker could exploit this vulnerability by injecting a time-based SQL command, such as 1 OR SLEEP(5)=0 --, as the user ID. This would cause the application to execute the SQL query SELECT username FROM users WHERE id = 1 OR SLEEP(5)=0 --, delaying the response by 5 seconds and confirming the presence of a SQL injection vulnerability.

The application is also vulnerable to other forms of SQL injection, as it does not sanitize or parameterize the user input in any way. This could allow an attacker to manipulate the SQL query to extract, modify, or delete data from the database.

Steps​

  • Use prepared statements or parameterized queries to prevent SQL injection.
  • Instead of concatenating the 'id' parameter directly into the SQL query, bind the parameter to a placeholder in the query.
  • Ensure that the 'id' parameter is properly validated and sanitized before using it in the query.
  • Implement proper input validation to only allow valid 'id' values.
  • Limit the execution time of SQL queries to prevent time-based attacks.
  • Use database-specific features or libraries to enforce query execution time limits.
  • Regularly update and patch the database and database driver to fix any known vulnerabilities.

Compliant code​

package main

import (
    "database/sql"
    "github.com/gin-gonic/gin"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "user:password@/dbname")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    router := gin.Default()

    router.GET("/user/:id", func(c *gin.Context) {
        id := c.Param("id")
        var username string

        // Use prepared statement
        stmt, err := db.Prepare("SELECT username FROM users WHERE id = ?")
        if err != nil {
            panic(err)
        }

        // Execute the prepared statement with the provided id
        err = stmt.QueryRow(id).Scan(&username)
        if err != nil {
            panic(err)
        }

        c.JSON(200, gin.H{
            "user": username,
        })
    })

    router.Run(":8080")
}

The original code was vulnerable to Time-based SQL Injection attacks because it concatenated the 'id' parameter directly into the SQL query. This could allow an attacker to inject malicious SQL code into the query, causing a delay in the execution of the queries or executing a heavy query that generates a time delay.

The fixed code uses a prepared statement to prevent SQL injection. Instead of concatenating the 'id' directly into the SQL query, it binds the 'id' to a placeholder in the query (?). This ensures that the 'id' is treated strictly as a value, not part of the SQL command, thus preventing SQL injection.

The 'id' parameter is obtained from the request context using c.Param("id"). It's important to validate and sanitize this 'id' parameter before using it in the query. This code assumes that such validation and sanitization are done before this point. If not, you should add appropriate validation and sanitization code.

To prevent time-based attacks, you should also limit the execution time of SQL queries. This can be done using database-specific features or libraries. Regularly updating and patching the database and database driver can also help to fix any known vulnerabilities.

References​

Last updated on