Anti SQL Inject

存储过程:
create procedure zsp_WithStaticSQL
@Parameter varchar(100)
as
select *
from sysobjects
where name = @Parameter

 3.3 存储过程 T–SQL 程序代码中,如果其传入参数被用于拼接动态 SQL,该参数应该只接受”值”,
  ,而不要用于传入包含表名、列名、关键字的 SQL 语句的一部分,并将该存储过程参数用于
  构造参数化的动态 SQL,并使用 sp_executesql 及其形参声明参数,实参参数执行,
  而绝对禁止直接拼接 SQL,并 exec 执行
create proc zsp_WithParameterizedDynamicSQL
 @Value varchar(20)
,@OutValue int output
as
—用于保存动态 SQL 的一定要使用 nvarchar 数据类型
declare @sql nvarchar(200) 
set @sql = ‘select * from sysobjects‘ 
if @value = null or len(@value) > 0
begin
  —@value 是存储过程的参数,将其拼接到动态 sql 语句中,起到了一个”字段值”的”值占位符”的作用
  —(一般是”=”右边的以”@”开始的 T-SQL 变量)
  —从而构造出安全的 “参数化动态SQL”
  set @sql = @sql + ‘ where name like (”%” + @ +”%”)‘
end

select @sql
—
exec sp_executesql @sql
  , N‘@ varchar(20)‘
  , @value

set @OutValue = @@rowcount
GO

declare @ varchar(100)
set @ = ‘sys‘
set @ = ”’%”—‘
declare @i int
exec zsp_WithParameterizedDynamicSQL @,@i out
select @i

declare @ids xml
set @ids = cast(‘<ids><id>1</id><id>2</id><id>3</id></ids>‘ as xml)

select A.* from TestUser A
 inner join
 (
  select X.c.value(‘.‘, ‘int‘) as UserID 
   from @ids.nodes(‘/ids/id‘) as X(c)
 ) B on A.UserID = B.UserID 
 
 
drop table #testTable

DECLARE @MyTable MyType
INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,‘abc‘,‘1/1/2000‘),
 (2,‘def‘,‘1/1/2001‘),
 (3,‘ghi‘,‘1/1/2002‘),
 (4,‘jkl‘,‘1/1/2003‘),
 (5,‘mno‘,‘1/1/2004‘)
 
SELECT * FROM @MyTable 

go
CREATE PROC usp_test @MyTableParam MyType READONLY
as
begin
 select *
 from [Table_1]
 where f2 in(select col1 FROM @MyTableParam)
end 
GO

‘Code
‘Create a command object that calls the stored proc
Dim command As New SqlCommand(“usp_AddRowsToMyTable“, conn)
command.CommandType = CommandType.StoredProcedure

‘Create a parameter using the new type
Dim param As SqlParameter = command.Parameters.Add(“@MyTableParam“, SqlDbType.Structured)
command.Parameters.AddWithValue(“@UserID“, “Kathi“)

Code
‘Set the value of the parameter
param.Value = table

‘Execute the query
command.ExecuteNonQuery()

BEGIN
set @Text = replace(@Text,‘ ‘,”)
set @Separator = ‘,‘
DECLARE @SeparatorLen int
SET @SeparatorLen=LEN(@Separator+‘$‘)–2
set @Text = replace(@Text,‘ ‘,”)
declare @i int
set @i = 1
WHILE CHARINDEX(@Separator,@Text )>0
BEGIN
    declare @v varchar(100)
    set @v = (LEFT(@Text ,CHARINDEX(@Separator,@Text )–1))
    INSERT @Table (id,F)
    select @i,@v
    where rtrim(ltrim(@v)) != ” 
            and not exists (select 1 from @Table  where F = @v)
    if @@rowcount > 0
    begin
        set @i = @i + 1
    end
    SET @Text = STUFF(@Text ,1,CHARINDEX(@Separator,@Text )+@SeparatorLen,”)
END
INSERT @Table  (id,F)
select @i,@Text
where rtrim(ltrim(@Text)) != ”
        and not exists (select 1 from @Table where F = @Text)
return
end

    /// <summary>
    /// Class1 的摘要说明。
    /// </summary>
    public class Class1
    {

        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        //[STAThread]
        static void Main(string[] args)
        {

            //
            // TODO: 在此处添加代码以启动应用程序
            //
            Console.WriteLine(“Hello World“);
            Console.WriteLine(Environment.Version.ToString());

            DataTable dt = DataAccess.Execute_SQL
                                (
                                    “abc“
                                    , “d“
                                    , “a,b,c,d“
                                    , SqlDbType.VarChar
                                    , 3
                                    , “sqlPara“
                                );
            Console.WriteLine(dt.Rows.Count);
            Console.ReadLine();
        }
    }
}

namespace Microshaoft.ParameterizedSqlTest
{

    using System;
    using System.Data;
    using System.Data.SqlClient;

    using System.Text;

    class DataAccess
    {

        public static string _ConnectionString = @”Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=.\sqlexpress“;//ConfigurationManager.ConnectionStrings[“ConnectionString”].ConnectionString;
        public static DataTable Execute_SQL
                                (
                                    string Parameter1                    //普通参数
                                    , string Parameter2                    //普通参数
                                    , string inClause                    //in 子句字段值列表
                                    , SqlDbType inClauseSqlDbType        //in 子句字段类型
                                    , int inClauseSqlDbTypeSize            //in 子句字段大小
                                    , string prefixSqlParameterName        //参数前缀
                                )
        {

            SqlConnection connection = new SqlConnection(_ConnectionString);

            StringBuilder sql = new StringBuilder(“select 1 where ‘abc’ = @Parameter1“);

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;

            SqlParameter sqlParameter1 = command.Parameters.Add(“@Parameter1“, SqlDbType.VarChar, 3);
            sqlParameter1.Direction = ParameterDirection.Input;
            sqlParameter1.Value = Parameter1;

            SqlParameter sqlParameter2 = command.Parameters.Add(“@Parameter2“, SqlDbType.VarChar, 3);
            sqlParameter2.Direction = ParameterDirection.Input;
            sqlParameter2.Value = Parameter2;

            string[] a = inClause.Split(‘,‘);
            if (a.Length > 0)
            {

                sql.Append(“ and @Parameter2 in (“);
                int i = 0;

                foreach (string var in a)
                {

                    i ++;
                    if (i > 1)
                    {

                        sql.Append(“,“);
                    }
                    string sqlParameterName = “@“ + prefixSqlParameterName + “_“ + i;
                    sql.Append(sqlParameterName);
                    SqlParameter parameter;
                    if (inClauseSqlDbTypeSize > 0)
                    {

                        parameter = command.Parameters.Add(sqlParameterName, inClauseSqlDbType, inClauseSqlDbTypeSize);
                    }
                    else
                    {

                        parameter = command.Parameters.Add(sqlParameterName, inClauseSqlDbType);
                    }
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = var;

                }
                sql.Append(“)“);
            }
            command.CommandText = sql.ToString();
            command.Connection = connection;

            Console.WriteLine(command.CommandText);
            SqlDataAdapter sda = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            connection.Close();
            return ds.Tables[0];
            
        }

    }
}