Invalid column name exception in SQL Query - VB.Net

Question

I made a button to receive the texts from two combo boxes. The connection is OK and the names in the combo box too, but when I run the application for any two valid names I receive the message Invalid column name. I'm sure that the column names are valid according to my data base.

Here's my code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnProcesar.Click

  sql.sqlcon.Open()
  sql.sqlsentence = New SqlCommand("SELECT * FROM dbo.Univ$ WHERE Sector =  " & ingSector.Text & "  AND Ciudad =  " & ingCiudad.Text, sql.sqlcon)

  Dim adaptador As SqlDataAdapter
  adaptador = New SqlDataAdapter(sql.sqlsentence)

  Dim ds As New DataSet
  adaptador.Fill(ds)
  sql.sqlcon.Close()
  dgrid.DataSource = ds.Tables(0)

End Sub

So, how can I fix that?

http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — Ňɏssa Pøngjǣrdenlarp, Jul 03 '14 at 00:35
If I were to key in **(' or 1=1--)** in your ingSector.Text. It will return all result from the table. — HengChin, Jul 03 '14 at 00:57

score 4 · Answer 1 · answered Jul 03 '14 at 01:05

Do this instead of your current one to avoid SQL injection.

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnProcesar.Click
    sql.sqlcon.Open()
    sql.sqlsentence = New SqlCommand("SELECT * FROM dbo.Univ$ WHERE Sector=@sector AND Ciudad=@ciudad" , sql.sqlcon)

    Dim adaptador As SqlDataAdapter
    adaptador = New SqlDataAdapter(sql.sqlsentence)
    adaptador.SelectCommand.Parameters.AddWithValue("@sector", ingSector.Text)
    adaptador.SelectCommand.Parameters.AddWithValue("@ciudad", ingCiudad.Text)

    Dim ds As New DataSet
    adaptador.Fill(ds)
    Sql.sqlcon.Close()
    dgrid.DataSource = ds.Tables(0)
  End Sub

Guffa · Accepted Answer · 2014-07-03T10:41:02.807

3

You don't have apostrophes around the string literals in the query, so the database thinks that the values are supposed to be column names.

Adding apostrophes around the values would make it work:

sql.sqlsentence = New SqlCommand("SELECT * FROM dbo.Univ$ WHERE Sector =  '" & ingSector.Text & "'  AND Ciudad =  '" & ingCiudad.Text & "'", sql.sqlcon)

However, you should really look into using a parameterised query to use the values, instead of concatenating the values into the query.

edited Jul 03 '14 at 10:41

answered Jul 02 '14 at 23:53

Guffa

687,336
108
737
1,005

Thanks for making that clear !. Why Should I look into using a parameterised query? Would you mind recommending a goord resource about that topic? – CreamStat Jul 03 '14 at 00:09
1

This will get you started. http://software-security.sans.org/developer-how-to/fix-sql-injection-microsoft-.net-with-parameterized-queries. While the focus is on security, I think the biggest values are escaping special characters, like apostrophes, and better performance. – Dan Bracuk Jul 03 '14 at 00:48

Invalid column name exception in SQL Query - VB.Net

2 Answers2