台灣最大程式設計社群網站
線上人數
1546
 
會員總數:246203
討論主題:189776
歡迎您免費加入會員
討論區列表 >> ASP >> 解決SQL Injection問題, 用了parameters但出現error 求救><!!
[]  
[我要回覆]
1
回應主題 加入我的關注話題 檢舉此篇討論 將提問者加入個人黑名單
解決SQL Injection問題, 用了parameters但出現error 求救><!!
價值 : 10 QP  點閱數:1755 回應數:11
樓主

maxyip
門外漢
0 4
119 11
發送站內信

大家好:

[b]1)在front-end, 我寫了一個表單, 供人們輸入個人資料, 輸入欄位name分別是:AeName, AcName, Sex, Tel, Email, Title

2)在back-end, 我配合parameters寫了insert statement, 如下, 可是出現Microsoft VBScript 編譯階段錯誤 (0x800A0414) 呼叫 Sub 程序時,不能使用括號, 應該怎麼改才run得到><? 麻煩各位救救我
[/b]
dim lo_con
dim ls_sqlstr

Dim UserIPAddress
UserIPAddress = Request.ServerVariables("REMOTE_ADDR")


set lo_con = Server.CreateObject("ADODB.Connection")
lo_con.Open "DRIVER={SQL Server};SERVER=xxxxxxxxxxx;DATABASE=xxxxx;UID=xxxxxxxx;PWD=xxxxxx"


lo_con.execute ("BEGIN TRAN")

ls_sqlstr= "insert into tb_recruit_job_application" & _
"(ename, cname, sex," & _
"tel, email, job_id, client_ip, client_mode, create_date, create_datetime) " & _
"values" & _
"(@0," & _
"@1," & _
"@2," & _
"@3," & _
"@4," & _
"@5," & _
"'" & Request.ServerVariables("REMOTE_ADDR") & "'," & _
"'" & "'," & _
"getdate()," & _
"getdate())"
set command = Server.CreateObject("ADODB.Command")
command.ActiveConnection = lo_con
Command.CommandText = ls_sqlstr

command.CreateParameter("@0", adVarChar, adParamInput, 20, Request.Params("AeName"))
command.CreateParameter("@1", adVarChar, adParamInput, 20, Request.Params("AcName"))
command.CreateParameter("@2", adVarChar, adParamInput, 20, Request.Params("sex"))
command.CreateParameter("@3", adVarChar, adParamInput, 20, Request.Params("tel"))
command.CreateParameter("@4", adVarChar, adParamInput, 40, Request.Params("email"))
command.CreateParameter("@5", adVarChar, adParamInput, 20, Request.Params("title"))

command.ExecuteNonQuery()



搜尋相關Tags的文章: [ sql ] , [ Injection ] ,
本篇文章發表於2015-06-23 18:01
1樓
作者回應

maxyip
檢舉此回應
請問有師兄可以救救我嗎=[ ?
我煩惱了一個星期還找不到方法,
求求各位師兄幫幫我/__\謝謝
本篇文章回覆於2015-06-25 16:55
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
2樓
回應

老頑童
捐贈 VP 給 老頑童 檢舉此回應
不能使用括號,那就把它拿掉就好了
例如:
若錯在lo_con.execute ("BEGIN TRAN")這行
那就改成lo_con.execute "BEGIN TRAN"

不過,程式中其它部份還有很大的問題
像Request.Params是ASP.Net用的,純ASP不能這樣用
CreateParameter之後,也得再append進去才行
建議參考底下連結中的程式重新改寫
https://vikaskanani.wordpress.com/2012/05/07/classic-asp-sql-injection-prevention-by-using-query-parameter/
本篇文章回覆於2015-06-25 17:31
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
3樓
作者回應

maxyip
檢舉此回應
非常感謝老頑童回應><

我參考了師兄的連結
重新改寫了程式如下, 沒有再出現任何編譯階段錯誤, 可是依然出現運行不到
---------------------------------------------------------------------------------------
dim lo_con
dim ls_sqlstr

Dim UserIPAddress
UserIPAddress = Request.ServerVariables("REMOTE_ADDR")

set lo_con = Server.CreateObject("ADODB.Connection")
lo_con.Open "DRIVER={SQL Server};SERVER=xxxxxxx;DATABASE=xxxxxxxx;UID=xxxxxxxxx;PWD=xxxxxxxxx"

lo_con.execute ("BEGIN TRAN")

set command = Server.CreateObject("ADODB.Command")

ls_sqlstr= "insert into tb_recruit_job_application" & _
"(ename, cname, sex," & _
"tel, email, job_id, client_ip, client_mode, create_date, create_datetime) " & _
"values" & _
"(?," & _
"?," & _
"?," & _
"?," & _
"?," & _
"?," & _
"'" & Request.ServerVariables("REMOTE_ADDR") & "'," & _
"'" & "'," & _
"getdate()," & _
"getdate())"


command.ActiveConnection = lo_con
command.CommandText = ls_sqlstr
command.CommandType = adCmdText

command.Parameters.Append cmd.CreateParameter("@AeName", adInteger, adParamInput, ,request("AeName"))
command.Parameters.Append cmd.CreateParameter("@AcName", adInteger, adParamInput, ,request("AcName"))
command.Parameters.Append cmd.CreateParameter("@Sex", adInteger, adParamInput, ,request("Sex"))
command.Parameters.Append cmd.CreateParameter("@Tel", adInteger, adParamInput, ,request("Tel"))
command.Parameters.Append cmd.CreateParameter("@Email", adInteger, adParamInput, ,request("Email"))
command.Parameters.Append cmd.CreateParameter("@Title", adInteger, adParamInput, ,request("Title"))


set rs = command.Execute

----------------------------------------------------------------------------------------


另外, 師兄提供的連結內的程式有點怪怪的:
cmd.CommandText = qText
cmd.CommandType = adCmdText
作者從來沒定義過qText 和 adCmdText, 為何要這樣加???

很感謝師兄的回覆, 因為小弟剛畢業作程式員, 很多不懂又不知問誰, 謝謝謝謝

本篇文章回覆於2015-06-25 18:59
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
4樓
回應

老頑童
捐贈 VP 給 老頑童 檢舉此回應
adCmdText, adInteger那些參數是定義在adovbs.inc檔案中
一般會把該檔include進來
當然也可以自行定義adCmdText = 1
這樣就不需額外再去include
本篇文章回覆於2015-06-26 10:22
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
5樓
作者回應

maxyip
檢舉此回應
抱歉, command.Parameters.Append cmd.CreateParameter內的data type錯了, 已更改為adVarchar
本篇文章回覆於2015-06-26 10:25
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
6樓
作者回應

maxyip
檢舉此回應
可是依師兄的意見, 為何程式還是未能運行? 已沒有再出現error, 可是sql收不到資料, 請師兄再幫幫忙, 我肯定sql連接資料是正確的, 以下為更新的程式:

dim lo_con
dim ls_sqlstr

Dim UserIPAddress
UserIPAddress = Request.ServerVariables("REMOTE_ADDR")

set lo_con = Server.CreateObject("ADODB.Connection")
lo_con.Open "DRIVER={SQL Server};SERVER=xxxxxx;DATABASE=xxxxxx;UID=xxxxxx;PWD=xxxxxxxxxx"

lo_con.execute ("BEGIN TRAN")

set command = Server.CreateObject("ADODB.Command")

ls_sqlstr= "insert into tb_recruit_job_application" & _
"(ename, cname, sex," & _
"tel, email, job_id, client_ip, client_mode, create_date, create_datetime) " & _
"values" & _
"(?," & _
"?," & _
"?," & _
"?," & _
"?," & _
"?," & _
"'" & Request.ServerVariables("REMOTE_ADDR") & "'," & _
"'" & "'," & _
"getdate()," & _
"getdate())"


command.ActiveConnection = lo_con
command.CommandText = ls_sqlstr
command.CommandType = adCmdText

command.Parameters.Append cmd.CreateParameter("@AeName", adVarchar, adParamInput, 50,request("AeName"))
command.Parameters.Append cmd.CreateParameter("@AcName", adVarchar, adParamInput, 50,request("AcName"))
command.Parameters.Append cmd.CreateParameter("@Sex", adVarchar, adParamInput, 50,request("Sex"))
command.Parameters.Append cmd.CreateParameter("@Tel", adInteger, adParamInput, 50,request("Tel"))
command.Parameters.Append cmd.CreateParameter("@Email", adVarchar, adParamInput, 50,request("Email"))
command.Parameters.Append cmd.CreateParameter("@Title", adInteger, adParamInput, 10,request("Title"))


set rs = command.Execute

本篇文章回覆於2015-06-26 10:37
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
7樓
能先用除錯模式,先把 ls_sqlstr 這個完整的show出來嗎?
本篇文章回覆於2015-06-26 11:39
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
8樓
回應

老頑童
捐贈 VP 給 老頑童 檢舉此回應
程式中還有錯誤,應該會有錯誤訊息才對


cmd.CreateParameter
需改成
command.CreateParameter


程式中只有BEGIN TRAN
但沒有COMMIT TRANSACTION
沒有COMMIT就不會寫入
你的程式只有INSERT一筆資料,應該用不到TRANSACTION
建議先拿掉
本篇文章回覆於2015-06-26 12:42
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
9樓
作者回應

maxyip
檢舉此回應
回應7樓:

感謝師兄的回覆, 由於我是用notepad++來寫的, 不太明白除錯模式的意思, 是用response.write嗎?使用response.write(ls_sqlstr)顯示如下:
insert into tb_recruit_job_application(ename, cname, sex,tel, email, job_id, client_ip, client_mode, create_date, create_datetime) values(?,?,?,?,?,?,'xx.xxx.x.xx','',getdate(),getdate())

回覆8樓:

1)很抱歉, 我已更改為command.CreateParameter, 但還是未能運行
2)其實我的程式內是有commit tran的, 因為我只copy了我認為有問題的部分給大家, 抱歉抱歉, 以下為完整的程式:(我一直也用了On Error resume Next, 起初每次也出現出現Microsoft VBScript 編譯階段錯誤, 可是更改為現在的程式後, 就沒有再出現過; 可是也catch到error, 顯示"我們無法收到閣下的申請"



<%
On Error resume Next

dim lo_con
dim ls_sqlstr
dim command
dim UserIPAddress

UserIPAddress = Request.ServerVariables("REMOTE_ADDR")

set lo_con = Server.CreateObject("ADODB.Connection")
lo_con.Open "DRIVER={SQL Server};SERVER=xxxxxx;DATABASE=xxxxxx;UID=xxxxxx;PWD=xxxxxxx"

lo_con.execute ("BEGIN TRAN")

set command = Server.CreateObject("ADODB.Command")

ls_sqlstr= "insert into tb_recruit_job_application" & _
"(ename, cname, sex," & _
"tel, email, job_id, client_ip, client_mode, create_date, create_datetime) " & _
"values" & _
"(?," & _
"?," & _
"?," & _
"?," & _
"?," & _
"?," & _
"'" & Request.ServerVariables("REMOTE_ADDR") & "'," & _
"'" & "'," & _
"getdate()," & _
"getdate())"


command.ActiveConnection = lo_con
command.CommandText = ls_sqlstr
command.CommandType = adCmdText

Set param1 = command.CreateParameter("@AeName", adVarchar, adParamInput, 50, request("AeName"))
Set param2 = command.CreateParameter("@AcName", adVarchar, adParamInput, 50, request("AcName"))
Set param3 = command.CreateParameter("@Sex", adVarchar, adParamInput, 50, request("Sex"))
Set param4 = command.CreateParameter("@Tel", adInteger, adParamInput, , request("Tel"))
Set param5 = command.CreateParameter("@Email", adVarchar, adParamInput, 50, request("Email"))
Set param6 = command.CreateParameter("@Title", adInteger, adParamInput, , request("Title"))


set rs = command.Execute


if Err = 0 then
lo_con.execute ("COMMIT TRAN")
response.write ("我們已收到閣下的申請")
else
lo_con.execute ("ROLLBACK TRAN")
response.clear
response.write ("我們無法收到閣下的申請")
end if

set lo_rs=nothing
set lo_con=nothing
%>
本篇文章回覆於2015-06-29 17:13
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
10樓
最有價值解答

老頑童
捐贈 VP 給 老頑童 檢舉此回應
1.
先把on error resume next註解掉,看看是出現什麼錯誤

2.
Set param1 = command.CreateParameter("@AeName", adVarchar, adParamInput, 50, request("AeName"))
這樣根本沒效果,還是需有command.Parameters.Append
本篇文章回覆於2015-06-30 10:19
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
11樓
作者回應

maxyip
檢舉此回應
@0@ 原來是由於on error resume next 引致我不知錯了什麼

我成功了!!!!!!!!!!!!!!!!!!

我把師兄的1. & 2. 步驟也做了, 另外我再自行定義了:
adCmdText = 1
adParamInput = 1
adInteger = 3
adVarChar = 200

另外我再參考了老頑童師兄在2011回應的問題: http://www.blueshop.com.tw/board/fum200410061525290ew/BRD2011110311375467G.html

這樣就成功了!!!!!!!!!太感謝老頑童的細心教導了!!!感謝感謝
本篇文章回覆於2015-06-30 15:20
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
   
1

回覆
如要回應,請先登入.