如何创建参数化 SQL 查询? 为什么要这样做？

小开

You want to go with your last example as this is the only one that is truly parametrized. Besides security concerns (which are much more prevalent then you might think) it is best to let ADO.NET handle the parametrization as you cannot be sure if the value you are passing in requires single quotes around it or not without inspecting the Type of each parameter.

[Edit] Here is an example:

SqlCommand command = new SqlCommand(
"select foo from bar where baz = @baz",
yourSqlConnection
);


SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@baz";
parameter.Value = "xyz";


command.Parameters.Add(parameter);

小开

最佳答案

The EXEC example in the question would NOT be parameterized. You need parameterized queries (prepared statements in some circles) to prevent input like this from causing damage:

';DROP TABLE bar;--

Try putting that in your fuz variable (or don't, if you value the bar table). More subtle and damaging queries are possible as well.

Here's an example of how you do parameters with Sql Server:

Public Function GetBarFooByBaz(ByVal Baz As String) As String
Dim sql As String = "SELECT foo FROM bar WHERE baz= @Baz"


Using cn As New SqlConnection("Your connection string here"), _
cmd As New SqlCommand(sql, cn)


cmd.Parameters.Add("@Baz", SqlDbType.VarChar, 50).Value = Baz
Return cmd.ExecuteScalar().ToString()
End Using
End Function

Stored procedures are sometimes credited with preventing SQL injection. However, most of the time you still have to call them using query parameters or they don't help. If you use stored procedures exclusively, then you can turn off permissions for SELECT, UPDATE, ALTER, CREATE, DELETE, etc (just about everything but EXEC) for the application user account and get some protection that way.

小开

Your command text need to be like:

cmdText = "SELECT foo FROM bar WHERE baz = ?"


cmdText = "EXEC foo_from_baz ?"

Then add parameter values. This way ensures that the value con only end up being used as a value, whereas with the other method if variable fuz is set to

"x'; delete from foo where 'a' = 'a"

can you see what might happen?

小开

Most people would do this through a server side programming language library, like PHP's PDO or Perl DBI.

For instance, in PDO:

$dbh=pdo_connect(); //you need a connection function, returns a pdo db connection


$sql='insert into squip values(null,?,?)';


$statement=$dbh->prepare($sql);


$data=array('my user supplied data','more stuff');


$statement->execute($data);


if($statement->rowCount()==1){/*it worked*/}

This takes care of escaping your data for database insertion.

One advantage is that you can repeat an insert many times with one prepared statement, gaining a speed advantage.

For instance, in the above query I could prepare the statement once, and then loop over creating the data array from a bunch of data and repeat the ->execute as many times as needed.

小开

Definitely the last one, i.e.

Or do I need to do somethng more extensive ...? (Yes, cmd.Parameters.Add())

Parametrized queries have two main advantages:

Security: It is a good way to avoid SQL Injection vulnerabilities
Performance: If you regularly invoke the same query just with different parameters a parametrized query might allow the database to cache your queries which is a considerable source of performance gain.
Extra: You won't have to worry about date and time formatting issues in your database code. Similarly, if your code will ever run on machines with a non-English locale, you will not have problems with decimal points / decimal commas.

小开

Here's a short class to start with SQL and you can build from there and add to the class.

MySQL

Public Class mysql


'Connection string for mysql
Public SQLSource As String = "Server=123.456.789.123;userid=someuser;password=somesecurepassword;database=somedefaultdatabase;"


'database connection classes


Private DBcon As New MySqlConnection
Private SQLcmd As MySqlCommand
Public DBDA As New MySqlDataAdapter
Public DBDT As New DataTable
Public BindSource As New BindingSource
' parameters
Public Params As New List(Of MySqlParameter)


' some stats
Public RecordCount As Integer
Public Exception As String


Function ExecScalar(SQLQuery As String) As Long
Dim theID As Long
DBcon.ConnectionString = SQLSource
Try
DBcon.Open()
SQLcmd = New MySqlCommand(SQLQuery, DBcon)
'loads params into the query
Params.ForEach(Sub(p) SQLcmd.Parameters.AddWithValue(p.ParameterName, p.Value))


'or like this is also good
'For Each p As MySqlParameter In Params
' SQLcmd.Parameters.AddWithValue(p.ParameterName, p.Value)
' Next
' clears params
Params.Clear()
'return the Id of the last insert or result of other query
theID = Convert.ToInt32(SQLcmd.ExecuteScalar())
DBcon.Close()


Catch ex As MySqlException
Exception = ex.Message
theID = -1
Finally
DBcon.Dispose()
End Try
ExecScalar = theID
End Function


Sub ExecQuery(SQLQuery As String)


DBcon.ConnectionString = SQLSource
Try
DBcon.Open()
SQLcmd = New MySqlCommand(SQLQuery, DBcon)
'loads params into the query
Params.ForEach(Sub(p) SQLcmd.Parameters.AddWithValue(p.ParameterName, p.Value))


'or like this is also good
'For Each p As MySqlParameter In Params
' SQLcmd.Parameters.AddWithValue(p.ParameterName, p.Value)
' Next
' clears params


Params.Clear()
DBDA.SelectCommand = SQLcmd
DBDA.Update(DBDT)
DBDA.Fill(DBDT)
BindSource.DataSource = DBDT  ' DBDT will contain your database table with your records
DBcon.Close()
Catch ex As MySqlException
Exception = ex.Message
Finally
DBcon.Dispose()
End Try
End Sub
' add parameters to the list
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New MySqlParameter(Name, Value)
Params.Add(NewParam)
End Sub
End Class

MS SQL/Express

Public Class MSSQLDB
' CREATE YOUR DB CONNECTION
'Change the datasource
Public SQLSource As String = "Data Source=someserver\sqlexpress;Integrated Security=True"
Private DBCon As New SqlConnection(SQLSource)


' PREPARE DB COMMAND
Private DBCmd As SqlCommand


' DB DATA
Public DBDA As SqlDataAdapter
Public DBDT As DataTable


' QUERY PARAMETERS
Public Params As New List(Of SqlParameter)


' QUERY STATISTICS
Public RecordCount As Integer
Public Exception As String


Public Sub ExecQuery(Query As String, Optional ByVal RunScalar As Boolean = False, Optional ByRef NewID As Long = -1)
' RESET QUERY STATS
RecordCount = 0
Exception = ""
Dim RunScalar As Boolean = False


Try
' OPEN A CONNECTION
DBCon.Open()


' CREATE DB COMMAND
DBCmd = New SqlCommand(Query, DBCon)


' LOAD PARAMS INTO DB COMMAND
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))


' CLEAR PARAMS LIST
Params.Clear()


' EXECUTE COMMAND & FILL DATATABLE
If RunScalar = True Then
NewID = DBCmd.ExecuteScalar()
End If
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)
Catch ex As Exception
Exception = ex.Message
End Try




' CLOSE YOUR CONNECTION
If DBCon.State = ConnectionState.Open Then DBCon.Close()
End Sub


' INCLUDE QUERY & COMMAND PARAMETERS
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New SqlParameter(Name, Value)
Params.Add(NewParam)
End Sub
End Class