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.
