Register for your free account! | Forgot your password?

Go Back   elitepvpers > MMORPGs > Rappelz > Rappelz Private Server
You last visited: Today at 01:12

  • Please register to post and access all features, it's quick, easy and FREE!

 

[GUIDE] Run SQL Query from LUA script

Reply
 
Old   #1
 
elite*gold: 30
Join Date: Mar 2012
Posts: 630
Received Thanks: 282
[GUIDE] Run SQL Query from LUA [UPDATED!]

How long time we were searching for solution?
Lets have some guide and use it to f**k officials by doggie style=)

Everything we must have - one function, one program and one brain to find it useful.
UPDATED! Changed a working schema to keep a good performance.

There is some files in the pack below:

Lua script for such a function sql_query.
VB Source code for Console SQL Querier (CSQLQ.exe)
Folder with all files to put into GS folder

How to use: put all files into GS folder, restart a server and start CSQLQ.exe
then use sql_query function from anywhere using LUA.
To see a progress, look at CSQLQ console.
If you wanna get logfile - change a code


Finally, we have a solution to run SQL query from any place in LUA script.

P.S. command doesnt work with SELECT operator to return values. It can be used for example, with UPDATE, DELETE or INSERT. But you can edit a program and LUA script to do it.

P.P.S. dont push me with my **** VB script lol its just a main idea how to run queries secure and easy, not more=) If you can make it better - make it better!
Attached Files
File Type: rar LUA_SQL.rar (41.0 KB, 254 views)



mongreldogg is offline  
Thanks
5 Users
Old   #2
 
elite*gold: 0
Join Date: Aug 2012
Posts: 312
Received Thanks: 252
Good idea

But about performance is great too?


gr4ph0s is offline  
Thanks
1 User
Old   #3
 
elite*gold: 30
Join Date: Mar 2012
Posts: 630
Received Thanks: 282
Quote:
Originally Posted by gr4ph0s View Post
Good idea

But about performance is great too?
i think, proggie can be modified. for example, it can use threading and work in system tray. ive just released basic guide, main idea=)
mongreldogg is offline  
Old   #4
 
elite*gold: 0
Join Date: Sep 2008
Posts: 1,570
Received Thanks: 1,154
This isn't the best way. The GS will freeze till it gets a returncode from the program. It will cause horrible lags/freezes if the commandexecution will take some time.

I tried something like this some time ago and server crashed lots of times if there are more than 20 players.

Oh and it's a biiiiiig lag of security. In my opinion nobody should use this on a productive Server if he don't know the mssql functions and what everyone with permission 100 is able to do with a function like that.

@Mongledogg: your first p.s. is wrong. you are able to do much more with that function.


c1ph3r is offline  
Thanks
1 User
Old   #5
 
elite*gold: 0
Join Date: Aug 2012
Posts: 312
Received Thanks: 252
Quote:
Originally Posted by c1ph3r View Post
This isn't the best way. The GS will stop till it gets a returncode from the program. It will cause horrible lags if the commandexecution will take some time.

It's what I thinked
gr4ph0s is offline  
Thanks
1 User
Old   #6
 
elite*gold: 30
Join Date: Mar 2012
Posts: 630
Received Thanks: 282
Quote:
Originally Posted by c1ph3r View Post
This isn't the best way. The GS will stop till it gets a returncode from the program. It will cause horrible lags if the commandexecution will take some time.
so where is the best solution?
and what about coroutines in LUA?
mongreldogg is offline  
Old   #7
 
elite*gold: 0
Join Date: Oct 2010
Posts: 2,557
Received Thanks: 2,412

Something like this used for only background updates (e.g. things that aren't dynamically used by the gs) would be wonderful -- saying you don't allow anyone to use it (e.g. keeping it out of dbo.allowedcommandsforpermission) The security implications are minimal saying you have a secured machine (within reason). As far as the way the GS would respond the impact would also be minimal.

For example I am creating a new series of quests in which the user must shift from one class to another while info about said class gets stored (so that when they take their main class back certain values are returned: level, jp, jlv etc..) Originally I thought of doing such with set_flag/get_flag but this method would be far better. As the variables I set would have no true bearing on the data being used by the GS I see no reason why the GS would act oddly.

I look forward to testing some implications of this method, thank you.
ismokedrow is offline  
Thanks
1 User
Old   #8
 
elite*gold: 30
Join Date: Mar 2012
Posts: 630
Received Thanks: 282
Quote:
Originally Posted by ismokedrow View Post

Something like this used for only background updates (e.g. things that aren't dynamically used by the gs) would be wonderful -- saying you don't allow anyone to use it (e.g. keeping it out of dbo.allowedcommandsforpermission) The security implications are minimal saying you have a secured machine (within reason). As far as the way the GS would respond the impact would also be minimal.

For example I am creating a new series of quests in which the user must shift from one class to another while info about said class gets stored (so that when they take their main class back certain values are returned: level, jp, jlv etc..) Originally I thought of doing such with set_flag/get_flag but this method would be far better. As the variables I set would have no true bearing on the data being used by the GS I see no reason why the GS would act oddly.

I look forward to testing some implications of this method, thank you.
ye, maybe much better to use flags and global variables in scripts to make some advanced scripts, but as i said:
we must find it useful.
as example, we can not get some values from dbo.Character and dbo.Summon using simple gv or gcv commands (such as summon slots or creature code, and i was looking for solution to find it and use in script).
of course proggie can be modified to work with SELECT operator and get values we need using queries. and we can also use LUA coroutines, we can use everything we know and will know getting experience in our work.
and as i said: thats just a main idea. modifying of this feature is work that can give a results, isnt it?
about security - if you have bad secured machine, you will lost database or GS with and without this feature=)

Quote:
Originally Posted by c1ph3r View Post
@Mongledogg: your first p.s. is wrong. you are able to do much more with that function.
i thought the same when i tried to test but i see that without modifying a proggie or sql query function, i cant return values that i got by SELECT
*edit : ye right sorry. updated main thread

Quote:
Originally Posted by ismokedrow View Post

I see no reason why the GS would act oddly.
C1ph3r's right. if we won't use something like threading, GS will freeze until program returns that query's done. because os.execute command runs and it will stop only when program returns something like 'done' or it crashes or closes itself. so if we will make query that will be executed by 10 or 15 sec, all this time GS will be freezed (as with WHILE operator, for example).
mongreldogg is offline  
Old   #9
 
elite*gold: 0
Join Date: Apr 2012
Posts: 449
Received Thanks: 770
you should use SQL Server Management Studio to do that, a SQL Server is not local only, it's a real server which you can open to the internet. But you have to add some security like a password for sa to do that (but that's would be definitely much secure and stable than using the gameserver)

If you don't know how to have a well secured server, then make your CQSQL.exe to be a server. Use of SSH can be a solution to limit security consideration in the CQSQL.exe program. .NET can offer some encryption algorithm if needed
glandu2 is offline  
Old   #10
 
elite*gold: 30
Join Date: Mar 2012
Posts: 630
Received Thanks: 282
Quote:
Originally Posted by glandu2 View Post
you should use SQL Server Management Studio to do that, a SQL Server is not local only, it's a real server which you can open to the internet. But you have to add some security like a password for sa to do that (but that's would be definitely much secure and stable than using the gameserver)

If you don't know how to have a well secured server, then make your CQSQL.exe to be a server. Use of SSH can be a solution to limit security consideration in the CQSQL.exe program. .NET can offer some encryption algorithm if needed
ARE YOU SERIOUS?xD
read the main post

Quote:
Originally Posted by gr4ph0s View Post
Good idea

But about performance is great too?
solved a problem, updated main thread
mongreldogg is offline  
Old   #11
 
elite*gold: 0
Join Date: Sep 2008
Posts: 1,570
Received Thanks: 1,154
Quote:
Originally Posted by ismokedrow View Post

Something like this used for only background updates (e.g. things that aren't dynamically used by the gs) would be wonderful -- saying you don't allow anyone to use it (e.g. keeping it out of dbo.allowedcommandsforpermission) The security implications are minimal saying you have a secured machine (within reason). As far as the way the GS would respond the impact would also be minimal.

For example I am creating a new series of quests in which the user must shift from one class to another while info about said class gets stored (so that when they take their main class back certain values are returned: level, jp, jlv etc..) Originally I thought of doing such with set_flag/get_flag but this method would be far better. As the variables I set would have no true bearing on the data being used by the GS I see no reason why the GS would act oddly.

I look forward to testing some implications of this method, thank you.
1. You can't dissallow anything for permission 100. Only possible to dissallow script execution via gameserver.opt and this feature is bugged.

2. Since it's possible to execute almost every dos command via sql commands directly on the root machine it's one of the biggest security issues in my opinion.

3. Keep this in your mind: Every player will have a short gamefreeze during the SQL Command!


The functions of the cqsql should be limited for security reasons. Or the user rights for the executing user must be limited!
c1ph3r is offline  
Old   #12
 
elite*gold: 30
Join Date: Mar 2012
Posts: 630
Received Thanks: 282
Quote:
Originally Posted by c1ph3r View Post
3. Keep this in your mind: Every player will have a short gamefreeze during the SQL Command!
updated main thread=)
found a solution. diferrence is:
lua function just writes query into file.
proggie opens a file and executes a query written there every 0.1 seconds.
i think its much better for performance and it will stop freezes

Quote:
Originally Posted by c1ph3r View Post
1. You can't dissallow anything for permission 100. Only possible to dissallow script execution via gameserver.opt and this feature is bugged.
another way, you can give permission 100 to NOBODY.
and you can manage permissions list in database (i already did)
mongreldogg is offline  
Old   #13
 
elite*gold: 0
Join Date: Sep 2008
Posts: 1,570
Received Thanks: 1,154
Quote:
Originally Posted by mongreldogg View Post
updated main thread=)
found a solution. diferrence is:
lua function just writes query into file.
proggie opens a file and executes a query written there every 0.1 seconds.
i think its much better for performance and it will stop freezes


another way, you can give permission 100 to NOBODY.
and you can manage permissions list in database (i already did)
yeah great idea every 0.1 ms a new sql connection let the gs crash.

but hey it's only my opinion and I personally don't like that way^^ Everyone can use it. But keep in mind what you are doing with a function like that.
c1ph3r is offline  
Old   #14
 
elite*gold: 30
Join Date: Mar 2012
Posts: 630
Received Thanks: 282
Quote:
Originally Posted by c1ph3r View Post
yeah great idea every 0.1 ms a new sql connection let the gs crash.
sql connection opens only once, when proggie starts. see code:
every 0.1 ms - just checking files for existing query text and executes query ONLY IF IT EXISTS and after clearing a file.

Quote:
Originally Posted by c1ph3r View Post
but hey it's only my opinion and I personally don't like that way^^ Everyone can use it. But keep in mind what you are doing with a function like that.
i understand and as i said and will say again
we must find it useful
even if its useless.
even more, its really useful i think
mongreldogg is offline  
Old   #15
 
elite*gold: 0
Join Date: Sep 2008
Posts: 1,570
Received Thanks: 1,154
Quote:
Originally Posted by mongreldogg View Post
sql connection opens only once, when proggie starts. see code:
every 0.1 ms - just checking files for existing query text and executes query ONLY IF IT EXISTS and after clearing a file.


i understand and as i said and will say again
we must find it useful
even if its useless.
even more, its really useful i think
my fault but as i said i wouldn't use this in .lua. it's not very elegant because every os.execute will cause a short freeze for every player.

and for me there is no need to execute sql via .lua. You can modify everything with the given functions. Even summon slots and creature code


c1ph3r is offline  
Reply

Tags
database, lua, query, script, sql



« Previous Thread | Next Thread »

Similar Threads
[Guide]how to add pets/mounts to your server and how to make a query
i made that post because more than 10 memmbers from Epvp asked me to teach them how to add a new pet or mount . Hope you like it ;)...
26 Replies - EO PServer Guides & Releases
[Suche]Query für "Alteklinge, Reichsklinge" &' Stichdolche NUR QUERY!
Ich weiß ich werd nervig aber ich suche die Query's für Alteklinge, Reichsklinge &' Strichdolche Why? Naya, weil ich in der DB i-wie nur...
1 Replies - Metin2 PServer - Discussions / Questions



All times are GMT +2. The time now is 01:12.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.

Support | Contact Us | FAQ | Advertising | Privacy Policy | Abuse
Copyright ©2017 elitepvpers All Rights Reserved.