Database Security

This section on OWASP SCP will cover all of the database security issues and actions developers and DBAs need to take when using databases in their web applications.

Go doesn’t have database drivers. Instead there is a core interface driver on the database/sql package. This means that you need to register your SQL driver (eg: MariaDB, sqlite3) when using database connections.

The best practice

Before implementing your database in Go, you should take care of some configurations that we’ll cover next:

• Secure database server installation¹.
  • Change/set a password for root account(s).
  • Remove the root accounts that are accessible from outside the localhost.
  • Remove any anonymous-user accounts.
  • Remove any existing test database.
• Remove any unnecessary stored procedures, utility packages, unnecessary services, vendor content (e.g. sample schemas).
• Install the minimum set of features and options required for your database to work with Go.
• Disable any default accounts that are not required on your web application to connect to the database.

Also, because it’s important to validate input, and encode output on the database, be sure to investigate the [Input Validation][#validation] and [Output Encoding][../ch3/] sections of this guide.

This basically can be adapted to any programming language when using databases.

Database Connections

The concept

sql.Open does not return a database connection but *DB: a database connection pool. When a database operation is about to run (e.g. query), an available connection is taken from the pool, which should be returned to the pool as soon as the operation completes.

Remember that a database connection will be opened only when first required to perform a database operation, such as a query. sql.Open doesn’t even test database connectivity: wrong database credentials will trigger an error at the first database operation execution time.

Looking for a rule of thumb, the context variant of database/sql interface (e.g. QueryContext()) should always be used and provided with the appropriate Context.

From the official Go documentation “Package context defines the Context type, which carries deadlines, cancellation signals, and other request-scoped values across API boundaries and between processes.”. At a database level, when the context is canceled, a transaction will be rolled back if not committed, a Rows (from QueryContext) will be closed and any resources will be returned.

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

type program struct {
    base context.Context
    cancel func()
    db *sql.DB
}

func main() {
    db, err := sql.Open("mysql", "user:@/cxdb")
    if err != nil {
        log.Fatal(err)
    }
    p := &program{db: db}
    p.base, p.cancel = context.WithCancel(context.Background())

    // Wait for program termination request, cancel base context on request.
    go func() {
        osSignal := // ...
        select {
        case <-p.base.Done():
        case <-osSignal:
            p.cancel()
        }
        // Optionally wait for N milliseconds before calling os.Exit.
    }()

    err =  p.doOperation()
    if err != nil {
        log.Fatal(err)
    }
}

func (p *program) doOperation() error {
    ctx, cancel := context.WithTimeout(p.base, 10 * time.Second)
    defer cancel()

    var version string
    err := p.db.QueryRowContext(ctx, "SELECT VERSION();").Scan(&version)
    if err != nil {
        return fmt.Errorf("unable to read version %v", err)
    }
    fmt.Println("Connected to:", version)
}

Connection string protection

To keep your connection strings secure, it’s always a good practice to put the authentication details on a separated configuration file, outside of public access.

Instead of placing your configuration file at /home/public_html/, consider /home/private/configDB.xml: a protected area.

<connectionDB>
  <serverDB>localhost</serverDB>
  <userDB>f00</userDB>
  <passDB>f00?bar#ItsP0ssible</passDB>
</connectionDB>

Then you can call the configDB.xml file on your Go file:

configFile, _ := os.Open("../private/configDB.xml")

After reading the file, make the database connection:

db, _ := sql.Open(serverDB, userDB, passDB)

Of course, if the attacker has root access, he will be able to see the file. Which brings us to the most cautious thing you can do - encrypt the file.

Database Credentials

You should use different credentials for every trust distinction and level, for example:

• User
• Read-only user
• Guest
• Admin

That way if a connection is being made for a read-only user, they could never mess up with your database information because the user actually can only read the data.

Database Authentication

Access the database with minimal privilege

If your Go web application only needs to read data and doesn’t need to write information, create a database user whose permissions are read-only. Always adjust the database user according to your web applications needs.

Use a strong password

When creating your database access, choose a strong password. You can use password managers to generate a strong password.

Remove default admin passwords

Most DBS have default accounts and most of them have no passwords on their highest privilege user.

For example, MariaDB, and MongoDB use root with no password,

Which means that if there is no password, the attacker could gain access to everything.

Also, don’t forget to remove your credentials and/or private key(s) if you’re going to post your code on a publicly accessible repository in Github.

Parameterized Queries

Prepared Statements (with Parameterized Queries) are the best and most secure way to protect against SQL Injections.

In some reported situations, prepared statements could harm performance of the web application. Therefore, if for any reason you need to stop using this type of database queries, we strongly suggest you read Input Validation and Output Encoding sections.

Go works differently from usual prepared statements on other languages - you don’t prepare a statement on a connection. You prepare it on the DB.

Flow

1. The developer prepares the statement (Stmt) on a connection in the pool
2. The Stmt object remembers which connection was used
3. When the application executes the Stmt, it tries to use that connection. If it’s not available it will try to find another connection in the pool

This type of flow could cause high-concurrency usage of the database and creates lots of prepared statements. Therefore, it’s important to keep this information in mind.

Here’s an example of a prepared statement with parameterized queries:

customerName := r.URL.Query().Get("name")
db.Exec("UPDATE creditcards SET name=? WHERE customerId=?", customerName, 233, 90)

Sometimes a prepared statement is not what you want. There might be several reasons for this:

• The database doesn’t support prepared statements. When using the MySQL driver, for example, you can connect to MemSQL and Sphinx, because they support the MySQL wire protocol. But they don’t support the “binary” protocol that includes prepared statements, so they can fail in confusing ways.

• The statements aren’t reused enough to make them worthwhile, and security issues are handled in another layer of our application stack (See: Input Validation and Output Encoding), so performance as seen above is undesired.

Stored Procedures

Developers can use Stored Procedures to create specific views on queries to prevent sensitive information from being archived, rather than using normal queries.

By creating and limiting access to stored procedures, the developer is adding an interface that differentiates who can use a particular stored procedure from what type of information he can access. Using this, the developer makes the process even easier to manage, especially when taking control over tables and columns in a security perspective, which is handy.

Let’s take a look into at an example…

Imagine you have a table with information containing users’ passport IDs.

Using a query like:

SELECT * FROM tblUsers WHERE userId = $user_input

Besides the problems of Input validation, the database user (for the example John) could access ALL information from the user ID.

What if John only has access to use this stored procedure:

CREATE PROCEDURE db.getName @userId int = NULL
AS
    SELECT name, lastname FROM tblUsers WHERE userId = @userId
GO

Which you can run just by using:

EXEC db.getName @userId = 14

This way you know for sure that user John only sees name and lastname from the users he requests.

Stored procedures are not bulletproof, but they create a new layer of protection to your web application. They give DBAs a big advantage over controlling permissions (e.g. users can be limited to specific rows/data), and even better server performance.