Problem with SQL query

Problem with SQL query

• markvanh

  Think of SQL as an entirely different language than VB.  It expects a certain string, and doesn't really care where it comes from.  I have no experience with vb6, but if the statement you gave actually worked, then vb6 acted as an intermediary that allowed you to express strings that way.

  VB.Net does not have that intermediary.  It's expecting a raw string.  Say me.textbox.text has a value of "Sally".  Therefore, with the statement "SELECT * FROM Table WHERE field1=me.textbox.text", the engine will be looking for a field called "me.textbox.text" and not "Sally".

  The most basic way to look for "Sally" is to append the contents of the textbox to the query, such as:

  "SELECT * FROM Table WHERE field1='" & me.textbox.text

  However, what happens if some meanie puts this in the textbox:  "Sally'; DELETE FROM Table"   Your table gets deleted because someone just inserted a Delete command into your SQL.  This is called an SQL Insertion attack.

  How to avoid this   One way is to use parameters, as noted in an earlier message.  The other way would be to filter out single-quotes and/or semicolons (both are needed in an injection attack), like:

  "SELECT * FROM Table WHERE field1=" & me.textbox.text.replace("'","").replace(";","")

  Using parameters is probably a bit less messy, especially if you expect your textbox to have a single-quote or semicolon in it.

  
  

• fictionforever

  Andreas, I've sent you an email... Thanks a lot for answers.

  ADDED: Sorry, your email doesn't work... do you have other

  
  

• Sverk

  Thanks, but i need user input. Any other ideas
  

• Richard Masse

  Do not concatenate user input to a sql statement! Use parameterized query instead. This will avoid sql injection attacks.

  Something like this if you use the sql client.

  Dim strQuery As String = "SELECT field1, field2, field3 FROM(table) WHERE(field1 = @field1)"
  Dim sqlCnn As SqlConnection = New SqlConnection(strConnectionString)
  Dim sqlCmd As SqlCommand = New SqlCommand(strQuery, sqlCnn)
  sqlCmd.Parameters.Add(New SqlParameter("@field1", Form.textfield.Text))
  Dim sqlDR As SqlDataReader = sqlCmd.ExecuteReader()

  EDIT: User input is handled by a parameter instead. Highlighted in bold.

  
  
  

• Bradster

  jwooley wrote:

  WHERE field1=form.textfield.Text

  It's not working :(

  
  

• briankerri

  Andreas answered the question, but I wanted to point out the source of the original problem. VB.Net does not allow for default properties like VB6 did. In the original instance, you were trying to compare field1 with the TEXTBOX called textfield. Instead, you need to use the text property of textfield as follows:

  WHERE field1=form.textfield.Text

  Make sure to use parameterized queries as Andreas showed. It avoids some security concerns and is often faster due to cached execution plans.

  Jim Wooley
  devauthority.com/blogs/jwooley/default.aspx

  
  
  

• Omen

  My address is afjohansson(-at-)hotmail.com
  Replace (-at-) with @
  
  
  
  
  

• dfl

  Study the sample code I provided. The text I highlighted in bold allows the Form.textfield.Text to be used as a parameter in the query.

  What you are trying to do can be dangerous. What you probably are trying to write is something like this

  Dim strQuery As String
  strQuery = "select field1 from table where field1 = '" + Form.TextField.Text + "'"

  If this does not help you it would help if you provide some code sample of what it not working.

  
  
  
• Problem with SQL query