上篇博客写了关于重构代码用到的SQLHelper类,这个类包括四种函数,根据是否含参和是否有返回值各分两种。在这里写写传参过程用到的SqlParameter。
如果我们使用如下拼接sql字符串的方式进行数据库操作存在脚本注入的危险:
- Dim sql As String = "insert into T_Loginlog(userID,loginDate,loginTime,computer)values('" + Enloginlog.user_userID + "','" & Enloginlog.user_loginDate & "','" & Enloginlog.user_loginTime & "','" & Enloginlog.user_computer & "')"
为了防止SQL注入,我们采用参数化查询的方式。执行带参数的sql增删改语句或存储过程的函数如下:
-
-
-
-
-
-
-
-
- Public Function ExecuteNonQueryCan(ByVal cmdtext As String, ByVal cmdtype As CommandType, ByVal paras As SqlParameter()) As Integer
- Dim conn = GetConn()
- Dim cmd As SqlCommand = New SqlCommand(cmdtext, conn)
- Dim res As Integer
- cmd.CommandType = cmdtype
- cmd.Parameters.AddRange(paras)
- Try
- res = cmd.ExecuteNonQuery()
- Catch ex As Exception
- MsgBox(ex.Message, , "数据库操作")
- Finally
- If conn.State = ConnectionState.Open Then
- conn.Close()
- End If
- End Try
- Return res
-
- End Function
在这里定义了cmdtext(以sql语句为例)、cmdtype和paras,在DAL层调用sqlhelper时,只需传入相应的参数即可。其中,paras参数部分构成如下:
-
-
-
-
-
-
- Public Function InsertIntoTLogin(ByVal Enlogin As Entity.EnLogin) As Boolean
-
- Enlogin.user_computer = System.Environment.MachineName
- Enlogin.user_loginDate = DateString
- Enlogin.user_loginTime = TimeOfDay
-
- Dim sql As String = "insert into T_Login(userID,loginDate,loginTime,computer)values(@userID,@loginDate,@loginTime,@computer)"
- Dim paras As SqlParameter() = {New SqlParameter("@userID", Enlogin.user_userID),
- New SqlParameter("@loginDate", Enlogin.user_loginDate),
- New SqlParameter("@loginTime", Enlogin.user_loginTime),
- New SqlParameter("@computer", Enlogin.user_computer)}
-
- Dim sh As SQLHelper = New SQLHelper
- If sh.ExecuteNonQueryCan(sql, CommandType.Text, paras) > 0 Then
- Return True
- Else
- Return False
- End If
-
- End Function
说到底还是封装的思想,我们将可能输入有误的地方以参数的形式固定下来,通过传参很好的解决了这个问题。