MySQL Bugs: #36205: .net connector should provide means to manually escape strings

Bug #36205	.net connector should provide means to manually escape strings
Submitted:	18 Apr 2008 10:29	Modified:	25 Jul 2008 11:07
Reporter:	Jeremy Morton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	5.2.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	manual escape string net connector

Description:
Normally, to insert arguments into an SQL statement to be sent to a MySqlCommand, the easiest and safest way to get the .net connector to escape necessary values for you is to use placeholders for any values you wish to be escaped in the statement, then use cmd.Parameters.AddWithValue() to add the values you wish to be escaped, later.  This is fine and dandy for simple string or integer arguments, but there may be times when you don't want this and you want direct access to the .net connector's "string escaping" functionality without having to directly add that string to the MySqlCommand's Paramters list in this way.

For example, I'm constructing a statement like "... WHERE name IN ('joe','john','mike')...".  To construct the ('joe','john','mike') value, I'm not aware of any way to get the MySql .net connector to do this right now, because passing the string ('joe','john','mike') to the cmd.Parameters.AddWithValue() method will simple treat the whole thing as a string to be escaped, rather than a list of strings.  It will escape the quote characters around the names, which you don't want it to do.  Nor will passing a List<> to the AddWithValue() method work, either.

How to repeat:
Try to correctly quote a list of values to be put in an SQL statement, using the .net connector.

Suggested fix:
I propose making a string escape function available to the programmer directly... something like a static method such as MySqlCommand.EscapeString().  That way, you can enumerate through the List<> of names (joe, john, and mike) and for each one, get the returned value from MySqlCommand.EscapeString() (which would be 'joe', 'john', and 'mike'.  You could then manually construct the list string, and use that string in the SQL statement.

edit.....

sorry, ignore the above, i was just testing the edit feature. :-)

One small comment to add to the above... the MySqlCommand.EscapeString() function would simply escape the string in exactly the same way as it would be escaped when you add it as a parameter using the .AddWithValue() method, and would return the escaped string.

Jeremy, thanks for reasonable feature request.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50225

Fixed in 5.2.3

An entry has been added to the 5.2.3 changelog:

String escaping functionality has been moved from the MySqlString class to the MySqlHelper class, where it can be accessed by the EscapeString method.