Wednesday, March 21, 2012

further informations

Just for the record:
- the statements are viewed exactly before execution with an unicode aware
application and they contain the right unicodes.
- the db columns supposed to store the unicodes are defined as nvarchar
- the db viewer (SQL Query Analizer) is also unicode aware. It shows right
unicodes for values inserted with "preparedStatements" but ''?...' for
those inserted with plain statements.
?Hint?
In statements we don't use the N prefix for the unicode values. F.ex.:
we do:
UPDATE Table1 SET Col1 = 'New Text' WHERE id = 1
not:
UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
Is there a way to eliminate the necessity of using N by means of DB/SQL
Server settings?
Many thanks in advance.I think the '? show up because you insert non-unicode strings, and
there's a mismatch between the code pages of the process that changes the
data and the process that reads the data.
The ideal way to fix this would be to stop generating UPDATE statements, if
you can't change your code like that then you need to insert the values as
Unicode.
As a side note, you'll want to pay attention to injection attacks.
In the statement below:
UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
If the value of 'Nex Text' comes from the user then you need to escape it
with QUOTENAME
UPDATE Table1 SET Col1 = QUOTENAME(N'New Text','''') WHERE id = 1
Otherwise it can be injected with >>>>New Text' do_a_bad_thing_here --<<<<
UPDATE Table1 SET Col1 = N'New Text' do_a_bad_thing_here -- WHERE id = 1
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cristian Senchiu" <Cristian Senchiu@.discussions.microsoft.com> wrote in
message news:88F7BF72-A378-4A4D-9D42-E2F9F98C697A@.microsoft.com...
> Just for the record:
> - the statements are viewed exactly before execution with an unicode aware
> application and they contain the right unicodes.
> - the db columns supposed to store the unicodes are defined as nvarchar
> - the db viewer (SQL Query Analizer) is also unicode aware. It shows right
> unicodes for values inserted with "preparedStatements" but ''?...' for
> those inserted with plain statements.
> ?Hint?
> In statements we don't use the N prefix for the unicode values. F.ex.:
> we do:
> UPDATE Table1 SET Col1 = 'New Text' WHERE id = 1
> not:
> UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
> Is there a way to eliminate the necessity of using N by means of DB/SQL
> Server settings?
> Many thanks in advance.

No comments:

Post a Comment