litec-sql dll (mysql for A7)

Hello people,

I spent the entire day on making a user friendly mysql connector! This version links to a database on my personal server and practically every query can be performed on it.
Exceptions can be read in the comments in the mysql_test.c.

I recommend not to make use of the database now for development, not that I care but it will be emptied every now and then. Feel free to use some queries on it, it's fully sql-injection sensitive . DROP and CREATE DATABASE won't work, and I would prefer you not to use DROP TABLE hiscores. There are other people who want to see what's in it .

Download:
litec-sql_0.1.zip


And A6?
I think it works fine for A6 as well, at least for now, if you convert it. If theres much animo for an easy to use c-script version, I'll make one. Vote in the poll.


The commands
There are three commands available:


Will connect you to the database
returns 1 when succesfull


Sends a query to the database
returns row(s), column(s) info, or an errormessage.
rows are separated by a comma (,), columns by a colon (:).


Closes the connection

There is a forth, called db_connect and allows to connect to a different database, but this will be available in the future.


The next step
This said, I have another plan for you beloved 3dgs forumusers. If this is running well, I will open a register page for people to create their own personal test database. Give your username and password in the registration form, fill them in in your testdb_connect() function, and you're set!

I do like to know if you think this (and other features) will come in handy,so please take ten seconds to fill in the poll below.

Regards,

Joozey



The Poll
[redirect link to poll, can't make polls in user contributions]


The screens




--

Here's some things I would add or change to make it more better

• Database connection would return connection variable.
• db_query should be altered to db_query(string* query, int connection)
• db_close should be altered to db_close(int connection);
• This allows multiple connections to be used (useful in some places).
• make db_connect(string* server, string* username, string* password); and db_select_database(string* database, int connection); or mix these, even though keeping these two separate does make it more agile (no need for new connection when hot switching databases)
• add useful functions such as db_num_rows, db_list_tables, etc.

Polls aside, you know I am 100% behind any A7-MySQL initiative, bro!!
Keep the DB dream alive!

Yes, noted.

I could, but you could do too within a query. I don't see how this helps improving the DLL other than doing the SQL for you .

You... don't like polls?

I'd like to see an implementaton similar to MySQL for PHP regarding function names, parameters, return values...

Apart from that: fine job

Joozy I love your contribution and you will save a lot of people a lot of work. I don't mean to give anyone a hard time but as an System Admin and serious C/C++ developer for over 25 years I have had to read a lot of licenses. We also have a patent/technical attorney to help us with the stuff we don't understand. MySQL is not interested in Game Development, and their license for using the dll in your own distributable program was over $10,000.00 USD the last time we checked. It is only free to web hosters and not to software developers. We were very sorry to learn this ourselves as we had developed an entire system online of MySQL databases and could not use them even on our local network. We were forced to move to SQLite because of the license restrictions from MySQL. Remember they are a for-profit company and have to sell something to keep developing. They sell seminars, support and licenses. For us small time developers the cost was prohibitive, plus we have to make sure that we can deliver all parts of our software legally and without restraint.

Nice work, this is a great LC based code for MySQL connectivity/compatibility towards A7

Cheers

Frazzle

How do we connect to a database??

can this connect to a database over the net?
How to configure the environment for that?

When applying for a data base, you need to set up a private server (password based to say it easy) via MySQL, connect the in and output via php (query based), send that data over a dll which Joozey already made for you (thank you again ^^) and use the corresponding dll functions in your script via SED (A7)

Cheers

Frazzle

thanks,
could be basically used to get a multiplayer server list then, or?

Joozey. I have not tested your release, but can you tell me if your plugin has replaced my updated mysql dll contribution? If so I don't have to continue working on that project. And can use time for mp on the linux.

@ Oxy:

That would indeed be a possible way to achieve a list of servers

Cheers

Frazzle

Blast, everyone started commenting this weekend.
Anyhow, I managed to put all the results in a multidimensional array, but it only features string types, no integers. So you'd need to convert to a number manually yourself. I'm still trying some things out so no new release yet.

You can also link to your own database with the query db_connect(), instead of making use of the test database placed on my own server.

Also, it didn't appear to work if you do not have libmysql.dll. This library you can download from internet, but I already included it in the next release. I saw Destop did the same for his mysql plugin.

@atomix:
Is your point that I'm not using the open source library in a legal way? I'm using a C API which appears to be free of charge. Aside that, you still need a MySQL database to get things running. So they make profits anyway.
And I make use of the services of my webhoster, so what's the problem? If there is, I'd like to know but I think there's no problem at all .

@oxy & frazzle:
Why yes, but the functionality to connect to your own database currently lacks you can currently only connect to a test database on my server. But when I'm done with the new version it's possible to use your own database as well.

For a project I was trying to get a list of servers done, and I practically managed to do so. You'd still actually need the use of cron jobs (script execution every x seconds on the server) to clean the database of false addresses. If your game crashed while it was on the server list, your game did not have the chance to remove itself from the list again, so you get some garbage in the list. Of course this doesn't occur alot, but you need something like that to keep the database clean. (another solution would be to remove the address when a client tries to connect to it).

@Frazzle:
Thanks for the kind words as always ^^.

@D3D:
I think all I have done is creating a different way of receiving the data, which A7 made possible. But I actually didn't try out your solution. I just wanted to try if I could do this, and baked it into a DLL for the heck of it ^^.

Joozey it's 'easy' once you get started and so much fun to play/learn while working with MySQL. My plugin (original by Destop) does not have a lot of functions, but most important are included for Litec and WDL. Have got many response on when i'd continue work on it though I made choice to wait for Conitec released stable Litec for A7 and very bussy with sth on linux. So I thought lucky me you will takeover.

Good explanation for the legal part on reply above. Furthermore the danger when cripple db's. That's why you need backup and cronjobs

All the best,

Rick

Joozey: I greatly appreciate your reply . As long as you don't distribute any of MySQL's code you are fine. But if your dll uses any of their dll's or lib files you better talk to them and get a license in writing. I assume you are creating a game to distribute to the piblic. Listen to a guy with over 30 years in the programming industry. Everyone is sue happy and the worse the economy gets the more they sue.

I still can't get your system to work as all of my MySQL databases are on external hosted web servers and I need to be able to include a link to them in order to open them. I refer you to the php statement required to connect:

In other words my databases are not local and are spread out over several servers with their own host domain names like mysql190.secureserver.net with different domain names for our different web sites.
We need a way to access any of the servers.

Take care with PHP/MySQL on those shared hosting plans. I would recommend read essential php security by chris shiflett. One of the books that made me switch from shared to dedicated at once.

Now back to the legal part. You can distribute the required dll's with your software, but you need to put proper c&c in your credit/copyright. At least this is what has been told to me when I worked on the plugin from Destop. I refer here to the community release of MySQL and no other products.

Atomix. I don't understand why you need different MySQL servers. I guess you need them as fallback? Maybe you can write a polling script to check if the server is alive and if not try the next one. So with the line of code above you should be able to get a 0 or 1 and go from there.

It doesn't bake the libmysql dll in, it just functions as a bridge to lite-c. You will need to have the mysql.dll located at your game folder yourself. So if you plan on distributing a game using libmysql.dll (downloadable from the mysql site), you will need to get the license (if there is such a license at all), not me .

Because the current downloadable version only connects to a database on my server. I was planning to let people who don't have a server themselves register their own small database at my server, and then my dll would connect to it when they use the authentication details I gave them. But a function to connect to an external database was requested earlier, so I implemented that in a new version. Which I didn't release yet :P. So no wonder you can't get it to work to your own database.

I'm not sure if I understood. Which shared hosting plans?

Joozey. If one has his/her site hosted with many other sites on the same server, this is called shared hosting. I guess you already know that. The security issue is that any other person on same server can for instance, but is not limited to sniff data.

Another example. One would think that their php script source can't be seen by others. Really in most cases this is false. A bad person with access to the server has also right to execute php and thus able to read your script's source in plain text.

Aside from the above. If the bad person download the game and connect to the MySQL server, he/she could use wireshark to sniff what is send as well. You could encrypt data which makes it a little harder for the wanabe-hacker though.

I'm not quite sure if I understand this. So if I use this .dll to access my database online from A7, and publicly release the game with this .dll in it, would there be any cause for concern?

@ Joozey:

I should have mentioned it more clear indeed, your DLL will lead to your database
Thus users need to wait until you changed it towards the final version.

@ William:

If you set up your own private MySQL database (and not shared by any other users), then there is really no problem. The problem occurs (as D3D mentioned) once you start letting other users connect towards your database. PHP is very easy to learn since that was the general intention of the developers of PHP but it brings one problem Due to the fact that it is that easy, a shared database makes the php scripts visible so other users can attempt to change it (like D3D mentioned as well ^^)

Keep to the basics, make it private to say it simple

Cheers

Frazzle

@D3D: I don't know the precise security details of my a server, but I do know that each database is separated from the rest of the server. Furthermore it is prohibited to use commands like drop database and create database. Also, the exact details to log in are baked into the DLL, and such I believe untraceable. So I don't think it's possible to sniff through other ones database without proper authentication, but I could be wrong. I'll take a look for wireshark as I have never heard of it . Any more possible breach tips are welcome, Thanks!

I searched a little about legality of mysql C api and shared database usage. But I don't think it's really all that tight. If you have your own database then there is nothing to worry about as far as I can read (like Frazzle said), I'm still unsure if creating databases for other people is legal, but as this is not possible yet anyway, theres nothing to be concerned about anyway.

Besides, if you happen to register a database on my server, I wont allow you to use it for commercial projects, so I believe it's still not an issue.

I greatly appreciate your input. If you say so on the license, but our attorney says stay away, and I'll listen to him. As far as multiple servers go that is not what I was talking about, to satisfy your curiosity though, suffice to say we have lot's of web servers. All of the MySQL databases need the 3 parameters to operate on our systems. But hey no biggie because we are NOT using MySQL them for our distributed software packages.

Joozey: If those MySQL db servers are seperate or chrooted it becomes a little harder. Though if you are on the same server using php it can become insecure very soon. That is if php can be executed by others on the same server. I'm not sure, but I think not a lot shared hosting have everything chrooted because it will take much more resource.

I can send few scripts to test. I've not posted them on this forum because i'm not certain if it's allowed. And not want to give anyone bad ideas. Security was also one of my problems when working on Destop MySQL plugin. On my server i'm using sth like fail2ban, portsentry & snort to keep out most bad people. This also helped to keep running stable and lockout the automated bruteforce hackers from locations such as China. In USA, Germany or Holland I can talk to the provider, but China..

Atomix: Have to take a look. Though i'd like to mention there is MySQL++ project which let you interface with MySQL and can be used with Litec. This project was released under LGPL license.

If i'm correct it's allowed to freely enchange and distribute, although this might not be the case anymore: if linked and fall under gpl; I'm not making commercial apps with GameStudio so i'm not really worried about all that.

For commercial applications you might require a license and must use enterprise instead of community release. Not going to speak against advise from your lawyer. Guess it's best to open another thread on this issue or talk to sales rep at MySQL department.

D3D: I bought a server domain at some hosting company. I don't own the whole server. They secured everything for me.

But if you like to try some scripts, I'm willing to test the security of the server ^^. I'll send you a PM.

If you want to make a commercial game that features an online database, you should probably be looking for your own server anyway.

D3D: Thank you for taking the time to respond to my statements. I will take a good look at MySQL++ (thanks for the tip) if I can't get the sqlite libraries working (haven't been able to yet) this may be the alternative.

i posted an example using sqlite at your other thread (about using dlls.), if you checked it and cant get it working, you can always ask me.

Just what I need

This is a fairly outdated version, although I have not yet released the renewed thing. It's still under development, and hope to release it very soon.

Though it looks great, I need to be albe to connect to my own database for it to be usefull to me.

Any aproximate date of when this feature will be released?

Is there any chance I could get a version of the current plugin but being albe to connect to any database?

after so much time withought an answer I'll take it as a "no", hehehe.
Just one question, are you still working on this project or is it abandoned?

It's suspended for an undefined amount of time.

:\

Hey, I like the easy way out

but is there anyother way to connect joozey? i know there is a beta plugin somewhere but the link is broken so i can't use it.

if you know something let me know.