January 21, 2003 at 3:28 pm
I have a client that would like to issue adhoc queries via the web. I would like them to be able to issue Adhoc Queries but want to make sure we protect our databases from the SQL Injection issues.
Does anyone have an SP the accepts TSQL, and validates that no SQL Injection is possible?
I'm sure a number of you think this is crazy, but then again you need to work here before you say that.
Allowing adhoc queries will eliminate a bunch of code to basicaly support adhoc reporting via pull downs. Trying to give customer reporting capabilities without building a reporting sub-system.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 21, 2003 at 4:25 pm
I previously posted code for this as an idea but cannot find it and of course cannot find a save here.
Basicall it was having an SP that took the entire string and using CHARINDEX ot PATINDEX looked for keywords that could be a potential issue. Like
TRUNCATE
DELETE
UPDATE
INSERT
xp_
sp_
GRANT
REVOKE
DENY
DBCC
etc.
I will look a bit more and see if I find but you might try digging around in the search to see if you come across it.
January 21, 2003 at 9:43 pm
Have you considered using a (different) login account for those adhoc queries that has very limited access? i.e Only select permission on the tables you want to let them query.
A malicious user could get around keyword filtering e.g:
exec('UPDA' + 'TE Account SET Balance = 1000000')
Edited by - BrendonSmith on 01/21/2003 9:49:41 PM
January 22, 2003 at 3:56 am
sorry should have included
exec
not really too much they can do without that as far as keywords go and sp_executesql is covered by
sp_
I am sure there are some others but right off cannot think of them all.
But I do agree if you only need SELECT rights then it should work great with a seperate user with read only rights.
Edited by - antares686 on 01/22/2003 03:58:06 AM
January 22, 2003 at 6:57 am
We where thinking the same on the SELECT only. Good thoughts here. I was kind of hoping of not having to re-invent (write) the wheel (code) that handles parsing the command to ensure that no SQL injection takes place.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply