URL and Title with Foreign Characters

Question

Someone can add articles on my ASPX webpage.

For example they type in something like this as Title:

Öçöçü

This is saved in the database as:

Öçöçü

But when I need to read it from the database to create an url for it, then I read it in this format:

Ococu

So I send people to read the article to this url: www.test.com/ococu and the Ococu is the Querystring.

I need to retrieve the content of this article with the title Öçöçü, but I can't do something like

SELECT * FROM Article WHERE Title = Ococu (Ococu = querystring), because it has been saved to the database as Öçöçü

What can I do to solve this? I need to execute that Query but that is not possible because the querystring doesn't contain the foreign characters which are saved in the database.

Or should I create two attributes in the database? One for the Title and the other for the URL? So I can execute the query in the where claus as: WHERE URL = Ococu

Please I need your help guys.

What database do you use? What is the type of variable you insert to the database? — Ghost93, Dec 25 '13 at 21:27
MySql, I just send a String to the database, for example: Turkish. — Swag, Dec 25 '13 at 21:29
Does the character set in your database set to the relevant one(Turkish)? — Ghost93, Dec 25 '13 at 21:33
@Ghost93 No it changes the special characters to: ü and things like that... — Swag, Dec 25 '13 at 21:34
Try this answer :http://stackoverflow.com/questions/10444004/mysql-turkish-character — Ghost93, Dec 25 '13 at 21:36

score 1 · Accepted Answer · edited May 23 '17 at 10:32

It's hard to tell how to fix this problem without knowing the way your columns are declared in your MySQL table, and how your entities (characters) are stored. This kind of multinational stuff is easiest to handle if your columns are declared something like this:

  Title VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_turkish_ci

This allows the table to contain your Turkish characters (and Greek and Hungarian, for that matter) without having to be entity-coded (ü, etc.)

If in fact your tables are coded this way, try the following SELECT statement:

 SELECT * 
   FROM Article
  WHERE Title = 'ococu' 
COLLATE utf8_general_ci

Being ignorant of Turkish, I don't know the reasons for this, but it's clear that the Turkish collation treats ö as a different letter from o, likewise for ç and c, and ü and u. However, the utf_general_ci collation treats those letters as the same. That's why the SELECT statement above works.

IF your data in the table is stored entity-coded (ü, etc.) you really ought to translate it to utf8 so you can use this kind of search.

Finally, the fragment of URL you're mentioning with the value ococu is often called a slug in the trade. Your title Öçöçü needs to be converted to the slug value for searching. My suggestion above employs the collation to do that. It's worth mentioning that content management systems often store an article's title and slug in separate columns in the database table. This allows the creation of the slug from the title to be done explicitly at the time the article is created.

Here's a Stack Overflow item explaining how to use C# to turn a unicode phrase into a slug.

URL Slugify algorithm in C#?

Thanks for your answer! Now I created 2 columns, one for the title with the foreign characters, and the other for the slug (this is used as the URL), is this a good solution too (like you mentioned)? — Swag, Dec 25 '13 at 22:33
Yes, the two-column approach will be high-quality and scale up easily. — O. Jones, Dec 25 '13 at 22:52

score 0 · Answer 2 · answered Dec 25 '13 at 21:46

0

Best would be to have 2 separate fields in the DB, one for title, another for url available. Sometimes you would want to have the title changed, but url intact, so the already existing links are not broken. And you probably would not want to have spaces and other special characters in your url, so you'd want to shorten and replace symbols (just as you describe in your question).

Also this mitigates some of the security risks that can be exposed, if third party create articles on your site, like executing nasty scripts from url parameter.

answered Dec 25 '13 at 21:46

trailmax

34,305
22
140
234

How should webprogrammers fix this? Should they also create 2 seperate fields? – Swag Dec 25 '13 at 21:48
I'd say the url handle should be generated from title automatically - replace spaces with `-`, remove special characters and cut the long string. This is not for a user to input, so no extra field on the input form, but extra field in DB table. – trailmax Dec 25 '13 at 21:51
Yea, I was also thinking about this, but I didn't know if there were other better options – Swag Dec 25 '13 at 21:52

URL and Title with Foreign Characters

2 Answers2