Best practice for handling SQL injection when calling a stored procedure
I inherited the code that I am fixing. What's the best practice for handling SQL injection when calling a stored procedure?
The code looks something like this:
StringBuilder sql = new StringBuilder("");
sql.Append(string.Format("Sp_MyStoredProc '{0}', {1}, {2}", sessionid, myVar, "0"));
lock (_lock)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Main"].ToString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sql.ToString(), connection))
{
command.CommandType = CommandType.Text;
command.CommandTimeout = 10000;
returnCode = (string)command.ExecuteScalar();
}
}
}
I just do the same with a normal SQL query and add the parameters with AddParameter
correct?
+3
source to share
1 answer
Q. What is the best practice for handling SQL injection?
and. Use parameterized queries
Example:
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SalesByCategory";
command.CommandType = CommandType.StoredProcedure;
// Add the input parameter and set its properties.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CategoryName";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = categoryName;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
// Open the connection and execute the reader.
connection.Open();
SqlDataReader reader = command.ExecuteReader();
.
.
.
+9
source to share