A8 - LiteC MYSQL Connector

EDIT: New Update for 1.1 And LINKS for Download WORK
Hello Friends.
First, I want to thank everyone who helped me with my doubts.
thank you!

This is my first contribution to the community.
I hope it is helpful to everyone.

What this is?
The name of the topic says it all!.

A8 - LiteC MYSQL Connector:
Used to manipulate the data in mysql table V. 5 + +

Download: A8 - LiteC MYSQL Connector
Download - V1.1

Documentation
Manual V1.1

Examples and documentation included in the zip package

Any questions or bug, please let me know.!!

This is a great contribution! There have been quite many discussions about mysql plugins but most were outdated. Thanks to you there is now well documented alternative.

I especially like the colorful manual. There is only one information missing: In what format are the lines returned? Are they always given as char* oder STRING*? Please add this information to the manual.
I couldn't try the plugin because I'm using the engine sdk, but I hope others will use it often.

Reaaaally well done! Especially the manual looks good!

Thanks for the comment SchokoKeks!
And Thanks for comment PadMalcom!
(^.^)

> On Documentation already have the the return type of each function has, However, I noticed that in some cases, it is poorly explained.

I'll improve the documentation and post the news

Cool! Hoping this will work with Transact-SQL as well!

LINKS WORKS and New Update. (^.^)

Whatever you are trying to do here, THIS is the wrong way.

Please, everyone, don't use this, and don't write your own wrappers over libmysql! MySQL is released under the GPL license, NOT LGPL, even just linking against the librarie requires you to publish your source code under the GPL as well, otherwise you are violating an ACTUAL license. The GPL is no Kindergarten, no "oh, some neckbeards jerk over it" thingy and a lot of people and companies have been sued over GPL violations.
I can't stress this enough, if you want to stay out of legal trouble, DON'T use this. Ever.

The second thing is, you are opening your game and server up to a tremendous security risk, and this is no Kindergarten either! Embedding your username and password for your MySQL database into your game is a HORRIBLE idea, everyone, and this includes my mom, can get them out of your game and run arbitrary queries against YOUR database. If you prefer your server to not become an anonymous FTP full of childporn and warez, DON'T fucking use this.

Now, to make it easy, here is what you should do in the style of a "choose your own adventure"

1) I'm a programmer and I would like to save my highscores locally (read answer A)
2) I'm an idiot and I believe that a database should drive my MMORPG (read answer B)
3) I'm a sane person and would like to use a database to store some information about my users (read answer C)

Answer A: Use sqlite3. It's the de facto industry standard for these kinds of things, extremely optimized, fast and well tested.

Answer B: MySQL scales exceptionally bad, you might be better of with an alternative. That doesn't mean that MySQL is bad, or doesn't has its advantages for large scale applications, but you will likely end up putting a lot of effort into your caching strategy to get the load off of your database servers (not saying that you won't put a lot of work into your caches anyways). Please read about alternatives and then pick something that gets the job done best for your MMORPG, and keep in mind to always allocate enough resources for the worst case (and be prepared to scale over night).

Answer C: Use MySQL on your server and provide a RESTful API, or similar, for your game. Use normal HTTPS requests to communicate with the API and let the server handle the MySQL stuff. For the love of god, sanitize your user input and use data bindings.

I get it, most of you aren't serious and just want to play around, but if you have in mind to do something serious, or even remotely serious, please, please, please, don't make the wrong mistakes.

^this.

The links are dead, someone has the version 1.1, which I myself have version 1.0

Here you go: http://www.file-upload.net/download-8061813/LITEC-SQLITE-CONNECTOR.zip.html

The staff not take very well this plugin. I decided to discontinue updates.
If it is useful I am working in sqlite3. For mananger table in CLIENT DATABASE SQLITE3.
https://sourceforge.net/projects/sqlite3cworkitv10/

The documentation is in Portuguese Brazil, and Pluguin only FOr Use in C# or C++ but if you are interested I can post the plugin for LITEC and some Sources to use the plugin. Works great!

Features:

void sql_init();
int sql_conect(char* database, int type);
int sql_select(char* query);
int sql_selectId(char* query);
char* sql_get_select(char* buffer, char *column, int row);
char* sql_get_selectId(char* buffer, int column, int row);
int sql_insert(char* table,char* column, char* values);
int sql_updateId(char* table,char* column, char* values, char* id);
int sql_update(char* table,char* column, char* values, char* cl_name, char* name);
int sql_removeId(char* table,char* id);
int sql_remove(char* table,char* condition, char* name);

Why dont you use a php script on your server which has access to the sqldatabase?
Much easier and secure for me

FOR EVERYONE THINKING OF USING THIS:

This plugin is NOT dangerous. Its how someone uses it.
If you need a MySQL Connection with a plugin do it on the server. Also do it localy...if the MySQL is in a local area connection with the server!
All clients must use only client applications , in the server code for all plugins you must give username and password. This will be visible if you give it to the clients.
Using a PHP Script to do this work (as I'm using right now) requires first a WEB Server and second needs more time to process. But gives the opportunity to sanitize the input (remove bad/exploit characters from the strings).

Never use usernames/passwords for a sql server in client applications, it can be captured with software as it is making its way out of your lan card. And make sure you remove any '-"; characters from your input strings before sending them.

Look at this guy doing input sanitation on the client.

Old_Bill recently told me that I'm no longer allowed to call user names, so let's make a riddle out of it: It starts with an 'I' and ends with 'diot'.

Sanitation localy (for dumb players 'wannabe hackers') and then on a remote WEB server (for serious 'penetration testers')...
Are you refering an "I" , "diot" to yourself here? I didnt understand...

If you are going to give meaningless replys , dont reply at all! Say something useful!

PS.: I'm trying to help. You're trying to 'look cool'. Not working...

You are telling people that it's not bad if they use this plugin. If you want to help them to get their MySQL database emptied, sure, you are helping them a metric fuckton there, but otherwise...

Doing sanitation twice, once on the client and once on the server is ridiculous. You don't have to defend against the pentester and the script kiddy separately (besides, assuming that only these two exist is just naive). Do the sanitation once and don't write code that you don't need. If you connect directly to the MySQL database... Well, don't do input sanitation at all, because its worthless anyways. Anyone can connect to your MySQL database and run queries against it anyhow, and people will do that, promised.

Not defining an API on a webserver that your client communicates with is just asking for trouble. Wether you implement that API in C, PHP, node.js, RoR... No one cares. It doesn't matter if its SOAP, REST, XMLRPC, what have you. Important is: Don't fucking trust third party, sanitize all input ON your server, not on the client.

Edit:
I feel like a fucking broken record here. I'm telling this since years in this forum, every time a thread like this pops up and everyone is like "hooray, MySQL in Gamestudio". And no one fucking realizes that this is just like going outside and handing everyone a key to your front door. Sometimes I think you people are just fucking around with me and not actually that retarded, but it appears to be that in fact you are. I realize that security isn't a state but a process and that it might not be obvious why this is a bad idea, but come fucking on, connecting from an untrusted client to a MySQL database is stupid. News at 11.

Seriously, this complete utter lack of responsibility in regards to security is baffling, and its something that is running around in the indie games scene like a kid with ADHD after you fed it a kilo of sugar and cocaine. It's not funny anymore, it's depressing and it makes you all look bad. Fucked if I know why this is so prevalent with game designers, but I have the theory that you are all just a bunch of hipster kids that sit around starbucks all day and sometimes circlejerk each other when no one looks.

You speak like people coudnt use this only on serverside.
Using it on client side of course is fully ridiculous.

You speak like someone who thinks that writing server side software with Gamestudio is a brilliant idea.

And don't say dedicated server, because in that case, you have a local sqlite3 database, not a local MySQL database. (Edit: Why do you need a database for a server that doesn't need persistence anyways?)


Oh, and just to make sure to really piss of everyone in this forum: My favorite Latvian joke:

Exactly my point. He says that you cant trust your own dedicated server to send input to your own database...

Calling everyone idiots just speaks bad for your childly attitude about life. You're not a misunderstood genius, you're just a regular guy with a shi**y morning!

As for your constructive criticism, I never wrote about connecting your client app directly with your SQL database. You just cant read right...thats all...
I say newbies shouldnt do it, because noone is familiar with protections until he builds a vunerable app.

As for the double sanitation, I just like to do it twice...it consumes my hardware, raises my FPS, clogs my lan card and crashes my server...but I'm just like that. Cant help it...

Wont somebody shut him up already? I had enough of his crap!

Why on earth would you install MySQL with your software and have the daemon running on your dedicated server?! I'm seriously having trouble understanding your use case here, and it feels like you are trying to open a door with a tank.


It's afternoon. And yeah, it's a shitty one. Go sue me.
And I know who I am, and I'm not pretending to be gods gift to earth. If you want to write insecure software, I'm not going to stop you from it. But I'll make damn sure to point out how ridiculously dangerous this is to everyone else, just in the off chance that it will stop someone from doing something like this.


See, and yet you want me to take you seriously.


There is an ignore button in the user profiles. I use it regularly, works like a charme. 10/10 button, would recommend.
Besides, old_bill or some other Mod will probably send me PM in the next 24 hours and tell me that I can't possible write stuff like this.

This is my last reply to this meaningless off-topic, what you say you write is exactly what I wrote. I gave advice for people that havent yet met the problems related to unprotected server code, to use this carefully and to watch what they send from a client app, to only trust their server code(on a dedicated machine).
And what did you do? Call me an idiot...Where's the 'pointing out' how dangerous this is? I dont think its dangerous to protect your app...

Anyway, I'm trying to help people that might use this plugin from making mistakes like everyone else, I dont wish to take the topic and the contribution to the trash. I'm not going to write off-topic anymore here.

Thanks to all who understand my motives in contributing with this plugin!
(^.^).

Unfortunately there are those who are against the use, and not given due weight to this contribution.
Currently I have only worked for their own use the MYSQL plugin, since I'm using in my application (Client Server x). However Descontinuei the contribution, to avoid problems with those who are against!


If there is someone really need to use send me PM I'll be contributing.

For those who would like to use a database like mysql but only in applications CUSTOMER, I have also worked in plugin for sqlite3.

http://www.sqlite.org/copyright.html

Okay, here is why this is wrong on more levels than there are ranks in Call of Duty, and why I have no problem to drag this contribution into the trash. I won't say fuck, I won't call anyone an idiot and I'm going to explain it in detail. It's grouped in multiple categories, so just read on before flaming me.

Connecting the Client to MySQL
- The very first problem here is that MySQL is under the GPL license. GPL is a viral license, meaning that anything that uses anything with the GPL license, must be under the GPL or a GPL conform license. Furthermore, GPL requires you to open source your source code. If someone asks for your source code, you have to give it to them, free of charge. This also goes when you use a dynamically linked library, like, for example: mysql.dll. If you link against it, your source code must be under the GPL, if it isn't, you can be sued. Like, really, in a court.

- The next thing is that MySQL isn't made for that. Users, and thus the client, are inherently not trustable. Everything that you receive from them should be treated as malicious, because you lose all control over the input and the user can easily tamper it. MySQL does exactly what you tell it to do, there is very little you can do in terms of restrictions and access control, and the things you can do are not enough. If you give the user the means to connect to your MySQL server, you are basically screwed, because everyone can then access your MySQL server and run arbitrary commands against it.

-- This doesn't count just for MySQL, it's a general rule of thumb. Whatever you ship to the user, you have to assume to be compromised. You can't ship secrets to the user and expect them to remain secret, if you don't want the user to access something, don't give it to them. If you don't want your game to be cracked, don't ship it. One way or another, your game has to decrypt its resources and make its connection, and that is done on the clients side, on their machines, which they have full control over. Ship it to the client and its compromised by design.

- They way to handle these things is by having a layer between your database and your client, which only you have control over. The client can submit requests to this layer, and get the appropriate responses, but they don't have any other control over the layer. The layer then sanitizes the input from the user. Remember, the client is going to send you ill formed data, if you just blindly accept everything and forward it to the database, they can again do whatever they want. The layer should provide a well formed API that your program accesses, and it should make sure that the user can't break out of the layer and gain additional control.

- The layer does the input sanitation. You can't trust the user, so performing it there just makes you write code twice that you then have to maintain. You can't assume that your client sends sane input just because your software, that, again, runs on the clients machine, is still sane when it reaches your server. Even worse, it gives attackers the possibility to gain knowledge about attack vectors. If you are trying to suppress certain things, then it's valid to assume that this is a potential attack vector into your server.

- The server is responsible for maintaining its data. Not the client. The server maintains the data, and has to prepare for changing clients. Once you ship your software, its in the wild and can't be changed anymore. If you want to change the server or how its represents the data... Well, good luck with that when the client maintains the data on the server. But if you use an intermediate layer that you always have control over, you can change your server, as long as the client facing side of the layer stays the same. Even better, with future updates, you can update your intermediate layer to support both versions, even if the new version has additional features that can't be provided to the previous one.

MySQL for your dedicated server:
- The licensing issue remains the same

- MySQL runs as a server. It's not a library that runs in the context of your application, it runs as an extra daemon, that you usually talk to via a socket. You now have two problems: First of all, you need to ship MySQL with your software (licensing), and spin it up every time your game starts (and make sure it doesn't clash with a MySQL that your user might run). On top of that, you have to use sockets. Sockets use IPC, the two processes aren't running in the address space, so the Operating System has to make expensive copy operations just so you can communicate with your database. IPC is expensive. Period. That's why we don't use microkernels.

- MySQL isn't made for that. Sure, you can run your 64 placer dedicated server with it, but it's like breaking doors open with a tank just because you found a tank in your garage. Simply put, MySQL is the wrong tool for this, it is made to be a long running daemon in the background that can store a lot of data consistently and persistently on a server. You don't spin up a long running daemon on your clients machine.

So, what then?
That depends on what you want to achieve. If you want persistence across all users, use a MySQL server with an intermediate layer that clients connect to. If you just need a database to store things for your dedicated server, use sqlite3 which is lightweight and runs in the same address space as your program (and doesn't have licensing issues, is fast, has a incredible amount of unit tests and is well proven, well tested software). If you just want to store some things for the lifetime of your server, just keep them in RAM in an appropriate data structure.

If you use Gamestudio as your non dedicated Server software: Don't. Even Conitec wrote their Sex MMORPG servers in C and for Linux. Why? Because a) Windows Servers are expensive, b) Gamestudio scales badly and c) Lite-C isn't made to server thousands of clients but to be a Client.

I hope this made things a bit more clear, and also why I disagree with you, EpisloN, about the fact that you gave helpful advice to secure software. If you have any questions, feel free to ask.

See? A helpful reply...

Its not so hard to speak like a human. Btw , congratz on the extensive post, now you gave a very meaningfull reply wich I enjoyed reading.

PS.: When I wrote MySQL I ment any SQL server, but I'm just used to refer to all SQL servers as MySQL. Thats my bad, I didnt explain myself.

SQLITE3 FOR LITEC UPDATE:
Topic:
http://www.opserver.de/ubb7/ubbthreads.php?ubb=showflat&Number=421684#Post421684

Projetc:
https://sourceforge.net/projects/sqlite3cworkitv10/files/3DGD/

Download Release:
https://sourceforge.net/projects/sqlite3cworkitv10/files/latest/download?source=files

Hey Sid, thanks for your post. I completely agree with you, but have a question regarding the GPL virality.



Are you sure code linking against mysql.dll would need to be GPL too? Wouldn't this mean web applications (like PHP scripts using mysqli or Ruby apps utilizing mysql2) were also affected? What did I miss here?
Thank you

Yes. That's the major point of the GPL. If you directly link against, or incorporate GPL'd code, your work must be under the GPL or a GPL conform license (as opposed to, for example, the 3 or 4 clause BSD license which requires you to only attribute the work).
There is a special version of the GPL, called LGPL, which allows you to link against binaries which are under the license without having to open up your source code (for example the glibc is under this license, so just because you use the standard C library on Linux doesn't mean your source code has to be GPL).

But, here is the point: You have to link against it. You can use other forms of communication, for example IPC or the programs ABI, to communicate with the program without having your applications become infected by the GPL. This is crucial, for example, it allows you to ship a disassembler like Hopper which can work with GDB, but doesn't have to be open source. Another example: GCC itself. Just because you compile some source with the GCC, or write an IDE which uses GCC doesn't mean your source code must be GPL. If the GPL'd program/library isn't part of your program (and it becomes if you link against it), you are good to go.

And that's why your PHP scripts are fine. PHP is under the GPL (and must be, because it links against a huge amount of GPL software), but your scripts don't have to be. They don't link against mysql, openssl or anything, they are just interpreted by PHP which does everything, and which in return is licenses under the GPL. Okay, now I hear you: But Ruby is under the 2-clause BSD, you don't know jackshit. Yes, Ruby is, but Ruby isn't PHP. It's not an umbrella over every open source software that didn't run away fast enough, the interpreter doesn't link against any GPL software. The mysql Gem however does, and guess what license it is under? GPL, of course.


Oh, and speaking of MySQL, if you want to use it, but hate the GPL: You can dual license GPL software. I mean, it's yours, you can change the license whenever the fuck you feel like it, with the exception that you can't change it in retrospect. Oracle does that, if you want, you can go to them and ask for a non GPL licensed MySQL. They will give you their bank details and you wire them some money (no, actually, if you smell like enough money, they will send an Oracle rep to you who gives you a five hour long power point presentation about why YOU need Oracle db and their enterprise support)