|
|
|
date: Thu, 28 Aug 2008 09:41:52 -0700 (PDT),
group: microsoft.public.sqlserver.security
back
Re: Website & SQL Server Security
"csgraham74" wrote in message
news:97929c8c-2936-4d02-ab24-683bec63d430@i76g2000hsf.googlegroups.com...
> Ok,
>
> im sure this has been dealt with before but when i read other posts i
> kinda get lost in the jargon. Ok the reason for my being here is that
> i developed a website that recently got hit by a sql injection attack.
>
> Of the back of that i have decided to rethink my security strategy to
> ensure it doesnt happen again.
>
> from my vague understanding of sql server i have decided to setup my
> sql server authentication to allow only db_datareader access and also
> to give grant permissions to my stored procedures.
>
> To me this means that front end users will only be able to run
> "SELECT" command statements on my datatables & run the stored
> procedures as i have set them up.
>
> what im wondering about is this really what this means or does this
> mean that someonce could do sql injection again through one of my
> forms and delete or insert into my database ????
>
>
> BTW ive added extra validation to all my user input formsto restrict
> SQL Injection but its the database stuff i need to know more about.
>
> any help appreciated.
You realize that to use impersonation (where SQL statements within a stored
procedure execute with privileges of the procedure's owner, when called by
less-privileged users who have been granted permission to execute) those
stored procedures cannot use any dynamic SQL within them. (Well actually,
they can, but dynamic SQL in them executes with the privileges of the
caller, not the owner.)
But the bonus of doing all your writes from within stored procedures is that
you guarantee yourself at least some degree of parameterization, and as long
as you don't concatenate dynamic T-SQL statements with user input passed as
parameters into your procs, it's much easier to safely implement a fair
level of dynamic -- for example, sp_executesql provides a way to pass
parameters to dynamic SQL. Given that this is the case, with due caution,
you can create privileged connections to execute the stored procedures, and
unprivileged connections to do the rest, without sacrificing too much
safety.
-Mark
> CG
date: Fri, 29 Aug 2008 16:05:07 -0700
author: Mark McGinty
Re: Website & SQL Server Security
csgraham74 (colin@thewebbureau.com) writes:
> from my vague understanding of sql server i have decided to setup my
> sql server authentication to allow only db_datareader access and also
> to give grant permissions to my stored procedures.
>
> To me this means that front end users will only be able to run
> "SELECT" command statements on my datatables & run the stored
> procedures as i have set them up.
>
> what im wondering about is this really what this means or does this
> mean that someonce could do sql injection again through one of my
> forms and delete or insert into my database ????
Yes, they can, but the possibly damage would be restricted, as all they can
do is to access data. Until the day you decide that your users need to
be able to update data, and you change the permissions.
You don't have to have very much understanding of SQL Server at all to
prevent SQL injection, but you need to know your client API.
SQL injection happens when you do things like:
sqlstring = "SELECT yadayada FROM tbl WHERE col = '" & inputvalue & "'"
Say that the users searches for information on his old mate Brian O'Brien.
What happens in this case? You get a syntax error:
SELCET yadayada FROM tbl WHERE col = 'O'Brien'
Which is not nice to the user.
If the user is more malicious, he may enter: '; DROP TABLE Orders --
Now you get:
SELCET yadayada FROM tbl WHERE col = ''; DROP TABLE Orders --
Which is not a syntax error at all, but it will execute if permissions
permit.
The proper way to do this is to use parameterised queries. The exact
syntax depends on your API, here is an outline:
sqlstring = "SELECT yadayada FROM tbl WHERE col = @par"
cmd.CommandText = sqlstring
cmd.Parameters.Add("@par", SqlDBTypes.Int)
cmd.Parameters("@par").Value = intputvalue
Now you leave it up to the API to make sure that no SQL injection occurs.
Any good client API will send a parameterised query to SQL Server,
and the values will never be expanded inline into the query.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
date: Sat, 30 Aug 2008 03:12:23 -0700
author: Erland Sommarskog
|
|
