Repository: web2py/pydal

Issue:
fix allowed table and field names
As discussed in web2py-developers (such as [here](https://groups.google.com/d/msg/web2py-developers/1r4bq-ZFFkY/fYNfGSjqKeUJ "discussion") ), we should avoid any table and field name that is not coherent with python identifiers. The cleanup() needs a revision and tests should be added to avoid regressions. We have rname for legacy and/or funny names. 

Discussion:

Patch:
diff --git a/CHANGES b/CHANGES
index d14a85c1..0bce22ec 100644
--- a/CHANGES
+++ b/CHANGES
@@ -13,7 +13,16 @@ Next
 - Implemented JSON serialization for objects
 - Refactored many internal objects to improve performance
 - Added python 3.x support (experimental)
-
+- New (and, as such, experimental) unicode handling in MSSQL
+  Use mssql4n:// or mssqn3n:// uris. NB: These adapters will probably 
+  become the de-facto standard for MSSQL handling. 
+  Any other adapter will continue to be supported just for legacy
+  databases
+- Restricted table and field names to a "dotted-notation-friendly" syntax,
+  meaning basically anything:
+   - alphanumeric
+   - not starting with underscore or an integer
+  you need to use the "rname" attribute for anything funnier than that
 
 Version 15.03
 -------------
diff --git a/pydal/helpers/regex.py b/pydal/helpers/regex.py
index 1139aaa4..c79094b6 100644
--- a/pydal/helpers/regex.py
+++ b/pydal/helpers/regex.py
@@ -10,7 +10,7 @@ REGEX_NO_GREEDY_ENTITY_NAME = r'(.+?)'
 REGEX_UPLOAD_PATTERN = re.compile('(?P<table>[\w\-]+)\.(?P<field>[\w\-]+)\.(?P<uuidkey>[\w\-]+)(\.(?P<name>\w+))?\.\w+$')
 REGEX_CLEANUP_FN = re.compile('[\'"\s;]+')
 REGEX_UNPACK = re.compile('(?<!\|)\|(?!\|)')
-REGEX_PYTHON_KEYWORDS = re.compile('^(and|del|from|not|while|as|elif|global|or|with|assert|else|if|pass|yield|break|except|import|print|class|exec|in|raise|continue|finally|is|return|def|for|lambda|try)$')
+REGEX_PYTHON_KEYWORDS = re.compile('^(and|del|from|not|while|as|elif|global|or|with|assert|else|if|pass|yield|break|except|import|print|class|exec|in|raise|continue|finally|is|return|def|for|lambda|try|False|True|nonlocal)$')
 REGEX_SELECT_AS_PARSER = re.compile("\s+AS\s+(\S+)")
 REGEX_CONST_STRING = re.compile('(\"[^\"]*?\")|(\'[^\']*?\')')
 REGEX_SEARCH_PATTERN = re.compile('^{[^\.]+\.[^\.]+(\.(lt|gt|le|ge|eq|ne|contains|startswith|year|month|day|hour|minute|second))?(\.not)?}$')
@@ -20,3 +20,4 @@ REGEX_QUOTES = re.compile("'[^']*'")
 REGEX_ALPHANUMERIC = re.compile('^[0-9a-zA-Z]\w*$')
 REGEX_PASSWORD = re.compile('\://([^:@]*)\:')
 REGEX_NOPASSWD = re.compile('\/\/[\w\.\-]+[\:\/](.+)(?=@)') # was '(?<=[\:\/])([^:@/]+)(?=@.+)'
+REGEX_VALID_TB_FLD = re.compile(r'^[^\d_][_0-9a-zA-Z]*\Z')
diff --git a/pydal/objects.py b/pydal/objects.py
index 7e5e18ca..28d20dde 100644
--- a/pydal/objects.py
+++ b/pydal/objects.py
@@ -19,7 +19,7 @@ from ._gae import Key
 from .exceptions import NotFoundException, NotAuthorizedException
 from .helpers.regex import REGEX_TABLE_DOT_FIELD, REGEX_ALPHANUMERIC, \
     REGEX_PYTHON_KEYWORDS, REGEX_STORE_PATTERN, REGEX_UPLOAD_PATTERN, \
-    REGEX_CLEANUP_FN
+    REGEX_CLEANUP_FN, REGEX_VALID_TB_FLD
 from .helpers.classes import Reference, MethodAdder, SQLCallableList, SQLALL, \
     Serializable, BasicStorage
 from .helpers.methods import list_represent, bar_decode_integer, \
@@ -205,8 +205,8 @@ class Table(Serializable, BasicStorage):
         self._actual = False  # set to True by define_table()
         self._db = db
         self._tablename = tablename
-        if (not isinstance(tablename, str) or tablename[0] == '_'
-            or hasattr(DAL, tablename) or '.' in tablename
+        if (not isinstance(tablename, str) or hasattr(DAL, tablename)
+            or not REGEX_VALID_TB_FLD.match(tablename)
             or REGEX_PYTHON_KEYWORDS.match(tablename)
             ):
             raise SyntaxError('Field: invalid table name: %s, '
@@ -1396,9 +1396,10 @@ class Field(Expression, Serializable):
             except UnicodeEncodeError:
                 raise SyntaxError('Field: invalid unicode field name')
         self.name = fieldname = cleanup(fieldname)
-        if not isinstance(fieldname, str) or hasattr(Table, fieldname) or \
-                fieldname[0] == '_' or '.' in fieldname or \
-                REGEX_PYTHON_KEYWORDS.match(fieldname):
+        if (not isinstance(fieldname, str) or hasattr(Table, fieldname)
+            or not REGEX_VALID_TB_FLD.match(fieldname)
+            or REGEX_PYTHON_KEYWORDS.match(fieldname)
+            ):
             raise SyntaxError('Field: invalid field name: %s, '
                               'use rname for "funny" names' % fieldname)
 


Test patch:
diff --git a/tests/sql.py b/tests/sql.py
index a422c6f1..7da2275b 100644
--- a/tests/sql.py
+++ b/tests/sql.py
@@ -52,6 +52,18 @@ def tearDownModule():
 class TestFields(unittest.TestCase):
 
     def testFieldName(self):
+        """
+        - a "str" something
+        - not a method or property of Table
+        - "dotted-notation" friendly:
+            - a valid python identifier
+            - not a python keyword
+            - not starting with underscore or an integer
+            - not containing dots
+        
+        Basically, anything alphanumeric, no symbols, only underscore as
+        punctuation
+        """
 
         # Check that Fields cannot start with underscores
         self.assertRaises(SyntaxError, Field, '_abc', 'string')
@@ -64,31 +76,48 @@ class TestFields(unittest.TestCase):
             self.assertRaises(SyntaxError, Field, x, 'string')
 
         # Check that Fields allows underscores in the body of a field name.
-        self.assert_(Field('a_bc', 'string'),
+        self.assertTrue(Field('a_bc', 'string'),
             "Field isn't allowing underscores in fieldnames.  It should.")
 
+        # Check that Field names don't allow a python keyword
+        self.assertRaises(SyntaxError, Field, 'True', 'string')
+        self.assertRaises(SyntaxError, Field, 'elif', 'string')
+        self.assertRaises(SyntaxError, Field, 'while', 'string')
+
+        # Check that Field names don't allow a non-valid python identifier
+        non_valid_examples = ["1x", "xx$%@%", "xx yy", "yy\na", "yy\n"]
+        for a in non_valid_examples:
+            self.assertRaises(SyntaxError, Field, a, 'string')
+
+        # Check that Field names don't allow a unicode string
+        non_valid_examples = non_valid_examples = ["ℙƴ☂ℌøἤ", u"ℙƴ☂ℌøἤ", 
+                u'àè', u'ṧøмℯ', u'тεṧт', u'♥αłüℯṧ', 
+                u'ℊεᾔ℮яαт℮∂', u'♭ƴ', u'ᾔ☤ρℌℓ☺ḓ']
+        for a in non_valid_examples:
+            self.assertRaises(SyntaxError, Field, a, 'string')
+
     def testFieldTypes(self):
 
         # Check that string, and password default length is 512
         for typ in ['string', 'password']:
-            self.assert_(Field('abc', typ).length == 512,
+            self.assertTrue(Field('abc', typ).length == 512,
                          "Default length for type '%s' is not 512 or 255" % typ)
 
         # Check that upload default length is 512
-        self.assert_(Field('abc', 'upload').length == 512,
+        self.assertTrue(Field('abc', 'upload').length == 512,
                      "Default length for type 'upload' is not 512")
 
         # Check that Tables passed in the type creates a reference
-        self.assert_(Field('abc', Table(None, 'temp')).type
+        self.assertTrue(Field('abc', Table(None, 'temp')).type
                       == 'reference temp',
                      'Passing an Table does not result in a reference type.')
 
     def testFieldLabels(self):
 
         # Check that a label is successfully built from the supplied fieldname
-        self.assert_(Field('abc', 'string').label == 'Abc',
+        self.assertTrue(Field('abc', 'string').label == 'Abc',
                      'Label built is incorrect')
-        self.assert_(Field('abc_def', 'string').label == 'Abc Def',
+        self.assertTrue(Field('abc_def', 'string').label == 'Abc Def',
                      'Label built is incorrect')
 
     def testFieldFormatters(self):  # Formatter should be called Validator
@@ -190,6 +219,18 @@ class TestFields(unittest.TestCase):
 class TestTables(unittest.TestCase):
 
     def testTableNames(self):
+        """
+        - a "str" something
+        - not a method or property of DAL
+        - "dotted-notation" friendly:
+            - a valid python identifier
+            - not a python keyword
+            - not starting with underscore or an integer
+            - not containing dots
+        
+        Basically, anything alphanumeric, no symbols, only underscore as
+        punctuation
+        """
 
         # Check that Tables cannot start with underscores
         self.assertRaises(SyntaxError, Table, None, '_abc')
@@ -202,9 +243,26 @@ class TestTables(unittest.TestCase):
             self.assertRaises(SyntaxError, Table, None, x)
 
         # Check that Table allows underscores in the body of a field name.
-        self.assert_(Table(None, 'a_bc'),
+        self.assertTrue(Table(None, 'a_bc'),
             "Table isn't allowing underscores in tablename.  It should.")
 
+        # Check that Table names don't allow a python keyword
+        self.assertRaises(SyntaxError, Table, None, 'True')
+        self.assertRaises(SyntaxError, Table, None, 'elif')
+        self.assertRaises(SyntaxError, Table, None, 'while')
+
+        # Check that Table names don't allow a non-valid python identifier
+        non_valid_examples = ["1x", "xx$%@%", "xx yy", "yy\na", "yy\n"]
+        for a in non_valid_examples:
+            self.assertRaises(SyntaxError, Table, None, a)
+
+        # Check that Table names don't allow a unicode string
+        non_valid_examples = ["ℙƴ☂ℌøἤ", u"ℙƴ☂ℌøἤ", 
+                u'àè', u'ṧøмℯ', u'тεṧт', u'♥αłüℯṧ', 
+                u'ℊεᾔ℮яαт℮∂', u'♭ƴ', u'ᾔ☤ρℌℓ☺ḓ']
+        for a in non_valid_examples:
+            self.assertRaises(SyntaxError, Table, None, a)
+
 
 class TestAll(unittest.TestCase):
 
@@ -230,12 +288,12 @@ class TestTable(unittest.TestCase):
 
         # Does it have the correct fields?
 
-        self.assert_(set(persons.fields).issuperset(set(['firstname',
+        self.assertTrue(set(persons.fields).issuperset(set(['firstname',
                                                          'lastname'])))
 
         # ALL is set correctly
 
-        self.assert_('persons.firstname, persons.lastname'
+        self.assertTrue('persons.firstname, persons.lastname'
                       in str(persons.ALL))
 
     def testTableAlias(self):
@@ -246,8 +304,8 @@ class TestTable(unittest.TestCase):
 
         # Are the different table instances with the same fields
 
-        self.assert_(persons is not aliens)
-        self.assert_(set(persons.fields) == set(aliens.fields))
+        self.assertTrue(persons is not aliens)
+        self.assertTrue(set(persons.fields) == set(aliens.fields))
         db.close()
 
     def testTableInheritance(self):
@@ -256,7 +314,7 @@ class TestTable(unittest.TestCase):
         customers = Table(None, 'customers',
                              Field('items_purchased', 'integer'),
                              persons)
-        self.assert_(set(customers.fields).issuperset(set(
+        self.assertTrue(set(customers.fields).issuperset(set(
             ['items_purchased', 'firstname', 'lastname'])))
 
 
@@ -1698,7 +1756,6 @@ class TestQuotesByDefault(unittest.TestCase):
     def testme(self):
         return
 
-
 class TestGis(unittest.TestCase):
 
     def testGeometry(self):
@@ -1897,7 +1954,7 @@ class TestRecordVersioning(unittest.TestCase):
         db.t0.insert(name='web2py2')
         db(db.t0.name == 'web2py2').delete()
         self.assertEqual(len(db(db.t0).select()), 1)
-        self.assertEquals(db(db.t0).count(), 1)
+        self.assertEqual(db(db.t0).count(), 1)
         db(db.t0.id == i_id).update(name='web2py3')
         self.assertEqual(len(db(db.t0).select()), 1)
         self.assertEqual(db(db.t0).count(), 1)