Store Q()-filter terms in Database?

Using Django Using the ORM
1

Hi again and right another question I couldn’t find a proper answer so far:

Is it possible (regardless, wether it is recommended :wink: ) to store a Q-filter code in database and load it back as executable python code? I mean, of course it is possible to store code text in database, that is not the point, but if i try so, I get a Type error when trying to execute tha database part of code:

title = request.POST.get('fix_query')
db_entry = DBquery.objects.get(title=title)
filter1 &= db_entry.filter1 <--TypeError thrown

printing db_entry.filter1leads to
Q(milcoursedetails__mil_course=MilitaryCourses.objects.get(course='foo').pk), which is the right line of code that would work as intended, if putted directly in python and not loaded from database. The field type of the filter1 field in DBquery is a CharField.

2

You have a couple different ways to do this.

  • You can pickle an object and store it.

  • You can store the source code and use eval or exec to execute it.

  • You can create your own serialized data format and dynamically build the filters from it in your code.

Keep in mind that a function call in Python like:
some_function(parm_1=value_1, parm_2=value_2)

is functionally identical to:

parm_dict = {'parm_1': value_1, 'parm_2': value_2}
some_function(**parm_dict)

which means you can dynamically create a filter from your data.

We actually use the last two techniques in a couple projects.

1 Like
3

As always: Thx for this hint, I will try it :slight_smile:

edit: Happy Birthday :partying_face:

4

Feedback: Doing it with eval just worked fine ! :slight_smile:

5

Note: Be extremely wary with doing this in any area where user-entered data is involved. You could be exposing yourself to a code-injection vulnerability. There are good reasons why the docs say “There be dragons here!”

6

I´m quite aware of that i am opening a door that should kept close. But in this special case, if only 3 people have access to the django admin portal and if the code is definde as Q()-filters…what could happen in worst case ?

If i define something like filters = Q() und fill them with filters &= databse_value…a not valid Q-filter would only lead to an error, or not ?

Which case of code injection could be possible here ? (seriously!?)

7

Yes, seriously. For a trivial example intended to illustrate and not provide an actual proof of concept - if you’re eval-ing a string in the database, and that string contains something like:

User.objects.all().delete()
ContentType.objects.all().delete()
Permission.objects.all().delete()
...

buried within something like a subquery, then you’ve got some problems. And it gets worse than that. How about an exec that runs an rm -r -f / command from the bash shell?

8

but does this also work when the python line starts with filters &= ...some code from database ? A query with delete command after filters &= is flawlessly executed ? (filters is a reference of Q()) I have never tried but normally i get error messages for lesser strange syntax :wink:

9

so in my case it looks like this at the moment. i can not see what kind of code injection could severe damage beside of querying other things as intended:

filter1, filter2, filter3, filter4, filter5 = Q(), Q(), Q(), Q(), Q()
    db_entry = DBAbfragen.objects.get(title=var)
    filter1 &= eval(db_entry.filter1)
    filter2 &= eval(db_entry.filter2)
    filter3 &= eval(db_entry.filter3)
    filter4 &= eval(db_entry.filter4)
    filter5 &= eval(db_entry.filter5)

    fix_query_set = Employees.objects.filter(filter1).filter(filter2).filter(filter3). \
        filter(filter4).filter(filter5).order_by('last_name')

    return fix_query_set
10

Something along the lines of Q(id = Subquery(User.objects.all().delete())) might do it.

11

interesting, didn`t know yet that one can wrap a powerful command like this in that way…

12

It’s a query, just like any other query.

It gets potentially scarier from there. Rather than just deleting data, the person could issue an update query to set their user is_supervisor flag to True, or alter other data.

Again, there are reasons why it’s so strongly recommended against using any form of eval with user-submitted (untrusted) data.

13

But in my case it’s not user submitted but picked up from database from a table only admins can enter stuff…so no user could send something that affects this lines of code through the front-end! I’m maybe a bit crazy but not insane :wink:

14

Understood. I’m just making sure that you’re aware that doing this creates an additional set of risks to your system’s security. It’s up to you to evaluate that risk to determine what your response to those risks are going to be.

15

I really thank you for caring about…but as you may have read between the lines in my original post, I was well aware, that I can open here a door to every shark around. After all, it is still work in progress, i don`t know where the code will be in the end :wink: But thx anyway for your engagement in this! You helped me out several times now in simply no time…would you mind to get cited with your account name here in the “thanks” part of my master thesis ?

1 Like
16

A kind thought and I don’t mind, but it’s not really necessary. We’re here to help.

1 Like
17

Of course it is not necessary, but adorn oneself with borrowed plumes is not my style and you helped me out several times with your expertise and knowledge…it is just fair :slight_smile: