CODEWITHSUNDEEP
pythonsqldjangodjango-modelsdjango-views
HM23MD
HM23MD

Reputation: 105

Python Django raw sql iteration leads to TypeError

I am having some problems with processing my form in python django when using raw sql querying. I used https://docs.djangoproject.com/en/dev/topics/db/sql/ for reference. I am getting errors when trying to iterate over the RawQuerySet that is returned from the raw sql query. Any help would be appreciated. Here is part of my view.

class SearchForm(forms.Form):
     pr_name = forms.CharField(label="Pr Name", max_length=64, required=False)
     org = forms.ModelChoiceField(queryset=Org.objects.all(), required=False)
     group_name = forms.CharField(label="Unique Submission Name", max_length=64, required=False)
     group_ref = forms.CharField(label="Ref", max_length=12, required=False)
     group_url = forms.URLField(label="URL", required=False)

def search(request):
    if request.method == 'POST':
        form = SearchForm(request.POST)
        if form.is_valid():
            p_ids = []
            g_ids = []
            f_ids = []

        logging.debug('hello1')
        # Filter first
        firstQuery = 'SELECT * FROM pr where '
        pr_name = form.cleaned_data['pr_name']
        if pr_name:
            logging.debug('hello2')
            firstQuery += '(name like \'%' + pr_name + '%\')'
        else:
            pass

        logging.debug('hello3')
        org = form.cleaned_data['org']
        if org:
            org = Org.objects.get(name = org)
            org_id = org.id
            firstQuery += '(org_id = ' + str(org_id) + ')'
        else:
            pass

        firstQuery = firstQuery.replace(')(', ') AND (')
        #logging.debug('First query: %s' % firstQuery)
        p_search_results = P.objects.raw(firstQuery)
        logging.debug('First query: %s' % p_search_results)
        for x in p_search_results:
            p_ids.append(x.id)
        logging.debug('p_ids: %s' % p_ids)


        # Filter Group
        secondQuery = 'SELECT * FROM group where '
                group_name = form.cleaned_data['group_name']
        if group_name:
            secondQuery += '(name like \'%' + group_name + '%\')'
        else:
            pass

        group_ref = form.cleaned_data['group_ref']
        if group_ref:
            secondQuery += '(ref like \'%' + group_ref + '%\')'
        else:
            pass

        group_url = form.cleaned_data['group_url']
        if group_url:
            secondQuery += '(method_url like \'%' + group_url + '%\')'
        else:
            pass

        secondQuery = secondQuery.replace(')(', ') AND (')
        logging.debug('Second query: %s' % secondQuery)
        group_search_results = PredictionGroup.objects.raw(secondQuery)
        logging.debug('Second query: %s' % group_search_results)
        for x in group_search_results:
            g_ids.append(x.id)
        logging.debug('g_ids: %s' % g_ids)

...
...
...

And the error is:

TypeError at /search/
not enough arguments for format string at:
for x in p_search_results:

Upvotes: 0

Views: 620

Answers (2)

Leopd
Leopd

Reputation: 42717

The error not enough arguments for format string comes from a line like this:

"Something %s and %s" % x

Your string has two %s which need to be substituted, but you only have one variable after the % operator.

I can't help wonder if something's wrong with your stack trace, because that error comes from using the % operator incorrectly. A for loop wouldn't trigger that.

Upvotes: 0

Daniel Roseman
Daniel Roseman

Reputation: 599480

Never, ever, ever, ever, ever build up SQL parameters like that. In fact, that's so important, I'm going to say it again: never, ever, ever, ever, ever, ever, build up SQL parameters like that. You've left yourself wide open to an SQL injection attack: what happens if someone submits "foo'; DELETE FROM pr;" into your pr_name field? That's right, the database will faithfully execute two commands and delete your pr table.

Django normally protects you from that, by properly escaping all input to SQL commands. For some reason, you've chosen to bypass the ORM: now, sometimes you need to, in order to formulate complex queries, but there's nothing complex in the queries you show above.

You should be doing something like this:

group_query = Group.objects.all()
group_name = form.cleaned_data['group_name']
if group_name:
    group_query = group_query.filter(name__icontains=group_name)
org = form.cleaned_data['org']
if org:
    group_query = group_query.filter(org__name=org)

and so on.

Upvotes: 2

Related Questions