[Django] #32158: SQLite3 DatabaseWrapper.check_constraints does not properly quote table names for PRAGMA statements causing a SQL statement failure

12 views
Skip to first unread message

Django

unread,
Oct 30, 2020, 3:04:13 PM10/30/20
to django-...@googlegroups.com
#32158: SQLite3 DatabaseWrapper.check_constraints does not properly quote table
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

unread,
Oct 31, 2020, 11:48:23 AM10/31/20
to django-...@googlegroups.com
#32158: SQLite3 DatabaseWrapper.check_constraints does not properly quote table
names for PRAGMA statements causing a SQL statement failure
-------------------------------------+-------------------------------------
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:
check_constraints | Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Nishant Sagar):

* owner: nobody => Nishant Sagar
* status: new => assigned


--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:1>

Django

unread,
Oct 31, 2020, 3:36:46 PM10/31/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.

-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* 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

unread,
Nov 4, 2020, 6:51:01 PM11/4/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

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

unread,
Nov 5, 2020, 1:49:43 AM11/5/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Nayan sharma):

* 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

unread,
Nov 5, 2020, 2:02:28 AM11/5/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

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

unread,
Nov 5, 2020, 2:21:38 AM11/5/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

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

unread,
Nov 5, 2020, 3:20:15 AM11/5/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

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

unread,
Nov 6, 2020, 12:04:54 AM11/6/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

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

unread,
Nov 6, 2020, 1:17:40 AM11/6/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: (none)
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Nayan sharma):

* owner: Nayan sharma => (none)
* status: assigned => new


--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:9>

Django

unread,
Nov 6, 2020, 1:51:08 PM11/6/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: (none)
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: loaddata SqlLite3 | Triage Stage: Accepted
check_constraints |
Has patch: 0 | Needs documentation: 0

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

unread,
Nov 7, 2020, 1:47:07 AM11/7/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by George Bezerra):

* owner: (none) => George Bezerra


* status: new => assigned


--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:11>

Django

unread,
Nov 7, 2020, 2:56:24 AM11/7/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

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

unread,
Nov 7, 2020, 6:41:13 PM11/7/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 0 | Needs documentation: 0

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

unread,
Nov 7, 2020, 9:29:08 PM11/7/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by George Bezerra):

* has_patch: 0 => 1


--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:14>

Django

unread,
Nov 8, 2020, 8:47:18 AM11/8/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* 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

unread,
Nov 11, 2020, 7:45:27 PM11/11/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0
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

unread,
Dec 23, 2020, 4:35:41 AM12/23/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0
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

unread,
Dec 23, 2020, 4:50:01 AM12/23/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0
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

unread,
Dec 23, 2020, 5:14:38 AM12/23/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Chinmoy):

* owner: George Bezerra => Chinmoy


--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:19>

Django

unread,
Dec 23, 2020, 4:14:58 PM12/23/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0
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

unread,
Dec 23, 2020, 4:21:13 PM12/23/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* needs_better_patch: 1 => 0


--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:21>

Django

unread,
Dec 26, 2020, 10:38:26 AM12/26/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* needs_better_patch: 0 => 1


--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:22>

Django

unread,
Dec 28, 2020, 2:59:55 AM12/28/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
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: Ready for
check_constraints | checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin


--
Ticket URL: <https://code.djangoproject.com/ticket/32158#comment:23>

Django

unread,
Dec 28, 2020, 3:19:17 AM12/28/20
to django-...@googlegroups.com
#32158: loaddata crashes on SQLite when table names are SQL keywords.
-------------------------------------+-------------------------------------
Reporter: Scott Herriman | Owner: Chinmoy
Type: Bug | Status: closed

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: fixed

Keywords: loaddata SqlLite3 | Triage Stage: Ready for
check_constraints | checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* 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>

Reply all
Reply to author
Forward
0 new messages