審計打分系統

最近在幫老師做一個項目，有關審計系統的打分。該系統運行架構是使用者在web端提交http請求，通過伺服器轉換成SQL語句，然後到資料庫中去進行資料查詢修改等。審計系統運行在伺服器與資料庫中間，起到一個網關的作用，它能接受到http請求以及伺服器傳遞過來的SQL查詢語句還有使用者IP地址等一系列東西，主要實現的是資料的監控。具體細節我也不清楚，總之是來打醬油的。

 

下面說說評分系統，該系統針對HTTP請求和產生的SQL語句進行分析，也就是針對一條http請求中？後面的欄位，讓其與伺服器產生的眾多SQL語句查詢欄位進行比較，通過比較得出一個得分。該得分包括兩個部分內容，一個是相似性，這裡考慮的是最長公用子串LCS，另一個是時間性，權衡結果是如果LCS越長，而且時間越靠近目前時間那麼得分就越高。當然這兩個方面各自所佔的比例還是不清楚的，老師暫時安排是一半對一半。

 

編寫了一個小程式類比了一下過程，涉及到LCS，快排，檔案等知識，用處還是挺大的，放在下面，其中時間就用出現的序號進行類比了，處理速度還行，快排確實很牛，LCS矩陣分析亦是如此。

 

#include <stdio.h>#include <string.h>#include <stdlib.h>#define R1 0.5//LCS比例#define R2 0.5//時間比例#define MaxNum 10000//待比較的SQL語句條數char str1[1002],str2[MaxNum][1002];//返回str1,str2的最長公用之串長度int commstr(char *str1, char *str2){int len1=strlen(str1),len2=strlen(str2),row,col,max=0;int **pf = new int*[len1+1];//動態分配一個二維數組作為輔助空間for (row=0; row<len1+1; row++)pf[row] = new int[len2+1];//數組賦初值for (row=0; row<len1+1; row++)pf[row][0] = 0;for (col=0; col<len2+1; col++)pf[0][col] = 0;for (row=1; row<=len1; row++)for (col=1;col<=len2; col++){if (str1[row-1] == str2[col-1]){pf[row][col] = pf[row-1][col-1] + 1;max = pf[row][col] > max ? pf[row][col] : max;}elsepf[row][col] = 0;}//空間回收     for (row=0; row<len1+1; row++)delete[] pf[row];delete[] pf;return max;                   }//排序演算法void QuickSort(double e[],int location[],int first, int end){int i=first,j=end,temp2=location[first];double temp=e[first];while(i<j){while(i<j && e[j]<=temp)j--;e[i]=e[j];location[i]=location[j];while(i<j && e[i]>=temp)i++;e[j]=e[i];location[j]=location[i];}e[i]=temp;location[i]=temp2;if(first<i-1)QuickSort(e,location,first,i-1);if(end>i+1)QuickSort(e,location,i+1,end);}void  createfile(int num,int length){FILE *stream;char s[27]="abcdefghijklmnopqrstuvwxyz";char list[1002];int i,  numwritten;int j;if( (stream = fopen( "sql.txt", "w+t" )) != NULL ) { for(j=0;j<num;j++){for ( i = 0; i < length; i++ ) list[i] = s[rand()%26]; list[i]='\n';numwritten = fwrite( list, sizeof( char ), length+1, stream ); }printf("file has been created\n");fclose( stream ); } }int main(){int t;int length;int sqlnumber;int sqllength;double lCSratio[MaxNum];int location[MaxNum];double ratio[MaxNum];FILE *fp;printf("please input the sql sentence number(Max=10000)\n");scanf("%d",&sqlnumber);printf("please input the each sql sentence length(Max=1000)\n");scanf("%d",&sqllength);if(sqlnumber<=10000&&sqllength<=1000)    {createfile(sqlnumber,sqllength);}elseprintf("wrong input!\n");if((fp=fopen("sql.txt","rt"))==NULL){        printf("Error Opening File.\n");            }printf("please enter the http sentence(Max=1000)\n");scanf("%s",&str1);length=strlen(str1);for(t=0;t<sqlnumber;t++){fgets(str2[t], sizeof(str2[t]), fp);lCSratio[t]=(double)commstr(str1,str2[t])/length;location[t]=t;}fclose(fp);for(t=0;t<sqlnumber;t++){ratio[t]=(double)(R1*lCSratio[t]+R2*1/(t+1));//比例計算,50%為LCS長度在整個字串中所佔比例，50%為時間比例}//針對比例進行排序QuickSort(ratio,location,0,sqlnumber-1);if((fp=fopen("result.txt","w"))==NULL){        printf("Error Opening File.\n");            }fprintf(fp,"The whole rank list\n");for(t=0;t<sqlnumber;t++)fprintf(fp,"The ID:%d The Ratio:%lf The SQL:%s\n",location[t],ratio[t],str2[location[t]]);printf("Job Done\n");    return 0;} 

 

 

後來發現快排不穩定，於是改成了歸併排序，這樣就能保證相同情況下序號靠前的就排在前面了

#include <stdio.h>#include <string.h>#include <stdlib.h>#define R1 0.5//LCS比例#define R2 0.5//時間比例#define MaxNum 10000//待比較的SQL語句條數char str1[1002],str2[MaxNum][1002];//返回str1,str2的最長公用之串長度int commstr(char *str1, char *str2){int len1=strlen(str1),len2=strlen(str2),row,col,max=0;int **pf = new int*[len1+1];//動態分配一個二維數組作為輔助空間for (row=0; row<len1+1; row++)pf[row] = new int[len2+1];//數組賦初值for (row=0; row<len1+1; row++)pf[row][0] = 0;for (col=0; col<len2+1; col++)pf[0][col] = 0;for (row=1; row<=len1; row++)for (col=1;col<=len2; col++){if (str1[row-1] == str2[col-1]){pf[row][col] = pf[row-1][col-1] + 1;max = pf[row][col] > max ? pf[row][col] : max;}elsepf[row][col] = 0;}//空間回收     for (row=0; row<len1+1; row++)delete[] pf[row];delete[] pf;return max;                   }//歸併排序void merge(double data[],int location[], int p, int q, int r) {         int i, j, k, n1, n2;         double L[MaxNum];   int LL[MaxNum];    double R[MaxNum]; int RL[MaxNum];n1 = q - p + 1;         n2 = r - q;     for(i = 0, k = p; i < n1; i++, k++)         {L[i] = data[k];LL[i]=location[k];}    for(i = 0, k = q + 1; i < n2; i++, k++)             {R[i] = data[k];RL[i]=location[k];}    for(k = p, i = 0, j = 0; i < n1 && j < n2; k++)         {                 if(L[i] >= R[j])                 {                         data[k] = L[i];    location[k]=LL[i];            i++;                 }                 else                 {                         data[k] = R[j];  location[k]=RL[j];            j++;                 }     }    if(i < n1)         {                 for(j = i; j < n1; j++, k++)             {data[k] = L[j];location[k]=LL[j];}    }      if(j < n2)         {                 for(i = j; i < n2; i++, k++)             { data[k] = R[i];location[k]=RL[i];}    }  }void merge_sort(double data[], int location[],int p, int r) {         if(p < r)         {                 int q = (p + r) / 2;                 merge_sort(data,location, p, q);                 merge_sort(data, location,q + 1, r);                 merge(data, location,p, q, r);         } } void  createfile(int num,int length){FILE *stream;char s[27]="abcdefghijklmnopqrstuvwxyz";char list[1002];int i,  numwritten;int j;if( (stream = fopen( "sql.txt", "w+t" )) != NULL ) { for(j=0;j<num;j++){for ( i = 0; i < length; i++ ) list[i] = s[rand()%26]; list[i]='\n';numwritten = fwrite( list, sizeof( char ), length+1, stream ); }printf("file has been created\n");fclose( stream ); } }int main(){int t;int sqlnumber;int sqllength;int location[MaxNum];double LCS[MaxNum]; int LCSRank[MaxNum];double ratio[MaxNum];FILE *fp;printf("please input the sql sentence number(Max=10000)\n");scanf("%d",&sqlnumber);printf("please input the each sql sentence length(Max=1000)\n");scanf("%d",&sqllength);if(sqlnumber<=10000&&sqllength<=1000)    {createfile(sqlnumber,sqllength);}elseprintf("wrong input!\n"); if((fp=fopen("sql.txt","rt"))==NULL){        printf("Error Opening File.\n");            }printf("please enter the http sentence(Max=1000)\n");scanf("%s",&str1);for(t=0;t<sqlnumber;t++){fgets(str2[t], sizeof(str2[t]), fp);LCS[t]=(double)commstr(str1,str2[t]);location[t]=t;}fclose(fp);//使用歸併排序merge_sort(LCS,location,0,sqlnumber-1);for(t=0;t<sqlnumber;t++){LCSRank[location[t]]=t;}if((fp=fopen("resultLCS.txt","w"))==NULL){        printf("Error Opening File.\n");            }for(t=0;t<sqlnumber;t++){fprintf(fp,"The ID:%d The LCS:%.0lf The LCSRank:%d The SQL:%s\n",location[t],LCS[t],LCSRank[location[t]],str2[location[t]]);}fclose(fp);for(t=0;t<sqlnumber;t++){ratio[t]=(double)(R1*1.0/(LCSRank[t]+1)+R2*1.0/(t+1));//比例計算,50%為LCS長度排名比例，50%為時間排名比例location[t]=t;}//使用歸併排序merge_sort(ratio,location,0,sqlnumber-1);if((fp=fopen("result.txt","w"))==NULL){        printf("Error Opening File.\n");            }fprintf(fp,"the http str is: %s\n", str1);fprintf(fp,"The whole rank list\n");for(t=0;t<sqlnumber;t++){fprintf(fp,"The ID:%d The Ratio:%lf The SQL:%s\n",location[t],ratio[t],str2[location[t]]);}fclose(fp);printf("Job Done\n");    return 0;}