SQLite Forum

Feature request: implement .parameter bind KEY VALUE in sqlite3 CLI (shell)
Login

Feature request: implement .parameter bind KEY VALUE in sqlite3 CLI (shell)

(1) By AlexJ (CompuRoot) on 2022-01-13 18:23:01 [source]

It would be very useful for those who using sqlite3 CLI program as a mechanism/frontend/TUI to access databases.

As of now users of sqlite3 CLI/shell should take care about supplied data from external resources and be responsible for data sanitation to prevent SQL injection.

Current .dot command ".parameter set" is not true key/value binding feature that can be used as a mechanism to prevent SQL injection, that's why I suggesting to implement new .dot command.

Proposal is to implement new .dot command .parameter bind KEY VALUE , where VALUE can be anything, including un-escaped quotes, so it can be safely used later in prepared statements.

To avoid "escaping hell" with supplied VALUE, I suggest to accept VALUE as raw bytes content on the right side of command, after first space that follows KEY and up to the end of line or up to first 0x00 byte.

If it going to be implemented, it will be also very useful, if VALUE in the proposed .dot command will support .read command, that can be distinct from proposed one as:

.parameter bindfile KEY FILE

or

.parameter bindblob KEY FILE

by utilizing existing code of .read command.

(2) By Larry Brasfield (larrybr) on 2022-01-13 22:07:56 in reply to 1 [link] [source]

Before we get to nuts and bolts here, please understand that I'm looking for insight here and hoping to get your request reduced to its vital essence.

It would be very useful for those who using sqlite3 CLI program as a mechanism/frontend/TUI to access databases.

This is how the dev team sees the sqlite3 shell also, with limitations that may be relaxed over time.

As of now users of sqlite3 CLI/shell should take care about supplied data from external resources and be responsible for data sanitation to prevent SQL injection.

I find this difficult to understand. (I understand SQL injection, just not the hazard in this context.)

If I am able to type at or prepare input for the shell, I need not bother with any tricky "SQL injection" because I can simply write the SQL I want executed and cause it to be run. Hence, I need to understand an actual hazard scenario that is alleviated by your suggestion.

Current .dot command ".parameter set" is not true key/value binding feature that can be used as a mechanism to prevent SQL injection, that's why I suggesting to implement new .dot command.

Before getting to how to avoid SQL injection, let's get to why.

Proposal is to implement new .dot command .parameter bind KEY VALUE , where VALUE can be anything, including un-escaped quotes, so it can be safely used later in prepared statements.

For purposes of this discussion, until that why is understood, I am translating "safely" to "conveniently".

To avoid "escaping hell" with supplied VALUE, I suggest to accept VALUE as raw bytes content on the right side of command, after first space that follows KEY and up to the end of line or up to first 0x00 byte.

I'm translating "avoid 'escaping hell'" to "make convenient". (I have no personal hope of avoiding "escaping Perdition". It goes with the territory.)

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.

If it going to be implemented, it will be also very useful, if VALUE in the proposed .dot command will support .read command, that can be distinct from proposed one as:
.parameter bindfile KEY FILE
or
.parameter bindblob KEY FILE
by utilizing existing code of .read command.

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?

(3) By AlexJ (CompuRoot) on 2022-01-14 14:06:58 in reply to 2 [link] [source]

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'")

#!/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

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.