Changeset 730


Ignore:
Timestamp:
Jan 12, 2016, 8:58:54 PM (4 years ago)
Author:
cito
Message:

Use query parameters instead of inline values

The single row methods of the DB wrapper class created queries with inline values
instead of passing them separately as parameters, even though our query method
does have this capability. Using query parameters also spares us a lot of quoting
and escaping that is necessary when passing values inline.

Location:
trunk
Files:
5 edited

Legend:

Unmodified
Added
Removed
  • trunk/docs/contents/changelog.rst

    r729 r730  
    3535  Note that OIDs are considered deprecated anyway, and they are not created
    3636  by default any more in PostgreSQL 8.1 and later.
    37 - Simplified the internal caching and mechanisms for automatic quoting
    38   of class names in the classic interface, these things should now both
    39   perform better and use less memory.
    40 
     37- The internal caching and automatic quoting of class names in the classic
     38  interface has been simplified and improved, it should now perform better
     39  and use less memory. Also, overhead for quoting and escaping values in the
     40  DB wrapper methods has been reduced and security has been improved by
     41  passing the values to libpq separately as parameters instead of inline.
    4142
    4243Version 4.2
  • trunk/pg.py

    r729 r730  
    3838from decimal import Decimal
    3939from collections import namedtuple
    40 from itertools import groupby
     40from functools import partial
    4141
    4242try:
     
    316316    # Auxiliary methods
    317317
    318     def _do_debug(self, s):
     318    def _do_debug(self, *args):
    319319        """Print a debug message."""
    320320        if self.debug:
     321            s = '\n'.join(args)
    321322            if isinstance(self.debug, basestring):
    322323                print(self.debug % s)
     
    333334        return bool(d) if get_bool() else ('t' if d else 'f')
    334335
    335     def _quote_text(self, d):
    336         """Quote text value."""
    337         if not isinstance(d, basestring):
    338             d = str(d)
    339         return "'%s'" % self.escape_string(d)
    340 
    341     _bool_true = frozenset('t true 1 y yes on'.split())
    342 
    343     def _quote_bool(self, d):
    344         """Quote boolean value."""
     336    _bool_true_values = frozenset('t true 1 y yes on'.split())
     337
     338    def _prepare_bool(self, d):
     339        """Prepare a boolean parameter."""
    345340        if isinstance(d, basestring):
    346341            if not d:
    347                 return 'NULL'
    348             d = d.lower() in self._bool_true
    349         return "'t'" if d else "'f'"
     342                return None
     343            d = d.lower() in self._bool_true_values
     344        return 't' if d else 'f'
    350345
    351346    _date_literals = frozenset('current_date current_time'
    352347        ' current_timestamp localtime localtimestamp'.split())
    353348
    354     def _quote_date(self, d):
    355         """Quote date value."""
     349    def _prepare_date(self, d):
     350        """Prepare a date parameter."""
    356351        if not d:
    357             return 'NULL'
     352            return None
    358353        if isinstance(d, basestring) and d.lower() in self._date_literals:
    359             return d
    360         return self._quote_text(d)
    361 
    362     def _quote_num(self, d):
    363         """Quote numeric value."""
     354            raise ValueError
     355        return d
     356
     357    def _prepare_num(self, d):
     358        """Prepare a numeric parameter."""
    364359        if not d and d != 0:
    365             return 'NULL'
    366         return str(d)
    367 
    368     def _quote_money(self, d):
    369         """Quote money value."""
    370         if d is None or d == '':
    371             return 'NULL'
    372         if not isinstance(d, basestring):
    373             d = str(d)
     360            return None
    374361        return d
    375362
    376     if bytes is str:  # Python < 3.0
    377         """Quote bytes value."""
    378 
    379         def _quote_bytea(self, d):
    380             return "'%s'" % self.escape_bytea(d)
    381 
    382     else:
    383 
    384         def _quote_bytea(self, d):
    385             return "'%s'" % self.escape_bytea(d).decode('ascii')
    386 
    387     _quote_funcs = dict(  # quote methods for each type
    388         text=_quote_text, bool=_quote_bool, date=_quote_date,
    389         int=_quote_num, num=_quote_num, float=_quote_num,
    390         money=_quote_money, bytea=_quote_bytea)
    391 
    392     def _quote(self, d, t):
    393         """Return quotes if needed."""
    394         if d is None:
    395             return 'NULL'
    396         try:
    397             quote_func = self._quote_funcs[t]
    398         except KeyError:
    399             quote_func = self._quote_funcs['text']
    400         return quote_func(self, d)
     363    def _prepare_bytea(self, d):
     364        return self.escape_bytea(d)
     365
     366    _prepare_funcs = dict(  # quote methods for each type
     367        bool=_prepare_bool, date=_prepare_date,
     368        int=_prepare_num, num=_prepare_num, float=_prepare_num,
     369        money=_prepare_num, bytea=_prepare_bytea)
     370
     371    def _prepare_param(self, value, typ, params):
     372        """Prepare and add a parameter to the list."""
     373        if value is not None and typ != 'text':
     374            try:
     375                prepare = self._prepare_funcs[typ]
     376            except KeyError:
     377                pass
     378            else:
     379                try:
     380                    value = prepare(self, value)
     381                except ValueError:
     382                    return value
     383        params.append(value)
     384        return '$%d' % len(params)
    401385
    402386    # Public methods
     
    579563            attnames.clear()
    580564            self._do_debug('pkey cache has been flushed')
    581 
    582565        try:  # cache lookup
    583566            names = attnames[cl]
     
    652635                raise _prg_error('Class %s has no primary key' % cl)
    653636        attnames = self.get_attnames(cl)
     637        params = []
     638        param = partial(self._prepare_param, params=params)
    654639        # We want the oid for later updates if that isn't the key
    655640        if keyname == 'oid':
     
    660645                arg = {qoid: arg}
    661646            what = '*'
    662             where = 'oid = %s' % arg[qoid]
     647            where = 'oid = %s' % param(arg[qoid], 'int')
    663648        else:
    664649            if isinstance(keyname, basestring):
     
    670655            what = ', '.join(attnames)
    671656            where = ' AND '.join(['%s = %s'
    672                 % (k, self._quote(arg[k], attnames[k])) for k in keyname])
     657                % (k, param(arg[k], attnames[k])) for k in keyname])
    673658        q = 'SELECT %s FROM %s WHERE %s LIMIT 1' % (
    674659            what, _quote_class_name(cl), where)
    675         self._do_debug(q)
    676         res = self.db.query(q).dictresult()
     660        self._do_debug(q, params)
     661        res = self.db.query(q, params).dictresult()
    677662        if not res:
    678663            raise _db_error('No such record in %s where %s' % (cl, where))
     
    707692        d.update(kw)
    708693        attnames = self.get_attnames(cl)
     694        params = []
     695        param = partial(self._prepare_param, params=params)
    709696        names, values = [], []
    710697        for n in attnames:
    711698            if n != 'oid' and n in d:
    712699                names.append('"%s"' % n)
    713                 values.append(self._quote(d[n], attnames[n]))
     700                values.append(param(d[n], attnames[n]))
    714701        names, values = ', '.join(names), ', '.join(values)
    715702        selectable = self.has_table_privilege(cl)
     
    720707        q = 'INSERT INTO %s (%s) VALUES (%s)%s' % (
    721708            _quote_class_name(cl), names, values, ret)
    722         self._do_debug(q)
    723         res = self.db.query(q)
     709        self._do_debug(q, params)
     710        res = self.db.query(q, params)
    724711        if ret:
    725712            res = res.dictresult()[0]
     
    727714                if n == 'oid':
    728715                    n = qoid
    729                 elif attnames.get(n) == 'bytea':
     716                elif attnames.get(n) == 'bytea' and value is not None:
    730717                    value = self.unescape_bytea(value)
    731718                d[n] = value
     
    765752        d.update(kw)
    766753        attnames = self.get_attnames(cl)
     754        params = []
     755        param = partial(self._prepare_param, params=params)
    767756        if qoid in d:
    768             where = 'oid = %s' % d[qoid]
     757            where = 'oid = %s' % param(d[qoid], 'int')
    769758            keyname = ()
    770759        else:
     
    777766            try:
    778767                where = ' AND '.join(['%s = %s'
    779                     % (k, self._quote(d[k], attnames[k])) for k in keyname])
     768                    % (k, param(d[k], attnames[k])) for k in keyname])
    780769            except KeyError:
    781770                raise _prg_error('Update needs primary key or oid.')
     
    783772        for n in attnames:
    784773            if n in d and n not in keyname:
    785                 values.append('%s = %s' % (n, self._quote(d[n], attnames[n])))
     774                values.append('%s = %s' % (n, param(d[n], attnames[n])))
    786775        if not values:
    787776            return d
     
    794783        q = 'UPDATE %s SET %s WHERE %s%s' % (
    795784            _quote_class_name(cl), values, where, ret)
    796         self._do_debug(q)
    797         res = self.db.query(q)
     785        self._do_debug(q, params)
     786        res = self.db.query(q, params)
    798787        if ret:
    799788            res = res.dictresult()[0]
     
    801790                if n == 'oid':
    802791                    n = qoid
    803                 elif attnames.get(n) == 'bytea':
     792                elif attnames.get(n) == 'bytea' and value is not None:
    804793                    value = self.unescape_bytea(value)
    805794                d[n] = value
     
    858847            d = {}
    859848        d.update(kw)
     849        params = []
     850        param = partial(self._prepare_param, params=params)
    860851        if qoid in d:
    861             where = 'oid = %s' % d[qoid]
     852            where = 'oid = %s' % param(d[qoid], 'int')
    862853        else:
    863854            try:
     
    870861            try:
    871862                where = ' AND '.join(['%s = %s'
    872                     % (k, self._quote(d[k], attnames[k])) for k in keyname])
     863                    % (k, param(d[k], attnames[k])) for k in keyname])
    873864            except KeyError:
    874865                raise _prg_error('Delete needs primary key or oid.')
    875866        q = 'DELETE FROM %s WHERE %s' % (_quote_class_name(cl), where)
    876         self._do_debug(q)
    877         return int(self.db.query(q))
     867        self._do_debug(q, params)
     868        return int(self.db.query(q, params))
    878869
    879870    def notification_handler(self, event, callback, arg_dict={}, timeout=None):
  • trunk/tests/test_classic.py

    r729 r730  
    193193
    194194        r = db.get('_test_schema', 1234)
     195        self.assertIn('dvar', r)
    195196        db.update('_test_schema', _test=1234, dvar=456)
    196197        r = db.get('_test_schema', 1234)
     
    201202        r = db.get('_test_schema', 1234)
    202203        self.assertEqual(r['dvar'], 456)
    203 
    204     def test_quote(self):
    205         db = opendb()
    206         q = db._quote
    207         self.assertEqual(q(0, 'int'), "0")
    208         self.assertEqual(q(0, 'num'), "0")
    209         self.assertEqual(q('0', 'int'), "0")
    210         self.assertEqual(q('0', 'num'), "0")
    211         self.assertEqual(q(1, 'int'), "1")
    212         self.assertEqual(q(1, 'text'), "'1'")
    213         self.assertEqual(q(1, 'num'), "1")
    214         self.assertEqual(q('1', 'int'), "1")
    215         self.assertEqual(q('1', 'text'), "'1'")
    216         self.assertEqual(q('1', 'num'), "1")
    217         self.assertEqual(q(None, 'int'), "NULL")
    218         self.assertEqual(q(1, 'money'), "1")
    219         self.assertEqual(q('1', 'money'), "1")
    220         self.assertEqual(q(1.234, 'money'), "1.234")
    221         self.assertEqual(q('1.234', 'money'), "1.234")
    222         self.assertEqual(q(0, 'money'), "0")
    223         self.assertEqual(q(0.00, 'money'), "0.0")
    224         self.assertEqual(q(Decimal('0.00'), 'money'), "0.00")
    225         self.assertEqual(q(None, 'money'), "NULL")
    226         self.assertEqual(q('', 'money'), "NULL")
    227         self.assertEqual(q(0, 'bool'), "'f'")
    228         self.assertEqual(q('', 'bool'), "NULL")
    229         self.assertEqual(q('f', 'bool'), "'f'")
    230         self.assertEqual(q('off', 'bool'), "'f'")
    231         self.assertEqual(q('no', 'bool'), "'f'")
    232         self.assertEqual(q(1, 'bool'), "'t'")
    233         self.assertEqual(q(9999, 'bool'), "'t'")
    234         self.assertEqual(q(-9999, 'bool'), "'t'")
    235         self.assertEqual(q('1', 'bool'), "'t'")
    236         self.assertEqual(q('t', 'bool'), "'t'")
    237         self.assertEqual(q('on', 'bool'), "'t'")
    238         self.assertEqual(q('yes', 'bool'), "'t'")
    239         self.assertEqual(q('true', 'bool'), "'t'")
    240         self.assertEqual(q('y', 'bool'), "'t'")
    241         self.assertEqual(q('', 'date'), "NULL")
    242         self.assertEqual(q(False, 'date'), "NULL")
    243         self.assertEqual(q(0, 'date'), "NULL")
    244         self.assertEqual(q('some_date', 'date'), "'some_date'")
    245         self.assertEqual(q('current_timestamp', 'date'), "current_timestamp")
    246         self.assertEqual(q('', 'text'), "''")
    247         self.assertEqual(q("'", 'text'), "''''")
    248         self.assertEqual(q("\\", 'text'), "'\\\\'")
    249204
    250205    def notify_callback(self, arg_dict):
  • trunk/tests/test_classic_connection.py

    r690 r730  
    13481348        en_money = '$34.25', '$ 34.25', '34.25$', '34.25 $', '34.25 Dollar'
    13491349        de_locales = 'de', 'de_DE', 'de_DE.utf8', 'de_DE.UTF-8'
    1350         de_money = ('34,25€', '34,25 €', '€34,25' '€ 34,25',
     1350        de_money = ('34,25€', '34,25 €', '€34,25', '€ 34,25',
    13511351            'EUR34,25', 'EUR 34,25', '34,25 EUR', '34,25 Euro', '34,25 DM')
    13521352        # first try with English localization (using the point)
  • trunk/tests/test_classic_dbwrapper.py

    r729 r730  
    406406        self.assertEqual(f(r'\\x4f007073ff21'), b'\\x4f007073ff21')
    407407
    408     def testQuote(self):
    409         f = self.db._quote
    410         self.assertEqual(f(None, None), 'NULL')
    411         self.assertEqual(f(None, 'int'), 'NULL')
    412         self.assertEqual(f(None, 'float'), 'NULL')
    413         self.assertEqual(f(None, 'num'), 'NULL')
    414         self.assertEqual(f(None, 'money'), 'NULL')
    415         self.assertEqual(f(None, 'bool'), 'NULL')
    416         self.assertEqual(f(None, 'date'), 'NULL')
    417         self.assertEqual(f('', 'int'), 'NULL')
    418         self.assertEqual(f('', 'float'), 'NULL')
    419         self.assertEqual(f('', 'num'), 'NULL')
    420         self.assertEqual(f('', 'money'), 'NULL')
    421         self.assertEqual(f('', 'bool'), 'NULL')
    422         self.assertEqual(f('', 'date'), 'NULL')
    423         self.assertEqual(f('', 'text'), "''")
    424         self.assertEqual(f(0, 'int'), '0')
    425         self.assertEqual(f(0, 'num'), '0')
    426         self.assertEqual(f(1, 'int'), '1')
    427         self.assertEqual(f(1, 'num'), '1')
    428         self.assertEqual(f(-1, 'int'), '-1')
    429         self.assertEqual(f(-1, 'num'), '-1')
    430         self.assertEqual(f(123456789, 'int'), '123456789')
    431         self.assertEqual(f(123456987, 'num'), '123456987')
    432         self.assertEqual(f(1.23654789, 'num'), '1.23654789')
    433         self.assertEqual(f(12365478.9, 'num'), '12365478.9')
    434         self.assertEqual(f('123456789', 'num'), '123456789')
    435         self.assertEqual(f('1.23456789', 'num'), '1.23456789')
    436         self.assertEqual(f('12345678.9', 'num'), '12345678.9')
    437         self.assertEqual(f(123, 'money'), '123')
    438         self.assertEqual(f('123', 'money'), '123')
    439         self.assertEqual(f(123.45, 'money'), '123.45')
    440         self.assertEqual(f('123.45', 'money'), '123.45')
    441         self.assertEqual(f(123.454, 'money'), '123.454')
    442         self.assertEqual(f('123.454', 'money'), '123.454')
    443         self.assertEqual(f(123.456, 'money'), '123.456')
    444         self.assertEqual(f('123.456', 'money'), '123.456')
    445         self.assertEqual(f('f', 'bool'), "'f'")
    446         self.assertEqual(f('F', 'bool'), "'f'")
    447         self.assertEqual(f('false', 'bool'), "'f'")
    448         self.assertEqual(f('False', 'bool'), "'f'")
    449         self.assertEqual(f('FALSE', 'bool'), "'f'")
    450         self.assertEqual(f(0, 'bool'), "'f'")
    451         self.assertEqual(f('0', 'bool'), "'f'")
    452         self.assertEqual(f('-', 'bool'), "'f'")
    453         self.assertEqual(f('n', 'bool'), "'f'")
    454         self.assertEqual(f('N', 'bool'), "'f'")
    455         self.assertEqual(f('no', 'bool'), "'f'")
    456         self.assertEqual(f('off', 'bool'), "'f'")
    457         self.assertEqual(f('t', 'bool'), "'t'")
    458         self.assertEqual(f('T', 'bool'), "'t'")
    459         self.assertEqual(f('true', 'bool'), "'t'")
    460         self.assertEqual(f('True', 'bool'), "'t'")
    461         self.assertEqual(f('TRUE', 'bool'), "'t'")
    462         self.assertEqual(f(1, 'bool'), "'t'")
    463         self.assertEqual(f(2, 'bool'), "'t'")
    464         self.assertEqual(f(-1, 'bool'), "'t'")
    465         self.assertEqual(f(0.5, 'bool'), "'t'")
    466         self.assertEqual(f('1', 'bool'), "'t'")
    467         self.assertEqual(f('y', 'bool'), "'t'")
    468         self.assertEqual(f('Y', 'bool'), "'t'")
    469         self.assertEqual(f('yes', 'bool'), "'t'")
    470         self.assertEqual(f('on', 'bool'), "'t'")
    471         self.assertEqual(f('01.01.2000', 'date'), "'01.01.2000'")
    472         self.assertEqual(f(123, 'text'), "'123'")
    473         self.assertEqual(f(1.23, 'text'), "'1.23'")
    474         self.assertEqual(f('abc', 'text'), "'abc'")
    475         self.assertEqual(f("ab'c", 'text'), "'ab''c'")
    476         self.assertEqual(f('ab\\c', 'text'), "'ab\\c'")
    477         self.assertEqual(f("a\\b'c", 'text'), "'a\\b''c'")
    478         self.db.query('set standard_conforming_strings=off')
    479         self.assertEqual(f('ab\\c', 'text'), "'ab\\\\c'")
    480         self.assertEqual(f("a\\b'c", 'text'), "'a\\\\b''c'")
    481 
    482408    def testQuery(self):
    483409        query = self.db.query
     
    787713        insert = self.db.insert
    788714        query = self.db.query
    789         server_version = self.db.server_version
    790715        bool_on = pg.get_bool()
    791716        decimal = pg.get_decimal()
     
    860785                    if m is not None:
    861786                        expect['m'] = decimal(m)
    862                 if data.get('m') and server_version < 910000:
    863                     # PostgreSQL < 9.1 cannot directly convert numbers to money
    864                     data['m'] = "'%s'::money" % data['m']
    865787                self.assertEqual(insert(table, data), data)
    866788                self.assertIn(oid_table, data)
     
    11301052        query('drop table if exists bytea_test')
    11311053        query('create table bytea_test (n smallint primary key, data bytea)')
     1054        # insert null value
     1055        r = self.db.insert('bytea_test', n=0, data=None)
     1056        self.assertIsInstance(r, dict)
     1057        self.assertIn('n', r)
     1058        self.assertEqual(r['n'], 0)
     1059        self.assertIn('data', r)
     1060        self.assertIsNone(r['data'])
     1061        s = b'None'
     1062        r = self.db.update('bytea_test', n=0, data=s)
     1063        self.assertIsInstance(r, dict)
     1064        self.assertIn('n', r)
     1065        self.assertEqual(r['n'], 0)
     1066        self.assertIn('data', r)
     1067        r = r['data']
     1068        self.assertIsInstance(r, bytes)
     1069        self.assertEqual(r, s)
     1070        r = self.db.update('bytea_test', n=0, data=None)
     1071        self.assertIsNone(r['data'])
    11321072        # insert as bytes
    11331073        s = b"It's all \\ kinds \x00 of\r nasty \xff stuff!\n"
Note: See TracChangeset for help on using the changeset viewer.