Check range of sqlparameter

Question

1.00/5 (1 vote)

See more:

check range of Sqlparameter, If in DataBase it is smallint then check value in sqlParameter and pass it in a sqlparameter collection.
Thanks.

What I have tried:

Dim sqlString As String = "Select RoleId from aspnet_UsersInRoles where UserID =@UserID And Id=@ID"
Dim myparm As SqlParameter() = New SqlParameter(1) {}

Dim chkuserID As New Guid(UserID)

myparm(0) = New SqlParameter("@UserID", chkuserID)
myparm(1) = New SqlParameter("@ID", SqlDbType.Char, 4)
myparm(1).Value = "Shubham"
But this code runs successfully but Shubham is 7 character word and parameter size is 4.
Please provide code for Int and date type values Also.

Posted 29-Apr-16 1:35am

Member 11449483

Updated 29-Apr-16 3:04am

Add a Solution

Comments

AnvilRanger 29-Apr-16 7:42am

What exactly is your question? Please use the Improve question link to update and clarify what you need.

If you are asking how to verify the length of you @ID parameter, the answer is simple use you VB code to validate the length. Always and I mean always validate data you get from the UI especially text.

Now you ask why passing a 7 char value in works when the parameter is 4? With text data types SQL will always truncate to the max length. So in your case it take Shub.

ZurdoDev 29-Apr-16 7:46am

What?

Member 11449483 29-Apr-16 7:58am

Thanks But i want to use Range for Sqlparameter of our code protect from SQL injection.
and please suggest me Range for Integer and Date type also.
I am using Code for Integer

myparm(1) = New SqlParameter("@ID", SqlDbType.SmallInt, 4)
myparm(1).Value = 32770
it also runs but smallint range is 32767 and i define size 4 so it should take only <9999.

Or give me any link.
Please.
Thans Again

Richard Deeming 29-Apr-16 8:55am

If you're using parameters properly, you don't need to worry about SQL Injection. The only time you'll have a SQLi vulnerability is if you build a query by concatenating parameter values into a string, either in your VB code, or within a stored procedure.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Answer 1 · 2016-04-29T03:04:00

Quote:
Thanks But i want to use Range for Sqlparameter of our code protect from SQL injection.

You don't need the .Size-property of SqlParameter for protection against SQL-injection. Using SQL-parameters will prevent SQL-injection with or without a specified size of the value.

But there's another reason you might want to set the .Size, .Precision, and .Scale-properties (where applicable to the data type) and that's because it improves query execution performance. Please refer to this MSDN blog: How Data Access Code Affects Database Performance[^]