`bind_params` not working with text regular expressions

[Ivo-Donchev]

• InfluxDB version: 1.7.7
• InfluxDB-python version: 5.2.3
• Python version: 3.7.2
• Operating system version: Ubuntu 18.04

Hi, I'm iterating over my application to add the new version's bind_params parameter everywhere. But I have the following query:

Query:
SELECT COUNT(event_id)
    FROM measurement
    WHERE some_param=~/(?i)SomeValue*/

But when I try to use the bind_params:

Query:
SELECT COUNT(event_id)
    FROM measurement
    WHERE some_param=~/(?i)$value*/
----------------------------------------------------------
Bind params:
{
    'value': 'SomeValue'
}

And the new query with the bind params returns an empty result (which is wrong). I tried a few more "variations" - regex part inside the param value, part of it inside, part of it outside - also not successfull :x:

I cannot find anything in the documentation about how are the bind_parameters used for text search parameters (regexes)

Can you help me ?

Thanks, Ivo

[russorat]

@Ivo-Donchev thanks for the issue. This might be better suited for the https://community.influxdata.com or the community slack https://influxdata.com/slack channel.

[Ivo-Donchev]

@russorat I just linked the issue here - https://influxcommunity.slack.com/archives/CH6GVB9NC/p1574594178211700 :slightly_smiling_face:

[rickspencer3]

Looking through the Python code, so far as I can tell, the value is sent to the database through an API through a REST API and the logic for reconstituting the query with the parameters lies in there, not in the Python code.

I suspect that since the purpose of bind_params is to stop execution of statements that a malicious user slips in, the fact that this query does not work in this way might be by design.

I could EASILY be wrong though. That was what I could clean from searching the code.

[Ivo-Donchev]

@rickspencer3 Hmm, but the regexes are mainly use for search. Then how would you protect your query from SQL injection when using regex :thinking: I mean something like that:

SELECT COUNT(event_id)
    FROM measurement
    WHERE some_param=~/(?i){my_input}*/

my_input = 'some_input*/; DROP MEASUREMENT ....'

I basically tried to protect the user by getting a control over the database. But I'm not sure how to that for this particular case :thinking:

[ozichert]

To bypass this issue you can concatenate your strings for the parameter(s) within the selection part. client.query('SELECT ' + bind_params["variable"] + ' FROM db WHERE time >= $from', bind_params={"from": datetime.datetime(2019,12,1).isoformat()+"Z", "variable": "Value"}) Nevertheless the security risk stays with this option.

[Ivo-Donchev]

@ozichert I'm basically doing this now :slightly_smiling_face: (+ some additional validations if there's another query injected inside).

But I think the main problem here is related to the ability to protect the database. Imagine you have a search filter in a web page that's searching in influx database. But some user enters following in the search box: 'some_input*/; DROP MEASUREMENT ....; UPDATE ....

Concatenation is an easy one to construct a valid query but it's not a good option for raw input. :slightly_frowning_face:

[yijia2413]

bind_params does not work with or condition regex, but without bind_params works fine.

SQL: 
select sid, type from xxx where sid=~$sid and type!=2

params:
{'sid':'/68b096b60469a1|65740faeda0f6/'}

error_log:
400: {"error":"error parsing query: found /68b096b60 ... , expected regex at line 1, char 43"}