[Django] #32158: SQLite3 DatabaseWrapper.check_constraints does not properly quote table names for PRAGMA statements causing a SQL statement failure
Django
names for PRAGMA statements causing a SQL statement failure
-------------------------------------+-------------------------------------
Reporter: Scott | Owner: nobody
Herriman |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) | Keywords: loaddata SqlLite3
Severity: Normal | check_constraints
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
**Steps to reproduce:**
* Create a Model called {{{Order}}}. ({{{order}}} is a SQL reserved word)
* Create fixtures for the model
* Use manage.py loaddata to load the fixture.
* **Notice that it fails with the following error**. This is because the
table name {{{order}}} is not quoted properly
{{{
(0.000) PRAGMA foreign_key_check(order); args=None
Traceback (most recent call last):
File "python3.7/site-packages/django/db/backends/utils.py", line 82, in
_execute
return self.cursor.execute(sql)
File "python3.7/site-packages/django/db/backends/sqlite3/base.py", line
411, in execute
return Database.Cursor.execute(self, query)
sqlite3.OperationalError: near "order": syntax error
}}}
**Root Cause**
* File: {{{python3.7/site-packages/django/db/backends/sqlite3/base.py}}}
line 327
* Function: {{{check_constraints}}}
* Details: due to missing back ticks around {{{%s}}} in the SQL statement
{{{PRAGMA foreign_key_check(%s)}}}
Here in check_constraints line 327 in context
{{{
if table_names is None:
violations = cursor.execute('PRAGMA
foreign_key_check').fetchall()
else:
violations = chain.from_iterable(
cursor.execute('PRAGMA foreign_key_check(%s)' %
table_name).fetchall()
for table_name in table_names
)
}}}
And here line 333
{{{
for table_name, rowid, referenced_table_name,
foreign_key_index in violations:
foreign_key = cursor.execute(
'PRAGMA foreign_key_list(%s)' % table_name
).fetchall()[foreign_key_index]
}}}
Issue confirmed in
* 3.1.0
* 3.1.2
--
Ticket URL: <https://code.djangoproject.com/ticket/32158>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
names for PRAGMA statements causing a SQL statement failure
-------------------------------------+-------------------------------------
| Sagar
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage:
check_constraints | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Nishant Sagar
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Nishant
| Sagar
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
check_constraints |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
Thanks for the report, I was able to reproduce this issue with `db_table =
'order'`.
Reproduced at 966b5b49b6521483f1c90b4499c4c80e80136de3.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Nishant
| Sagar
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Simply wrapping `table_name` in `connection.ops.quote_name` should address
the issue for anyone interested in picking the issue up.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:3>
Django
-------------------------------------+-------------------------------------
| sharma
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: Nishant Sagar => Nayan sharma
Comment:
a little guidance needed as this is my first ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Nayan
| sharma
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Nayan sharma):
will the issue be fixed if i just wrap %s around `%s ` as in 'PRAGMA
foreign_key_check(`%s`)' and 'PRAGMA foreign_key_list(`%s`)' %
table_name?
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Nayan
| sharma
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Nayan, Have you seen
[https://code.djangoproject.com/ticket/32158#comment:3 Simon's comment]?
We should wrap with `quote_name()`.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Nayan
| sharma
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Nayan sharma):
"Details: due to missing back ticks around %s in the SQL statement PRAGMA
foreign_key_check(%s)"
But it is quoted in the issue that this should fix the issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Nayan
| sharma
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Nayan sharma):
shall i wrap "table_name" in "quote_name" as in "quote_name(table_name)"?
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:8>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: Nayan sharma => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: (none)
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
> shall i wrap "table_name" in "quote_name" as in
"quote_name(table_name)"?
yes, `self.ops.quote_name(table_name)`
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:10>
Django
-------------------------------------+-------------------------------------
| Bezerra
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: (none) => George Bezerra
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: George
| Bezerra
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by George Bezerra):
First contribution, currently trying to understand the code to figure out
how to write a regression test for this.
Any help in how to unit test this is appreciated.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: George
| Bezerra
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by George Bezerra):
I believe I got it. Will put my test in `tests/fixtures_regress` which has
a lot of regression tests for `loaddata` already, creating a new `Order`
model and a fixture for it.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: George
| Bezerra
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: George
| Bezerra
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
Comment:
Suggested test improvements to avoid the creation of another model.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: George
| Bezerra
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by George Bezerra):
Things have been busy for me but I'm still on it, I'll do the changes to
the patch later this week.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: George
| Bezerra
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Chinmoy):
Since this issue hasn't received any activity recently, may I assign it to
myself?
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:17>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: George
| Bezerra
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Sure, feel-free.
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:18>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: George Bezerra => Chinmoy
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:19>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Chinmoy
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Chinmoy):
Patch awaiting review. PR: [https://github.com/django/django/pull/13807]
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:20>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Chinmoy
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:21>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Chinmoy
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:22>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Chinmoy
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
check_constraints | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:23>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Chinmoy
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: loaddata SqlLite3 | Triage Stage: Ready for
check_constraints | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"270072c4c231acd72a03534357a4aa59010e76ff" 270072c]:
{{{
#!CommitTicketReference repository=""
revision="270072c4c231acd72a03534357a4aa59010e76ff"
Fixed #32158 -- Fixed loaddata crash on SQLite when table/column names are
SQL keywords.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:24>