Results 1 to 7 of 7

Thread: [RESOLVED] Application interperting textbox context as SQL Code

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Resolved [RESOLVED] Application interperting textbox context as SQL Code

    Hi All

    Hope you are all well. I have a button in my application which updates a field in a sql table. An incidence has arose where the user has typed into the textbox the word BOM's and the word with.
    Needless to say, when clicking on the submit button, the application fires up an error message stating incorrect syntax after 's and also error at the with. If I take the above words out, it all works fine. So

    Code:
    "UPDATE Temp_Table SET change_detail = '" & txt_req_details.Text & "' WHERE IssueID = '" & Main_Menu.txt_maxid.Text & "'" &
    So I am assuming as ' and with is used it sql it thinks the content of the textbox (txt_req_details) is part of the sql code and the apostrophe in the BOM's is the end of the sql expression.

    How can I code so that the contents of txt_req_details are updated.

    Thank you in advance

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,727

    Re: Application interperting textbox context as SQL Code

    The ' character (and various other characters, or combinations of characters) can cause problems - including severe security issues.

    You can avoid all of those issues by using Parameters to pass values to the database, especially if those values come from user input etc.

    For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,254

    Re: Application interperting textbox context as SQL Code


  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,614

    Re: Application interperting textbox context as SQL Code

    Here is an option which separates data operations from the form. Looks like both values for the UPDATE are strings, if not change the type in each command parameter.

    UpdateOrder returns a named tuple, see the following TechNet article

    Code:
    Imports System.Data.SqlClient
    
    Public Class Operations
        Private Shared ConnectionString As String = "Your connection string"
    
        Public Shared Function UpdateOrder(changeDetail As String, issueIdentifier As String) As (success As Boolean, exception As Exception)
            Dim updateStatement = "UPDATE Temp_Table SET change_detail = @ChangeDetail WHERE IssueID = @IssueID"
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn, .CommandText = updateStatement}
    
                    cmd.Parameters.Add("@ChangeDetail", SqlDbType.NVarChar).Value = changeDetail
                    cmd.Parameters.Add("@IssueID", SqlDbType.NVarChar).Value = issueIdentifier
    
                    Try
                        cn.Open()
                        Return (cmd.ExecuteNonQuery = 1, Nothing)
                    Catch exception As Exception
                        Return (False, exception)
                    End Try
                End Using
            End Using
        End Function
    End Class
    Usage

    Code:
    Dim results As (success As Boolean, exception As Exception) = Operations.UpdateOrder(txt_req_details.Text, Main_Menu.txt_maxid.Text)
    If results.success Then
        ' record updated
    Else
        ' show error message -> results.exception.Message
    End If

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Re: Application interperting textbox context as SQL Code

    Hi, apologies for the late response, have been on annual leave and then working on another project.
    Thank you for all your responses, I have mainly looked at the solution provided by paul as that example is more at my level of coding, however I am still getting an error - System.Data.SqlClient.SqlException: 'Incorrect syntax near 's' Unclosed quotation mark after the character string ''.', when I enter the words where and Bom's into the textbox txt_impact.Text

    my code is as follows

    Code:
        Public Sub updatedets()
            con.Open()
            cmd = con.CreateCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "Update ECN_Details SET impact = @impact WHERE proper_ID = @maxid"
            cmd.Parameters.AddWithValue("@impact", txt_impact.Text)
            cmd.Parameters.AddWithValue("@maxid", Main_Menu.txt_maxid.Text)
            Dim o As Object = cmd.ExecuteScalar()
            con.Close()
    
        End Sub
    Thank you in advance for your time

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,634

    Re: Application interperting textbox context as SQL Code

    I don't see how that new code could possibly be generating that exception. As you can see for yourself, there are no quotes at all in that SQL code. Parameter values are not interpreted as part of the SQL code, which is the whole point. I suspect that you have some other code somewhere else that is not using parameters that is actually responsible for the exception. Use the debugger to step through the code and see exactly where the exception is thrown, or just look at the stack trace. Unless something is broken on your system, it cannot be in the code you posted.

  7. #7

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Re: Application interperting textbox context as SQL Code

    Hi JM

    Thank you for your response. I was getting that error but you are right it was not because of the code! user error! I put in the wrong text box within the code (it should not have been txt_impact.text
    All working now, thank you to you and everyone else who responded - much appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width