(MS) SQL statement filtering

Jenda has asked for the wisdom of the Perl Monks concerning the following question:

In a project I work on I need the admins (not really programmers) to be able to specify pieces of SQL that are to be evaluated to compute the value of some fields. The number of people how have this level of access is very small but nevertheless this gives them too much power. They might cause a lot of problems with incorrect SQL.

They are supposed to use only things like

if @variable = ''
  SET @value = 'No option selected!'
else
  SET @value = 'They selected ' + @variable + '.'
[download]

That is they should be allowed to use only "SET", "IF', "ELSE", "BEGIN", "END", execute a few carefully selected stored procedures, use several selected functions like SUBSTR and CHARINDEX etc.

A message saying "You are doing something potentialy dangerous, please don't". would be just great.

Did anyone ever did something like this? Does anyone have a MS SQL (2000) parser that I could use to parse the SQL before I start looking whether they are using only the allowed stuff?

Thanks, Jenda
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.
-- Rick Osborne

Comment on (MS) SQL statement filtering Download Code

Replies are listed 'Best First'.
Re: (MS) SQL statement filtering by cciulla (Friar) on Jun 04, 2003 at 19:03 UTC
Is it within the realm of the possible to give the admins a sandbox without data modification rights (e.g., db_denydatawriter or some such) -- that is, have your U.I. log into SQL Server using a special account using this role, and they can play all they want without hosing anything up. In the event they need to do anything potentially hazardous ("drop database master"), they can log into Query Analyzer with another account... C²	[reply]
Re: Re: (MS) SQL statement filtering by Jenda (Abbot) on Jun 04, 2003 at 19:13 UTC
Well I don't think that would be reasonably complex to do. I mean the SQL is not something they would want executed when they enter it. It's part of a "site"'s settings and the SQL is being run when I "export" data for that "site". I guess I should have given more background information. The project is a web based systems that lets you post job ads to many different sites at once (Monster, HotJobs, AJB, ...). It bends backwards to simplify the users' task. It first asks for the "common" fields (except those that are not being used by any of the sites) and the for the site specific ones. Where it makes sense it prepopulates the site specific fields with some data extracted from the common fields. Later on it creates files with batches of jobs for each particular site and sends them. Sometimes the "prepopulation" rules are rather complex and sometimes I have to mangle even the data entered into the site specific fields to suit the site's twisted requirements. There are special actions predefined for the prepopulation, but to prevent myself from going crazy from having to create hundreds of different actions I allow the admins to enter a piece of SQL that massages/combines the data as necessary. The code is then run inside the stored procedure that returns the entered/computed value of a field either when showing the fields to a user or when creating the batch file. And the code needs to be able to read the values of other fields and/or some other job related information. Jenda Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Rick Osborne Edit by castaway: Closed small tag in signature	[reply]