Re: sql escaping

["Aaron Bockover" <abockover novell com>]

Xavier's patch looks fine, it essentially expands to the longer snippet
I originally posted. Regarding sqlite2, all I know is that in Banshee we
have required sqlite3 since the start of the project (or somewhere close
to there). 

--Aaron


On Thu, 2007-09-06 at 10:26 +0200, Stephane Delcroix wrote:
> Aaron,
> 
> Please note that, even though using Parameters solves quoting and sql
> injections issues, it miserably fails on sqlite2 due to an error in
> SqliteClient.dll. See http://bugzilla.gnome.org/show_bug.cgi?id=467011
> for more info.
> 
> regards
> 
> s
> 
> On Wed, 2007-09-05 at 18:09 -0600, Aaron Bockover wrote:
> > Using String.Format to construct a query with parameters is very
> > incorrect, for this very reason (and often if escaping is not done
> > properly, it can lead to security issues). While in applications like
> > F-Spot and Banshee, using String.Format is okay in many situations,
> > using the following method of command construction is critical for when
> > strings are parameters (as you have just run into).
> > 
> > The correct way to create this command is like this:
> > 
> > ...
> > IDbCommand command = connection.CreateCommand();
> > command.CommandText = "INSERT INTO photo_versions (photo_id, version_id,
> > name, uri) VALUES (:photo_id, :version_id, :name, :uri)";
> > 
> > IDbDataParameter param = command.CreateParameter();
> > param.ParameterName = "photo_id";
> > param.Value = Convert.ToUInt32(reader[0]);
> > command.Parameters.Add(param);
> > 
> > param = command.CreateParameter();
> > param.ParameterName = "version_id";
> > param.Value = Convert.ToUInt32(reader[1]);
> > command.Parameters.Add(param);
> > 
> > param = command.CreateParameter();
> > param.ParameterName = "name";
> > param.Value = (string)reader[2];
> > command.Parameters.Add(param);
> > 
> > param = command.CreateParameter();
> > param.ParameterName = "uri";
> > param.Value = uri;
> > command.Parameters.Add(param);
> > ...
> > 
> > Granted, doing that manually is highly annoying. In Banshee I have a
> > utility layer to make constructing proper parameter commands much
> > simpler. Either way, this should solve the escaping issue.
> > 
> > --Aaron
> > 
> > 
> > On Wed, 2007-09-05 at 23:35 +0200, Xavier Bouchoux wrote:
> > > hi,
> > > 
> > > I'm having a crash with latest svn, when Updating database from version 
> > > 7 to 8:
> > > > Unhandled Exception: Mono.Data.SqliteClient.SqliteSyntaxException: near "image": syntax error
> > > >   at Mono.Data.SqliteClient.SqliteCommand.GetNextStatement (IntPtr pzStart, System.IntPtr& pzTail, System.IntPtr& pStmt) [0x00000] 
> > > >   at Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior, Boolean want_results, System.Int32& rows_affected) [0x00000] 
> > > >   at Mono.Data.SqliteClient.SqliteCommand.ExecuteNonQuery () [0x00000] 
> > > >   at Banshee.Database.QueuedSqliteCommand.Execute () [0x00000] 
> > > 
> > > 
> > > I tracked it back to this statement:
> > > 
> > > > string statement = String.Format ("INSERT INTO photo_versions (photo_id, version_id, name, uri) " + 
> > > > 		"VALUES ({0}, {1}, '{2}', '{3}')",
> > > > 		Convert.ToUInt32 (reader [0]),
> > > > 		Convert.ToUInt32 (reader [1]),
> > > > 		(string)(reader [2]),
> > > > 		uri);
> > > 
> > > 
> > > with version_id = "Modifié dans Éditeur d'image GIMP" (notice the "'")...
> > > 
> > > so I was wondering if there was a String.Format() look-alike, but that 
> > > did sql escaping of the arguments, and/or what is the policy in f-spot 
> > > for this.
> > > 
> > > xavier
> > > _______________________________________________
> > > F-spot-list mailing list
> > > F-spot-list gnome org
> > > http://mail.gnome.org/mailman/listinfo/f-spot-list
> > 
> >  Novell, Inc. 
> > Software for the Open Enterprise™
> > http://www.novell.com
> > _______________________________________________
> > F-spot-list mailing list
> > F-spot-list gnome org
> > http://mail.gnome.org/mailman/listinfo/f-spot-list
> 

 Novell, Inc. 
Software for the Open Enterprise™
http://www.novell.com