Allow Manager.raw() execute not only "Pure selects"

[dzentoo]

Since the raw query validation only matches if the request startswith("select") I would like to execute queries like "(SELECT) UNION (SELECT)".

[dzentoo]

attached patch

[Russell Keith-Magee]

Like I said in IRC - the use cases for raw() are fairly strictly limited to SELECT clauses; I'd rather see the validation logic improved to catch the edge cases, rather than just dropping validation.

[dzentoo]

would a regexp suffice ?

[Russell Keith-Magee]

Yeah - a regex should do the job.

[dzentoo]

Can raw() execute this kind of queries ?

​http://www.davidcramer.net/code/django/6939/scaling-threaded-comments-on-django-at-disqus.html

[Russell Keith-Magee]

Right now? No. Could it? Sure. You just need to make the validation sufficiently flexible.

[kemar]

Why are raw queries strictly limited to SELECT queries? Is this a security measure?
Seems like determining whether a query is a SELECT or not require a complex regex.

[Russell Keith-Magee]

It's not a security measure -- it's a matter of basic functionality. The rest of the behavior of raw() deals with converting the returned results into Django objects. The query validation is purely about ensuring that the query will actually return results that can be converted into Django objects.

[dzentoo]

Well, I tried "sqlparse" without success ..

res = sqlparse.parse("(SELECT * FROM pouet LIMIT 5) UNION (SELECT * FROM repouet)")
st = res[0]
print st.get_type()
'UNKNOWN'

What If I match if the query does not contain UPDATE / INSERT / DELETE ?
what do you mean by 'flexible validation' ? Any idea on how you 'll proceed ?

[Chris Beaven]

​http://thebuild.com/blog/2010/12/13/very-large-result-sets-in-django-using-postgresql/ also has a use case for wanting to use FETCH 1000 FROM gigantic_cursor with raw().

Really, it feels like we're doing too much validation here - raw is supposed to be for the edge cases and we're artificially limiting how it can be used.

[Christophe Pettus]

I think that the validation is trying too hard to make this foolproof. It's not practical to integrate into Django a validation across all possible backends to catch all and only set-returning statements that can be passed in. That's not even in a static set of operations (for example, right now, the DO statement in PostgreSQL can't return sets, but there are proposals to allow it to do so).

[Ramiro Morales]

#15209 was a duplicate.

[Ramiro Morales]

See also #14898, #13678 was a duplicate.

[Christophe Pettus]

Per discussion on django-developers, a patch to remove the restrictions and update the documentation

[Jacob]

In [15803]:

Fixed #14733: no longer "validate" .raw() queries.

Turns out that a lot more than just SELECT can return data, and this list is
very hard to define up front in a cross-database manner. So let's just assume
that anyone using raw() is at least halfway competant and can deal with
the error messages if they don't use a data-returning query.

Thanks to Christophe Pettus for the patch.