合法/非法的SQL文
1. 问题描述
'sSQL = "select * from student where id in (190)"
'sSQL = "select * from student where id in (190)"
sSQL = "select * frrom student where id in (190,)"
给出以上三句示例,如何区分出以上哪个SQL文合法?如果只是检查语句是否合法,放到查询分析器里执行就可以得到提示。若要写进代码,举例如下。
2. 实验环境
VS2008,VB.Net,SQLServer2008。
3. SET PARSEONLY简介
检查每个 Transact-SQL 语句的语法并返回任何错误信息,但不编译和执行语句。
3.1. 语法
SET PARSEONLY { ON | OFF }
3.2. 注释
当 SET PARSEONLY 为 ON 时,Microsoft® SQL Server™ 仅分析语句。当 SET PARSEONLY 为 OFF 时,SQL Server 编译并执行语句。
SET PARSEONLY 的设置是在分析时设置,而不是在执行或运行时设置。
在存储过程或触发器中不要使用 PARSEONLY。如果 OFFSETS 选项为 ON 而且没有出现错误,则 SET PARSEONLY 返回偏移量。
3.3. 权限
SET PARSEONLY 权限默认授予所有用户。
4. 代码示例
Private Sub sqlCheck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles sqlCheck.Click
Dim sSQL As String = ""
'sSQL = "select * from student where id in (190)"
'sSQL = "select * from student where id in (190)"
sSQL = "select * frrom student where id in (190,)"
Dim sError As String = ""
sError = Me.CheckSQLExpressionError(sSQL)
End Sub
Private Function CheckSQLExpressionError(ByVal sSQL As String) As String
Dim sErrorMessage As String = ""
Dim myConn As SqlClient.SqlConnection
myConn = New SqlClient.SqlConnection("Integrated Security = SSPI; Initial Catalog = datamrp; Data Source = (local) ")
myConn.Open()
Dim myComm As SqlClient.SqlCommand
myComm = myConn.CreateCommand
myComm.CommandType = CommandType.Text
Try
myComm.CommandText = "SET PARSEONLY ON " & sSQL
myComm.ExecuteNonQuery()
Catch ex As Exception
sErrorMessage = ex.Message
Finally
myComm.CommandText = "SET PARSEONLY OFF"
myComm.ExecuteNonQuery()
End Try
Return sErrorMessage
End Function
5. 疑难解答
问:如果我的数据库没有SQL文(sSQL)中对应的表,那么也就异常了,是吧?
答:上述示例只是检查语法是否合法,至于表或者字段什么的是不是真的存在是不会检查的。