qw20012: Dynamic Where Query vs. Sql injection

f_split: https://www.blogger.com/blog/post/edit/4093535634636061865/4702745486128136601

can parse the filter string as below:

N'''a'' = ''b'' and o.optname=''merge'' and (''merge'' <a.name and ''merge'' < optname or (low > 10)) And name<> name or name >= ''merge'' and name between ''a'' and ''b'' and name in (''a'',''b'' , ''c'' ) and ''a''=''a'' and (name like ''%s'' or not name like ''%s'' or name not like ''%s'') and (name is null or name is not null or not name is null) '

1 Test Code:

SELECT

N'select * from spt_values as s where name=''rpc'' ' AS Before

, N'number = 2 or (type=''a'')' AS Filter

, N'' AS [After]

, N'spt_values' AS [Tables]

INTO #SecureDynamicParameters

EXEC [SecureDynamicSql]

2 Stored Procedure SecureDynamicSql as below:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF OBJECT_ID('dbo.SecureDynamicSql') IS NOT NULL

DROP PROCEDURE dbo.SecureDynamicSql

Create procedure [dbo].[SecureDynamicSql]

Begin

Declare

@v0 Nvarchar(max)

, @v1 Nvarchar(max)

, @v2 Nvarchar(max)

, @v3 Nvarchar(max)

, @v4 Nvarchar(max)

, @v5 Nvarchar(max)

, @v6 Nvarchar(max)

, @v7 Nvarchar(max)

, @v8 Nvarchar(max)

, @v9 Nvarchar(max)

, @v10 Nvarchar(max)

, @v11 Nvarchar(max)

, @v12 Nvarchar(max)

, @v13 Nvarchar(max)

, @v14 Nvarchar(max)

, @v15 Nvarchar(max)

, @v16 Nvarchar(max)

, @v17 Nvarchar(max)

, @v18 Nvarchar(max)

, @v19 Nvarchar(max)

, @v20 Nvarchar(max)

, @v21 Nvarchar(max)

, @v22 Nvarchar(max)

, @v23 Nvarchar(max)

, @v24 Nvarchar(max)

, @v25 Nvarchar(max)

, @v26 Nvarchar(max)

, @v27 Nvarchar(max)

, @v28 Nvarchar(max)

, @v29 Nvarchar(max)

, @v30 Nvarchar(max)

, @v31 Nvarchar(max)

, @v32 Nvarchar(max)

, @v33 Nvarchar(max)

, @v34 Nvarchar(max)

, @v35 Nvarchar(max)

, @v36 Nvarchar(max)

, @v37 Nvarchar(max)

, @v38 Nvarchar(max)

, @v39 Nvarchar(max)

, @v40 Nvarchar(max)

, @v41 Nvarchar(max)

, @v42 Nvarchar(max)

, @v43 Nvarchar(max)

, @v44 Nvarchar(max)

, @v45 Nvarchar(max)

, @v46 Nvarchar(max)

, @v47 Nvarchar(max)

, @v48 Nvarchar(max)

, @v49 Nvarchar(max)

, @v50 Nvarchar(max)

Declare

@Delimiter Nchar

, @Condition Nvarchar(max)

, @PartCondition Nvarchar(max)

, @PeriodIndex int

, @Seq int

, @ReversedCondition Nvarchar(20)

, @HoldParenthesis Nvarchar(10)

, @HoldParm Nvarchar(max)

, @HoldKey Nvarchar(10)

, @ParsedFilter Nvarchar(max)

, @ParmList Nvarchar(max)

, @Sql Nvarchar(max)

, @DataType Nvarchar(20)

, @MaximumLength int

, @Pricision int

, @PricisionRadix int

, @ValueIndex int

, @SelectClauseIndex int

, @FromClauseIndex int

, @WhereClauseIndex int

, @PartValueIndex int

, @StartValueIndex int

, @EndValueIndex int

, @PartValue Nvarchar(max)

Declare

@Before Nvarchar(max)

, @Filter Nvarchar(max)

, @After Nvarchar(max)

, @Tables Nvarchar(max)

, @Severity INT

SET @Severity = 0

IF OBJECT_ID(N'tempdb..#SecureDynamicParameters') IS NULL OR NOT EXISTS(SELECT 1 FROM #SecureDynamicParameters)

RETURN @Severity

SELECT TOP 1

@Before = Before

, @Filter = Filter

, @After = [After]

, @Tables = [Tables]

FROM #SecureDynamicParameters

Declare @t table (val nvarchar(max), seq int)

Set @Delimiter = N' '

Set @Filter += @Delimiter

Set @Filter = Replace(@Filter, N'(', N' ( ')

Set @Filter = Replace(@Filter, N')', N' ) ')

Set @Filter = Replace(@Filter, N',', N' , ')

Set @Filter = Replace(@Filter, N'<>', N'$@$')

Set @Filter = Replace(@Filter, N'>=', N'$@@$')

Set @Filter = Replace(@Filter, N'<=', N'$@@@$')

Set @Filter = Replace(@Filter, N'>', N' > ')

Set @Filter = Replace(@Filter, N'<', N' < ')

Set @Filter = Replace(@Filter, N'=', N' = ')

Set @Filter = Replace(@Filter, N'$@$', N' <> ')

Set @Filter = Replace(@Filter, N'$@@$', N' >= ')

Set @Filter = Replace(@Filter, N'$@@@$', N' <= ')

Set @ParsedFilter = N''

Set @ParmList = N''

Set @ValueIndex = 0

SET @PartValueIndex = 0

SET @StartValueIndex = CHARINDEX('''', @Filter)

WHILE @StartValueIndex > 0

BEGIN

Set @EndValueIndex = CHARINDEX('''', @Filter, @StartValueIndex + 1)

IF @EndValueIndex <= @StartValueIndex - 1

break

SET @PartValue = SUBString(@Filter, @StartValueIndex, @EndValueIndex - @StartValueIndex + 1)

set @Filter= REPLACE(@Filter, @PartValue, Replace(@PartValue, N' ', NCHAR(30)))

SET @StartValueIndex = CHARINDEX('''', @Filter, @EndValueIndex + 1)

END

;With a As

(

Select Cast(1 As BigInt) f, CharIndex(@Delimiter, @Filter) t, 1 seq

Union all

Select t + 1, CharIndex(@Delimiter, @Filter, t + 1), seq + 1

From a

Where CharIndex(@Delimiter, @Filter, t + 1) > 0

)

Insert @t

Select substring(@Filter, f, t - f), seq from a option (maxrecursion 0)

Declare ConditionCusror Cursor Scroll

For Select val From @t

Open ConditionCusror

While(1 = 1)

Begin

Fetch From ConditionCusror

Into @Condition

IF @@Fetch_Status <> 0

Break

SET @StartValueIndex = CHARINDEX('''', @Condition)

IF @StartValueIndex > 0

BEGIN

Set @EndValueIndex = CHARINDEX('''', @Condition, @StartValueIndex + 1)

IF @EndValueIndex > @StartValueIndex - 1

BEGIN

SET @PartValue = SUBString(@Condition, @StartValueIndex, @EndValueIndex - @StartValueIndex + 1)

set @Condition= REPLACE(@Condition, @PartValue, Replace(@PartValue, NCHAR(30), N' '))

set @Condition= REPLACE(@Condition, N'N''', N'')

set @Condition= REPLACE(@Condition, N'''', N'')

END

Set @Condition = Rtrim(Ltrim(@Condition))

If @Condition = N'' Continue

-- Process operand and key word

If Upper(@Condition) In (N'AND', N'OR', N'LIKE', N'IS', N'NOT', N'NULL', N'BETWEEN', N'IN', N'>', N'<', N'=', N'<>', N'>=', N'<=', N'(', N')', N',')

Begin

Set @ParsedFilter += N' ' + @Condition + N' '

If Upper(@Condition) In (N'AND', N'OR', N'LIKE', N'IS', N'BETWEEN', N'IN')

And Not (Upper(@Condition) = N'AND' And @HoldKey = N'BETWEEN')

Set @HoldKey = Upper(@Condition)

If Upper(@Condition) = N')' And @HoldKey = N'IN'

Set @HoldParm = null

Continue

End

Set @PeriodIndex = CharIndex(N'.', @Condition)

If @PeriodIndex >= 0

Set @PartCondition = Substring(@Condition, @PeriodIndex + 1, Len(@Condition) - @PeriodIndex)

Else

Set @PartCondition = @Condition

Select

@DataType = DATA_TYPE

, @MaximumLength = CHARACTER_MAXIMUM_LENGTH

, @Pricision = NUMERIC_PRECISION

, @PricisionRadix = NUMERIC_PRECISION_RADIX

From INFORMATION_SCHEMA.COLUMNS

Where TABLE_NAME in (select val FROM dbo.f_split(@Tables, N','))

And COLUMN_NAME = @PartCondition

If @DataType is not null -- Object name

Begin

Set @ParsedFilter += N' ' + QuoteName(@Condition)

If Upper(@DataType) = N'NVARCHAR' OR Upper(@DataType) = N'VARCHAR'

Begin

Set @HoldParm = @DataType + N' (' + CAST(@MaximumLength AS nvarchar(20))+ N')'

End

Else If Upper(@DataType) = N'DECIMAL'

Begin

Set @HoldParm = @DataType + N' (' + Cast(@Pricision As Nvarchar(5)) + N', ' + Cast(@PricisionRadix As Nvarchar(5)) + N')'

End

Else

Begin

Set @HoldParm = @DataType

End

Set @DataType = null

End

Else -- Value

Begin

If @ValueIndex = 0

Set @v0 = @Condition

If @ValueIndex = 1

Set @v1 = @Condition

If @ValueIndex = 2

Set @v2 = @Condition

If @ValueIndex = 3

Set @v3 = @Condition

If @ValueIndex = 4

Set @v4 = @Condition

If @ValueIndex = 5

Set @v5 = @Condition

If @ValueIndex = 6

Set @v6 = @Condition

If @ValueIndex = 7

Set @v7 = @Condition

If @ValueIndex = 8

Set @v8 = @Condition

If @ValueIndex = 9

Set @v9 = @Condition

If @ValueIndex = 10

Set @v10 = @Condition

If @ValueIndex = 11

Set @v11 = @Condition

If @ValueIndex = 12

Set @v12 = @Condition

If @ValueIndex = 13

Set @v13 = @Condition

If @ValueIndex = 14

Set @v14 = @Condition

If @ValueIndex = 15

Set @v15 = @Condition

If @ValueIndex = 16

Set @v16 = @Condition

If @ValueIndex = 17

Set @v17 = @Condition

If @ValueIndex = 18

Set @v18 = @Condition

If @ValueIndex = 19

Set @v19 = @Condition

If @ValueIndex = 20

Set @v20 = @Condition

If @ValueIndex = 21

Set @v21 = @Condition

If @ValueIndex = 22

Set @v22 = @Condition

If @ValueIndex = 23

Set @v23 = @Condition

If @ValueIndex = 24

Set @v24 = @Condition

If @ValueIndex = 25

Set @v25 = @Condition

If @ValueIndex = 26

Set @v26 = @Condition

If @ValueIndex = 27

Set @v27 = @Condition

If @ValueIndex = 28

Set @v28 = @Condition

If @ValueIndex = 29

Set @v29 = @Condition

If @ValueIndex = 30

Set @v30 = @Condition

If @ValueIndex = 31

Set @v31 = @Condition

If @ValueIndex = 32

Set @v32 = @Condition

If @ValueIndex = 33

Set @v33 = @Condition

If @ValueIndex = 34

Set @v34 = @Condition

If @ValueIndex = 35

Set @v35 = @Condition

If @ValueIndex = 36

Set @v36 = @Condition

If @ValueIndex = 37

Set @v37 = @Condition

If @ValueIndex = 38

Set @v38 = @Condition

If @ValueIndex = 39

Set @v39 = @Condition

If @ValueIndex = 40

Set @v40 = @Condition

If @ValueIndex = 41

Set @v41 = @Condition

If @ValueIndex = 42

Set @v42 = @Condition

If @ValueIndex = 43

Set @v43 = @Condition

If @ValueIndex = 44

Set @v44 = @Condition

If @ValueIndex = 45

Set @v45 = @Condition

If @ValueIndex = 46

Set @v46 = @Condition

If @ValueIndex = 47

Set @v47 = @Condition

If @ValueIndex = 48

Set @v48 = @Condition

If @ValueIndex = 49

Set @v49 = @Condition

If @ValueIndex = 50

Set @v50 = @Condition

If @HoldParm is null

Begin

Set @HoldParm = N''

If @ParmList <> N''

Set @ParmList += N', '

Set @ParsedFilter += N' @v' + Cast(@ValueIndex as Nvarchar(5))

Set @ParmList += N' @v' + Cast(@ValueIndex as Nvarchar(5)) + N' ' + N'Varchar(50)'

End

Else If @HoldParm = N''

Begin

/*If (@HoldKey <> 'BETWEEN' And @HoldKey <> 'IN') OR @HoldKey IS NULL

Begin

Print 'Filter clause is invlaid'

Break

End */

If @ParmList <> N''

Set @ParmList += N', '

Set @ParsedFilter += N' @v' + Cast(@ValueIndex as Nvarchar(5))

Set @ParmList += N' @v' + Cast(@ValueIndex as Nvarchar(5)) + N' ' + N'Varchar(50)'

End

Else

Begin

If @ParmList <> N''

Set @ParmList += N', '

Set @ParsedFilter += N' @v' + Cast(@ValueIndex as Nvarchar(5))

Set @ParmList += N' @v' + Cast(@ValueIndex as Nvarchar(5)) + N' ' + REPLACE(@HoldParm, '''', '') --Replace: Avoid AppScan scanning

If @HoldKey <> N'BETWEEN' And @HoldKey <> N'IN'

Set @HoldParm = null

End

Set @ValueIndex += 1

End

Close ConditionCusror

Deallocate ConditionCusror

-- print @ParmList

-- Print @ParsedFilter

SET @ParsedFilter = REPLACE(@ParsedFilter, '''', '') --Replace: Avoid AppScan scanning

If CHARINDEX(N'WHERE', @Before, 0) > 0 AND ISNULL(@ParsedFilter, N'') <> N''

Set @ParsedFilter = N' AND ' + @ParsedFilter

ELSE IF ISNULL(@ParsedFilter, N'') <> N''

Set @ParsedFilter = N' where ' + @ParsedFilter

Set @Sql = @Before + N' ' + ISNULL(@ParsedFilter, N'') + N' ' + @After

print @Sql

If @ValueIndex = 0

EXEC sp_executesql @Sql

If @ValueIndex = 1

EXEC sp_executesql @Sql, @ParmList, @v0

If @ValueIndex = 2

EXEC sp_executesql @Sql, @ParmList, @v0, @v1

If @ValueIndex = 3

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2

If @ValueIndex = 4

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3

If @ValueIndex = 5

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4

If @ValueIndex = 6

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5

If @ValueIndex = 7

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6

If @ValueIndex = 8

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7

If @ValueIndex = 9

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8

If @ValueIndex = 10

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9

If @ValueIndex = 11

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10

If @ValueIndex = 12

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11

If @ValueIndex = 13

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12

If @ValueIndex = 14

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13

If @ValueIndex = 15

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14

If @ValueIndex = 16

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15

If @ValueIndex = 17

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16

If @ValueIndex = 18

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17

If @ValueIndex = 19

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18

If @ValueIndex = 20

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19

If @ValueIndex = 21

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20

If @ValueIndex = 22

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21

If @ValueIndex = 23

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21, @v22

If @ValueIndex = 24

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21, @v22, @v23

If @ValueIndex = 25

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21, @v22, @v23, @v24

If @ValueIndex = 26

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21, @v22, @v23, @v24, @v25

If @ValueIndex = 27

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21, @v22, @v23, @v24, @v25, @v26

If @ValueIndex = 28

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21, @v22, @v23, @v24, @v25, @v26, @v27

If @ValueIndex = 29

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21, @v22, @v23, @v24, @v25, @v26, @v27, @v28

If @ValueIndex = 30

If @ValueIndex = 31

If @ValueIndex = 32

If @ValueIndex = 33

If @ValueIndex = 34

If @ValueIndex = 35

EXEC sp_executesql @Sql, @ParmList, @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @V17, @v18, @v19, @v20, @v21, @v22, @v23, @v24, @v25, @v26, @v27, @v28, @v29, @v30, @v31, @v32, @v33, @v34

If @ValueIndex = 36

If @ValueIndex = 37

If @ValueIndex = 38

If @ValueIndex = 39

If @ValueIndex = 40

If @ValueIndex = 41

If @ValueIndex = 42

If @ValueIndex = 43

If @ValueIndex = 44

If @ValueIndex = 45

If @ValueIndex = 46

If @ValueIndex = 47

If @ValueIndex = 48

If @ValueIndex = 49

If @ValueIndex = 50

If @ValueIndex = 51

Return

End

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

qw20012

Friday, December 13, 2024

Dynamic Where Query vs. Sql injection

No comments:

Post a Comment

Understanding Financial Statement Analysis with One Picture

Report Abuse

Labels