Select Query Help

  • 10 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










  • 10 years ago

    Replace your code as follow:





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



    It will work.

     

     

  • 10 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?

  • 10 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.















  • 10 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

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Perl - The only language that looks the same before and after RSA encryption.” - Keith Bostic