Re: sql escaping

[Stephane Delcroix <stephane delcroix org>]

Hi Aaron,

We also have your utility layer in f-spot, shamelessly stolen from
banshee ;)
We just aren't using it (yet) everywhere

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