By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,773 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,773 IT Pros & Developers. It's quick & easy.

Intercepting invalid parameters to a stored procedure

P: n/a
I've seen some people calling a stored procedure
on MS SQL Server and intercepting an invalid
parameter list by a switch statement, demanding
that each of the parameters names is exactly as
the one in the procedure that's about to be
called. If we let in an invalid parameter, we'll
get the following error message.

Msg 8145, Level 16, State 1, Procedure Prc, Line 0
@Param is not a parameter for procedure Prc.

I wonder if i can call the prodedure without a
check and protect against invalid parameters on
the database level. In that case, how?

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
Oct 7 '08 #1
Share this Question
Share on Google+
8 Replies


P: n/a
SqlCommand cares about argument names; and frankly, your code should
be getting the names right. However, if you want to pass by position,
one option (while keeping injection safety) would be something like:

cmd.CommandText = "EXEC FooProcedure @Arg1, @Arg2, @Arg3";
cmd.CommandType = System.Data.CommandType.Text;

Where @Arg1 / @Arg2 etc are the locally defined names. Personally, I'd
just get the names right instead... however - I used to use positional
arguments a lot with VB6 etc, so I appreciate how tempting they are.

Marc

Oct 7 '08 #2

P: n/a
SqlCommand cares about argument names; and
frankly, your code should be getting the
names right.
Thanks. Now, _MY_ code is getting the
arguments right. It's _OTHERS_ code i was
worrying about. I was aiming at writing an
idiot-proof stored procedure. (Or, at least,
a crash-proof one...)

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
Oct 7 '08 #3

P: n/a
As far as I am aware, there is NO way to do this. SqlServer has no way to
provide a handler for this situation.
Why not provide people that use you database with an assembly that will call
the stored procedures for them. That way they dont have the chance to mess up
the actual call, they will be bound by the compiler to call your functions
correctly.

--
Ciaran O''Donnell
http://wannabedeveloper.spaces.live.com
"K Viltersten" wrote:
SqlCommand cares about argument names; and
frankly, your code should be getting the
names right.

Thanks. Now, _MY_ code is getting the
arguments right. It's _OTHERS_ code i was
worrying about. I was aiming at writing an
idiot-proof stored procedure. (Or, at least,
a crash-proof one...)

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
Oct 7 '08 #4

P: n/a
That's the solution i'm using today. A switch statement that
cuts up the query string into pieces and barks if the input
wasn't valid. I hoped for an even better way. I guess i'm
already at the optimal point, hehe.

Thanks!

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
"Ciaran O''Donnell" <Ci************@discussions.microsoft.comskrev i
meddelandet news:43**********************************@microsof t.com...
As far as I am aware, there is NO way to do this. SqlServer has no way to
provide a handler for this situation.
Why not provide people that use you database with an assembly that will
call
the stored procedures for them. That way they dont have the chance to mess
up
the actual call, they will be bound by the compiler to call your functions
correctly.

--
Ciaran O''Donnell
http://wannabedeveloper.spaces.live.com
"K Viltersten" wrote:
SqlCommand cares about argument names; and
frankly, your code should be getting the
names right.

Thanks. Now, _MY_ code is getting the
arguments right. It's _OTHERS_ code i was
worrying about. I was aiming at writing an
idiot-proof stored procedure. (Or, at least,
a crash-proof one...)

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.

Oct 7 '08 #5

P: n/a
<snippage>
As Ciaran has suggested write an assembly
that calls the stored procedures for them.
The following example shows an InsertOrder
and UpdateOrder Stored Procedure with a
Data Access Layer class that calls them and
enforces the required values through the
methods.
<snippage>

Yes, that's more or less what i'm doing
already. Thanks!

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
Oct 8 '08 #6

P: n/a
<heavy snippage>
SqlCommand cmd
= new SqlCommand("InsertOrder", Connection);
cmd.CommandType = CommandType.StoredProcedure;
I noticed that you create a command with the
name of the stored proedure and then set the
CommandType property accordingly. Wouldn't it
be less recommended to use this?

new SqlCommand("EXEC InsertOrder", Connection);

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
Oct 8 '08 #7

P: n/a
On 8 Oct, 07:24, "K Viltersten" <t...@viltersten.comwrote:
<heavy snippage>
SqlCommand cmd
* = new SqlCommand("InsertOrder", Connection);
cmd.CommandType = CommandType.StoredProcedure;

I noticed that you create a command with the
name of the stored proedure and then set the
CommandType property accordingly. Wouldn't it
be less recommended to use this?

new SqlCommand("EXEC InsertOrder", Connection);

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
I certainly don't recommend it. I'm not sure what are getting at, but
in your example you would then need to set the parameters in the
command string if the CommandType.Text, then specify the parameter
values to pass to the command string in the same way as with a
CommandType.StoredProcedure.

new SqlCommand("EXEC InsertOrder @OrderNumber output, @CustomerName,
@ProductId, @Qty", Connection);

cmd.Parameters.Add("@OrderNumber", SqlDbType.Int, 4);
cmd.Parameters["@OrderNumber"].Direction = ParameterDirection.Output;

cmd.Parameters.AddWithValue("@CustomerName", customerName);
cmd.Parameters.AddWithValue("@ProductId", productId);
cmd.Parameters.AddWithValue("@Qty", quantity);

Oct 8 '08 #8

P: n/a
>>SqlCommand cmd
>>= new SqlCommand("InsertOrder", Connection);
cmd.CommandType = CommandType.StoredProcedure;
I noticed that you create a command with the
name of the stored proedure and then set the
CommandType property accordingly. Wouldn't it
be less recommended to use this?
new SqlCommand("EXEC InsertOrder", Connection);

I certainly don't recommend it. I'm not sure
what are getting at, but in your example you
would then need to set the parameters in the
command string if the CommandType.Text, then
specify the parameter values to pass to the
command string in the same way as with a
CommandType.StoredProcedure.
Got it. Thanks.

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
Oct 8 '08 #9

This discussion thread is closed

Replies have been disabled for this discussion.