I read a lot about SQL injections lately and I know a SqlParameter
will not prevent injections for sure, but is a table parameter as safe as a single parameter?
Some untested uncompiled example code for clarification:
Is this...
SQL:
CREATE PROCEDURE dbo.InsertSingle
@Name nvarchar(max),
@Phone nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO FooBar.dbo.SomeTable
VALUES(@Name, @Phone)
END
GO
C#:
foreach(User u in Users)
{
Connection.Open();
SqlCommand com = Connection.CreateCommand();
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "dbo.InsertSingle";
SqlParameter p = new SqlParameter("@Name", u.Name);
com.Parameters.Add(p);
p = new SqlParameter("@Phone", u.Phone);
com.Parameters.Add(p);
com.ExecuteScalar();
}
as safe as this?
SQL:
CREATE PROCEDURE dbo.InsertBunch
@ValuesAsTable dbo.ValuesAsTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO FooBar.dbo.SomeTable
SELECT *
FROM @ValuesAsTable
END
GO
C#:
DataTable valuesAsTable = Users.GetSomeInsertData();
Connection.Open();
SqlCommand com = Connection.CreateCommand();
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "dbo.InsertBunch";
SqlParameter p = new SqlParameter("@valuesAsTable", valuesAdTable);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = "dbo.ValuesAsTableType";
com.Parameters.Add(p);
com.ExecuteScalar();
I really tried to search it, but I cannot find good input. Can anyone link me in the right direction?
Thanks in advance
Typed parameters will prevent SQL injection if there is no possibility that they get interpreted as literal commands, and executed. Whether they are transported as scalar or table-valued parameters, does not make any difference in this regard.