如何最大程度地降低golang服务中下游服务中SQL注入的风险？

Question

I'm writing a semi-externally facing web service in golang that lets users query information about their accounts which are spread over several internal legacy services.

My service passes user input strings onto multiple backend RESTful APIs which do MySQL lookups based on the strings to generate the results which are passed back to my service to give to the user.

Historically, these legacy backend services have not been exposed to user input, so I'm not sure that they have the proper guards in place against SQL injection.

Ordinarily I'd prevent SQL injection with Prepared Statements to keep the DB engine from treating user strings as parsable, but in this context I don't control the DB calls -- they're far downstream and it isn't practical to audit them all right now.

What can I do in my golang code to sanitize the user input as much as possible to minimize the risk of an SQL injection slipping through? This is ultimately a stopgap measure until all the downstream DB calls can be audited for injection-safety.

Edit: The user input can for all practical purposes be an arbitrary string, but it shouldn't ever be executable code. My service expects field values from the user, not code.

Answer 1

At a past job, I maintained a facility for our service that permitted ad hoc queries. It allowed managers to request reports without having to wait weeks for a code deployment (back in the quaint days when deployments took weeks).

We did NOT support ad hoc report queries by making the service accepting arbitrary strings as input and execute them as SQL. That's terribly insecure, as I'm sure you know.

The way it worked was that the report queries were stored in a database, along with the number of query parameters required.

CREATE TABLE ManagerQueries (
  id INT PRIMARY KEY,
  query TEXT NOT NULL,
  description TEXT NOT NULL,
  num_params TINYINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO ManagerQueries
SET query = 'SELECT COUNT(*) FROM logins WHERE user_id = {0} AND created_at > {1}',
    description = 'Count a given user logins since a date',
    num_params = 2;

The manager front-end could request a query by its primary key, NOT by specifying an arbitrary SQL string in a web request.

Only the DBA and perhaps other developers or managers who knew how to write safe queries were allowed to add new queries to this repository, so there was some assurance that the queries were tested and vetted.

When a report query was requested through the UI, it forced the user to supply values for the query parameters. In our case, it read the SQL from the database, did a prepare() and then bound the values for the execute(). So SQL injection defense was satisfied.

In your case, your code might not have direct access to the legacy service's database, so you can't do the prepare/execute and use bound parameters. You have to submit a static query with the values integrated.

In other languages, you can make any string value safe for interpolation into an SQL query by escaping. See the MySQL C API function mysql_real_escape_string().

Numeric values are even easier. You don't have to escape anything, you just have to make sure the numeric value is a genuine numeric. Once you cast a dynamic value to a numeric, it's safe to interpolate back into any SQL string.

Unfortunately, I don't think golang SQL packages support any escaping function. This has been requested as a feature, but as far as I know, there's no supported implementation yet. See discussion here: https://github.com/golang/go/issues/18478

So you may have to implement your own escaping function. You could for example model it after the official MySQL C implementation: https://github.com/mysql/mysql-server/blob/8.0/mysys/charset.cc#L716

Note that it's a bit trickier than just using a regexp substitution, because you need to account for multi-byte character sets.