Server-side processing UTF-8 search in DataTables

Question

I use DataTables with server-side processing

$('#usersTable').DataTable(
            {
                responsive: true,
                "pageLength": 20,
                "processing": true,
                "serverSide": true,
                "bLengthChange": true,
                "bSort" : false,
                "bInfo" : false,
                "aLengthMenu": [[20, 50, 75, -1], [20, 50, 75, "ყველა"]],
                "ajax": "helpers/server_processing.php"
            }
        );

I also changed db connection in ssp.class.php to

$db = @new PDO(
"mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
$sql_details['user'],
$sql_details['pass'],
array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'" )
);

Still I'm having problems while trying to search UTF-8 characters in search bar

can anybody help?

The queries (copied from a comment)

SELECT  COUNT(`id`)
    FROM  `users`
    WHERE  (`id` LIKE :binding_0
              OR  `mac` LIKE :binding_1
              OR  `ip` LIKE :binding_2
              OR  `sname` LIKE :binding_3
              OR  `login` LIKE :binding_4
              OR  `tariff_plan_id` LIKE :binding_5
              OR  `now_playing_type` LIKE :binding_6
              OR  `now_playing_content` LIKE :binding_7
              OR  `now_playing_start` LIKE :binding_8
              OR  `keep_alive` LIKE :binding_9
              OR  `id` LIKE :binding_10
              OR  `status` LIKE :binding_11
           ) 
SELECT  COUNT(`id`)
    FROM  `users`
SELECT  `id`, `mac`, `ip`, `sname`, `login`, `tariff_plan_id`,
        `now_playing_type`, `now_playing_content`, `now_playing_start`,
        `keep_alive`, `id`, `status`
    FROM  `users`
    WHERE  (`id` LIKE :binding_0
              OR  `mac` LIKE :binding_1
              OR  `ip` LIKE :binding_2
              OR  `sname` LIKE :binding_3
              OR  `login` LIKE :binding_4
              OR  `tariff_plan_id` LIKE :binding_5
              OR  `now_playing_type` LIKE :binding_6
              OR  `now_playing_content` LIKE :binding_7
              OR  `now_playing_start` LIKE :binding_8
              OR  `keep_alive` LIKE :binding_9
              OR  `id` LIKE :binding_10
              OR  `status` LIKE :binding_11
           )
    ORDER BY  id ASC
    LIMIT  0, 20

EDIT: additional information

I discovered that LIKE queries with utf-8 values doesn't work with DateTime field in mysql

but DataTables compare every field to search string automatically. Is there an easy way I can tell DataTables not to search DateTime type columns if string contains utf-8 characters?

See the following answer http://stackoverflow.com/a/31065121/617373 , it might help — Daniel, Jan 31 '17 at 07:11
as you can see in second code block, I already inserted "set names utf8" in my pdo connection — Mister M, Jan 31 '17 at 07:19
Just a wild guess: The collation settings for the database/certain table are bad, e.g. not utf8. check this question on so: http://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error — Jojo, Jan 31 '17 at 07:19
could you show a) the output of `show full columns from your-table` and b) the query that throws that error? — Jojo, Jan 31 '17 at 07:50
here you can see columns with collations: http://imgur.com/a/xnYbl — Mister M, Jan 31 '17 at 08:03
as for which query throws that error, i'm note sure since when I try to log the query from sql_exec function in ssp, it isn't getting logged — Mister M, Jan 31 '17 at 08:03
@MisterM , not sure what the following does, but it seems to solve the issue in the other thread `$db->exec("set names utf8");` — Daniel, Jan 31 '17 at 08:15
please show the output of `$db->get_charset()` from your ssp.class.php — Jojo, Jan 31 '17 at 08:34
that function (get_charset) isn't defined in $db, it produces error — Mister M, Jan 31 '17 at 08:40
@Daniel I'm already doing that but with different syntax, it won't change anything. "set names utf8" helped fix UTF-8 character display issue, but search issue still remains. — Mister M, Jan 31 '17 at 08:42
sorry, the get_charset is from the mysqli php module, i didn't think of that. nevertheless we need to check if there is a difference between the charset your connection is using and the one you defined your tables/columns with. see this question to learn how to do this with pdo: http://stackoverflow.com/questions/26124474/get-charset-with-mysql-pdo — Jojo, Jan 31 '17 at 23:44

score 3 · Answer 1 · edited May 23 '17 at 11:45

3

Use new PDO('dblib:host=host;dbname=db;charset=UTF8', $user, $pwd); instead of ATTR_INIT_COMMAND.

->set_charset is for mysqli interface, not PDO.

According to the image, the columns are CHARACTER SET utf8 COLLATION utf8_general_ci, which is sufficient for Georgian.

Show us the query with the LIKE that is complaining.

The problem has to do with a LIKE statement. If it is in a Stored Routine, let's see SHOW CREATE ...; probably the wrong charset/collation was in effect when it was created.

As it says in the "Best Practice" section of this , "HTML forms should start like ". Check the other items there.

edited May 23 '17 at 11:45

Community

1
1

answered Feb 01 '17 at 01:44

Rick James

135,179
13
127
222

here are queries I'm able to log from ssp: http://pastebin.com/2xtB1DrP – Mister M Feb 01 '17 at 08:10
How is the "binding" performed? Perhaps that is where the charset/collation was messed up? – Rick James Feb 01 '17 at 17:21
it uses $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] ); with bindings array http://pastebin.com/fNbhYYN7 – Mister M Feb 02 '17 at 08:48

score 0 · Answer 2 · answered Feb 01 '17 at 17:26

0

Try this:

$db_link = new PDO($dsn, $username, $password) // DB-connection

// CHARSET: utf8
$db_link->query('SET NAMES utf8');

answered Feb 01 '17 at 17:26

SacrumDeus

156
1
13

score 0 · Accepted Answer · answered Feb 02 '17 at 09:33

I fixed the issue by altering filter function of ssp.class.php to exlude DateTime columns from search if search string is utf-8

static function filter ( $request, $columns, &$bindings )
{
    $globalSearch = array();
    $columnSearch = array();
    $dtColumns = self::pluck( $columns, 'dt' );

    if ( isset($request['search']) && $request['search']['value'] != '' ) {
        $str = $request['search']['value'];

        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];

            //**ADDED THIS**
            if(mb_detect_encoding($request["search"]["value"])=="UTF-8"){
                if($column['db']=="keep_alive" || $column['db']=="now_playing_start"){
                    continue;
                }
            }

            if ( $requestColumn['searchable'] == 'true' ) {
                $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }
    }

    // Individual column filtering
    if ( isset( $request['columns'] ) ) {
        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];

            $str = $requestColumn['search']['value'];

            if ( $requestColumn['searchable'] == 'true' &&
                $str != '' ) {
                $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }
    }

    // Combine the filters into a single string
    $where = '';

    if ( count( $globalSearch ) ) {
        $where = '('.implode(' OR ', $globalSearch).')';
    }

    if ( count( $columnSearch ) ) {
        $where = $where === '' ?
            implode(' AND ', $columnSearch) :
            $where .' AND '. implode(' AND ', $columnSearch);
    }

    if ( $where !== '' ) {
        $where = 'WHERE '.$where;
    }

    return $where;
}

Server-side processing UTF-8 search in DataTables

3 Answers3