Brackets in Insert-statements causes internal error

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Insert a new row in a table, with a column that has a bracket "{" in it

Example: 
 - Database.Insert("MyTable", new[] { "MyValue" }, new[] { "... { ..." });

Migrator version: 0.8.0.31860
.NET version: 3.5

-- Stack trace:

System.FormatException: Input string was not in a correct format.
   at System.Text.StringBuilder.FormatError()
   at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, 
String fo
rmat, Object[] args)
   at System.String.Format(IFormatProvider provider, String format, Object
[] arg
s)
   at NAnt.Core.Task.Log(Level messageLevel, String message, Object[] 
args) in d
:\Source\nant-20080210T102007Z\src\NAnt.Core\Task.cs:line 282
   at Migrator.NAnt.Loggers.TaskLogger.Trace(String format, Object[] args) 
in e:
\MigratorDotNet\src\Migrator.NAnt\Loggers\TaskLogger.cs:line 127
   at Migrator.Providers.TransformationProvider.ExecuteNonQuery(String 
sql) in e
:\MigratorDotNet\src\Migrator.Providers\TransformationProvider.cs:line 551
   at Migrator.Providers.TransformationProvider.Insert(String table, String
[] co
lumns, String[] values) in 
e:\MigratorDotNet\src\Migrator.Providers\Transformati
onProvider.cs:line 657

---

Thanks in advance,

Best Regards

Brian Holmgård Kristensen

Original issue reported on code.google.com by bhk@yourhost.dk on 8 Oct 2008 at 1:21

[GoogleCodeExporter]

This is a problem with string.format.
To fix it temporarily, please use a touble brace like {{ or }} instead of a 
single
one in column name.

There should be a sanitization process for table, column name. I might look at
implementing that along the way.

Original comment by dip...@gmail.com on 10 Oct 2008 at 11:52

[GoogleCodeExporter]

i can't recreate the string.format exception, all i get is an sql exception, 
can you
post the code that does this?

Original comment by dip...@gmail.com on 10 Oct 2008 at 12:28

[GoogleCodeExporter]

Thanks for your quick response.

About doubling the bracket; I already tried it, but the result was that the 
value in 
the database column had double brackets as well.

Example:

Database.Insert("MyTable", new[] { "MyValue" }, new[] { "... {{ ..." });

Result in db-column: "... {{ ...".

I'll get back to you once I get the code from my workstation at work.

/Brian

Original comment by bhk@yourhost.dk on 10 Oct 2008 at 3:53

[GoogleCodeExporter]

Here is the specific code that fails:

Database.Insert("Merchants", new[] { "MerchantNumber", "AuthKey1", "AuthKey2" 
}, new
[] {
 "Number", "oaMu!-[x]zP,_r@+M1^O123Mg(lLzI4Q", "]c1232_DTNpQULR}LZNk$dcBU1Fq+JIF" });

Best Regards,

Brian Holmgård Kristensen

Original comment by bhk@yourhost.dk on 13 Oct 2008 at 6:23

[GoogleCodeExporter]

And by the way, when I write brackets, I mean curly brackets or braces :-) 

Sorry about that.

/Brian

Original comment by bhk@yourhost.dk on 13 Oct 2008 at 10:02

[GoogleCodeExporter]

i've just ran your test code on the latest version from the trunk it seems to 
pass.
however when i ran it without your code example, i was thinking you were using a
brace "{" inside the _column_ name. 
From what i've seen there is no escaping of the column name, and that gets an 
exception. 

to address the latter problem you can wrap your column in brackets: [col{mn]
im wondering why its not escaped in the firstplace, is there a disadvantage to 
escape
(put inside [] ) all columns as a default?

Original comment by dip...@gmail.com on 31 Oct 2008 at 10:13

[GoogleCodeExporter]

I think some provides escape column names differently, but it would be nice to
otherwise have a helper function to escape column/table names in the databases
preferred format. I could submit a patch that provides this functionality and 
update
the providers to use it... if there's interest.

Original comment by james.sa...@gmail.com on 8 Mar 2009 at 9:30

[GoogleCodeExporter]

I just ran into this myself, not with column names but with the values being 
updated or inserted.

The problem is when the framework goes to log the SQL statement being executed 
in the Execute* methods in TransformationProvider. So if my query is inserting 
a value such as a JSON object, like: '{"field": "value"}', the migrations will 
fail because the query is being ultimately passed to String.Format and 
String.Format doesn't like extra brackets.

Doubling up on brackets doesn't work because while it will get past the 
logging, when the statement ends up being executed the double brackets are 
still there in the SQL and will get inserted into the database.

Anyway, the attached patch solved my particular issue, though I am sure some of 
the other methods in TransformationProvider that log SQL or send SQL through 
String.Format will need to be updated.

Thanks for a great library!

rusty

Original comment by russell....@gmail.com on 20 Jan 2011 at 2:24

Attachments:

• sql-logging.patch