Changeset 743


Ignore:
Timestamp:
Jan 14, 2016, 12:32:01 PM (4 years ago)
Author:
cito
Message:

Test error messages and security of the get() method

The get() method should be immune against SQL hacking with apostrophes in
values, and give a proper and helpful error message if a row is not found.

Files:
3 edited

Legend:

Unmodified
Added
Removed
  • branches/4.x/tests/test_classic_dbwrapper.py

    r731 r743  
    724724        self.assertIn('v4', r)
    725725        self.assertEqual(r['v4'], 'abc4')
     726
     727    def testGetLittleBobbyTables(self):
     728        get = self.db.get
     729        query = self.db.query
     730        query("drop table if exists test_students")
     731        query("create table test_students (firstname varchar primary key,"
     732            " nickname varchar, grade char(2))")
     733        query("insert into test_students values ("
     734              "'D''Arcy', 'Darcey', 'A+')")
     735        query("insert into test_students values ("
     736              "'Sheldon', 'Moonpie', 'A+')")
     737        query("insert into test_students values ("
     738              "'Robert', 'Little Bobby Tables', 'D-')")
     739        r = get('test_students', 'Sheldon')
     740        self.assertEqual(r, dict(
     741            firstname="Sheldon", nickname='Moonpie', grade='A+'))
     742        r = get('test_students', 'Robert')
     743        self.assertEqual(r, dict(
     744            firstname="Robert", nickname='Little Bobby Tables', grade='D-'))
     745        r = get('test_students', "D'Arcy")
     746        self.assertEqual(r, dict(
     747            firstname="D'Arcy", nickname='Darcey', grade='A+'))
     748        try:
     749            get('test_students', "D' Arcy")
     750        except pg.DatabaseError as error:
     751            self.assertEqual(str(error),
     752                'No such record in public.test_students where firstname = '
     753                "'D'' Arcy'")
     754        try:
     755            get('test_students', "Robert'); TRUNCATE TABLE test_students;--")
     756        except pg.DatabaseError as error:
     757            self.assertEqual(str(error),
     758                'No such record in public.test_students where firstname = '
     759                "'Robert''); TRUNCATE TABLE test_students;--'")
     760        q = "select * from test_students order by 1 limit 4"
     761        r = query(q).getresult()
     762        self.assertEqual(len(r), 3)
     763        self.assertEqual(r[1][2], 'D-')
     764        query('drop table test_students')
    726765
    727766    def testInsert(self):
  • trunk/pg.py

    r740 r743  
    370370        params.append(value)
    371371        return '$%d' % len(params)
     372
     373    def _list_params(self, params):
     374        """Create a human readable parameter list."""
     375        return ', '.join('$%d=%r' % (n, v) for n, v in enumerate(params, 1))
    372376
    373377    @staticmethod
     
    661665        res = q.dictresult()
    662666        if not res:
    663             raise _db_error('No such record in %s where %s' % (table, where))
     667            raise _db_error('No such record in %s\nwhere %s\nwith %s' % (
     668                table, where, self._list_params(params)))
    664669        for n, value in res[0].items():
    665670            if n == 'oid':
  • trunk/tests/test_classic_dbwrapper.py

    r739 r743  
    816816        self.assertIn('v4', r)
    817817        self.assertEqual(r['v4'], 'abc4')
     818
     819    def testGetLittleBobbyTables(self):
     820        get = self.db.get
     821        query = self.db.query
     822        query("drop table if exists test_students")
     823        query("create table test_students (firstname varchar primary key,"
     824            " nickname varchar, grade char(2))")
     825        query("insert into test_students values ("
     826              "'D''Arcy', 'Darcey', 'A+')")
     827        query("insert into test_students values ("
     828              "'Sheldon', 'Moonpie', 'A+')")
     829        query("insert into test_students values ("
     830              "'Robert', 'Little Bobby Tables', 'D-')")
     831        r = get('test_students', 'Sheldon')
     832        self.assertEqual(r, dict(
     833            firstname="Sheldon", nickname='Moonpie', grade='A+'))
     834        r = get('test_students', 'Robert')
     835        self.assertEqual(r, dict(
     836            firstname="Robert", nickname='Little Bobby Tables', grade='D-'))
     837        r = get('test_students', "D'Arcy")
     838        self.assertEqual(r, dict(
     839            firstname="D'Arcy", nickname='Darcey', grade='A+'))
     840        try:
     841            get('test_students', "D' Arcy")
     842        except pg.DatabaseError as error:
     843            self.assertEqual(str(error),
     844                'No such record in test_students\nwhere "firstname" = $1\n'
     845                'with $1="D\' Arcy"')
     846        try:
     847            get('test_students', "Robert'); TRUNCATE TABLE test_students;--")
     848        except pg.DatabaseError as error:
     849            self.assertEqual(str(error),
     850                'No such record in test_students\nwhere "firstname" = $1\n'
     851                'with $1="Robert\'); TRUNCATE TABLE test_students;--"')
     852        q = "select * from test_students order by 1 limit 4"
     853        r = query(q).getresult()
     854        self.assertEqual(len(r), 3)
     855        self.assertEqual(r[1][2], 'D-')
     856        query('drop table test_students')
    818857
    819858    def testInsert(self):
Note: See TracChangeset for help on using the changeset viewer.