OperationalError: near "13": syntax error

[Douglas Marquardt]

After installing this plugin, I got the attached error when I click at Roadmaps.

[Douglas Marquardt]

I can't add any comment here. ​http://trac-hacks.org/ is giving me an error.

[Douglas Marquardt]

Error page content when clicking Roadmap

[Douglas Marquardt]

Error attached to the ticket.

[Ryan J Ollos]

Replying to douglasmarquardt@gmail.com:

I can't add any comment here. ​http://trac-hacks.org/ is giving me an error.

What is the error? Was it rejecting the content as spam? That is a known problem that we can't do much about atm, but it might help if you register for an account.

[falkb]

Your file reports a syntax error which is quite strange. I also have Trac 1.0 with SQLite and it works pretty well. What does that "13" mean in your error message OperationalError: near "13": syntax error ? Have you accidentally edited the file and inserted 13?

[Douglas Marquardt]

Replying to rjollos:

Replying to douglasmarquardt@gmail.com:

I can't add any comment here. ​http://trac-hacks.org/ is giving me an error.

What is the error? Was it rejecting the content as spam? That is a known problem that we can't do much about atm, but it might help if you register for an account.

I could add comments and attachments after that. I'm sorry, but I didn't save the error I got. The ​http://trac-hacks.org is working now.

[Douglas Marquardt]

Replying to falkb:

Your file reports a syntax error which is quite strange. I also have Trac 1.0 with SQLite and it works pretty well. What does that "13" mean in your error message OperationalError: near "13": syntax error ? Have you accidentally edited the file and inserted 13?

No, I didn't edited any file. I don't use to customize any Trac and/or plugin code. I don't know what that mean either. One detail is that if disable the "SmpRoadmapProject — Groups milestones by 'Project'" checkbox at the plugins' admin page, I can load the Roadmap page successfully. However, the unchecked feature is not being presented of course.

[anonymous]

Replying to douglasmarquardt@gmail.com:

I could add comments and attachments after that. I'm sorry, but I didn't save the error I got.

It was probably the common "database is locked error" then. That's another that doesn't have a quick fix, but usually the problem will go away after a short wait.

[falkb]

Can you open trac.db with Sqlitedatabasebrowser and tell me if table smp_milestone_project exists? What is the sources of your "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 48? A click on the callstack should tell you.

[Douglas Marquardt]

The table exists. I used the Linux sqlite3 program to open the database and check the smp_milestone_project existence. It's there.

sdadm:/trac_root/db# sqlite3 trac.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .schema smp_milestone_project
CREATE TABLE smp_milestone_project (
    id integer PRIMARY KEY,
    milestone varchar(255),
    id_project integer
);

Please find below what it has at the line 48

43	    sqlite_version_string = sqlite.sqlite_version
44	 
45	    class PyFormatCursor(sqlite.Cursor):
46	        def _rollback_on_error(self, function, *args, **kwargs):
47	            try:
48	                return function(self, *args, **kwargs)
49	            except sqlite.DatabaseError:
50	                self.cnx.rollback()
51	                raise
52	        def execute(self, sql, args=None):
53	            if args:

[falkb]

no idea anymore :( Is milestone that goes into function get_project_milestone() maybe None here? Can you check this?

[falkb]

or is 13 probably the name of the milestone or of the project?

[Jun Omae]

It seems that the plugin has SQL injection. When the milestone name is Project'13, that issue probably will happen. We must not use string-formatting. See ​t:wiki:TracDev/DatabaseApi#Parameterpassing.

The example is the following. The other methods in model.py have still the same issue.

simplemultiproject/model.py

@@ -208 +208 @@
                         smp_project AS p,
                         smp_milestone_project AS m
                    WHERE
-                        m.milestone='%s' and
-                        m.id_project = p.id_project""" % (milestone)
+                        m.milestone=%s and
+                        m.id_project = p.id_project"""
 
-        cursor.execute(query)
+        cursor.execute(query, [milestone])
         return cursor.fetchone()
 
     def get_id_project_milestone(self,milestone):

[Ryan J Ollos]

Jun's patch looks good to me.

[falkb]

again I've learnt a lot, thanks for the review! I'm gonna patch it on Monday... stay tuned

[falkb]

(In [12659]) bugfix (refs #10890): removed possibility of SQL injections; plugin likely can stand project names like Project'13 now, thanks to jun66j5

[falkb]

@Douglas: please, test! @jun66j5+rjollos: please, review! TIA

[Douglas Marquardt]

Replying to falkb:

or is 13 probably the name of the milestone or of the project?

Yes, there are milestones with the current year out there, like "SD/DECS June'13 Release IT1"

[Douglas Marquardt]

Replying to falkb:

@Douglas: please, test! @jun66j5+rjollos: please, review! TIA

I've never applied a patch before. Can you teach me?

[falkb]

You just have to download the latest version, or update from SVN. I've committed the patch already.

[Douglas Marquardt]

I downloaded the latest version and updated my Trac instance. All is working fine now. Thank you very much for all the help.

[falkb]

You're welcome. It's another piece in the puzzle of stability. Kudos to jun66j5.

[falkb]

(In [12662]) bugfix (refs #10890): further testing revealed [12659] was not fully water-proofed, this should fix the project_id SQL argument (dougbm, please update again)