> Hence, I need to understand an actual hazard scenario that is alleviated by your suggestion.
Simplified use case workflow:
1. A system receiving/pulling data automatically (runs via cron or run on event) from external resources where you have no control on that data and system passing this data as arguments to your program.
2. You have to store data that neither standardized nor well structured, but it can be a good one as JSON.
3. You have to follow rule: do not trust user supplied data.
4. A data must be kept as original content, non modified.
5. You aren't allowed to parse incoming data to figure out what type of content is before supplying this data to database.
6. you are restricted on creation of temporary files (on OS level allowed read/write permission to particular sqlite database only).
7. you have already created database for storing data (so no "CREATE TABLE" queries needed.)
8. you have to use standard sqlite3 executable CLI from trustful resources only.
9. Implementation must be done in plain **`/bin/sh`** or **`/bin/dash`** (no bash, zsh, python... due to their ability to talk over network)
Direct substitution of data in queries isn't solution due to #3 and existing **`.parameter`** feature is the same as simply substitute data with shell's variables.
Use of temporary files where one can temporarily save data and then read back with readfile() directly into temp.sqlite_parameters is restricted by #6
The best tool to complete this task IMHO is "prepared SQL statements" that would help to create clean, secure and understandable code, but to be able to achieve it, sqlite3 should use real binding of KEY/VALUE.
Proposed in another thread solution to use double quoting won't work due to #4 and #5:
Example #1 (guard data as "'data'")
<strong>
```
#!/bin/sh -x
data1='{"key":"value", "key2","val2"}'
data2='+1+2+3'
qb="\"'"; qe="'\""
echo ".param init
.param set @data1 ${qb}${data1}${qe}
.param set @data2 ${qb}${data2}${qe}
.param list
" | sqlite3
```
</strong>
This will prevent evaluation of $data2 but sqlite3 will fail on $data1 (JSON with internal " double quotes)
Using just a single quotes instead of double quoting will keep JSON, but then it will evaluate $data2 and insted of +1+2+3 it became 6 that break rule #4
Besides of that, incoming data may have a string like: **+1-123-456-7890 Bob's phone** that will cause error in case of using single only single guarding quotes.
I might choose different style of quoting dynamically before supplying data to sqlite3 but rule #5 restricts you to analyze data, and again, incoming data might contain both, double and single quotes that will confuse sqlite3 parser.
> I understand SQL injection, just not the hazard in this context.
If data for example would be walled with **"'xxx'"** stanza, then supplying string **xx"xx** as a data - will cause parser error in sqlite3. (I can not escape raw data, it must be saved in database as is)
> Your proposal means breaking the interface between the shell's parser and the meta-command implementations. That is a very big step, and overkill for the problem at hand, in my (present) opinion.
>An alternative would be to join, with spaces, all arguments past the KEY. If somebody wants more spaces, they can endure some quoting work.
The case I described isn't involves humans and fully automated, so everything have to be done using scripts and due to #4 and #5 I cant escape internal data spaces. If we would going to join **azArg[2] - azArg[n]** in sqlite3 CLI parser then we might loose spaces that might be there in data on purpose.
> I believe that the existing ".param set KEY VALUE" can be made to set a blob value in the temp.sqlite_parameters by using the readfile() function. How does this proposal differ in effect from doing that?
Manipulating directly on **temp.sqlite_parameters** looks to me more as a hack than solutions when there is dedicated for this dot command that doing parameter assignment. Besides of that I can't save data to a temporary file and read it back with readfile() due to restriction on OS level in rule #6.
I wish to have a choice to simply write own program and use sqlite3_bind_XXX commands or simply flip **`%s`** into **`%Q`** at least in `sqlite3.c`, but it will break rule#8.
Anyway, having true binding of KEY/VALUE is a good feature for any other users who processing data using sqlite3 CLI I think.