Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 11th, 2003, 01:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Insert statement with apostrophe

When using an Insert statement that contains string type fields and the values contain an apostrophe; What are some option to handle this and allow the user to enter an apostrophe.

Example: Insert Statement:
INSERT INTO tblAttributes (AttributeID, AttributeName, AttributeDescription) VALUES ('B211F2FAD9ED4B53B31DCEB897C1145', 'APPN'D', 'What Now')

The problem being that string data types are delimited by the single quatation mark and the apostrophe is viewed as the closing delimiter.

I know this cannot be a unique situation and there must be a way to allow this.

Any help would be greatly appreciated.

Larry Asher
__________________
Larry Asher
Reply With Quote
  #2 (permalink)  
Old June 11th, 2003, 01:47 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi Larry,

Depending on your environment, there are a few options.

The quickest way is to escape each single apostrophe with an extra apostrope, like this:

Code:
INSERT INTO Users (Name) VALUES ('O''Larry''s')
This will insert the name O'Larry's into the database.

Alternatively, you can use your technology to do the escaping for you automatically. For example, if you use Access, ASP VB etc in combination with ADO, you can use Stored Procedures and Command objects which will take care of the conversion.

HtH,

Imar
Reply With Quote
  #3 (permalink)  
Old June 11th, 2003, 02:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much. That worked nicely I am going to automate the solution next. I was just working on an alternative to check string values and replace the apostrophe with chr(180) which to me looked almost identical but didn't cause any problems with SQL.

Thanks again.

Larry Asher
Reply With Quote
  #4 (permalink)  
Old June 13th, 2003, 12:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Imar
 Hi Larry,

Depending on your environment, there are a few options.

The quickest way is to escape each single apostrophe with an extra apostrope, like this:

Code:
INSERT INTO Users (Name) VALUES ('O''Larry''s')
This will insert the name O'Larry's into the database.

Alternatively, you can use your technology to do the escaping for you automatically. For example, if you use Access, ASP VB etc in combination with ADO, you can use Stored Procedures and Command objects which will take care of the conversion.

HtH,

Imar
Well, I have my function working that will validate string parameters in a SQL statement. It was quit fun to write and I think it will cover the almost all instances. I say that because in instances like this it is very hard to predict and can be dependent on coding convention. So, getting to my question you mentioned I would be able to use VB and ADO to do the conversion for me. Could you elaborate on that? If there is a way to make my function more encompassing I would like to do it. One of the goals I had when developing this validation function was to be able to pass it an existing SQL string and verify the string parameters, checking for apostrophies. What I have determines the operation, SELECT, INSERT, etc. then performs the verification and any required modification. I also feel it can be expanded on to cover other types of validation as needed. One of the reasons for being able to handle an existing SQL string is it allows me to add this function to app's I have already developed and pass the existing SQL strings without any major code revisions.

As much fun as it is to code interesting solutions there are times when other solutions make more sense. I just wanted to know what you and others think in this case.

Thank you,

Larry Asher
Reply With Quote
  #5 (permalink)  
Old June 13th, 2003, 05:30 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi Larry,

The code I'll post below is for ASP, but the idea is the same for VB as well. It's a simple example for a stored procedure that expects a few parameters. I'll post both the ASP code as the sproc, so you can see how things fit together. The code may look a bit detailed, but that's because it is created by a code generator. I also changed some of the code in this message, so I haven't actually run the code. Beware of bugs.....

A short description:

The sproc takes two arguments: the description of the new category, and the userID of the user who created it. Once it has been created, the new ID is returned, ready to be used in the calling application.

The ASP code creates a command object, sets a few parameters and then adds parameters to the Command. As values for the parameters, I pass in the Description and UserID variables. In a real world scenario, you could retrieve these values from the Form collection, like this:

Description = Request.Form("txtDescription")

In a VB application, you would do it like this:

Description = txtDescription.Text

If Description would contain an apostrophe, ADO will handle the communication with the database, so there is no need to double them anymore.

For more information, I suggest you get yourself a copy of the ADO 2.6 Programmer's Reference, published by Wrox. It contains a lot of information on this topic.

If you have any questions, please let me know.


By the way, would you mind posting your replace solution here? I am sure it will benefit other readers of this forum......

Regards,

Imar


[Stored Procedure]
Code:
CREATE PROCEDURE sprocCreateCategory
/*    '================================================
    '   NAME:         sprocCreateCategory
    '   DATE CREATED: June, 14 2003
    '   CREATED BY:   
    '   CREATED FOR:  
    '   FUNCTION:     Creates a new category
    '   IN:                  
    '   OUT:          Nothing
    '   VERSION: 1           
    '   EXAMPLE:             
    '   COMMENTS:            
    '   MODIFIED ON:         
    '   MODIFIED BY:         
    '   REASON MODIFICATION: 
    '================================================*/

    @ID int,
    @Description varchar (50),
    @UserID int

    AS

    DECLARE @ReturnValue int

    INSERT INTO Categories
    (
        Description,
                CreatedBy
    )
    VALUES
    (
        @Description,
                @UserID
    )

    -- You can return @@IDENTITY right away, but by storing it 
        -- in a local variable, we can do other things 
        -- with it in the sproc.
        SELECT @ReturnValue = @@IDENTITY
        RETURN @ReturnValue


[ASP Page]
Code:
' Create ADODB Command object
Set objCategoriesItem = Server.CreateObject("ADODB.Command")

' Set required properties like connection (string or object),
' command type (stored procedure), and sproc name
' Add paramaters for each field in the sproc
With objCategoriesItem
    .ActiveConnection = CONNECTION_STRING
    .CommandType = adCmdStoredProc
    .CommandText = "sprocCreateCategory"
    .Parameters.Append(.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue))
    .Parameters.Append(.CreateParameter("@Description", adVarChar, adParamInput, 50, Description))
    .Parameters.Append(.CreateParameter("@UserID", adInteger, adParamInput, 4, UserID))
    .Execute , , adExecuteNoRecords
        ' Get the new ID of the Category
    iResult = .Parameters("RETURN_VALUE").Value
End With
' Clean up
Set objCategoriesItem = Nothing
Reply With Quote
  #6 (permalink)  
Old June 16th, 2003, 09:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar,

Here is the function as requested. If others find this useful that would be great.

Any feedback would also be appreciated.

Public Function ValidateStringParams(ByRef SQLString As String) As Long
' This function will check the string parameters within the sql statement
' It is checking for reserved delimiters that cause known errors
'
' Currently we are only checking for the apostrophe mark
' If found, the apostrophe mark will be replaced with chr(180)
' It looks like an apostrophe but does not interfere will the SQL statement
'
' This function can be expanded in the future to include other reserved characters or delimiters

    Dim lStartAt As Long
    Dim lPos As Long
    Dim lStartingDelimiter As Long
    Dim lEndingDelimiter As Long
    Dim lLen As Long
    Dim lDelimiterCount As Long
    Dim strTestChar As String
    Dim strFirstHalf As String
    Dim strSecondHalf As String
    Dim strParam As String
    Dim strSQLType As String

    On Error GoTo ErrorHandler

    ' Determine the SQL type, SELECT, DELETE, INSERT, UPDATE
    ' Find the first blank space
    lPos = InStr(1, SQLString, " ", vbTextCompare)
    ' Start at the first character to the first blank space
    ' This should be the type of sql statement
    strSQLType = Mid(SQLString, 1, lPos - 1)
    ' Init starting point and count
    lStartAt = 0
    lDelimiterCount = 0
    lPos = 0
    ' Determine the delimiters and identify the string parameters in the SQL String
    Do
        ' Increment count
        ' We are counting the valid delimiters by default start at one
        lDelimiterCount = lDelimiterCount + 1
        ' Init first, second, and param
        ' These will be set when we have a valid second delimiter
        strFirstHalf = ""
        strSecondHalf = ""
        strParam = ""
        ' Set this back to zero to intialize the position of the delimiter
        ' It will be reset on each pass of the loop
        lPos = 0
        ' The first thing we need to do is identify all of the string values in the statement
        lPos = InStr(lStartAt + 1, SQLString, "'", vbTextCompare)
        ' Test for a position greater than 0
        If lPos > 0 Then
            ' Set the start position to position
            lStartAt = lPos
        Else
            ' Nothing else to do
            Exit Do
        End If
        ' Test count, if count = 1 then we
        ' should have the beginning and end of a string parameter
        If lDelimiterCount = 1 Then
            ' Record first position
            lStartingDelimiter = lPos
        ElseIf lDelimiterCount = 2 Then
            ' Record second pos
            lEndingDelimiter = lPos
            ' Test the character following the delimiter
            ' We are testing for a comma indicating another parameter follow's
            ' or a closed parenthesis indicating this is the last parameter
            ' This will rely somewhat on good coding convention
            ' If we program the sql statement with a blank space then
            ' we would over run the parameter and have a really screwy result
            ' But for the most part it is standard sql syntax
            strTestChar = Mid(SQLString, lStartAt + 1, 1)
            '
            ' Depending on the sql type there are differnt ways to validate
            ' the second delimiter
            '
            Select Case UCase(strSQLType)
            Case Is = "SELECT", "DELETE"
                ' This is a SELECT OR A DELETE statement
                If StrComp(strTestChar, " ", vbTextCompare) = 0 _
                Or StrComp(strTestChar, "", vbTextCompare) = 0 Then
                    ' The test character now idicates that we have a string parameter
                    ' We can now test the parameter for any reserved charaters
                    ' The start pos and end pos indicate the position of valid delimiters
                    ' The delimiter we are testing for is the single quotation mark
                    ' We want the parameter without the delimiter in order to test it
                    ' Parse off the delimiter by adding one to the start position
                    ' and subtracting one from the second position
                    strParam = Mid(SQLString, lStartingDelimiter + 1, (lEndingDelimiter - lStartingDelimiter) - 1)
                    ' Break the string into pieces
                    ' Include the delimiter as the last character
                    ' of the first half of the string
                    ' and as the first character of the second string
                    strFirstHalf = Left(SQLString, lStartingDelimiter)
                    strSecondHalf = Mid(SQLString, lEndingDelimiter)
                    '
                    ' Modify string if needed
                    ' Currently we are only checking for apostrophies
                    ' We can expand this in the future if any other problems arise
                    strParam = Replace(strParam, "'", Chr(180), , , vbTextCompare)
                    ' Check the length of the string to the end of the parameter
                    ' Add one to ensure we start after a known delimiter
                    ' Use this as the starting reference
                    lStartAt = Len(strFirstHalf) + Len(strParam) + 1
                    ' Rebuild string
                    SQLString = strFirstHalf & strParam & strSecondHalf
                    ' Reset lDelimiterCount to 0
                    lDelimiterCount = 0
                Else
                    ' Resest lDelimiterCount to 1
                    lDelimiterCount = 1
                End If
            Case Is = "INSERT"
                ' This is an INSERT statement
                If StrComp(strTestChar, ",", vbTextCompare) = 0 _
                Or StrComp(strTestChar, ")", vbTextCompare) = 0 Then
                    ' The test character now idicates that we have a string parameter
                    ' We can now test the parameter for any reserved charaters
                    ' The start pos and end pos indicate the position of valid delimiters
                    ' The delimiter we are testing for is the single quotation mark
                    ' We want the parameter without the delimiter in order to test it
                    ' Parse off the delimiter by adding one to the start position
                    ' and subtracting one from the second position
                    strParam = Mid(SQLString, lStartingDelimiter + 1, (lEndingDelimiter - lStartingDelimiter) - 1)
                    ' Break the string into pieces
                    ' Include the delimiter as the last character
                    ' of the first half of the string
                    ' and as the first character of the second string
                    strFirstHalf = Left(SQLString, lStartingDelimiter)
                    strSecondHalf = Mid(SQLString, lEndingDelimiter)
                    '
                    ' Modify string if needed
                    ' Currently we are only checking for apostrophies
                    ' We can expand this in the future if any other problems arise
                    strParam = Replace(strParam, "'", Chr(180), , , vbTextCompare)
                    ' Check the length of the string to the end of the parameter
                    ' Add one to ensure we start after a known delimiter
                    ' Use this as the starting reference
                    lStartAt = Len(strFirstHalf) + Len(strParam) + 1
                    ' Rebuild string
                    SQLString = strFirstHalf & strParam & strSecondHalf
                    ' Reset lDelimiterCount to 0
                    lDelimiterCount = 0
                Else
                    ' Resest lDelimiterCount to 1
                    lDelimiterCount = 1
                End If
            Case Is = "UPDATE"
                ' This is an UPDATE statement
                If StrComp(strTestChar, ",", vbTextCompare) = 0 _
                Or StrComp(strTestChar, " ", vbTextCompare) = 0 _
                Or StrComp(strTestChar, "", vbTextCompare) = 0 Then
                    ' The test character now idicates that we have a string parameter
                    ' We can now test the parameter for any reserved charaters
                    ' The start pos and end pos indicate the position of valid delimiters
                    ' The delimiter we are testing for is the single quotation mark
                    ' We want the parameter without the delimiter in order to test it
                    ' Parse off the delimiter by adding one to the start position
                    ' and subtracting one from the second position
                    strParam = Mid(SQLString, lStartingDelimiter + 1, (lEndingDelimiter - lStartingDelimiter) - 1)
                    ' Break the string into pieces
                    ' Include the delimiter as the last character
                    ' of the first half of the string
                    ' and as the first character of the second string
                    strFirstHalf = Left(SQLString, lStartingDelimiter)
                    strSecondHalf = Mid(SQLString, lEndingDelimiter)
                    '
                    ' Modify string if needed
                    ' Currently we are only checking for apostrophies
                    ' We can expand this in the future if any other problems arise
                    strParam = Replace(strParam, "'", Chr(180), , , vbTextCompare)
                    ' Check the length of the string to the end of the parameter
                    ' Add one to ensure we start after a known delimiter
                    ' Use this as the starting reference
                    lStartAt = Len(strFirstHalf) + Len(strParam) + 1
                    ' Rebuild string
                    SQLString = strFirstHalf & strParam & strSecondHalf
                    ' Reset lDelimiterCount to 0
                    lDelimiterCount = 0
                Else
                    ' Resest lDelimiterCount to 1
                    lDelimiterCount = 1
                End If
            End Select
        End If
    Loop While lPos <> 0
    ' Return 0
    ValidateStringParams = 0

    Exit Function

ErrorHandler:

    ' Return error
    ValidateStringParams = Err.Number
    ' Log error only, will be raise by client
    '
    ' The following line is for a custom error handler
    'ClassError Err.Number, Err.Description, "ValidateStringParams", True

End Function


Larry Asher
Reply With Quote
  #7 (permalink)  
Old June 16th, 2003, 02:36 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi Larry,

I haven't looked at the entire function, but it seems like an awful lot of code. Usually, this will work:

Code:
Public Function CleanSQLValue(ByVal SQL)
    CleanSQLValue = Replace(SQL, "'", "''")
End Function
This just cleans the value, not the entire string and you can use it like this:

Dim UserName
UserName = CleanSQLValue(Request.Form("txtUserName"))

Now UserName is ready to be appended to your SQL statement......

I guess your function tries to replace just the apostropes in the values of an entire SQL statement, while leaving the string delimiters in place, right?

Was the ADO Command code of any use?

Imar
Reply With Quote
  #8 (permalink)  
Old June 16th, 2003, 03:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That is true it does look like an awful lot of code. I do use the Replace() function in the code. However, it is important to identify the string parameter within the SQL statement. The purpose of the function is to check the SQL statement as a whole, identify the string parameters, and then modify the code by replacing any apostrophies found in the string parameter(s). In order to do this I had to account for needed single quotation marks that are valid delimiters and various SQL operations such as Select and Insert. Simply using the replace function on the entire string would yeild undesirable results as it would replace each of the apostrophies in the SQL statement.

Thank you for your input.

Larry Asher
Reply With Quote
  #9 (permalink)  
Old June 16th, 2003, 06:42 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'd suggest moving all your SQL code to the database server if possible (eg if using SQL Server). SQL injection is an issue and the best way to protect from SQL inject is to use prepared statements, which in ASP corresponds to command objects. To summarise SQL Injection, basically it is about a parameter of value overflowing its bounds. If you clearly define the bounds (using ADO Command objects and parameterised stored procedures) this is not a problem.

For more information on this see:
http://www.nextgenss.com/papers/adva..._injection.pdf

If this is not possible, then you should be mostly OK if you do two things:
1. For string values, replace all ' with ''.
2. For numeric values, make sure that you have numbers not strings

I think that your function attacks the problem from the wrong position. You are checking the SQL statement once it has been generated, you need to check/fix each parameter as it gets added to the SQL Statement. Sorry to be saying this once you have put the time into it.

Example (ASP):

Code:
Dim strSQL, strVal
Dim intVal

strVal = Replace(Request.Form("stringval"), "'", "''")
If Not IsNumeric(intVal) Then
    ' set to default value
    intVal = 0
End If

strSQL = "INSERT INTO MyTable (IntCol, varcharCol) VALUES ('" & intVal & "', " & strVal & ")"
regards
David Cameron
Reply With Quote
  #10 (permalink)  
Old June 17th, 2003, 08:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I very much agree with you in regards to attacking the problem from the wrong angle. However, when looking at existing applications it seemed much less intrusive to validate the string as a whole versus re-writing every instance where a SQL statement is built.

On the other hand when talking about best practices. I have been trying to develop using the principles of the Microsoft Solution Framework (MSF) methodology. This has lead me into developing from a n-tiered approach giving me a presentation layer, a logic layer, and a data services layer. I have been using dll's in the logic layer to handle all of the data validation, manipulation and retrieval. In almost every instance these days I create a dll representing the data objects I will be working with, such as , a recipe or a document. That gives me the ability to pass data objects between applications, as well as, other nice features. In most solutions to date I have only used two physical layers while the logical layers remain unchanged. My thought of the idea place to validate the SQL parameters is in the Let() Property representing a parameter of the class module within the Dll and any other function responsible for building an SQL statement.

In regards to SQL Inject I may have heard it mention once or twice. I have not done very much with SQL server to this point. But, recently I had requested SQL server, got the approval, and the parts are showing up now. I would be very interested in reading any documentation you could point me to supporting your ideas on implementing SQL solutions.

One of my goals in development is not to re-invent the wheel. I find there is plenty of room for innovation within each solution. While practices, methodologies, and common task are something we can share and build upon.

Thanks for the input.

Larry Asher
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
T-SQL statement causing error with insert/update saf SQL Language 0 September 7th, 2007 04:48 PM
Creating an INSERT INTO SQL statement for 2 tables sburgess Access 1 April 18th, 2007 08:52 AM
Help with SQL Insert statement in VBA Code ausmoran Access VBA 1 December 28th, 2006 06:16 PM
Insert Statement Iashia06 Access 0 April 7th, 2006 12:45 PM



All times are GMT -4. The time now is 05:33 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.