EN [HELP] Character encoding (including polish chars)

[K4M1ss]

Hi. I'm trying to save clients nicknames in my database. I have one problem, polish chars (e.g ĄŻĆŁĘ). They are saving like "?". My database encoding is: utf8mb4_polish_ci. after first connection to db i'm executing "SET NAMES `utf8mb4`". Is it my fault or DB module is not supporting different encoding?

 

[irgendwr]

My database encoding is: utf8mb4_polish_ci

I'm reasonably sure that the DB module can handle utf8.
Try converting it to the regular utf8mb4 charset as described here, if that doesn't work: ¯\_(ツ)_/¯

 

[Multivitamin]

can you post an example on how you save it to the database?

 

[K4M1ss]

Code below;

function mysql_real_escape_string (str) {
        return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
            switch (char) {
                case "\0":
                    return "\\0";
                case "\x08":
                    return "\\b";
                case "\x09":
                    return "\\t";
                case "\x1a":
                    return "\\z";
                case "\n":
                    return "\\n";
                case "\r":
                    return "\\r";
                case "\"":
                case "'":
                case "\\":
                case "%":
                    return "\\"+char; // prepends a backslash to backslash, percent,
                                      // and double/single quotes
            }
        });
    }

if (dbc) dbc.exec("INSERT INTO Users (Nick, DBID, Registered, LastIP, OnlineTime, OwnedChannel) VALUES (?, ?, ?, ?, ?, ?)", mysql_real_escape_string(ev.client.nick()), ev.client.databaseID(), 0, ev.client.getIPAddress(), 0, -1);

mysql_real_escape_string function found on internet.

 

[irgendwr]

mysql_real_escape_string

In this case you don't need to escape the nickname because you are using a prepared statement (with the "?" after VALUES).

Try my suggestion above without mysql_real_escape_string

 

[Multivitamin]

you should not need the function
mysql_real_escape_string

the library itself will handle escaping by itself

if (dbc) dbc.exec("INSERT INTO Users (Nick, DBID, Registered, LastIP, OnlineTime, OwnedChannel) VALUES (?, ?, ?, ?, ?, ?)", ev.client.nick(), ev.client.databaseID(), 0, ev.client.getIPAddress(), 0, -1);

 

[K4M1ss]

i've removed this function, the same problem + now i have problem with saving nicknames with " ' "

2019/01/21 23:50:39 841b8334 62e368f5 PLUGIN database error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'owy', LastIP='--------------' WHERE DBID=22' at line 1

 

[irgendwr]

i've removed this function, the same problem + now i have problem with saving nicknames with " ' "

2019/01/21 23:50:39 841b8334 62e368f5 PLUGIN database error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'owy', LastIP='--------------' WHERE DBID=22' at line 1

Phew, that's not good...
What sinusbot version are you using?

 

[K4M1ss]

SinusBot 0.14.3-0e747fd

 

[Multivitamin]

can you post your code again?

 

[K4M1ss]

My script:

registerPlugin({
    name: 'UserManager',
    version: '1.0.0',
    backends: ['ts3'],
    description: 'Skrypt zarządzający użytkownikami',
    author: 'K4M1s <[email protected]>',
    vars: []
}, function (sinusbot, config) {
    
    Array.prototype.contains = function(obj) {
        var i = this.length;
        while (i--) {
            if (this[i] == obj) {
                return true;
            }
        }
        return false;
    }
    
    function mysql_real_escape_string (str) {
        return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
            switch (char) {
                case "\0":
                    return "\\0";
                case "\x08":
                    return "\\b";
                case "\x09":
                    return "\\t";
                case "\x1a":
                    return "\\z";
                case "\n":
                    return "\\n";
                case "\r":
                    return "\\r";
                case "\"":
                case "'":
                case "\\":
                case "%":
                    return "\\"+char; // prepends a backslash to backslash, percent,
                                      // and double/single quotes
            }
        });
    }
    
    var db = require('db');
    var engine = require('engine');
    var helpers = require('helpers');
    var backend = require('backend');
    var event = require('event');
    
    var dbc = db.connect({ driver: 'mysql', host: '127.0.0.1', username: '------', password: '-----', database: '-----' }, function(err) {
        if (err) {
            engine.log(err);
        } else {
            if (dbc) dbc.exec("SET NAMES `utf8`");
        }
    });
    
    var waitingForRegistration = [];
    var clientsOnlineTime = [];
    
    setInterval(function() {
        var clients = backend.getClients();
        for(var i=0; i<clients.length; i++) {
            if (!clients[i].isSelf()) {
                clientsOnlineTime[clients[i].id()] = parseInt(clientsOnlineTime[clients[i].id()]) + 1;
                if (parseInt(clientsOnlineTime[clients[i].id()]) >= 60) {
                    if (dbc) dbc.exec("UPDATE Users SET OnlineTime=OnlineTime + " + clientsOnlineTime[clients[i].id()] * 1000 + " WHERE DBID=" + clients[i].databaseID());
                    clientsOnlineTime[clients[i].id()] = 0;
                }
            }
        }
    }, 1000);
    
    event.on('clientMove', function(ev) {
        setTimeout(function() {
        
            if (typeof ev.fromChannel == 'undefined' && !ev.client.isSelf()) {
            
                if (dbc) dbc.query("SELECT * FROM Users WHERE DBID = "+ev.client.databaseID(), function(err, res) {
                    if (!err) {
                        if (res.length == 0) {
                            if (dbc) dbc.exec("INSERT INTO Users (Nick, DBID, Registered, LastIP, OnlineTime, OwnedChannel) VALUES (?, ?, ?, ?, ?, ?)", mysql_real_escape_string(ev.client.nick()), ev.client.databaseID(), 0, ev.client.getIPAddress(), 0, -1);
                            ev.client.chat("\n[color=blue]▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬[/color]\n"+
                                           "\nWitaj [b]" + ev.client.nick() + "[/b]! \n" +
                                           "To Twoja [b]Pierwsza[/b] wizyta u nas!\n" +
                                           "Cieszymy się, że wybrałeś nasz serwer!\n" +
                                           'Jeśli chcesz się zarejestrować udaj się na kanał "► Rejestracja"\n'+
                                           'Jeśli chcesz otrzymać kanał prywatny udaj się na kanał "► Prośba o kanał"\n'+
                                           'Pamiętaj, aby założyć kanał musisz mieć w sumie 1 godzinę online."\n'+
                                           'Miłych rozmów."\n'+
                                           "\n[color=blue]▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬[/color]");
                        } else {
                            if (dbc) dbc.exec("UPDATE Users SET Nick='" + mysql_real_escape_string(ev.client.nick()) + "', LastIP='" + ev.client.getIPAddress() + "' WHERE DBID=" + ev.client.databaseID());
                            var onlineToMsg = parseInt(helpers.toString(res[0].OnlineTime)) / 1000;
                            var Minutes = 0;
                            var Hours = 0;
                            var Days = 0;
                            var Months = 0;
                            var Years = 0;
                            var OnlineTimeMsg = "";
                            //lata
                            if (onlineToMsg > 31104000) {
                                Years = Math.floor(onlineToMsg / 31104000);
                                onlineToMsg = onlineToMsg % 31104000;
                                OnlineTimeMsg += Years;
                                if (Years < 11) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) == 1) {
                                        OnlineTimeMsg += " rok";
                                    } else if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " lata";
                                    } else {
                                        OnlineTimeMsg += " lat";
                                    }
                                } else if (Years > 19) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " lata";
                                    } else {
                                        OnlineTimeMsg += " lat";
                                    }
                                }
                            }
                            
                            //miesiące
                            if (onlineToMsg > 2592000) {
                                Months = Math.floor(onlineToMsg / 2592000);
                                onlineToMsg = onlineToMsg % 2592000;
                                OnlineTimeMsg += Months;
                                
                                if (Months < 11) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) == 1) {
                                        OnlineTimeMsg += " miesiąc";
                                    } else if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " miesiące";
                                    } else {
                                        OnlineTimeMsg += " miesięcy";
                                    }
                                } else if (Months > 19) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " miesiące";
                                    } else {
                                        OnlineTimeMsg += " miesięcy";
                                    }
                                }
                                
                            }
                            //dni
                            if (onlineToMsg > 86400) {
                                Days = Math.floor(onlineToMsg / 86400);
                                onlineToMsg = onlineToMsg % 86400;
                                OnlineTimeMsg += Days;
                                
                                if (Days < 11) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) == 1) {
                                        OnlineTimeMsg += " dzień";
                                    } else if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " dni";
                                    } else {
                                        OnlineTimeMsg += " dni";
                                    }
                                } else if (Days > 19) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " dni";
                                    } else {
                                        OnlineTimeMsg += " dni";
                                    }
                                }
                                
                            }
                            //godziny
                            if (onlineToMsg > 3600) {
                                Hours = Math.floor(onlineToMsg / 3600);
                                onlineToMsg = onlineToMsg % 3600;
                                OnlineTimeMsg += Hours;
                                if (Hours < 11) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) == 1) {
                                        OnlineTimeMsg += " godzinę i ";
                                    } else if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " godziny i ";
                                    } else {
                                        OnlineTimeMsg += " godzin i ";
                                    }
                                } else if (Hours > 19) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " godziny i ";
                                    } else {
                                        OnlineTimeMsg += " godzin i ";
                                    }
                                }
                            }
                            //minuty
                            if (onlineToMsg > 60) {
                                Minutes = Math.floor(onlineToMsg / 60);
                                OnlineTimeMsg += Minutes;
                                if (Minutes < 11) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) == 1) {
                                        OnlineTimeMsg += " minutę";
                                    } else if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " minuty";
                                    } else {
                                        OnlineTimeMsg += " minut";
                                    }
                                } else if (Minutes > 19) {
                                    if (parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) > 1 && parseInt(OnlineTimeMsg.charAt(OnlineTimeMsg.length-1)) < 5 ) {
                                        OnlineTimeMsg += " minuty";
                                    } else {
                                        OnlineTimeMsg += " minut";
                                    }
                                }
                            }
                            
                            ev.client.chat("\n[color=blue]▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬[/color]\n"+
                                           "\nWitaj [b]" + ev.client.nick() + "[/b]! \n" +
                                           "To Twoja [b]" + ev.client.getTotalConnections() + "[/b] wizyta u nas!\n" +
                                           "Łącznie na naszym serwerze spędziłeś [b]" + OnlineTimeMsg + "[/b]\n" +
                                           "Bardzo nam z tego faktu miło. [b]Udanych rozmów![/b]\n"+
                                           "\n[color=blue]▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬[/color]");
                        }
                        clientsOnlineTime.splice(ev.client.id(), 0, 0);
                    } else {
                        engine.log(err);
                    }
                });
            }
            
        }, 1000);
        
        if ((typeof ev.toChannel == 'undefined' && !ev.client.isSelf()) || ( typeof ev.toChannel != 'undefined' && typeof ev.fromChannel != 'undefined' )) {
            if (dbc) dbc.exec("UPDATE Users SET OnlineTime=OnlineTime + " + clientsOnlineTime[ev.client.id()] * 1000 + " WHERE DBID=" + ev.client.databaseID());
            clientsOnlineTime[ev.client.id()] = 0;
        }
        
        if (typeof ev.toChannel != "undefined" && typeof ev.toChannel.parent() != "undefined") {
            if (typeof ev.toChannel.topic() != "undefined" && ev.toChannel.topic().length > 3) {
                if (ev.client.getChannelGroup().id() == 5) {
                    var topic = new Date().getTime();
                    ev.toChannel.setTopic(topic);
                }
            }   
        }
        
        if (typeof ev.toChannel != 'undefined' && (ev.toChannel.id() == "24" || ev.toChannel.id() == "81") && !ev.client.isSelf()) {
            var clients = backend.getClients();
            var adminsCount = 0;

            clients.forEach(function(client) {
                var groups = client.getServerGroups()
                for (var i=0; i<groups.length; i++) {
                    if (groups[i].id() == "31" || groups[i].id() == "6") {
                        adminsCount++;
                        client.poke(ev.client.nick() + " potrzebuje Twojej pomocy na kanele: " + ev.toChannel.name());
                        break;
                    }
                }
            });
            if (adminsCount > 0) {
                ev.client.poke("Administracja została poinformowana o tym, że potrzebujesz pomocy. Zaraz ktoś się zjawi.");
            } else {
                ev.client.poke("Niestety żaden administrator nie jest online. Spróbuj ponownie później.");
            }
        }
        
        if (typeof ev.toChannel != 'undefined' && ev.toChannel.id() == "26" && !ev.client.isSelf()) {
            var clientGroups = ev.client.getServerGroups()
            
            for(var i=0; i<clientGroups.length; i++) {
                if (clientGroups[i].id() == "28") {
                    ev.client.moveTo(ev.fromChannel);
                    ev.client.chat('Jesteś już zarejestrowany!');
                    return;
                } else if (clientGroups[i].id() == "30") {
                    ev.client.moveTo(ev.fromChannel);
                    ev.client.chat('Jesteś już zarejestrowana!');
                    return;
                }
            }
            ev.client.poke("Sprawdź chat z BOT'em :)");
            ev.client.chat('Aby nadać Ci rangę muszę znać Twoją płeć. Napisz "chłopak" lub "dziewczyna"');
            waitingForRegistration.push(ev.client);
        }
        
        if (typeof ev.toChannel != 'undefined' && ev.toChannel.id() == "25" && !ev.client.isSelf()) {
            var clientGroups = ev.client.getServerGroups()
            
            for(var i=0; i<clientGroups.length; i++) {
                if (clientGroups[i].id() == "28" || clientGroups[i].id() == "30") {
                    if (dbc) dbc.query("SELECT * FROM Users WHERE DBID = "+ev.client.databaseID(), function(err, res) {
                        if (!err) {
                            if (res.length == 1) {
                                
                                if (parseInt(helpers.toString(res[0].OwnedChannel)) > 0 ) {
                                    if (typeof backend.getChannelByID(helpers.toString(res[0].OwnedChannel)) != 'undefined') {
                                        ev.client.moveTo(backend.getChannelByID(helpers.toString(res[0].OwnedChannel)));
                                        ev.client.poke("Posiadasz już swój kanał!");
                                    } else {
                                        if (dbc) dbc.exec("UPDATE Users SET OwnedChannel=-1 WHERE DBID=" + ev.client.databaseID());
                                        ev.client.poke("Przepraszamy ale Twój kanał chyba zniknął. Wejdź na kanał 'Prośba o kanał' ponownie by utworzyć nowy");
                                        ev.client.moveTo(backend.getChannelByID(18));
                                    }
                                    return;
                                }
                                
                                var OnlineTime = parseInt(helpers.toString(res[0].OnlineTime));
                                var OnlineTime2 = OnlineTime / 1000 / 60 / 60;
                                if (OnlineTime2 >= 1) {
                                    var name = "Kanał użytkownika " + ev.client.nick();
                                    var parent = 64;
                                    var description = "Nowy kanał użytkownika " + ev.client.nick();
                                    var topic = new Date().getTime();
                                    
                                    for(var i=0; i<clientGroups.length; i++) {
                                        if (clientGroups[i].id() == "11") {
                                            parent = 34;
                                        }
                                    }
                                    
                                    var createdChannel = backend.createChannel({
                                        name: name,
                                        parent: parent,
                                        description: description,
                                        topic: topic,
                                        permanent: true,
                                        maxClients: -1
                                    });
                                    for (var i=0; i<2; i++) {
                                        backend.createChannel({
                                            name: "Podkanał " + (i+1),
                                            parent: createdChannel,
                                            description: description,
                                            permanent: true,
                                            maxClients: -1
                                        });
                                    }
                                    if (dbc) dbc.exec("UPDATE Users SET OwnedChannel=" + createdChannel.id() + " WHERE DBID=" + ev.client.databaseID());
                                    createdChannel.setChannelGroup(ev.client, backend.getChannelGroupByID(5));
                                    ev.client.moveTo(createdChannel);
                                    ev.client.chat("Twój nowy kanał został właśnie założony!");
                                    ev.client.chat("Pamiętaj aby odwiedzać swój kanał GŁÓWNY co najmniej raz na tydzień.");
                                    ev.client.chat("Jeśli tego nie zrobisz Twój kanał zostanie usunięty z powodu braku aktywności.");
                                    ev.client.chat("Miłego użytkowania! Pozdrawia administracja TeamSpeak Olejnik.org.pl!");
                                } else {
                                    OnlineTime = new Date(OnlineTime);
                                    ev.client.poke("Aby założyć kanał musisz być w sumie 1h online. Twój aktualny czas online: " + Math.floor(OnlineTime2) + " godzin i " + OnlineTime.getMinutes() + " minut.");
                                    if (typeof ev.fromChannel != 'undefined') {
                                        ev.client.moveTo(backend.getChannelByID(18));
                                    }
                                }
                            }
                        }
                    });
                }
            }
        }
        
    });
    
    
    event.on('chat', function(ev) {
        waitingForRegistration.forEach(function(client) {
            if (ev.client.id() == client.id()) {
                if (ev.text.toLowerCase() == "chłopak") {
                    waitingForRegistration.splice( waitingForRegistration.indexOf(client), 1 );
                    backend.getServerGroupByID("28").addClientByDatabaseId(ev.client.databaseID());
                    ev.client.chat('Gratulacje! Zostałeś zarejestrowany!');
                } else if (ev.text.toLowerCase() == "dziewczyna") {
                    waitingForRegistration.splice( waitingForRegistration.indexOf(client), 1 );
                    backend.getServerGroupByID("30").addClientByDatabaseId(ev.client.databaseID());
                    ev.client.chat('Gratulacje! Zostałeś zarejestrowana!');
                } else {
                    ev.client.chat('Coś poszło nie tak... Podaj swoją płeć(chłopak lub dziewczyna).');
                }
            }
        });
        
        if (ev.text == "debug") {
            var channels = backend.getChannels();
            channels.forEach(function(channel) {
                if (typeof channel.parent() != "undefined") {
                    if (typeof channel.topic() != "undefined" && channel.topic().length > 3) {
                        var channelDate = new Date(channel.topic());
                        var difference = new Date().getTime() - channelDate.getTime();
                        if (difference / 1000 / 60 / 60 / 24) {
                            if (Math.floor(difference) > 7) {
                                if ( channel.delete() ) {
                                    if (dbc) dbc.exec("UPDATE Users SET OwnedChannel=-1 WHERE DBID=" + ev.client.databaseID());
                                }
                            }
                        }
                    }
                }
            });
        }
        
    });
    
    engine.log('[UserManager] Loaded!');
});

 

[Multivitamin]

you are still using the mysql_real_escape_string functions on some of your locations
please also replace

dbc.exec("UPDATE Users SET Nick='" + mysql_real_escape_string(ev.client.nick()) + "', LastIP='" + ev.client.getIPAddress() + "' WHERE DBID=" + ev.client.databaseID());

with

dbc.exec("UPDATE Users SET Nick=?, LastIP=? WHERE DBID=?", ev.client.nick(), ev.client.getIPAddress(), ev.client.databaseID());

 

[K4M1ss]

i've removed this from all locations and it didn't help

 

[Multivitamin]

Then please post the code where you removed this from all your locations

 

[irgendwr]

i've removed this from all locations and it didn't help

You are not using prepared statements correctly as multi said.

Did you convert the charset of the table like I mentioned?

 

[K4M1ss]

So yeah, i edited the script, i mean the lines that insert or update something to database as u said.
It didn't worked.
Still getting:

while my nickname looks like:

database encoding: utf8mb4_general_ci