ä stored as ä value is encoded in database

Question

I write out a variable on the asp page:

name="ända"
response.write name

It shows ända on the page, good!

When inserting it into the database, the value written to the database is ända

The page is encoded with <%Response.charset="iso-8859-1"%>

How can I get this value ända to be written to the database?

<%Response.charset="iso-8859-1"%>

folderName=request.querystring("foretagsnamn")

         folderName = replace(folderName, "å" , "a")
         folderName = replace(folderName, "ä" , "a")
         folderName = replace(folderName, "ö" , "o")
         folderName = replace(folderName, "Å" , "a")
         folderName = replace(folderName, "Ä" , "a")
         folderName = replace(folderName, "Ö" , "o")
         folderName = LCase(folderName)
        response.write folderName

And then just a sql insert to the database.

sql="INSERT INTO users(folderName) VALUES('"&folderName&"');"
    conn.execute(sql)

Its a mySql database, classic asp.

The querystring comes from a creditcard payment service, and the strange thing is that when I perform a transaction and I resive the querystring, it is wrong, but if I then just update the page so it runs the code and querystring again, it is right!?

What database server are you using and what does the code look like that you're using to write to the db? — RQDQ, Feb 15 '11 at 18:08
At some point, your character is being converted into an HTML entity. Like Joe Phillips said, we need to see the code to figure out where/why. — Powerlord, Feb 15 '11 at 18:16
Is the column that you're putting the data into CHAR/VARCHAR or NCHAR/NVARCHAR? — RQDQ, Feb 15 '11 at 18:40
Ahh - you'll need to change it to NVARCHAR to be able to store that character (it takes two bytes to store the binary value which in this case is 228). — RQDQ, Feb 15 '11 at 18:58
I still don't know if that explains why it comes out as an HTML char — Joe Phillips, Feb 15 '11 at 19:01
My advice: Drop the iso-8859-1 encodings in your database and website and go for UTF8 already, everywhere. My dayjob involves a website that made the mistake of using iso-8859-1 years ago, and not a day goes by where we aren't kicking ourselves for that. — Enno, Feb 16 '11 at 05:35
You should do something about the [SQL injection vulnerability](http://en.wikipedia.org/wiki/SQL_injection) in your code, **especially** since you're dealing with credit cards. — josh3736, Feb 16 '11 at 14:13

score 2 · Answer 1 · answered Feb 15 '11 at 19:46

2

URL parameters are URL-encoded, and you need to decode URL parameter values to get the original values.

For example, see this implementation of URLDecode

In case of ända, this is HTML-encoded, and you find an HTML decoding function at the same address.

Not sure why you get an HTML encoded string as result of querystring().

answered Feb 15 '11 at 19:46

devio

36,858
7
80
143

I have asked the guy from where I get the querystring and he say that they send iso-8859-1, and when I look at the page souce and when I look at the page with response.write thevariable it looks right, but when it is inserted it gets wrong? – Claes Gustavsson Feb 15 '11 at 20:04
ã¶ is what I get when testing now? – Claes Gustavsson Feb 15 '11 at 20:21

RQDQ · Answer 2 · 2011-02-15T18:43:28.480

1

Ahh - use Bind Parameters instead of just concatenating your SQL statement together. That solves a number of problems (performance, sql injection attacks, etc)

EDIT: I haven't played with MySQL in a while, but the idea is this:

command = new Command("INSERT INTO USERS(folderName) VALUES (@folderName)");

command.Parameters.Add(new MySqlParameter("@folderName", DbType.NVarChar, 255, folderName));

command.ExecuteNonQuery();

Also, folderName must be a unicode column (NCHAR or NVARCHAR).

edited Feb 15 '11 at 18:43

answered Feb 15 '11 at 18:30

RQDQ

15,461
2
32
59

Sorry, but I dont know what Bind Parameters is. – Claes Gustavsson Feb 15 '11 at 18:35
I don't know if classic ASP even has bound parameters – Joe Phillips Feb 15 '11 at 18:39
I have allway used VARCHAR in the db, whats the difference? – Claes Gustavsson Feb 15 '11 at 18:58
VARCHAR uses a single byte to store a character and NVARCHAR (unicode) uses two bytes to store a character making it possible to store something with an ascii code of 228 (the character you're having problems with). – RQDQ Feb 15 '11 at 19:12
but if I try to change to NVARSCHAR in the db it wont take it, I get errornr 1064? – Claes Gustavsson Feb 15 '11 at 19:15
1064 appears to indicate a syntax error in your sql statement. – RQDQ Feb 15 '11 at 19:17
I just took edit on the column and changed it to NVARCHAR(45) – Claes Gustavsson Feb 15 '11 at 19:21
Should I allways use NVARCHAR? – Claes Gustavsson Feb 15 '11 at 19:22
I tryed to make a new column with NVARCHAR but it still came out like VARCHAR, it wouldnt take it? – Claes Gustavsson Feb 15 '11 at 19:43
1

Ahh - sorry for my ignorance of MySql. It looks like you have to set the collation of the table: http://stackoverflow.com/questions/3328968/how-can-i-store-unicode-in-mysql – RQDQ Feb 15 '11 at 19:49

score 0 · Answer 3 · answered Feb 15 '11 at 18:23

0

It seems to be passed via querystring as the wrong value. Where is the value coming from? That seems to be where the problem is being created.

answered Feb 15 '11 at 18:23

Joe Phillips

49,743
32
103
159

But when I write the folderName variable on the page it is right. But then it gets wrong when it is inserted into the database. – Claes Gustavsson Feb 15 '11 at 18:37
When you look at the page are you looking at the source code or the page in a browser? – Joe Phillips Feb 15 '11 at 18:38
When I resive the querystring I write out the variable folderName on the page so I see it in the browser. – Claes Gustavsson Feb 15 '11 at 18:43
@Claes You need to view the page source and look at it there – Joe Phillips Feb 15 '11 at 18:44

ä stored as ä value is encoded in database

3 Answers3