[GUIDE] Run SQL Query from LUA script

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 damn 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!

Good idea :)

But about performance is great too?

Quote:

Originally Posted by gr4ph0s 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=)

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.

Quote:

Originally Posted by c1ph3r 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 :)

Quote:

Originally Posted by c1ph3r 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?

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.

Quote:

Originally Posted by ismokedrow 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 @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 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).

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

Quote:

Originally Posted by glandu2 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 Good idea :) But about performance is great too?

solved a problem, updated main thread

Quote:

Originally Posted by ismokedrow 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!

Quote:

Originally Posted by c1ph3r 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 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)

Quote:

Originally Posted by mongreldogg 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 :D 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.

Quote:

Originally Posted by c1ph3r yeah great idea every 0.1 ms a new sql connection :D 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 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

Quote:

Originally Posted by mongreldogg sql connection opens only once, when proggie starts. see code:  Spoiler Connection.ConnectionString = "Data Source=127.0.0.1;Initial Catalog=Telecaster;User ID=sa;Password=" Connection.Open() Dim i As Integer = 0 While i < 1 aTimer = New System.Timers.Timer(10000) AddHandler aTimer.Elapsed, AddressOf OnTimedEvent aTimer.Interval = 15 aTimer.Enabled = True Console.ReadLine() End While Connection.Close() every 0.1 ms - just checking files for existing query text and executes query ONLY IF IT EXISTS and after clearing a file.  Spoiler If IO.File.ReadAllText(file1) = "" Then 'here is nothing to do xD Else Try myCmd.CommandText = IO.File.ReadAllText(file1) IO.File.Create(file1) myCmd.ExecuteNonQuery() Console.WriteLine("Query execution successful!") Catch ex As Exception Console.WriteLine("ERROR occured when tried to execute SQL query!!!") End Try End If 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 ;)