• If you need help or want to discuss things, you now can also join us on our Discord Server!
  • A first preview of the unlimited version of SinusBot can be found in the Upcoming Changes thread. A version for Windows will follow, but we don't have a release date, yet.

EN [HELP] Character encoding (including polish chars)

K4M1ss

#stayinghome
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

no longer active, "retired" staff member
is awesome!
V.I.P.
is uber awesome!
Contributor
Insider
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

Well-Known Member
Tier III
is awesome!
V.I.P.
is uber awesome!
Contributor
Insider
can you post an example on how you save it to the database?
 

K4M1ss

#stayinghome
Code below;
JavaScript:
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.
 

Multivitamin

Well-Known Member
Tier III
is awesome!
V.I.P.
is uber awesome!
Contributor
Insider
you should not need the function
mysql_real_escape_string

the library itself will handle escaping by itself

JavaScript:
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

#stayinghome
i've removed this function, the same problem + now i have problem with saving nicknames with " ' "
Code:
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

no longer active, "retired" staff member
is awesome!
V.I.P.
is uber awesome!
Contributor
Insider
i've removed this function, the same problem + now i have problem with saving nicknames with " ' "
Code:
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

#stayinghome
My script:

JavaScript:
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

Well-Known Member
Tier III
is awesome!
V.I.P.
is uber awesome!
Contributor
Insider
you are still using the mysql_real_escape_string functions on some of your locations
please also replace
JavaScript:
dbc.exec("UPDATE Users SET Nick='" + mysql_real_escape_string(ev.client.nick()) + "', LastIP='" + ev.client.getIPAddress() + "' WHERE DBID=" + ev.client.databaseID());
with
JavaScript:
dbc.exec("UPDATE Users SET Nick=?, LastIP=? WHERE DBID=?", ev.client.nick(), ev.client.getIPAddress(), ev.client.databaseID());
 
Last edited:

Multivitamin

Well-Known Member
Tier III
is awesome!
V.I.P.
is uber awesome!
Contributor
Insider
Then please post the code where you removed this from all your locations
 

irgendwr

no longer active, "retired" staff member
is awesome!
V.I.P.
is uber awesome!
Contributor
Insider
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

#stayinghome
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:
Screenshot_1.png
while my nickname looks like:
Screenshot_2.png
database encoding: utf8mb4_general_ci
 
Top