salve,
alengy ha scritto:
Ciao a tutti i partecipanti!
Mi è venuto un forte dubbio:
se io associo al mio command una query con i parameters ad esempio come questa:
MioCommand.CommandText =
"SELECT campo1
FROM tabella
WHERE campo1 LIKE '%' @parameter + '%'";
Ci sono rischi di sql injection ( data dal + che concatena ... ) oppure sono tranquillo perchè uso i parameters e non corro rischio alcuno?
Grazie a tutti!!
metti i caratteri jolly direttamente al momento del caricamento del parameter..
Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=tempdb;Trusted_Connection=Yes;")
con.Open()
Dim sCmd As String = "CREATE TABLE dbo.TestTB( Id int NOT NULL PRIMARY KEY, data varchar(20) NOT NULL);"
For i As Integer = 1 To 10
sCmd &= String.Format("INSERT dbo.TestTB VALUES({0}, {1});", i.ToString, New String(i.ToString, 10))
Next
Dim cmd As New SqlClient.SqlCommand
With cmd
.CommandType = CommandType.Text
.CommandTimeout = 5
.CommandText = sCmd
.Connection = con
End With
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
Dim InputParam As String = "%3%"
cmd = New SqlClient.SqlCommand
With cmd
.CommandType = CommandType.Text
.CommandTimeout = 5
.CommandText = "SELECT Id FROM dbo.TestTB WHERE data LIKE @InputParam;"
.Connection = con
Dim par As New SqlClient.SqlParameter
With par
.Direction = ParameterDirection.Input
.ParameterName = "@InputParam"
.SqlDbType = SqlDbType.VarChar
.Size = InputParam.Length
.SqlValue = InputParam
End With
.Parameters.Add(par)
par = Nothing
End With
Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader
While rdr.Read
For i As Integer = 0 To rdr.FieldCount - 1
Debug.WriteLine(rdr(i))
Next
Debug.WriteLine("")
End While
If Not rdr Is Nothing Then rdr.Close()
rdr = Nothing
Debug.WriteLine("tentativo di intrusione")
InputParam = "%3%; INSERT INTO dbo.TestTB VALUES ( 100, 'prova');"
cmd = New SqlClient.SqlCommand
With cmd
.CommandType = CommandType.Text
.CommandTimeout = 5
.CommandText = "SELECT Id FROM dbo.TestTB WHERE data LIKE @InputParam; SELECT * FROM dbo.TestTB;"
.Connection = con
Dim par As New SqlClient.SqlParameter
With par
.Direction = ParameterDirection.Input
.ParameterName = "@InputParam"
.SqlDbType = SqlDbType.VarChar
.Size = InputParam.Length
.SqlValue = InputParam
End With
.Parameters.Add(par)
par = Nothing
End With
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
cmd = New SqlClient.SqlCommand
With cmd
.CommandType = CommandType.Text
.CommandTimeout = 5
.CommandText = "SELECT * FROM dbo.TestTB;"
.Connection = con
End With
rdr = cmd.ExecuteReader
While rdr.Read
For i As Integer = 0 To rdr.FieldCount - 1
Debug.WriteLine(rdr(i))
Next
Debug.WriteLine("")
End While
If Not rdr Is Nothing Then rdr.Close()
rdr = Nothing
cmd = New SqlClient.SqlCommand
With cmd
.CommandType = CommandType.Text
.CommandTimeout = 5
.CommandText = "DROP TABLE dbo.TestTB;"
.Connection = con
End With
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
con.Dispose()
con = Nothing
saluti