MySQL Escaping on the Client-Side With Go

Day-to-day database operation requires, from an administrator, deep knowledge of db internals and security issues, in particular things like SQL injections. In order to prevent such kind of an attack, we have included go-sql-driver into our code for secure placeholder escaping.

Unfortunately, not all cases are secured by the driver.

In case we are using the standard driver for working with MySQL, if we need to pass a variable to the database query, we use a placeholder “?” in order for the server to understand that it needs to process the incoming variable to avoid injection. It works fine with just regular SELECT/INSERT/UPDATE statements, but, unfortunately, MySQL server is not able to process all types of queries.

For example:

db.Exec("CREATE USER ?@? IDENTIFIED BY ?", name, host, pass)

This query will return an error from the server, so we have to conduct all operations on the client-side.

For such cases in the “go-sql-driver/mysql” there is an option called “interpolateParams”. If you set it to “true”, the driver will escape the variables for any requests and send ready-for-use queries to the server.

db, err := sql.Open("mysql", "root:pass@tcp(localhost)/?interpolateParams=true")

Don’t forget that interpolateParams cannot be used together with the multibyte encodings BIG5, CP932, GB2312, GBK, or SJIS. These are rejected as they may introduce a SQL injection vulnerability!

Now we are ready to use our request.

db.Exec("CREATE USER ?@? IDENTIFIED BY ?", "myuser", "localhost", "password")

The server will receive a single query string with given parameters and will look like this:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password'

So now you are able to not use, for example, SQL variables or/and CONCAT() function for creating queries.

As we see the use of the go-sql-driver allows us to reduce the number of operations with the database, and to facilitate working with it. And, of course, using interpolateParams, we can easily – and most importantly – safely work with any kind of request.

Good luck!

by Max Dudin via Percona Database Performance Blog