Re: sql escaping

["Aaron Bockover" <abockover novell com>]

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