Re: Preventing Injection - Client Side

From: Geoff N. Hiten (sqlcraftsman_at_gmail.com)
Date: 11/11/05


Date: Fri, 11 Nov 2005 09:03:31 -0500

Comments inline

"willy" <willrich33@yahoo.com> wrote in message
news:1131557696.739603.25830@g14g2000cwa.googlegroups.com...
> Thanks
>
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid.

You already missed this goal by using Access as the front end. :)

> I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
>
This would require opening a SQL port to the internet. Not a particularly
good idea from a security standpoint, especially when you are talking about
the privileges necessary to do what you ask.

> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
>
Doesn't matter. With open network ports and SQL credentials in the Access
app, your server is open to conection via Query Analyzer or any other SQL
client app. Who cares about SQL injection when I can send any SQL command I
desire.
>
> But besides this measure, the program has these access/security
> needs/issues.
>
> Tables - Create Drop Read Write
> SPs -- Create Execute
>
> In addition the program needs to read from
>
> system_user
> db_name()
>
> information_schema.tables
> Information_schema.columns
> sysobjects
>
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
Maybe on a throwaway system. I certainly wouldn't allow any such
application anywhere near any of my servers.
>
> Willy
>

Sorry if I sound harsh, but I am trying to discourage you from making some
fundamental mistakes in building a SQL application. Access front end
'applications' have caused me more headaches than any other single app dev
environment when connecting to SQL Server.

-- 
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP