ASP.net Web Forms Apostrophe causing issues with stored procedure
Hi
In ASP.net web forms C# I am adding a record using the following code that utilizes a stored procedure.
There is a quotation mark in one of the text boxes that is being assigned to Broker. This is causing issues. I assumed that stored procedures handle this automatically. Am I wrong?
String strConnString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertBroker";
cmd.Parameters.Add("@Broker", SqlDbType.VarChar).Value = Broker2.Text;
cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = "";
cmd.Parameters.Add("@Broker_Full_Name", SqlDbType.VarChar).Value = Broker_Contact_Name.Text;
cmd.Parameters.Add("@Broker_First_Name", SqlDbType.VarChar).Value = "";
cmd.Parameters.Add("@Broker_Surname", SqlDbType.VarChar).Value = "";
cmd.Parameters.Add("@Email_Address", SqlDbType.VarChar).Value = Broker_Email.Text;
cmd.Parameters.Add("@Type", SqlDbType.VarChar).Value = "";
cmd.Parameters.Add("@User_Email", SqlDbType.NVarChar).Value = this.lblEmailUser.Text;
cmd.Parameters.Add("@Follow_Up_Required", SqlDbType.Bit).Value = 0;
// SET @ID as output parameter....
SqlParameter outputParam = cmd.Parameters.Add("@ID", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//throw ex;
//this.Label_ErrorMessage.Text = "Error: Broker not saved" + ex.Message;
}
finally
{
con.Close();
con.Dispose();
}ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>@slightwv how does string concatenation open up sql injection?
I don't want to hijack the question but Google it:
https://www.easysoft.com/developer/sql-injection.html#:~:text=SQL%20injection%20exploits%20applications%20that,to%20perform%20a%20database%20action.
I don't want to hijack the question but Google it:
https://www.easysoft.com/developer/sql-injection.html#:~:text=SQL%20injection%20exploits%20applications%20that,to%20perform%20a%20database%20action.
ASKER
/****** Object: StoredProcedure [dbo].[InsertBroker] Script Date: 6/1/2022 1:12:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertBroker]
@Broker nvarchar(255),
@Location nvarchar(100),
@Broker_Full_Name nvarchar(100),
@Broker_First_Name nvarchar(50),
@Broker_Surname nvarchar(255),
@Email_Address nvarchar(100),
@Type nvarchar(255),
@Follow_Up_Required bit,
@User_Email nvarchar(50),
@ID int output
AS
BEGIN
SET NOCOUNT ON;
Insert Into Brokers(Broker,Location,Broker_Full_Name,Broker_First_Name,Broker_Surname,Email_Address,Type,Follow_Up_Required,User_Email) Values (@Broker,@Location,@Broker_Full_Name,@Broker_First_Name,@Broker_Surname,@Email_Address, @Type, @Follow_Up_Required, @User_Email)
SET @id=SCOPE_IDENTITY()
RETURN @iD
END
So, you've accepted all comments as the solution.
You posted the code after you accepted solutions.
Do you have your answer?
You posted the code after you accepted solutions.
Do you have your answer?
indeed.
what is the benefit of using a stored proceedure rather than straight SQL command ? Other than hiding table names.
what is the benefit of using a stored proceedure rather than straight SQL command ? Other than hiding table names.
There are several benefits to use stored procedures over imbedded SQL.
The main being security as you mentioned: masking underlying architecture.
Secondary, you can change the underlying architecture and never have to recompile.
There are a few more but those are the main ones.
The main being security as you mentioned: masking underlying architecture.
Secondary, you can change the underlying architecture and never have to recompile.
There are a few more but those are the main ones.
Benefit of stored procedure is to prevent sql injection. If you use inline sql commands then user can give rogue input and get access to more data than they should
BY using single quote input can be fudged and sql statement modified
Google sql injection to understand how sql statements can be hacked
BY using single quote input can be fudged and sql statement modified
Google sql injection to understand how sql statements can be hacked
This will tell you all about sql injection
https://www.easysoft.com/developer/sql-injection.html#:~:text=SQL%20injection%20exploits%20applications%20that,to%20perform%20a%20database%20action
https://www.easysoft.com/developer/sql-injection.html#:~:text=SQL%20injection%20exploits%20applications%20that,to%20perform%20a%20database%20action
>>Benefit of stored procedure is to prevent sql injection.
Not by itself. You still need to be aware of it even with stored procedures.
Not by itself. You still need to be aware of it even with stored procedures.
Use " + <your variable with apostrophe causing the problem> +" while inserting