sql injection prevention

[Doug]

Hi,

I have a question on sql injection attacks. I am building a tool that
will be used exclusively by our other developers and will generate
stored procs for them dynamically based off input from them. I wanted
to add a "parser" functionality where based off the table and where
clause they choose, the app will parse the query to see if it's valid.
So I'm building a query something like this to run:


SELECT TOP 1 *
FROM [Database].[dbo].[Table] --(Database and table are determined
by user)
WHERE CLAUSE


I won't know what the where clause is, the user can put in pretty much
anything (thus the reason for the parser).


So, I've got it working but now am looking at my code. The call to the

database is done in a DLL and not in my app and thus needs to be
exposed as a public method. I do this with a lot of the code for this
app in case we decide to make the app a Web UI (right now it's a
Windows UI).


Since, it's a public method, it's technically feasible anything can
call it if they reference the DLL. The query I built above is passed
to this DLL, so if someone else used it, it would be possible to use a
sql injection attack on this method.


So my question is, if I wanted to build some code to prevent a sql
injection attack, what kind of rules would I check for? I know some of

the basics, but am not sure of everything to check for.


If it gets too complicated I may just pull this logic out altogether.
I thought it would be a helpful feature but the more I think about the
less I'm sure.

[Marc Gravell]

Re: sql injection prevention

Since you are actively asking users to enter SQL, I'm not sure I would
class [ab]use here as an injection attack... if you leave the door that
far open, etc...?

Personally, unless I was writing a database UI (i.e. Access), I
wouldn't let my users any where near SQL; you can exclude most common
attacks by checks, but at the end of the day, people likely to be
attempting SQL injection are likely to be reasonably smart, and may be
able to find something you haven't thought of.

I would try to parse the user input by known conditions, checking
column names etc from a known list, and construct my own SQL from
theirs, using parameters for all value inputs (
*especially* strings).

And exclude all of EXEC, --, /*, GO, SELECT, CREATE, DROP, JOIN, UNION,
RAISERROR, etc ;-p (or rather, sepcifically don't include these in my
approved list). Additionally, this allows you to abstract query
aliases... i.e. the user puts "CustomerNa me" in their query, you put
"c.Name" in your query, because you have joined to the CUSTOMER table
as "c"...

This way you can change your outer query and even the entire table
schema without impacting the users. Of course, a view can achieve this
last bit as well...

Marc