VB.NET + PreStatement (By Shuja Ali)-优快云博客

本文介绍如何在编程中使用预处理SQL语句来避免SQL注入攻击，提高代码执行速度，并且简化参数输入过程。通过创建Command对象和预处理SQL语句，可以轻松实现动态SQL执行，同时确保数据安全。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Or instead of using a direct SQL Statement, use Preparaed SQL Statements. When you execute a prepared SQL statement, you don't have to worry about special characters in your Text. Prepared Statements will automatically take care of the Single Quote and other symbols.

Moreover Prepared Statements are better that the SQL Statements that are insertted in the code, they are faster and reduce the chances of SQL Injection attacks.

Here is a simple example of how to use Command Object and Preparaed SQL Statements

CODE:
Dim cmdSQLInsert As ADODB.Command
Set cmdSQLInsert = New ADODB.Command

'Create the query
cmdSQLInsert.CommandText = "Insert Into Table1(ID, NAME, AGE) Values(?,?,?)"
cmdSQLInsert.CommandType = adCmdText
cmdSQLInsert.Prepared = True

'Create the parameters
'in this case we will create three parameters
'-----Param 1 (for Field ID)-------------
Dim gParam As ADODB.Parameter
Set gParam = New ADODB.Parameter
With gParam
    .Name = "ID"
    .Direction = adParamInput
    .Type = adChar
    .Size = 10
    .Value = "xxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam

'-----Param 2 (for Field Name)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "NAME"
    .Direction = adParamInput
    .Type = adVarChar
    .Size = 50
    .Value = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam

'-----Param 3 (for Field AGE)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "AGE"
    .Direction = adParamInput
    .Type = adChar
    .Size = 2
    .Value = "xx"
End With
cmdSQLInsert.Parameters.Append gParam

'Set the connection property of the command object
Set cmdSQLInsert.ActiveConnection = mySQLConnection
'pass the values that need to be inserted to specific parameters that we created above
cmdSQLInsert("ID") = txtID.Text
cmdSQLInsert("NAME") = txtName.Text
cmdSQLInsert("AGE") = txtAge.Text

'Execute the command
cmdSQLInsert.Execute

Remember once the Prepared Statement is built, next time you just need to pass on the values for the Parameters and execute the statement.

This makes code look more handsome and easily maintainable.

You could also look in MSDN for more about Preparaed Statements and search this forum too.