如何创建参数化 SQL 查询？我为什么要？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/542510/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create a parameterized SQL query? Why Should I?
提问by Jim Counts
I've heard that "everyone" is using parameterized SQL queries to protect against SQL injection attacks without having to vailidate every piece of user input.
我听说“每个人”都在使用参数化 SQL 查询来防止 SQL 注入攻击，而不必验证每个用户输入。
How do you do this? Do you get this automatically when using stored procedures?
你怎么做到这一点？使用存储过程时是否会自动获得此信息？
So my understanding this is non-parameterized:
所以我的理解这是非参数化的：
cmdText = String.Format("SELECT foo FROM bar WHERE baz = '{0}'", fuz)
Would this be parameterized?
这会被参数化吗？
cmdText = String.Format("EXEC foo_from_baz '{0}'", fuz)
Or do I need to do somethng more extensive like this in order to protect myself from SQL injection?
或者我是否需要做一些更广泛的事情来保护自己免受 SQL 注入？
With command
.Parameters.Count = 1
.Parameters.Item(0).ParameterName = "@baz"
.Parameters.Item(0).Value = fuz
End With
Are there other advantages to using parameterized queries besides the security considerations?
除了安全考虑之外，使用参数化查询还有其他优势吗？
Update: This great article was linked in one of the questions references by Grotok. http://www.sommarskog.se/dynamic_sql.html
更新：这篇很棒的文章链接在 Grotok 的一个问题参考文献中。 http://www.sommarskog.se/dynamic_sql.html
采纳答案by Joel Coehoorn
Your EXEC example would NOT be parameterized. You need parameterized queries (prepared statements in some circles) to prevent input like this from causing damage:
您的 EXEC 示例不会被参数化。您需要参数化查询（某些圈子中准备好的语句）来防止这样的输入造成损坏：
';DROP TABLE bar;--
';删除表格栏;--
Try putting that in your fuz variable (or don't, if you value your bar table). More subtle and damaging queries are possible as well.
试着把它放在你的 fuz 变量中（或者不要，如果你重视你的条形表）。更微妙和破坏性的查询也是可能的。
Here's an example of how you do parameters with Sql Server:
以下是如何使用 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.
存储过程有时被认为可以防止 SQL 注入。但是，大多数时候您仍然必须使用查询参数调用它们，否则它们无济于事。如果你使用存储过程完全，那么你可以为应用程序的用户帐户关闭权限SELECT，UPDATE，ALTER，创建，删除等（刚才的一切，但EXEC），并得到了一些保护的方式。
回答by Tamas Czinege
Definitely the last one, i.e.
绝对是最后一个，即
Or do I need to do somethng more extensive ...? (Yes,
cmd.Parameters.Add())
或者我需要做更广泛的事情......？（是的，
cmd.Parameters.Add()）
Parametrized queries have two main advantages:
参数化查询有两个主要优点：
- Security: It is a good way to avoid SQL Injectionvulnerabilities
- 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.
- 安全性：是避免SQL注入漏洞的好方法
- 性能：如果您定期使用不同的参数调用相同的查询，则参数化查询可能允许数据库缓存您的查询，这是性能提升的重要来源。
- 额外：您不必担心数据库代码中的日期和时间格式问题。同样，如果您的代码将在非英语语言环境的机器上运行，您将不会遇到小数点/小数点逗号的问题。
回答by Andrew Hare
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 Typeof each parameter.
您想使用最后一个示例，因为这是唯一一个真正参数化的示例。除了安全问题（这比您想象的要普遍得多），最好让 ADO.NET 处理参数化，因为您无法确定传入的值是否需要用单引号括起来而不检查Type每个参数的.
[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);
回答by JAL
Most people would do this through a server side programming language library, like PHP's PDO or Perl DBI.
大多数人会通过服务器端编程语言库来做到这一点，比如 PHP 的 PDO 或 Perl DBI。
For instance, in PDO:
例如，在 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.
例如，在上面的查询中，我可以准备一次语句，然后从一堆数据中循环创建数据数组，并根据需要重复 ->execute 多次。
回答by Tony Andrews
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
然后添加参数值。这种方式确保值 con 最终仅用作值，而如果变量 fuz 设置为另一种方法
"x'; delete from foo where 'a' = 'a"
can you see what might happen?
你能看到会发生什么吗？
回答by Chillzy
Here's a short class to start with SQL and you can build from there and add to the class.
这是一个从 SQL 开始的简短课程，您可以从那里构建并添加到课程中。
MySQL
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
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
