CODEWITHSUNDEEP
c#sqlpostgresql
GBreen12
GBreen12

Reputation: 1900

Executing user created SQL queries

I need to know if there is a safe way to run a SQL query that comes in directly from the user. We have an admin tool where the admins can create Key Performance Indicators by giving a SQL query to be run against the database every hour. The problem is this process is obviously susceptible to bad things happening by user error or malicious users (for example, a user could easily delete data from a table in this query). I'm wondering if there is a safe way to execute these statements or know what they are going to do beforehand so if they are doing anything other than a select we can block it.

One suggestion is running the SQL in a transaction and then just rolling back the transaction every time. Is this a decent solution?

Upvotes: 1

Views: 161

Answers (3)

Twelfth
Twelfth

Reputation: 7180

A few methods...

Find a BI tool that gives them some ability to design their own report. Build a cube to support it and let em go nuts (BI tool would prevent the more harmful queries, and also ensure it's assembling queries according to how your DB is setup)

You can save their query as a string and do some substring functions to look for key words like drop, delete, update, or anything else you might think is malicious. This would appear to be the hard work method for you as string searching is never that fun.

Finally...permissions work, just get them into something read only. I have the strange feeling thats a no go by the way you've worded this though.

As a last comment...as dangerous as deletes and updates could be, you'll also have to be vary wary of horridly designed select statements. Select * from tbl1,tbl2,tbl3,tbl4,tbl5,etc... Could lock up your database and consume every resource it's got.

Upvotes: 2

dev8675309
dev8675309

Reputation: 396

Sounds like your best bet may be to allow the queries to run by a user with limited permissions (i.e. a read-only user). Something similar to this answer: How to grant users read-only access to all databases

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28920

Few more suggestions

1.Search for DDL Commands in query and reject query if they exists
2.Try limiting Permissions by granting only select if possible
3.Log query and user who gave the query ,so it will be helpfull for forensic analysis

Upvotes: 2

Related Questions