程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> MySQL 及 SQL 注入與防備辦法

MySQL 及 SQL 注入與防備辦法

編輯:MSSQL

MySQL 及 SQL 注入與防備辦法。本站提示廣大學習愛好者:(MySQL 及 SQL 注入與防備辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是MySQL 及 SQL 注入與防備辦法正文


parameter sniff成績是重用其他參數生成的履行籌劃,招致以後參數采取該履行籌劃非最優化的景象。想必熟習數據的同窗都應當曉得,發生parameter sniff最典范的成績就是應用了參數化的SQL(或許存儲進程中應用了參數化)寫法,假如存在數據散布不平均的情形下,正常情形下生成的履行籌劃,在傳入在散布數據較多的參數的情形下,重用了正常參數生成的履行籌劃,而這類緩存的履行籌劃並不是合適以後參數的一種情形。

這類情形,在現實營業中,湧現的頻率照樣比擬高的,由於存儲進程普通都是采取參數化的寫法,這時候,碰到散布不平均的數據參數時,parameter sniff景象就湧現了,這類成績照樣比擬讓人頭疼的。

詳細parameter sniff發生的緣由,我就不做過量的說明了,說明這個就顯得太low了

我舉個簡略的例子,模仿一下這個景象,解釋參數化的存存儲進程是怎樣寫的,存在哪些成績,又若何處理parameter sniff成績,

先創立一個測試情況:

create table ParameterSniffProblem
(
id int identity(1,1),
CustomerId int,
OrderId int,
OrederStatus int,
CreateDate Datetime,
Remark varchar(200)
)
declare @i int = 0
while @i<500000
begin
INSERT INTO ParameterSniffProblem values (@i%10000,@i,RAND()*10,GETDATE()-RAND()*100,NEWID())
set @i=@i+1
end
--假設某一個客戶有異常多的定單,模仿數據散布不平均的情形
INSERT INTO ParameterSniffProblem values (6666,RAND()*100000,1,GETDATE()-RAND()*100,NEWID())
GO 100000
--創立正常的索引
CREATE CLUSTERED INDEX IDX_CreateDate on ParameterSniffProblem(CreateDate
)
CREATE INDEX IDX_CustomerId ON ParameterSniffProblem(CustomerId)

參數化存儲進程的寫法:

在編寫存儲進程的時刻,我們普通建議采取參數化的寫法,目標是為了削減存儲進程的編譯和增強履行籌劃緩存的重用

年夜概是如許子的

CREATE PROCEDURE [dbo].ParameterSniffTest 
( 
@p_CustomerId int,
@p_Status int,
@p_FromDate datetime,
@p_ToDate datetime
) 
AS 
BEGIN
SET NOCOUNT ON 
DECLARE
@Parm NVARCHAR(MAX),
@sqlcommand NVARCHAR(MAX) = N''
SET @sqlcommand = 'SELECT * FROM ParameterSniffProblem WHERE 1=1'
     IF(@p_CustomerId IS NOT NULL)
SET @sqlcommand = CONCAT(@sqlcommand,'AND CustomerId=@p_CustomerId ')
IF(@p_Status IS NOT NULL)
SET @sqlcommand = CONCAT(@sqlcommand,'AND OrederStatus=@p_Status ')
IF(@p_FromDate IS NOT NULL)
SET @sqlcommand = CONCAT(@sqlcommand,'AND CreateDate>=@p_FromDate ')
IF(@p_ToDate IS NOT NULL)
SET @sqlcommand = CONCAT(@sqlcommand,'AND CreateDate<=@p_ToDate ')
    SET @Parm= '@p_CustomerId int,
@p_Status   int,
@p_FromDate  datetime,
@p_ToDate   datetime '
    EXEC sp_executesql @sqlcommand,@Parm,
@p_CustomerId = @p_CustomerId,
@p_Status = @p_Status,
@p_FromDate = @p_FromDate,
@p_ToDate = @p_ToDate 
END
GO

Parameter Sniff成績:

這就潛伏一個parameter sniff成績,

好比我查詢用戶ID=100的定單信息,一個正常的散布的數據,存儲進程第一次編譯,這個履行籌劃完整沒有成績,

假如我接著轉變參數履行查詢用戶6666的信息,一個散布及其不平均的數據,然則由於重用下面緩存的履行籌劃,就湧現parameter sniff成績了,這個履行籌劃明顯是不公道的

IO就不看了,銳意造的例子

假如我清空履行籌劃緩存,從新履行上述查詢,由於有了重編譯,履行籌劃就是不這個模樣,關於CustomerID=6666這個參數來講,明顯走全表掃描價值要更小一點

想必這是一個開辟中罕見的成績給,我們參數化SQL就是為了讓分歧參數的查詢重用履行籌劃,然則很不幸,數據散布不平均的時刻,重用履行籌劃恰好又給數據庫形成了損害,例中,假如是正常參數重用了散布較多半據的履行籌劃,好比定名可以用到索引,成果是表掃描,效果會更嚴重。

那末,既想要盡量的重用履行籌劃,又要防止由於履行籌劃重用發生parameter sniff成績,怎樣辦?

我們曉得成績在於@p_CustomerId身上,那末可弗成以對有能夠發生parameter sniff成績的@p_CustomerId不做參數化,直接拼集在SQL中,假如@p_CustomerId變更了就重編譯SQL,也就是對傳入出去的@p_CustomerId重編譯

假如是@p_CustomerId不變,其他參數有變更,好比這裡時光字段的變更,還可以享用參數化帶來的履行籌劃重用的利益 也就是如許處置 @p_CustomerId這個參數,直接把@p_CustomerId以字符串的方法平湊在SQL語句中,如許的話,就相當於即席查詢了,欠亨過參數化的方法給CustomerId這個查詢前提字段賦值

IF(@p_CustomerId IS NOT NULL)
SET @sqlcommand = CONCAT(@sqlcommand,'AND CustomerId= ',@p_CustomerId)

如許再去履行存儲進程的時刻,

帶入@p_CustomerId=1的時刻,履行IDX_CustomerId的index seek

帶入@p_CustomerId=6666的時刻,重編譯,履行籌劃是全表掃描,防止重用下面生成的履行籌劃,形成不公道的履行方法對效力和數據庫辦事器資本的消費

如許會盡量的削減parameter sniff成績帶來的影響,當緩存了@p_CustomerId=1的履行籌劃的時刻,再次傳入@p_CustomerId=1,其他前提有較小的變更,好比時光字段上有修改,仍然可以重用緩存的履行籌劃,防止重編譯帶來的影響

結論:

這類方法於處置parameter sniff成績,固然不是完善的,確定也有成績,我固然曉得一旦@p_CustomerId分歧就要重編譯

確定會由於@p_CustomerId參數值分歧,如許的話,弗成防止地增長了重編譯的機遇,

然則卻不會由於不公道的履行籌劃重用,帶來的parameter sniff成績

要曉得一旦發生parameter sniff成績,年夜量的查詢用到不公道的履行籌劃,會對全部辦事器發生異常嚴重的影響,好比能夠會發生年夜量的IO等

同時存在一個利益,好比第一次傳入@p_CustomerId=1,

再次傳入@p_CustomerId=1,其他前提有較小的變更,好比時光字段上有修改,仍然可以重用緩存的履行籌劃,防止重編譯帶來的影響固然我這裡只是一個簡略的例子,現實運用中遠遠比這個龐雜

好比散布的特殊的多的數據有兩個特色,第一散布的標示不只僅只要一個,第二散布不均的數據是靜態的,有能夠第一季度是A這部門數據占領年夜多半,有能夠是第二季度B數據占絕年夜多半

所以很難采取Plan Guide的方法處理parameter sniff成績

這類方法可以在必定水平上也可以或許重用緩存的履行籌劃,可以削減(但弗成防止)重編譯的次數

同時,這類方法與拼集一個SQL字符串履行的即席查詢方法比擬,同時還可以應用參數化帶來的其他利益,好比SQL注入等等

總結:

    parameter sniff成績的處理方法有許多,紛歧一煩瑣了

    最典范的就是強迫重編譯,

    或許應用EXEC履行一個拼集出來的字符串,這類方法屬於Adhoc查詢

    或許查詢提醒,

    或許是應用當地變量,

      或許應用Plan Guide等等等等,

    每種方法都有他的局限性,至多到今朝為止,還沒有一種渾然一體的方法來處理parameter sniff成績

    碰到成績,處理辦法有許多種,以最小的價值處理成績才是霸道。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved