Author: cito
Date: Thu Jan 14 12:32:01 2016
New Revision: 743
Log:
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.
Modified:
branches/4.x/tests/test_classic_dbwrapper.py
trunk/pg.py
trunk/tests/test_classic_dbwrapper.py
Modified: branches/4.x/tests/test_classic_dbwrapper.py
==============================================================================
--- branches/4.x/tests/test_classic_dbwrapper.py Thu Jan 14 11:01:34
2016 (r742)
+++ branches/4.x/tests/test_classic_dbwrapper.py Thu Jan 14 12:32:01
2016 (r743)
@@ -724,6 +724,45 @@
self.assertIn('v4', r)
self.assertEqual(r['v4'], 'abc4')
+ def testGetLittleBobbyTables(self):
+ get = self.db.get
+ query = self.db.query
+ query("drop table if exists test_students")
+ query("create table test_students (firstname varchar primary key,"
+ " nickname varchar, grade char(2))")
+ query("insert into test_students values ("
+ "'D''Arcy', 'Darcey', 'A+')")
+ query("insert into test_students values ("
+ "'Sheldon', 'Moonpie', 'A+')")
+ query("insert into test_students values ("
+ "'Robert', 'Little Bobby Tables', 'D-')")
+ r = get('test_students', 'Sheldon')
+ self.assertEqual(r, dict(
+ firstname="Sheldon", nickname='Moonpie', grade='A+'))
+ r = get('test_students', 'Robert')
+ self.assertEqual(r, dict(
+ firstname="Robert", nickname='Little Bobby Tables', grade='D-'))
+ r = get('test_students', "D'Arcy")
+ self.assertEqual(r, dict(
+ firstname="D'Arcy", nickname='Darcey', grade='A+'))
+ try:
+ get('test_students', "D' Arcy")
+ except pg.DatabaseError as error:
+ self.assertEqual(str(error),
+ 'No such record in public.test_students where firstname = '
+ "'D'' Arcy'")
+ try:
+ get('test_students', "Robert'); TRUNCATE TABLE test_students;--")
+ except pg.DatabaseError as error:
+ self.assertEqual(str(error),
+ 'No such record in public.test_students where firstname = '
+ "'Robert''); TRUNCATE TABLE test_students;--'")
+ q = "select * from test_students order by 1 limit 4"
+ r = query(q).getresult()
+ self.assertEqual(len(r), 3)
+ self.assertEqual(r[1][2], 'D-')
+ query('drop table test_students')
+
def testInsert(self):
insert = self.db.insert
query = self.db.query
Modified: trunk/pg.py
==============================================================================
--- trunk/pg.py Thu Jan 14 11:01:34 2016 (r742)
+++ trunk/pg.py Thu Jan 14 12:32:01 2016 (r743)
@@ -370,6 +370,10 @@
params.append(value)
return '$%d' % len(params)
+ def _list_params(self, params):
+ """Create a human readable parameter list."""
+ return ', '.join('$%d=%r' % (n, v) for n, v in enumerate(params, 1))
+
@staticmethod
def _prepare_qualified_param(name, param):
"""Quote parameter representing a qualified name.
@@ -660,7 +664,8 @@
q = self.db.query(q, params)
res = q.dictresult()
if not res:
- raise _db_error('No such record in %s where %s' % (table, where))
+ raise _db_error('No such record in %s\nwhere %s\nwith %s' % (
+ table, where, self._list_params(params)))
for n, value in res[0].items():
if n == 'oid':
n = qoid
Modified: trunk/tests/test_classic_dbwrapper.py
==============================================================================
--- trunk/tests/test_classic_dbwrapper.py Thu Jan 14 11:01:34 2016
(r742)
+++ trunk/tests/test_classic_dbwrapper.py Thu Jan 14 12:32:01 2016
(r743)
@@ -816,6 +816,45 @@
self.assertIn('v4', r)
self.assertEqual(r['v4'], 'abc4')
+ def testGetLittleBobbyTables(self):
+ get = self.db.get
+ query = self.db.query
+ query("drop table if exists test_students")
+ query("create table test_students (firstname varchar primary key,"
+ " nickname varchar, grade char(2))")
+ query("insert into test_students values ("
+ "'D''Arcy', 'Darcey', 'A+')")
+ query("insert into test_students values ("
+ "'Sheldon', 'Moonpie', 'A+')")
+ query("insert into test_students values ("
+ "'Robert', 'Little Bobby Tables', 'D-')")
+ r = get('test_students', 'Sheldon')
+ self.assertEqual(r, dict(
+ firstname="Sheldon", nickname='Moonpie', grade='A+'))
+ r = get('test_students', 'Robert')
+ self.assertEqual(r, dict(
+ firstname="Robert", nickname='Little Bobby Tables', grade='D-'))
+ r = get('test_students', "D'Arcy")
+ self.assertEqual(r, dict(
+ firstname="D'Arcy", nickname='Darcey', grade='A+'))
+ try:
+ get('test_students', "D' Arcy")
+ except pg.DatabaseError as error:
+ self.assertEqual(str(error),
+ 'No such record in test_students\nwhere "firstname" = $1\n'
+ 'with $1="D\' Arcy"')
+ try:
+ get('test_students', "Robert'); TRUNCATE TABLE test_students;--")
+ except pg.DatabaseError as error:
+ self.assertEqual(str(error),
+ 'No such record in test_students\nwhere "firstname" = $1\n'
+ 'with $1="Robert\'); TRUNCATE TABLE test_students;--"')
+ q = "select * from test_students order by 1 limit 4"
+ r = query(q).getresult()
+ self.assertEqual(len(r), 3)
+ self.assertEqual(r[1][2], 'D-')
+ query('drop table test_students')
+
def testInsert(self):
insert = self.db.insert
query = self.db.query
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
