Intercepting invalid parameters to a stored procedure

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?
 
M

Marc Gravell

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
 
K

K Viltersten

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...)
 
C

Ciaran O''Donnell

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.
 
K

K Viltersten

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!
 
J

JTC^..^

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();
}
}
}
 
J

JTC^..^

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
 
K

K Viltersten

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!
 
K

K Viltersten

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

JTC^..^

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

K Viltersten

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invalid Column Name Sql-Error :Executing a stored procedure 1
Stored Procedure Parameters 1
help with stored procedure and parameters. 2
Could not find stored procedure - but works on second attempt 2
Linq, stored procedure with timestamp output column 1
run oracle stored procedure from c# 5
Autogenerate Stored Procedure parameters... 3
Stored procedure parameter 1

Top