Intercepting invalid parameters to a stored procedure

  • Thread starter Thread starter K Viltersten
  • Start date Start date
K

K Viltersten

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?
 
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
 
SqlCommand cares about argument names; and
frankly, your code should be getting the
names right.
Click to expand...

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...)
 
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.
 
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!
 
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" <[email protected]> skrev i
meddelandet




- Show quoted text -
Click to expand...

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" <[email protected]> skrev i
meddelandet




- Show quoted text -
Click to expand...

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" <[email protected]> skrev i
meddelandet




- Show quoted text -
Click to expand...

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.

CREATE PROCEDURE InsertOrder
@OrderNumber int output
@CustomerName varchar(20)
@ProductID varchar(20)
@Qty int = 1
AS
BEGIN
INSERT INTO Order (CustomerName, ProductId, Qty)
VALUES (@CustomerName, @ProductID, @Qty)

SET @OrderNumber = @@IDENTITY
END

CREATE PROCEDURE UpdateOrder
@OrderNumber int output
@ProductID int
@Qty int
AS
BEGIN
UPDATE Order
SET Qty = @Qty
WHERE OrderNumber = @OrderNumber AND ProductID = @ProductID
END

Example Data Access Layer Component (Exception handling etc omitted
for clarity)

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace OrderEntry.DataAccessLayer
{
public class OrderDALC
{
public static SqlConnection Connection
{
get
{
string connStr =
ConfigurationManager.ConnectionStrings["Order Entry Connection
String"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
return conn;
}
}

public static void InsertOrder(out int orderNumber, string
customerName, int productId, int quantity)
{
SqlCommand cmd = new SqlCommand("InsertOrder",
Connection);
cmd.CommandType = CommandType.StoredProcedure;

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);

Connection.Open();

cmd.ExecuteNonQuery();

Connection.Close();

orderNumber = cmd.Parameters["@OrderNumber"].Value;
}

public static void UpdateOrder(int orderNumber, int productId,
int quantity)
{
SqlCommand cmd = new SqlCommand("UpdateOrder",
Connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@OrderNumber", orderNumber);
cmd.Parameters.AddWithValue("@ProductId", productId);
cmd.Parameters.AddWithValue("@Qty", quantity);

Connection.Open();

cmd.ExecuteNonQuery();

Connection.Close();
}
}
}
 
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.

--
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" <[email protected]> skrev i
meddelandetnews:[email protected]...
Click to expand...
- Show quoted text -

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.

--
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" <[email protected]> skrev i
meddelandetnews:[email protected]...
Click to expand...
- Show quoted text -

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.

--
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" <[email protected]> skrev i
meddelandetnews:[email protected]...
Click to expand...
- Show quoted text -
Click to expand...

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.

CREATE PROCEDURE InsertOrder
@OrderNumber int output
@CustomerName varchar(20)
@ProductID varchar(20)
@Qty int = 1
AS
BEGIN
   INSERT INTO Order (CustomerName, ProductId, Qty)
   VALUES (@CustomerName, @ProductID, @Qty)

   SET @OrderNumber = @@IDENTITY
END

CREATE PROCEDURE UpdateOrder
@OrderNumber int output
@ProductID int
@Qty int
AS
BEGIN
   UPDATE Order
   SET Qty = @Qty
   WHERE OrderNumber = @OrderNumber AND ProductID = @ProductID
END

Example Data Access Layer Component (Exception handling etc omitted
for clarity)

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace OrderEntry.DataAccessLayer
{
    public class OrderDALC
    {
        public static SqlConnection Connection
        {
            get
            {
                string connStr =
ConfigurationManager.ConnectionStrings["Order Entry Connection
String"].ConnectionString;
                SqlConnection conn = new SqlConnection(connStr);
                return conn;
            }
        }

        public static void InsertOrder(out int orderNumber, string
customerName, int productId, int quantity)
        {
            SqlCommand cmd = new SqlCommand("InsertOrder",
Connection);
            cmd.CommandType = CommandType.StoredProcedure;

            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);

            Connection.Open();

            cmd.ExecuteNonQuery();

            Connection.Close();

            orderNumber = cmd.Parameters["@OrderNumber"].Value;
        }

        public static void UpdateOrder(int orderNumber, int productId,
int quantity)
        {
            SqlCommand cmd = new SqlCommand("UpdateOrder",
Connection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@OrderNumber", orderNumber);
            cmd.Parameters.AddWithValue("@ProductId", productId);
            cmd.Parameters.AddWithValue("@Qty", quantity);

            Connection.Open();

            cmd.ExecuteNonQuery();

            Connection.Close();
        }
    }



}- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...

Oops! Correction to stored procedures

CREATE PROCEDURE InsertOrder
@OrderNumber int output
@CustomerName varchar(20)
@ProductID varchar(20)
@Qty int
AS
BEGIN
INSERT INTO Order (CustomerName, ProductId, Qty)
VALUES (@CustomerName, @ProductID, @Qty)


SET @OrderNumber = @@IDENTITY
END


CREATE PROCEDURE UpdateOrder
@OrderNumber int
@ProductID int
@Qty int
AS
BEGIN
UPDATE Order
SET Qty = @Qty
WHERE OrderNumber = @OrderNumber AND ProductID = @ProductID
END
 
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.
Click to expand...
<snippage>

Yes, that's more or less what i'm doing
already. Thanks!
 
SqlCommand cmd
= new SqlCommand("InsertOrder", Connection);
cmd.CommandType = CommandType.StoredProcedure;
Click to expand...

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 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.
Click to expand...

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);
 
SqlCommand cmd
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.
Click to expand...

Got it. Thanks.
 
Back
Top