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 12:29	Modified:	25 Jul 2008 13:07
Reporter:	Jeremy Morton
Status:	Closed
Category:	Connector/Net	Severity:	S4 (Feature request)
Version:	5.2.1.0	OS:	Any
Assigned to:		Target Version:
Tags:	manual escape string net connector
Triage:	D4 (Minor)

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.