Select Query Help

• • Reply

  14 years ago

  by materialised

  Mick Walker

  Northern Ireland, United Kingdom

  Joined 14 years ago

  Hi all,
  I need some help with a simple select query using MS SQL Server 2005. The problem I am having is due to the contents of the table, it can contain names which use punctuation marks such as:
  
  S'Algar
  Olhos D'Agua
  Praia D'Oura
  etc.etc
  This is breaking my SQL Queries. I select the data using

  Dim command As New SqlCommand("Select * from Navigation where Keyword='" & Index & "'", connection)

  
  and simply read the data returned by the query, into a dataset.
  I'd be grateful for any help offered

  Kind Regards

  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by rajaram.mhadgut

  Rajaram Mhadgut

  Mumbai, India

  Joined 15 years ago

  Replace your code as follow:
  
  
  
  
  
  Dim command As New SqlCommand("Select * from Navigation where Keyword='" & Index.Replace("'","''") & "'", connection)
  
  
  
  It will work.

   

   

  Report abuse
• • Reply

  14 years ago

  by materialised

  Mick Walker

  Northern Ireland, United Kingdom

  Joined 14 years ago

  Thanks,
  I have also found that this isnt an issue when I get the data using a sotred proceedure. Do you have any idea why?
  

  Report abuse
• • Reply

  14 years ago

  by amarsuperstar

  amar raja

  , United Kingdom

  Joined 14 years ago
  The data is escaped when put into the stored procedure.
  
  Basically, doing queries like this is a bad habit, SQL Injection attacks could allow me to (providing user access rights) change, delete and do pretty much what i want with the info. For example, I was showing a company that their website was vulnerable... with this command in the search textbox
  
  "'); update products set price=price*0.5; --
  
  i dropped all the products by 50%, placed an order and then using the same type of command, put the prices back -- no one could tell.
  
  Get into the habit of always using parameterized queries, they are much, much safer...
  
  http://www.uberasp.net/ArticlePrint.aspx?id=46
  
  Amar.
  
  DISCLAIMER: The actions mentioned above were carried out with permission from the company in question; i have never used such techniques for personal gain and do not condone such activity. The example above is to help new sql users to understand why we should write safe sql and get into good practices.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  14 years ago

  by Stormbind

  Myke

  , Philippines

  Joined 14 years ago

  select * FROM authors where au_lname = 'O''Ringer'

  Well that means you need to check you string before you can pass it to your query. The code above shows how yo query using the name O'Ringer.

  Hope this can help

  Report abuse