From 680b512fc1509ca05cf56fada30f6b833735362a Mon Sep 17 00:00:00 2001 From: Gilberto Gonçalves Date: Sat, 22 Jun 2013 14:05:12 +0100 Subject: Fixed #20587 -- Made convert_values handle None values --- django/db/backends/sqlite3/base.py | 3 +++ 1 file changed, 3 insertions(+) (limited to 'django/db/backends/sqlite3') diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py index a2d925ac3f..18c4029649 100644 --- a/django/db/backends/sqlite3/base.py +++ b/django/db/backends/sqlite3/base.py @@ -250,6 +250,9 @@ class DatabaseOperations(BaseDatabaseOperations): and gets dates and datetimes wrong. For consistency with other backends, coerce when required. """ + if value is None: + return None + internal_type = field.get_internal_type() if internal_type == 'DecimalField': return util.typecast_decimal(field.format_number(value)) -- cgit v1.3 From 273dc550a4eccdad958541f456332312b3369504 Mon Sep 17 00:00:00 2001 From: Andrew Clark Date: Wed, 26 Jun 2013 01:07:18 -0700 Subject: Fixed #20462 -- null/non-string regex lookups are now consistent Thanks to noirbizarre for the report and initial patch. --- AUTHORS | 2 ++ django/db/backends/postgresql_psycopg2/operations.py | 2 +- django/db/backends/sqlite3/base.py | 2 +- tests/lookup/tests.py | 15 +++++++++++++++ 4 files changed, 19 insertions(+), 2 deletions(-) (limited to 'django/db/backends/sqlite3') diff --git a/AUTHORS b/AUTHORS index 5fcdf833f0..ba1f4036e9 100644 --- a/AUTHORS +++ b/AUTHORS @@ -152,6 +152,7 @@ answer newbie questions, and generally made Django that much better: Antonis Christofides Michal Chruszcz Can Burak Çilingir + Andrew Clark Ian Clelland Travis Cline Russell Cloran @@ -273,6 +274,7 @@ answer newbie questions, and generally made Django that much better: Brian Harring Brant Harris Ronny Haryanto + Axel Haustant Hawkeye Kent Hauser Joe Heck diff --git a/django/db/backends/postgresql_psycopg2/operations.py b/django/db/backends/postgresql_psycopg2/operations.py index f96757da8b..c5aab84693 100644 --- a/django/db/backends/postgresql_psycopg2/operations.py +++ b/django/db/backends/postgresql_psycopg2/operations.py @@ -69,7 +69,7 @@ class DatabaseOperations(BaseDatabaseOperations): # Cast text lookups to text to allow things like filter(x__contains=4) if lookup_type in ('iexact', 'contains', 'icontains', 'startswith', - 'istartswith', 'endswith', 'iendswith'): + 'istartswith', 'endswith', 'iendswith', 'regex', 'iregex'): lookup = "%s::text" # Use UPPER(x) for case-insensitive lookups; it's faster. diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py index 18c4029649..b9caf8b99f 100644 --- a/django/db/backends/sqlite3/base.py +++ b/django/db/backends/sqlite3/base.py @@ -522,4 +522,4 @@ def _sqlite_format_dtdelta(dt, conn, days, secs, usecs): return str(dt) def _sqlite_regexp(re_pattern, re_string): - return bool(re.search(re_pattern, re_string)) + return bool(re.search(re_pattern, str(re_string))) if re_string is not None else False diff --git a/tests/lookup/tests.py b/tests/lookup/tests.py index de7105f92d..fe8a5fac64 100644 --- a/tests/lookup/tests.py +++ b/tests/lookup/tests.py @@ -610,6 +610,21 @@ class LookupTests(TestCase): self.assertQuerysetEqual(Article.objects.filter(headline__regex=r'b(.).*b\1'), ['', '', '']) + def test_regex_null(self): + """ + Ensure that a regex lookup does not fail on null/None values + """ + Season.objects.create(year=2012, gt=None) + self.assertQuerysetEqual(Season.objects.filter(gt__regex=r'^$'), []) + + def test_regex_non_string(self): + """ + Ensure that a regex lookup does not fail on non-string fields + """ + Season.objects.create(year=2013, gt=444) + self.assertQuerysetEqual(Season.objects.filter(gt__regex=r'^444$'), + ['']) + def test_nonfield_lookups(self): """ Ensure that a lookup query containing non-fields raises the proper -- cgit v1.3 From a9ea7d8c708c8265cccc17f23c62b2268d9e94f8 Mon Sep 17 00:00:00 2001 From: Loic Bistuer Date: Wed, 26 Jun 2013 21:30:58 +0700 Subject: Fixed #20462 - Replaced the str() cast introduced in 273dc55 by force_text() --- django/db/backends/sqlite3/base.py | 3 ++- tests/lookup/tests.py | 7 +++++++ 2 files changed, 9 insertions(+), 1 deletion(-) (limited to 'django/db/backends/sqlite3') diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py index b9caf8b99f..324adfd97b 100644 --- a/django/db/backends/sqlite3/base.py +++ b/django/db/backends/sqlite3/base.py @@ -19,6 +19,7 @@ from django.db.backends.sqlite3.introspection import DatabaseIntrospection from django.db.models import fields from django.db.models.sql import aggregates from django.utils.dateparse import parse_date, parse_datetime, parse_time +from django.utils.encoding import force_text from django.utils.functional import cached_property from django.utils.safestring import SafeBytes from django.utils import six @@ -522,4 +523,4 @@ def _sqlite_format_dtdelta(dt, conn, days, secs, usecs): return str(dt) def _sqlite_regexp(re_pattern, re_string): - return bool(re.search(re_pattern, str(re_string))) if re_string is not None else False + return bool(re.search(re_pattern, force_text(re_string))) if re_string is not None else False diff --git a/tests/lookup/tests.py b/tests/lookup/tests.py index fe8a5fac64..ee9c5afe1d 100644 --- a/tests/lookup/tests.py +++ b/tests/lookup/tests.py @@ -625,6 +625,13 @@ class LookupTests(TestCase): self.assertQuerysetEqual(Season.objects.filter(gt__regex=r'^444$'), ['']) + def test_regex_non_ascii(self): + """ + Ensure that a regex lookup does not trip on non-ascii characters. + """ + Player.objects.create(name='\u2660') + Player.objects.get(name__regex='\u2660') + def test_nonfield_lookups(self): """ Ensure that a lookup query containing non-fields raises the proper -- cgit v1.3 From d097417025e71286ad5bbde6e0a79caacabbbd64 Mon Sep 17 00:00:00 2001 From: Shai Berger Date: Fri, 28 Jun 2013 06:15:03 +0300 Subject: Support 'pyformat' style parameters in raw queries, Refs #10070 Add support for Oracle, fix an issue with the repr of RawQuerySet, add tests and documentations. Also added a 'supports_paramstyle_pyformat' database feature, True by default, False for SQLite. Thanks Donald Stufft for review of documentation. --- django/db/backends/__init__.py | 5 +++ django/db/backends/oracle/base.py | 66 ++++++++++++++++++++++++-------------- django/db/backends/sqlite3/base.py | 1 + django/db/models/query.py | 5 ++- docs/ref/databases.txt | 8 +++++ docs/releases/1.6.txt | 6 ++++ docs/topics/db/sql.txt | 25 ++++++++++++--- tests/backends/tests.py | 44 +++++++++++++++++++++++-- tests/raw_query/tests.py | 21 ++++++++++-- 9 files changed, 147 insertions(+), 34 deletions(-) (limited to 'django/db/backends/sqlite3') diff --git a/django/db/backends/__init__.py b/django/db/backends/__init__.py index 1a74232704..9abb9a9637 100644 --- a/django/db/backends/__init__.py +++ b/django/db/backends/__init__.py @@ -613,6 +613,11 @@ class BaseDatabaseFeatures(object): # when autocommit is disabled? http://bugs.python.org/issue8145#msg109965 autocommits_when_autocommit_is_off = False + # Does the backend support 'pyformat' style ("... %(name)s ...", {'name': value}) + # parameter passing? Note this can be provided by the backend even if not + # supported by the Python driver + supports_paramstyle_pyformat = True + def __init__(self, connection): self.connection = connection diff --git a/django/db/backends/oracle/base.py b/django/db/backends/oracle/base.py index 3f39a15aa7..5e2b763f52 100644 --- a/django/db/backends/oracle/base.py +++ b/django/db/backends/oracle/base.py @@ -757,20 +757,37 @@ class FormatStylePlaceholderCursor(object): self.cursor.arraysize = 100 def _format_params(self, params): - return tuple([OracleParam(p, self, True) for p in params]) + try: + return dict((k,OracleParam(v, self, True)) for k,v in params.items()) + except AttributeError: + return tuple([OracleParam(p, self, True) for p in params]) def _guess_input_sizes(self, params_list): - sizes = [None] * len(params_list[0]) - for params in params_list: - for i, value in enumerate(params): - if value.input_size: - sizes[i] = value.input_size - self.setinputsizes(*sizes) + # Try dict handling; if that fails, treat as sequence + if hasattr(params_list[0], 'keys'): + sizes = {} + for params in params_list: + for k, value in params.items(): + if value.input_size: + sizes[k] = value.input_size + self.setinputsizes(**sizes) + else: + # It's not a list of dicts; it's a list of sequences + sizes = [None] * len(params_list[0]) + for params in params_list: + for i, value in enumerate(params): + if value.input_size: + sizes[i] = value.input_size + self.setinputsizes(*sizes) def _param_generator(self, params): - return [p.force_bytes for p in params] + # Try dict handling; if that fails, treat as sequence + if hasattr(params, 'items'): + return dict((k, v.force_bytes) for k,v in params.items()) + else: + return [p.force_bytes for p in params] - def execute(self, query, params=None): + def _fix_for_params(self, query, params): # cx_Oracle wants no trailing ';' for SQL statements. For PL/SQL, it # it does want a trailing ';' but not a trailing '/'. However, these # characters must be included in the original query in case the query @@ -780,10 +797,18 @@ class FormatStylePlaceholderCursor(object): if params is None: params = [] query = convert_unicode(query, self.charset) + elif hasattr(params, 'keys'): + # Handle params as dict + args = dict((k, ":%s"%k) for k in params.keys()) + query = convert_unicode(query % args, self.charset) else: - params = self._format_params(params) + # Handle params as sequence args = [(':arg%d' % i) for i in range(len(params))] query = convert_unicode(query % tuple(args), self.charset) + return query, self._format_params(params) + + def execute(self, query, params=None): + query, params = self._fix_for_params(query, params) self._guess_input_sizes([params]) try: return self.cursor.execute(query, self._param_generator(params)) @@ -794,22 +819,15 @@ class FormatStylePlaceholderCursor(object): raise def executemany(self, query, params=None): - # cx_Oracle doesn't support iterators, convert them to lists - if params is not None and not isinstance(params, (list, tuple)): - params = list(params) - try: - args = [(':arg%d' % i) for i in range(len(params[0]))] - except (IndexError, TypeError): + if not params: # No params given, nothing to do return None - # cx_Oracle wants no trailing ';' for SQL statements. For PL/SQL, it - # it does want a trailing ';' but not a trailing '/'. However, these - # characters must be included in the original query in case the query - # is being passed to SQL*Plus. - if query.endswith(';') or query.endswith('/'): - query = query[:-1] - query = convert_unicode(query % tuple(args), self.charset) - formatted = [self._format_params(i) for i in params] + # uniform treatment for sequences and iterables + params_iter = iter(params) + query, firstparams = self._fix_for_params(query, next(params_iter)) + # we build a list of formatted params; as we're going to traverse it + # more than once, we can't make it lazy by using a generator + formatted = [firstparams]+[self._format_params(p) for p in params_iter] self._guess_input_sizes(formatted) try: return self.cursor.executemany(query, diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py index 324adfd97b..92dbf354ae 100644 --- a/django/db/backends/sqlite3/base.py +++ b/django/db/backends/sqlite3/base.py @@ -101,6 +101,7 @@ class DatabaseFeatures(BaseDatabaseFeatures): has_bulk_insert = True can_combine_inserts_with_and_without_auto_increment_pk = False autocommits_when_autocommit_is_off = True + supports_paramstyle_pyformat = False @cached_property def uses_savepoints(self): diff --git a/django/db/models/query.py b/django/db/models/query.py index b0ce25f5b5..27a87a3f65 100644 --- a/django/db/models/query.py +++ b/django/db/models/query.py @@ -1445,7 +1445,10 @@ class RawQuerySet(object): yield instance def __repr__(self): - return "" % (self.raw_query % tuple(self.params)) + text = self.raw_query + if self.params: + text = text % (self.params if hasattr(self.params, 'keys') else tuple(self.params)) + return "" % text def __getitem__(self, k): return list(self)[k] diff --git a/docs/ref/databases.txt b/docs/ref/databases.txt index a648ac1709..4e5f136e2e 100644 --- a/docs/ref/databases.txt +++ b/docs/ref/databases.txt @@ -623,6 +623,14 @@ If you're getting this error, you can solve it by: SQLite does not support the ``SELECT ... FOR UPDATE`` syntax. Calling it will have no effect. +"pyformat" parameter style in raw queries not supported +------------------------------------------------------- + +For most backends, raw queries (``Manager.raw()`` or ``cursor.execute()``) +can use the "pyformat" parameter style, where placeholders in the query +are given as ``'%(name)s'`` and the parameters are passed as a dictionary +rather than a list. SQLite does not support this. + .. _sqlite-connection-queries: Parameters not quoted in ``connection.queries`` diff --git a/docs/releases/1.6.txt b/docs/releases/1.6.txt index 2c1fffd8cd..1fd98e1271 100644 --- a/docs/releases/1.6.txt +++ b/docs/releases/1.6.txt @@ -337,6 +337,12 @@ Minor features default) to allow customizing the :attr:`~django.forms.Form.prefix` of the form. +* Raw queries (``Manager.raw()`` or ``cursor.execute()``) can now use the + "pyformat" parameter style, where placeholders in the query are given as + ``'%(name)s'`` and the parameters are passed as a dictionary rather than + a list (except on SQLite). This has long been possible (but not officially + supported) on MySQL and PostgreSQL, and is now also available on Oracle. + Backwards incompatible changes in 1.6 ===================================== diff --git a/docs/topics/db/sql.txt b/docs/topics/db/sql.txt index 2ec31a4988..7437d51d28 100644 --- a/docs/topics/db/sql.txt +++ b/docs/topics/db/sql.txt @@ -166,9 +166,17 @@ argument to ``raw()``:: >>> lname = 'Doe' >>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname]) -``params`` is a list of parameters. You'll use ``%s`` placeholders in the -query string (regardless of your database engine); they'll be replaced with -parameters from the ``params`` list. +``params`` is a list or dictionary of parameters. You'll use ``%s`` +placeholders in the query string for a list, or ``%(key)s`` +placeholders for a dictionary (where ``key`` is replaced by a +dictionary key, of course), regardless of your database engine. Such +placeholders will be replaced with parameters from the ``params`` +argument. + +.. note:: Dictionary params not supported with SQLite + + Dictionary params are not supported with the SQLite backend; with + this backend, you must pass parameters as a list. .. warning:: @@ -181,14 +189,21 @@ parameters from the ``params`` list. **Don't.** - Using the ``params`` list completely protects you from `SQL injection + Using the ``params`` argument completely protects you from `SQL injection attacks`__, a common exploit where attackers inject arbitrary SQL into your database. If you use string interpolation, sooner or later you'll fall victim to SQL injection. As long as you remember to always use the - ``params`` list you'll be protected. + ``params`` argument you'll be protected. __ http://en.wikipedia.org/wiki/SQL_injection +.. versionchanged:: 1.6 + + In Django 1.5 and earlier, you could pass parameters as dictionaries + when using PostgreSQL or MySQL, although this wasn't documented. Now + you can also do this whem using Oracle, and it is officially supported. + + .. _executing-custom-sql: Executing custom SQL directly diff --git a/tests/backends/tests.py b/tests/backends/tests.py index c6cad56ec1..c1a26df7fc 100644 --- a/tests/backends/tests.py +++ b/tests/backends/tests.py @@ -456,13 +456,24 @@ class SqliteChecks(TestCase): class BackendTestCase(TestCase): def create_squares_with_executemany(self, args): + self.create_squares(args, 'format', True) + + def create_squares(self, args, paramstyle, multiple): cursor = connection.cursor() opts = models.Square._meta tbl = connection.introspection.table_name_converter(opts.db_table) f1 = connection.ops.quote_name(opts.get_field('root').column) f2 = connection.ops.quote_name(opts.get_field('square').column) - query = 'INSERT INTO %s (%s, %s) VALUES (%%s, %%s)' % (tbl, f1, f2) - cursor.executemany(query, args) + if paramstyle=='format': + query = 'INSERT INTO %s (%s, %s) VALUES (%%s, %%s)' % (tbl, f1, f2) + elif paramstyle=='pyformat': + query = 'INSERT INTO %s (%s, %s) VALUES (%%(root)s, %%(square)s)' % (tbl, f1, f2) + else: + raise ValueError("unsupported paramstyle in test") + if multiple: + cursor.executemany(query, args) + else: + cursor.execute(query, args) def test_cursor_executemany(self): #4896: Test cursor.executemany @@ -491,6 +502,35 @@ class BackendTestCase(TestCase): self.create_squares_with_executemany(args) self.assertEqual(models.Square.objects.count(), 9) + @skipUnlessDBFeature('supports_paramstyle_pyformat') + def test_cursor_execute_with_pyformat(self): + #10070: Support pyformat style passing of paramters + args = {'root': 3, 'square': 9} + self.create_squares(args, 'pyformat', multiple=False) + self.assertEqual(models.Square.objects.count(), 1) + + @skipUnlessDBFeature('supports_paramstyle_pyformat') + def test_cursor_executemany_with_pyformat(self): + #10070: Support pyformat style passing of paramters + args = [{'root': i, 'square': i**2} for i in range(-5, 6)] + self.create_squares(args, 'pyformat', multiple=True) + self.assertEqual(models.Square.objects.count(), 11) + for i in range(-5, 6): + square = models.Square.objects.get(root=i) + self.assertEqual(square.square, i**2) + + @skipUnlessDBFeature('supports_paramstyle_pyformat') + def test_cursor_executemany_with_pyformat_iterator(self): + args = iter({'root': i, 'square': i**2} for i in range(-3, 2)) + self.create_squares(args, 'pyformat', multiple=True) + self.assertEqual(models.Square.objects.count(), 5) + + args = iter({'root': i, 'square': i**2} for i in range(3, 7)) + with override_settings(DEBUG=True): + # same test for DebugCursorWrapper + self.create_squares(args, 'pyformat', multiple=True) + self.assertEqual(models.Square.objects.count(), 9) + def test_unicode_fetches(self): #6254: fetchone, fetchmany, fetchall return strings as unicode objects qn = connection.ops.quote_name diff --git a/tests/raw_query/tests.py b/tests/raw_query/tests.py index e404c8b065..7242b8309b 100644 --- a/tests/raw_query/tests.py +++ b/tests/raw_query/tests.py @@ -3,7 +3,7 @@ from __future__ import absolute_import from datetime import date from django.db.models.query_utils import InvalidQuery -from django.test import TestCase +from django.test import TestCase, skipUnlessDBFeature from .models import Author, Book, Coffee, Reviewer, FriendlyAuthor @@ -123,10 +123,27 @@ class RawQueryTests(TestCase): query = "SELECT * FROM raw_query_author WHERE first_name = %s" author = Author.objects.all()[2] params = [author.first_name] - results = list(Author.objects.raw(query, params=params)) + qset = Author.objects.raw(query, params=params) + results = list(qset) self.assertProcessed(Author, results, [author]) self.assertNoAnnotations(results) self.assertEqual(len(results), 1) + self.assertIsInstance(repr(qset), str) + + @skipUnlessDBFeature('supports_paramstyle_pyformat') + def testPyformatParams(self): + """ + Test passing optional query parameters + """ + query = "SELECT * FROM raw_query_author WHERE first_name = %(first)s" + author = Author.objects.all()[2] + params = {'first': author.first_name} + qset = Author.objects.raw(query, params=params) + results = list(qset) + self.assertProcessed(Author, results, [author]) + self.assertNoAnnotations(results) + self.assertEqual(len(results), 1) + self.assertIsInstance(repr(qset), str) def testManyToMany(self): """ -- cgit v1.3