Re: sql injection query

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 05/25/04 

Date: Tue, 25 May 2004 12:16:56 -0400

Yes, but you still will want to take other precautions. Using Params
escapes the values so this alone greatly enhances security. However limiting
the allowable field lenght, is another way to do this... If you can only
type "Where 1=1 OR" b/c the length is limited, they aren't going to be able
to write out a very damaging query. I'd still check for any characters that
indicate a DDL statement or any words like Drop Table or delete. In
addition, just give the user account execute permissions to the proc but
nothing else. Least privilege this account into the ground. I also wrote
some logic so that I detect any attempt to use stuff like SA (which I don't
have on my system) or any reserved words. You can load the reserved words
into a Table (and you don't even need all of them) so you can query against
this dictionary to see if they are trying any funny business. Since you can
do this with a proc that has no other permissions this will give you a good
deal of protection. Anyway, if I detect that this is happening, I add the
IP Address of the computer to my blocked list. Then (in ASP.NET or w/ Web
Services) if check the IP Address right off the back against my blocked list
so I don't even process anything that comes from this IP.

You are in a trade off b/c increasing the password size makes it more secure
to dictionary attacks, but on the other hand, it makes you more susceptible
to Injections. Anyway, you can put password at say 10 characters (I think
14 was a magic number I read somewhere for true strong passwords but I'm not
positive) but really try to limit other field sizes. You prohibit reserved
words , or at a minimium SELECT, INSERT, DELETE etc, use Least Privilege
permissioning. block IP addresses who exhibit suspicious behavior (I have
yet to have even one call of a legit user getting locked out for the scheme
mentioned above and we have a huge Userbase), Don't allow Spaces.

Another thing I found ( Let me find it but in the meantime someone might
remember it, there was a post here a while ago about Validating SQL
Statements. There's a Stored Procedure In SQL Server that does this for
you, system stored procedure that is) is a System Stored Proc that Query
Analyzer uses to validate a Statement to ensure it's valid. You can send
your password to this, and make sure it Fails as a final precaution...if
their passord alone parses as Valid SQL, you defintiely have a problem.

The bottom line is that simply using Parameters
http://msmvps.com/williamryan/posts/4063.aspx is a Tremendous Plus over
dynamic Sql and you are making the right choice. There's no downside worth
mentioning, particularly from a security POV. However, Injection attacks
aren't the only thing there is, and since hackers are so crafty, you don't
want to count on only one line of defense, just in case there's some other
exploit we dont' know about or you overlook something. (I saw a
demonstration by Jeff Prosise where he was showing an example using Regex
validators in ASP.NET. The scenario was that the developer was using
Dynamic SQL b/c he was using Regular Expression Validators to make sure the
code was legit and his regex's were bullet proof. [Prosise even said that
these regex's were very good] so he had no worries about Injection. Prosise
proceeded to go to the Browser's Tools, Options, and turned off JavaScript.
He then deleted the whole database. So much for the bullet proof regex's)
However, if the imaginery developer used the Regex's and used Parameters and
used a Least Privilege Permission scheme and parsed for reserved words in
the proc, none of this could happen. Sure it's a pain to code and may
hamper performance depending on how anal you get, but that's the trade off
you have to decide on. You can always loosen things up if you are
comfortable that your security is overkill. But you can't make it more
secure after they hack your DB and expect to stay employed in most cases).
There are many other attacks and the best course of action is thinking about
security wholisiticly. You definitely seem like you are on the right track.

HTH,

Bill 

-- 
W.G. Ryan, eMVP
http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
"scottrm" <scottrm123@nospam.com> wrote in message
news:C2239D50-470D-41E0-9DF6-6D0715AD4B15@microsoft.com...
> According to Microsoft's "improving web application security document" to
prevent sql injection you should use the parameters collection with stored
procedures and they give the following code
>
> Sql Parameter parm =
myCommand.SelectCommand.Parameters.Add("@au_id",SqlDBType.VarChar,11)
> parm.Value = Login.Text
>
> I am thinking of using Microsoft's data access appplication block and
wanted to know if I call a storedprocedure like this I would be similarly
protected against sql injection as I notice the way they seem to call stored
procedures (see below) is a bit different in that is passes the data (in
this case the integer 1) directly rather than declaring the type and length
first like above.
>
> drCat1 = SqlHelper.ExecuteReader(CONN_STRING, CommandType.StoredProcedure,
_ "GetProductsByCategory", New SqlParameter("@CategoryID", 1))