Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Use commandparameters instead of string concatenation
From Wiki
To avoid SQL-injection (see Wikipedia:SQL injection) you should use the command parameters of the commandobject and not String concatenation.
This is how you would and could do it with string concatenation
- Dim _connection As System.Data.SqlClient.SqlConnection = New SqlClient.SqlConnection("Connectionstring")
- _connection.Open()
- Dim _command As New SqlClient.SqlCommand()
- _command.Connection = _connection
- _command.CommandType = CommandType.Text
- _command.CommandText = "SELECT * FROM table1 WHERE field1 = '" & Me.Text & "'"
- Dim _adapter As New SqlClient.SqlDataAdapter(_command)
- Dim _datatable As New DataTable
- _adapter.Fill(_datatable)
Especially this part is bad
- _command.CommandText = "SELECT * FROM table1 WHERE field1 = '" & Me.Text & "'"
We would need to change it like this.
- Dim _connection As System.Data.SqlClient.SqlConnection = New SqlClient.SqlConnection("Connectionstring")
- _connection.Open()
- Dim _command As New SqlClient.SqlCommand()
- _command.Connection = _connection
- _command.CommandType = CommandType.Text
- _command.CommandText = "SELECT * FROM table1 WHERE field1 = @field1"
- _command.Parameters.Add(New SqlClient.SqlParameter("@field1",SqlDbType.NVarChar,30)
- _command.Parameters(0).Value = Me.Text
- Dim _adapter As New SqlClient.SqlDataAdapter(_command)
- Dim _datatable As New DataTable
- _adapter.Fill(_datatable)
So we added a @field1 to our select statement and removed any single quotes the parameter will take care of that, which makes life eassier for us.
Then we create the parameter with the type and length matching our field in the table. And the we give it a value.
Much safer then using concatenation.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.