How do I put textarea data into an SQL Server database?

Question

(This is classic ASP) I'm sure this is just a case of escaping the text, but I haven't found anything yet. I have a textarea that may contain all sorts of characters and I need to UPDATE the record with whatever is in that textarea, but I'm getting an error that breaks my SQL query, which I'm positive is related to the content of the textarea.

So I have this so far as my SQL query:

"UPDATE document SET displayheading='" & _
    stripillegal(request("txt_displayheading")) & _
    "', displayorder=" & displayorder & _
    ", shortdescription='" & stripillegal(request("txt_shortdescription")) & _
    "', document_note='" & request("document_note") ...blah blah blah

So I'm wondering how do I contain the textarea so that I can update the record without ruining the query?

Answer 1

The query will break if there is character like ', you need to replace them first:

newStr = Replace("'", "''", request("txt_displayheading"))

And in your query use the newStr variable instead of request("txt_displayheading"

Answer 2

Here's a way to avoid dynamic SQL and address the SQL injection potential. It uses a parameterized query. Here's a SQL solution:

    DECLARE @DisplayHeading varchar(100)
    DECLARE @DisplayOrder int
    DECLARE @ShortDescription varchar(100)
    DECLARE @DocumentNote varchar(100)

    DECLARE @command nvarchar(500)
    DECLARE @params nvarchar(500)

    SET @command = N'UPDATE document
        SET displayheading=@DisplayHeading
        , displayorder=@DisplayOrder
        , shortdescription=@ShortDescription
        , document_note=@DocumentNote
        --...blah blah blah'

    SET @params = N'@DisplayHeading varchar(100), @DisplayOrder int, 
             @ShortDescription varchar(100), @DocumentNote varchar(100)'

-- Assign values to user variables.

EXEC sp_executesql @command, @params, @DisplayHeading,
     @DisplayOrder, @ShortDescription, @DocumentNote

And here's what you can try with ADO:

    cmd.CommandText = "UPDATE document " & _
         "SET displayheading=@DisplayHeading, displayorder=@DisplayOrder, " & _
         "shortdescription=@ShortDescription, document_note=@DocumentNote " & _
         "--...blah blah blah"

    cmd.Parameters.Add(New SqlParameter("@DisplayHeading", DisplayHeading))
    cmd.Parameters.Add(New SqlParameter("@DisplayOrder", DisplayOrder))
    cmd.Parameters.Add(New SqlParameter("@ShortDescription", ShortDescription))
    cmd.Parameters.Add(New SqlParameter("@DocumentNote", DocumentNote))