MySQL Wrong Order by varchar

Question

I created a page with an ACP and save my data there. Everything runs without problems. But suddenly I have a mysterious problem. In the public area I always output the data alphabetically, but an entry ("Damaged Goods") is always ranked last.

My command to select:

SELECT lyric_id, lyric_title, lyric_lang_00, lyric_text_00, lyric_lang_01,   
       lyric_text_01, lyric_lang_02, lyric_text_02, lyric_lang_03, lyric_text_03, 
       lyric_lang_04, lyric_text_04, lyric_lang_05, lyric_text_05 
FROM dnd_lyrics 
WHERE lyric_text_00!='' OR lyric_text_01!='' OR lyric_text_01!='' OR   
      lyric_text_02!='' OR lyric_text_03!='' OR lyric_text_04!='' OR 
      lyric_text_05!='' OR lyric_text_03!='' ORDER BY lyric_title ASC"

My command to insert:

$db->query("INSERT INTO dnd_lyrics (lyric_title,lyric_lang_00) 
  VALUES('".$db->str($_POST['lyric_title'])."','".$db->str($config['lang'])."')");

Affected database: https://syntaxhigh.normanhuth.com/FlniL9

So far, the problem has only occurred with this one entry. And if I enter it manually via phpMyAdmin, it works.

Maybe there's a space or some other character before the name? What are some other records? What does `var_dump` show? You also are possibly open to SQL injections with this code. — user3783243, Aug 28 '18 at 21:15
having a look at your data, i can see a mystery character before the **Damaged Goods** looks like a possible control character, that's the issue. depending on the encoding is shows up as `â€‹` — , Aug 28 '18 at 21:21
OK. Thanks. I take the autocolplete content from a other table with the tracks names and there have a few cols the same problem. Now I must search there. Thank You. — Norman Huth, Aug 28 '18 at 21:52

Bill Karwin · Accepted Answer · 2018-08-28T21:43:46.153

Tested your data and your query, and got this result:

. . .

|        8 | World War 3                        |
|       79 | W+J                              |
|       88 | Damaged Goods                     |
+----------+------------------------------------+

Notice the alignment of the right bars. There are non-printing spaces in the data. On my screen, it looks like:

. . .

|        8 | World War 3                        |
|       79 | W + J                              |
|       88 |  Damaged Goods                     |
+----------+------------------------------------+

The "Damaged Goods" title has an extra non-printing space at the beginning, which makes it sort after all other titles.

If I open your data in vim, I see:

(88, '<200b>Damaged Goods', 'en',

Unicode 200b is "zero width space": https://www.fileformat.info/info/unicode/char/200B/index.htm

You should do some kind of whitespace-trimming operation on your data before inserting it to the database. Unfortunately, the reglar PHP trim() function doesn't do the job.

See Trim unicode whitespace in PHP 5.2 for a solution.

Re your comment:

Your use of trim() function in PHP won't work. The PHP trim() function understands only ASCII whitespace characters, not unicode whitespace-like characters. See http://php.net/trim for the list of characters trim looks for.

I built the trim function directly into the MySQL class return $this->link->real_escape_string(trim($string)); — Norman Huth, Aug 28 '18 at 21:37
Trim unicode whitespace in PHP 5.2 is the solution for me. Thanks. — Norman Huth, Aug 28 '18 at 22:15
Hex('W+J') = 57 E2808B 2B E2808B 4A (with some spacing added) — Rick James, Aug 29 '18 at 05:07

score 0 · Answer 2 · answered Aug 29 '18 at 10:59

There really is a sign in the database that is not visible right away. Probably during the first tests with Javascript/Ajax.

Bill Karwin's tip helped. I added the "Trim unicode whitespace in PHP 5.2" function to my MySQL class. And which simply run through the tables in a loop and rewrite the contents with this function and thus get the database clean.

Thanks for your help.

MySQL Wrong Order by varchar

2 Answers2