Limitation of SQL Execution
 

Right now, I'm designing an interface on Classic to make a bridge way for the database and Staff. Although this makes a simplicity for others to use it, the vulnerabilities for requestsql() and requestsql2() exist.

If possible, I think it would be a wise idea to allow some sort of Server option to allow these commands to be executed only by certain allowed DB NPCs, Maybe classes, etc. The interface I'm designing though is compatible with anything that wants to request access to the databases, complete w/ permissions. But still, its security is still compromised due to the fact anyone with NC or Level access can cause unwanted hassles with our databases.

Couldn't you use callstack protection? You can't restrict the commands themselves but you can restrict whatever system you're using to write and access the information and make it so only that system can make alterations.

I think he's referring to someone just doing in a level or script somewhere.

But if they know how to do that then they're probably knowledgeable enough to use the restricted scripts anyway.

Best course of action, don't let people you don't trust have access to the scripts and levels, and filter through them if you have to. Besides there are so many better things they could do maliciously.

I suppose you could still incorporate callstack, though, by storing a backup of the data and making it so if the system that is supposed to access the database isn't the one updating the database then whatever changes made to the data is automatically reverted back to the backup.

Could cause an even bigger problem...

How would one detect what's calling requestsql() to update the database?

Yes, especially since someone could get ALL the table names from sqlite_master. Luckily, backing up a SQLite database is as easy as copying a single file. Still, I agree that there should be some kind of built-in protection.

I would propose something like a server option to limit the calling of requestsql() to a list of weapons, similar to what was done for Client-RC. This would need to protect against external class joins too, I don't know how or if the Client-RC did that.

At the very least, it should be disabled in level scripts.

You could use a loop to compare the database with the backup and revert the database to the backup if they aren't the same file, then when the main system updates the database, simply have it update the backup at the same time. Of course, if there's no way of protecting the backup file, then the backup file itself could be changed, but I suppose you could try hiding it as well as possible to avoid other people knowing about it (random file name).

This wouldn't be very secure unless you made some file name randomizer that always recreates the backup file with a different, random name so that no one can know its name unless they had direct rights to the file, which would help prevent people from messing with it that aren't supposed to. It would take some complex systems but I suppose it would be possible to mimic something like this.

Ahhh, I see what you're saying. However, for large databases with thousands of records, it isn't a very good idea to be looping. I guess one solution would be to keep track of the last inserted/updated record, and validate that instead.

buuuuuut, I'd still rather see built-in protection :p.

This is all wrong (though logical) until Stefan allows .db files to be modified by script.

It would be good to have some kind of script rights system, may be for the start some way to set general rights that apply to all npcs, weapons and levels, and then a way to define rights for each single object. So it could be used to disallow putnpc2 by level npcs, and also disallow sql access.

We do not need to be modifying database files with anything but SQL queries or built-in functions. Anything else just invites trouble like file corruption or abuse.