SqlConnection and transfer encoding

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi...

We're in the process of trying to internationalize our databases, converting
a lot of columns to nvarchar and the like. We just came across an odd
phenomenon with ado.net; that is passing parameters of type string get
processed correctly but executing strings of sql with literal appear to be
getting mangled/accents stripped before it gets to the sql server side.

We wrote a little sample - a db with a VARCHAR(50) column and an
NVARCHAR(50) column, a little sproc that just does an insert and 2 calls:

SqlCommand scmd = new SqlCommand("TESTINSERT", sx);
SqlParameter sqp;
sqp = new SqlParameter("@VC", "1ĞŞİışğ"); // gets accents stripped
scmd.Parameters.Add(sqp);
sqp = new SqlParameter("@NC", "1ĞŞİışğ"); // preserves accents
scmd.Parameters.Add(sqp);
scmd.CommandType = System.Data.CommandType.StoredProcedure;
scmd.ExecuteNonQuery();

scmd = new SqlCommand("TESTINSERT '2ĞŞİışğ', '2ĞŞİışğ'", sx);
scmd.ExecuteNonQuery(); // both columns have all accents stripped

Is there any way to control how ADO.Net is going to treat the literals?

Thanks
Mark
 
Hi Mark,

..net is unicode and thus it doesn't have the problem with accents.
As per your last example - do use parameters with NVARCHAR and you won't
have problems. Probably.
Is there a particular need to avoid parameters besides allowing sql
injection attacks?
 
It's not .net that has the problem with the accents but apparently it is the
ado.net infrastructure that has problems serializing literals.

Connecting to the db with Management Studio, I can see the connection
properties indicate the connection has a latin1 collation. Kind of apples
and oranges, I know but I didn't see any properties on the SqlConnection
object to set the transfer collation. In the past I've worked with the MySql
..net classes and they did expose that if I recall correctly.

In terms of our existing code base and bringing everything into line, the
big problem with using parameters is that SqlClient doesn't support
positional parameters, like Odbc. So either I have to convert all the uses
over to odbc (a substantial effort) or go find all the sprocs corresponding
to all of the calls in the code, figure out what all the parameter names are,
and rework all the query calls to name the parameters explicitly (also a
significant effort).

I was hoping there would be some control over the transfer encoding
somewhere to get the literals handled properly.

Thanks
Mark

Miha Markic said:
Hi Mark,

..net is unicode and thus it doesn't have the problem with accents.
As per your last example - do use parameters with NVARCHAR and you won't
have problems. Probably.
Is there a particular need to avoid parameters besides allowing sql
injection attacks?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: blog.rthand.com


Mark said:
Hi...

We're in the process of trying to internationalize our databases,
converting
a lot of columns to nvarchar and the like. We just came across an odd
phenomenon with ado.net; that is passing parameters of type string get
processed correctly but executing strings of sql with literal appear to be
getting mangled/accents stripped before it gets to the sql server side.

We wrote a little sample - a db with a VARCHAR(50) column and an
NVARCHAR(50) column, a little sproc that just does an insert and 2 calls:

SqlCommand scmd = new SqlCommand("TESTINSERT", sx);
SqlParameter sqp;
sqp = new SqlParameter("@VC", "1ĞŞİışğ"); // gets accents stripped
scmd.Parameters.Add(sqp);
sqp = new SqlParameter("@NC", "1ĞŞİışğ"); // preserves accents
scmd.Parameters.Add(sqp);
scmd.CommandType = System.Data.CommandType.StoredProcedure;
scmd.ExecuteNonQuery();

scmd = new SqlCommand("TESTINSERT '2ĞŞİışğ', '2ĞŞİışğ'", sx);
scmd.ExecuteNonQuery(); // both columns have all accents stripped

Is there any way to control how ADO.Net is going to treat the literals?

Thanks
Mark
Click to expand...
Click to expand...
 
Mark said:
It's not .net that has the problem with the accents but apparently it is
the
ado.net infrastructure that has problems serializing literals.

Connecting to the db with Management Studio, I can see the connection
properties indicate the connection has a latin1 collation. Kind of apples
and oranges, I know but I didn't see any properties on the SqlConnection
object to set the transfer collation. In the past I've worked with the
MySql
.net classes and they did expose that if I recall correctly.
Click to expand...

Not sure about this.
In terms of our existing code base and bringing everything into line, the
big problem with using parameters is that SqlClient doesn't support
positional parameters, like Odbc. So either I have to convert all the
uses
over to odbc (a substantial effort) or go find all the sprocs
corresponding
to all of the calls in the code, figure out what all the parameter names
are,
and rework all the query calls to name the parameters explicitly (also a
significant effort).
Click to expand...

So you are willing to be open to sql injection attacks? Good luck with that,
I hope you don't work for military ;-)

Anyway, converting to paramterized commands shouldn't be that difficult -
you may combine template based code generation (i.e. CodeSmith) with some
runtime helper methods and do much of the process automatic.
I was hoping there would be some control over the transfer encoding
somewhere to get the literals handled properly.
Click to expand...

Perhaps somebody else will...
 
Hello Mark,

I use the SQL Profiler to monitor the SQL request. I find that the Unicode
character is sent to the server side correctly indeed. Actually, the real
problem here is we need to use N before the string to let the SQL Server to
know it is a Unicode string.

I have tried the followings in SQL Server Express 2005. It works fine.
Please let me know if it resolves your issue.

SqlConnection con = new SqlConnection(@"Data
Source=.\SQLEXPRESS;Initial Catalog=School;Integrated Security=True");
con.Open();
SqlCommand com = new SqlCommand(@"TestInsert N'²âÊÔ'", con);
com.ExecuteNonQuery();
con.Close();

Have a nice day!

Best regards,
Colbert Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
And Mark, by the way, I totally agree with Miha. I understand that may take
you a lot of manual work. But doing a literal string SQL query will
increase attacked possibility. Otherwise you need write you own codes to
check user input.

Best regards,
Colbert Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
As I said, I inherited a huge codebase of already-written sprocs and calls.
The belief here is that since pretty much all the calls are to sprocs that
sql injection is not an issue.

The annoyance in conversion, as I said, is that I either have to switch
everything over to odbc, where I can have positional parameters, or I have to
go dig up the sproc for every single call to get the parameters named
properly. When there are hundreds of calls and sprocs, that's a clerical
headache.

And I am still puzzled why ado.net doesn't a) serialize literals better
and/or b) doesn't expose some way to control the transfer encoding.

MySql allows you to specify the connection/transfer encoding in the
connection string.

Thanks
Mark
 
Thank you, Colbert. That's a very simple, quick work-around; it will be very
helpful.

I understand your and Miha's objection to using non-parameterized queries,
but as I say I inherited a large code base this way; it wouldn't have been my
first choice. If SqlClient supported positional parameters it would be much
quicker to fix.

My coworkers say that since pretty much all the queries are sproc executions
that sql injection doesn't apply, but it is still just a suspect practice...

Thank you again.
Mark
 
My coworkers say that since pretty much all the queries are sproc
executions
that sql injection doesn't apply, but it is still just a suspect
practice...
Click to expand...

It does, it does. It is the same - no difference. When you concatenate sql
commands with user input there is no good protection.
Say, you have this statement:
string.Format("exec myproc '{0}', {1}", a, b)

Immagine user passing
a="', 5; DROP TABLE PricelessRecords";
b = " --";
 
I mentioned the compound statement issue, but they brushed me off...

From my johnny-come-lately point of view, it would be a lot simpler to fix
if SqlClient supported positional parameters like odbc :)

Thanks
Mark
 
Back
Top