Summary / TLDR :

How do I safely (without room for sql injections) convert sql placeholders? like make SELECT * FROM $1 go from SELECT * FROM ? (regex substitution works with pattern \$\d{1,} but it has it's very obvious flaws like capturing literal values enclosed with quotes) vice versa is helpful as well to go from SELECT * FROM ? to SELECT * FROM $1

What I am trying to do :

I have a slow IO-bound postgres connection which is slow and wanted a caching system, I wanted this cache to accept sql and give the output, so I what I decided is to clone the entire remote database and cast it into a local disk bount sqlite3 space which acts as an "cache" everytime my application starts up (I anticipated that this'll be faster than redis because having something locally will more often than not be faster)

I am using asyncpg to interact with the remote database, and this module accepts $n placeholder (where n denotes the number of placeholders starting at 1)

and using sqlite3 which uses ? as placeholder for values and wanted to use a single query and convert between these two.

As aforementioned, I tried using regex and string templates but they weren't safe enough, is there a way of doing this either built into asyncpg or sqlite3 itself?

CodePudding user response：

The documentation for SQLite on parameters mentions two salient points:

Named parameters are also numbered

...

A dollar-sign followed by an identifier name also holds a spot for a named parameter with the name $AAAA

In other words, you can use a query like SELECT * FROM foo WHERE bar = $1 from PostgreSQL in SQLite or vice versa.

This isn't to say all possible queries are interchangeable, they certainly aren't, but in this one case, you can limit your queries to use the $1 style syntax for parameters and pass them along unmodified.