SQL Injection: Parameters Collection

[Paxxer]

Microsoft recommends using the parameters collection at this link to help prevent SQL Injection.

I am using the following code snipet

cmd.CommandText = "SELECT plazaName from Plaza WHERE zip=@ZipCode";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar, 4);
cmd.Parameters["@ZipCode"].Value = "45840";
 

Basically, I expect an exception to be raised when the value of @ZipCode is set to a string of length 5. But no error is raised?

Can any of you shed light on why this error isn't being raised?

Thanks
Tom

[PRR]

You need to have client and server side validation on your inputs before you can submit you input to be put into database .....You can raise exception or tell user about the requirements of fields beforehand( while validating ) ....
check this too

[Paxxer]

DeepBlue,

Thank you for the reply and link to the article.

Yes, I have validation, but my question is not about how to use validation to limit SQL Injection, but why doesn't the Parameters collection raise an error.

[PRR]

i think when you execute query you get the error ... and not when adding parameters...parameter are added as literals i guess , so they as passed as it is..so when you are passing "45840" its passed as it is... (not as int or number etc).. the checking (and or conversion ) happens at the stored procedure end .... i may be wrong here...

[Frinavale]

Parameters are not meant to validate your input.

Your code should validate your input before it reaches the stage where you want to use the input in an SQL query.

SQL Insertion attack can occur if you dynamically create your SQL query using unvalidated input from the user.

Say you create your SQL query as such:

SELECT * FROM myTable WHERE theid = '" + myTextBox.Text + "'"

This is compiled by the database into a command which it executes.
So, under normal use the compiled command would be something like:

SELECT * FROM myTable WHERE theid = '12345'

But, if someone were trying to do something malicious to your application they may enter their own SQL command instead of the expected input. This would be compiled along with your code and they would be able to execute what ever they wanted to....all they have to do is end your SQL query, add their own SQL query and then comment out the rest of your query.

So, in order to prevent this, you should use parameters.
These allow you to provide user input as literals. It indicates that these values should not be compiled as SQL but used as harmless Strings etc instead.

The parameters are not meant to validate your input.
You should still check to make sure that the values you're updating with will fit in the database and match the type that is expected etc etc....data validation. This will not only avoid SQL Insertion Attacks but will also avoid other problems that may result from leaving these things unchecked (liked Buffer Over Flow Attacks etc etc).

Check out this article for more information about SQL Injection Attacks

-Frinny

[Paxxer]

Hey Frinny,

It seems to be the case, that the parameters collection is not doing any validations. From my link at the beginning of this thread I was lead to believe that validation would occur.
here is a snippet:
Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.
.
So I was trying to us "DEFENSE in DEPTH", yet, no exception is triggered, by a parameter value that is too long for the specified parameter.

(I think I will write a routine that takes the parameter collection as a parameter, then just loop through and do the checks myself and raise errors as needed. That way I'll still get the "DEFENSE in DEPTH", incase a field gets through my client and server side validation).

Thanks for you interest in this issue Frinny,
I get alot of benefit from bytes.com
Tom
..

[Frinavale]

@Paxxer
I've been looking into this more, despite what that article said about the SqlParameterCollection raising an exception if the data is out of range, I cannot find any information on any exceptions the SqlParameterCollection should raise.

Could you try something for me?
Could you increase the length of your string to include at least 1 more character for your zip code parameter? See if this raises an exception....it's kind of a strange thought but in VB.NET arrays are declared by providing the number of indexes expected as apposed to the size expected and I'm wondering if this might be what's going on here...by specifying 4, it's actually creating space for 5. It probably wont raise the exception but it would satisfy my curiosity.

Either that article is wrong, or an exception is supposed to be raised if the data doesn't fit.

There is another SqlParameterCollection Add Method:

Add(String, SqlDbType, Int32, String)

This one adds a SqlParameter to the SqlParameterCollection with the parameter name, the data type, and the column length. I wonder if this one would raise an exception?

[Paxxer]

The SqlParameterCollection does not seem to be raising the errors.

I tried what you suggested. I made the string input much longer than the length of the specified parameter, and the parameter was happy to get the value.

Then I set a breakpoint so I could examine the parameter. The parameter Size property shows the Int32 value as expected. Yet when you look at the Value property or SqlValue property they are both holding strings with a longer lenght then the size.

Next I thought SQL Server might throw an error if the size was too long. But no, it only truncates the input without an error.

---
Future ideas:
When i get to it. I will definately write a routine that loops through all of the parameters just prior to executing the query. What I like about this is, I will be able to create one error message for all of the parameter since I am checking the parameters together as a "batch".

--
Another note
If you use a stored procedure, that defines the length, then it will raise an error if you pass in a parameter that is too long.

Thanks for you efforts
Tom

[Frinavale]

@Paxxer
I actually do this quite often.
It makes sense to do things in batches rather than having the user constantly have to post back just to find that there's something else that they've entered incorrectly.

@Paxxer
This is interesting.
I'll have to keep this in mind for the future.