MySQL - Searching for Swedish Characters

Question

I have stored Swedish words in MysQL database. The encoding of the table is

utf8_unicode_ci

I also tried

utf8_swedish_ci

when I'm doing a search in database, 'a' and 'ä' are treated the same. So the results include both words that begin with a and ä. This is true vice-versa as well. My application is developed with CakePHP and I have the following in my core.php

Configure::write('App.encoding', 'UTF-8');

Am I missing anything here?

This is my query:

$term = $this->request->query['term'];

$this->loadModel('Words');
$condition = array('word LIKE' => trim($term) . '%', 'is_active' => true);
$words = $this->Words->find('list', array(
    'fields' => array('word'),
    'limit' => 7,
    'conditions' => $condition));

return $this->respond($words, true);

I use to for Jquery autocomplete:

$(function () {
        $("#tags").autocomplete({
            source: 'autocomplete'
        });
    });

http://stackoverflow.com/questions/2607130/mysql-treats-%C3%85%C3%84%C3%96-as-aao — Mihai, Nov 20 '14 at 01:26

score 0 · Answer 1 · answered Nov 20 '14 at 03:59

0

$query_result = $this->Words->query("SELECT word FROM tbl_words WHERE word LIKE '" . $term . "%' COLLATE utf8_bin LIMIT 7 ;");

This works great for me. Thanks @Mihai for your comment.

answered Nov 20 '14 at 03:59

Ali.B

307
4
15

There's nothing better then the smell of fresh SQL injection vulnerabilities in the morning :) Please manually escape your user input (`DboSource::value()`) **http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#model-query** or use prepared statements **http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#prepared-statements** – ndm Nov 20 '14 at 04:59
I'm trying to do this way but for some reason I'm not getting anything: $db = $this->Words->getDataSource(); $query_result = $db->fetchAll( 'SELECT word FROM tbl_words WHERE word LIKE "?%" COLLATE utf8_bin AND is_active = TRUE LIMIT 7', array('als') ); – Ali.B Nov 20 '14 at 20:31

score 0 · Answer 2 · answered Aug 27 '22 at 12:00

I had similar issue. I wanted to list all the autocomplete suggesstions by a tag which may contain swedish characters. I had already a right charset in the mysql database. So no issues there. But I was still failing to search properly from the mysql database with a swedish tag name. However, it turns out to be not related to mysql. I missed to decode the tag in my php backend.

$term = urldecode($this->request->query['term']);

That makes sense, because the jquery autocompate sends out the tag in the url.

MySQL - Searching for Swedish Characters

2 Answers2