[Modeling-cvs] SF.net SVN: modeling: [995] trunk/ProjectModeling

[<sbi...@us...>]

Revision: 995
Author:   sbigaret
Date:     2006-04-19 13:30:59 -0700 (Wed, 19 Apr 2006)
ViewCVS:  http://svn.sourceforge.net/modeling/?rev=995&view=rev

Log Message:
-----------
Fixed bug #855257 and #918092: objects w/ string values containing simple quotes, and fetch requests containing simple quotes, were not properly handled by the SQLite and Oracle adaptor layers

Modified Paths:
--------------
    trunk/ProjectModeling/CHANGES
    trunk/ProjectModeling/Modeling/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py
    trunk/ProjectModeling/Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py
    trunk/ProjectModeling/Modeling/SQLExpression.py
    trunk/ProjectModeling/Modeling/tests/test_EditingContext_Global.py
Modified: trunk/ProjectModeling/CHANGES
===================================================================
--- trunk/ProjectModeling/CHANGES	2006-04-19 19:39:18 UTC (rev 994)
+++ trunk/ProjectModeling/CHANGES	2006-04-19 20:30:59 UTC (rev 995)
@@ -7,8 +7,14 @@
  ** Distributed under a 3-clause BSD-style license, see LICENSE for details **
  -----------------------------------------------------------------------------
 
+  * Fixed bug #855257 and #918092: objects w/ string values containing simple
+    quotes, and fetch requests containing simple quotes, were not properly
+    handled by the SQLite and Oracle adaptor layers (they were escaped with
+    backslashes, they are now replaced by two consecutive single quotes,
+    following the SQL standard)
+
   * Fixed bug #1471992: objects w/ values containing a single backslash are
-    not properly handled by the MySQL and Postgresql adaptor layer (the
+    not properly handled by the MySQL and Postgresql adaptor layers (the
     generated SQLExpression are incorrect; for example, insertions of such
     objects failed)
 

Modified: trunk/ProjectModeling/Modeling/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py
===================================================================
--- trunk/ProjectModeling/Modeling/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py	2006-04-19 19:39:18 UTC (rev 994)
+++ trunk/ProjectModeling/Modeling/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py	2006-04-19 20:30:59 UTC (rev 995)
@@ -238,8 +238,9 @@
   def sqlStringForString(self, aString):
     """
     Formats 'aString' and returns the string suitable for inclusion in a SQL
-    statement. SQLExpression's implementation surrounds the string with
-    simple quotes, and back-quotes any simple quotes 'aString' may have.
+    statement. This method surrounds the string with simple quotes, so, every
+    simple quote in 'aString' is transformed into a sequence a two simple
+    quotes. Single backslashes are escaped as well.
     It returns 'NULL' if 'aString' is None.
 
     See also: formatValueForAttribute()
@@ -248,7 +249,7 @@
       # quote the string:  interprets the strings
       aString = repr(aString)[1:-1]
       from Modeling.SQLExpression import escapeQuote
-      str=escapeQuote.sub("\\'", aString)
+      str=escapeQuote.sub("''", aString)
       return "'"+str+"'"
     else:
       return 'NULL'

Modified: trunk/ProjectModeling/Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py
===================================================================
--- trunk/ProjectModeling/Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py	2006-04-19 19:39:18 UTC (rev 994)
+++ trunk/ProjectModeling/Modeling/DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py	2006-04-19 20:30:59 UTC (rev 995)
@@ -116,8 +116,9 @@
   def sqlStringForString(self, aString):
     """
     Formats 'aString' and returns the string suitable for inclusion in a SQL
-    statement. SQLExpression's implementation surrounds the string with
-    simple quotes, and back-quotes any simple quotes 'aString' may have.
+    statement. This method surrounds the string with simple quotes, so, every
+    simple quote in 'aString' is transformed into a sequence a two simple
+    quotes. Single backslashes are escaped as well.
     It returns 'NULL' if 'aString' is None.
 
     See also: formatValueForAttribute()
@@ -126,7 +127,7 @@
       # quote the string: postgresql interprets the strings
       aString = repr(aString)[1:-1]
       from Modeling.SQLExpression import escapeQuote
-      str=escapeQuote.sub("\\'", aString)
+      str=escapeQuote.sub("''", aString)
       return "'"+str+"'"
     else:
       return 'NULL'

Modified: trunk/ProjectModeling/Modeling/SQLExpression.py
===================================================================
--- trunk/ProjectModeling/Modeling/SQLExpression.py	2006-04-19 19:39:18 UTC (rev 994)
+++ trunk/ProjectModeling/Modeling/SQLExpression.py	2006-04-19 20:30:59 UTC (rev 995)
@@ -1387,14 +1387,15 @@
   def sqlStringForString(self, aString):
     """
     Formats 'aString' and returns the string suitable for inclusion in a SQL
-    statement. SQLExpression's implementation surrounds the string with
-    simple quotes, and back-quotes any simple quotes 'aString' may have.
+    statement. This method surrounds the string with simple quotes, so, every
+    simple quote in 'aString' is transformed into a sequence a two simple
+    quotes.
     It returns 'NULL' if 'aString' is None.
 
     See also: formatValueForAttribute()
     """
     if aString is not None:
-      str=escapeQuote.sub("\\'", aString)
+      str=escapeQuote.sub("''", aString)
       return "'"+str+"'"
     else:
       return 'NULL'

Modified: trunk/ProjectModeling/Modeling/tests/test_EditingContext_Global.py
===================================================================
--- trunk/ProjectModeling/Modeling/tests/test_EditingContext_Global.py	2006-04-19 19:39:18 UTC (rev 994)
+++ trunk/ProjectModeling/Modeling/tests/test_EditingContext_Global.py	2006-04-19 20:30:59 UTC (rev 995)
@@ -1425,16 +1425,19 @@
     ec.insert(b1); ec.insert(b2); ec.insert(b3); ec.insert(b4)
     ec.saveChanges()
 
+    ec=EditingContext()
     res=ec.fetch('Book', 'title == "abc\\n"')
     self.assertEqual(len(res), 1)
     self.assertEqual(res[0].getTitle(), r"abc\n")
     self.assertEqual(res[0].getPrice(), 1)
 
+    ec=EditingContext()
     res=ec.fetch('Book', 'title == "abc\n"')
     self.assertEqual(len(res), 1)
     self.assertEqual(res[0].getTitle(), "abc\n")
     self.assertEqual(res[0].getPrice(), 2)
 
+    ec=EditingContext()
     res=ec.fetch('Book', 'title == "\\"')
     self.assertEqual(len(res), 1)
     self.assertEqual(res[0].getTitle(), "\\")
@@ -1444,6 +1447,37 @@
     #self.assertEqual(res[0].getTitle(), "abc%d")
     # TBD: with LIKE and ILIKE
     
+  def test_31_insert_and_fetch_single_quotes(self):
+    "[EditingContext] insert and fetch single quotes"
+    # bug #855257 and #918092
+    ec=EditingContext()
+    b1=Book(); b1.setPrice(1.0); b1.setTitle("ab'c")
+    b2=Book(); b2.setPrice(2.0); b2.setTitle("'")
+    b3=Book(); b3.setPrice(3.0); b3.setTitle("ab''c")
+    ec.insert(b1); ec.insert(b2); ec.insert(b3)
+    ec.saveChanges() # should not fail
+
+    ec=EditingContext()
+    res=ec.fetch('Book', 'title == "ab\'\'c"')
+    self.assertEqual(len(res), 1)
+    self.assertEqual(res[0].getTitle(), "ab''c")
+    self.assertEqual(res[0].getPrice(), 3)
+
+    ec=EditingContext()
+    res=ec.fetch('Book', 'title == "ab\'c"')
+    self.assertEqual(len(res), 1)
+    self.assertEqual(res[0].getTitle(), "ab'c")
+    self.assertEqual(res[0].getPrice(), 1)
+
+    res=ec.fetch('Book', 'title == "\'"')
+    self.assertEqual(len(res), 1)
+    self.assertEqual(res[0].getTitle(), "'")
+    self.assertEqual(res[0].getPrice(), 2)
+
+    ec=EditingContext()
+    res=ec.fetch('Book', '''title == "ab''''c"''')
+    self.assertEqual(len(res), 0)
+
   def test_999_customSQLQuery(self):
     "[EditingContext] custom SQL Query"
     fs=FetchSpecification(entityName='Writer')


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.