How to send literal colons in a query?

[bgemmill]

For setup, I'm using SOCI 4.0.0 to connect to a postgres 11 database.

If we consider the two queries:

select 42::int2 what;
select array_to_string((array[1,2,3,4,5,6])[1:2], ',', '*');

Both will fail with soci-odbc, and the second will fail with soci-postgresql.

This happens because the colons and following characters are interpreted to be query parameters.

For ODBC, the issue appears to be that there's no way to escape the colon literal: https://github.com/SOCI/soci/blob/4.0.0/src/backends/odbc/statement.cpp#L73

For soci-postgresql, the first query is successful due to the work put in here: https://github.com/SOCI/soci/pull/148 Where it looks like a harder problem to determine that the :2 is not a parameter in the second query.

I'd like to get this working at least for ODBC, and saw a few ways to go about it. Any thoughts before I put in a PR would be greatly appreciated.

1. Add a SOCI_NO_NAMED_PARAMETERS or similar build-time flag to disable it globally.
2. Add an option like "no_named_parameters" to soci::connection_parameters that the odbc driver could look for.
3. Add an escape character to parsing. This feels complicated since each backend appears to parse differently.
4. Any other ideas?

[vadz]

I think we need to implement common parsing of parameters for all backends and handle escaping colons in it (either by backslashing them, which is a bit awkward in C++, as it means you'd have to use "\\:" in the source code, or maybe by doubling them). Unfortunately this would require a non-trivial amount of work that I wouldn't be able to contribute in the observable future.

[bgemmill]

Would you be open to option 1 in the short term?

Optionally turning named parameters off and throwing a soci::error if they're supplied in the use vector seems pretty straightforward.

[vadz]

Do people really use SOCI without query parameters? I just don't see how can it be useful to disable them completely...

[bgemmill]

While I can't speak for others, I tend to use https://fmt.dev for string formatting and generate queries that way.

Another thought is an option to pass in a string without the rewriting named parameters, something like:

std::string query; // set from elsewhere
std::string out;
soci::statement st = (conn.prepare_raw << query,
          soci::into(out)
          );

[vadz]

Err, fmt is great and all and I use it too, of course, but using it is the straightest route to SQL injection I can think of. You really shouldn't be doing things like this.

[bgemmill]

I'm definitely not advocating for SQL injection!

I'm wondering if there's a shorter-term answer to allowing colon literals in queries by turning off query rewriting of named parameters until the much larger work of a single parser comes about.

[vadz]

I've looked at the code and it's a mess :-(

• Firebird parses the query and ignores colons inside single-quoted SQL strings, but nothing else.
• MySQL does the same, but also handles := specially.
• DB/2 does the same, but recognizes :: specially.
• ODBC doesn't recognize neither := nor ::, but does recognize Access string literals starting with #.
• PostgreSQL recognizes both := and ::, but still not things like 1:2 in your example.
• SQLite and Oracle use sqlite3_prepare_v2() and OCIStmtPrepare() respectively, which probably means that they already handle everything correctly, but we'd still have to change the code of these backends if we implement any kind of escaping mechanism.

Also, Firebird, DB/2 and ODBC replace parameters with ?, while PostgreSQL replaces them with $. MySQL does something completely different and weird.

I am not sure what would be a reasonable way out, especially without silently changing the behaviour of the existing code (which would be pretty bad). I guess using :: for escaping the colon is out of question because it's already handled specially in some places, so the simplest acceptable change would be to handle \: (i.e., again, "\\:" in the source code) as a colon. This would mean modifying all the existing backends to handle it, including SQLite and Oracle that would need to remove the backslash.

Ideal would be to refactor the existing code first, but this would require even more work...

[Stifael]

I also ran into this issue. In my case, I need to support a legacy application, which already provides raw sql-statements, where colons are all over the place (for dates, keys etc). To me, it is not entirely obvious, why the prepare-function not just skips all the ':' unless there is a special order like ':=' and '::'?

[vadz]

Using :param is supposed to work in SOCI, so ignoring all columns is a non-starter.