orden - plan de ejecución y optimización de consultas sql server 2008 r2

¿Cómo creo una consulta SQL parametrizada? ¿Por qué debería?

Question

He oído que "todos" usan consultas SQL parametrizadas para protegerse contra ataques de inyección SQL sin tener que actualizar cada parte de la entrada del usuario.

¿Cómo haces esto? ¿Lo obtiene automáticamente cuando usa procedimientos almacenados?

Entonces mi entendimiento es que no tiene parámetros:

cmdText = String.Format("SELECT foo FROM bar WHERE baz = ''{0}''", fuz)

¿Estaría parametrizado?

cmdText = String.Format("EXEC foo_from_baz ''{0}''", fuz)

¿O tengo que hacer algo más extenso como este para protegerme de la inyección de SQL?

With command
.Parameters.Count = 1
.Parameters.Item(0).ParameterName = "@baz"
.Parameters.Item(0).Value = fuz
End With

¿Existen otras ventajas en el uso de consultas parametrizadas además de las consideraciones de seguridad?

Actualización: este gran artículo fue vinculado en una de las preguntas de referencias de Grotok. http://www.sommarskog.se/dynamic_sql.html

Answer 1

Aquí hay una clase corta para comenzar con SQL y puedes construir desde allí y agregar a la clase.

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

Answer 2

Definitivamente el segundo.

Las consultas parametrizadas tienen dos ventajas principales:

• Seguridad: es una buena forma de evitar las vulnerabilidades de inyección de SQL
• Rendimiento: si invoca regularmente la misma consulta solo con diferentes parámetros, una consulta parametrizada podría permitir que la base de datos guarde en caché sus consultas, lo cual es una fuente considerable de ganancia de rendimiento.
• Extra: no tendrá que preocuparse por los problemas de formato de fecha y hora en el código de su base de datos. De forma similar, si su código se ejecutará alguna vez en máquinas con una configuración regional que no esté en inglés, no tendrá problemas con los puntos decimales / comas decimales.

Answer 3

Desea ir con su último ejemplo, ya que es el único realmente parametrizado. Además de los problemas de seguridad (que son mucho más frecuentes, entonces podría pensar) es mejor dejar que ADO.NET maneje la parametrización, ya que no puede estar seguro si el valor que está pasando requiere comillas simples o no, sin inspeccionar el Type de cada parámetro.

[Editar] Aquí hay un ejemplo:

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);

Answer 4

La mayoría de las personas haría esto a través de una biblioteca de lenguaje de programación del lado del servidor, como PHP PDO o Perl DBI.

Por ejemplo, en 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*/}

Esto se ocupa de escanear sus datos para la inserción de la base de datos.

Una ventaja es que puede repetir una inserción muchas veces con una declaración preparada, ganando una ventaja de velocidad.

Por ejemplo, en la consulta anterior pude preparar la declaración una vez, y luego recorrer la creación de la matriz de datos de un grupo de datos y repetir el -> ejecutar tantas veces como sea necesario.

Answer 5

Su ejemplo de EXEC NO se parametrizará. Necesita consultas parametrizadas (declaraciones preparadas en algunos círculos) para evitar que una entrada como esta cause daños:

''; Barra DROP TABLE; -

Intenta poner eso en tu variable fuz (o no, si valoras tu tabla de barras). También son posibles consultas más sutiles y perjudiciales.

Aquí hay un ejemplo de cómo se hacen los parámetros con el servidor Sql:

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

Los procedimientos almacenados a veces se acreditan con la prevención de la inyección SQL. Sin embargo, la mayoría de las veces todavía tiene que llamarlos usando parámetros de consulta o no ayudan. Si usa procedimientos almacenados exclusivamente , entonces puede desactivar los permisos para SELECCIONAR, ACTUALIZAR, ALTERAR, CREAR, ELIMINAR, etc. (casi todo excepto EXEC) para la cuenta de usuario de la aplicación y obtener cierta protección de esa manera.

Answer 6

Su texto de comando debe ser como:

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

Luego agrega valores de parámetros. De esta forma se asegura que el valor con solo termine siendo usado como un valor, mientras que con el otro método si la variable fuz se configura como

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

¿Puedes ver lo que podría pasar?