SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)

    OK, I'm confused...

    I've asked others here at work and they don't see the error, so I'll ask here. I've got a SQL query which is throwing me an error I don't understand.

    Basically, what I'm doing is looping through all of the tables in a database looking for remnants of a scripting attack in the (n)char, (n)varchar and (n)text fields. As I'm looping through, I'm writing the results out of a temp table so we can report on them.

    The problem I'm running into is when I'm hitting some of the text columns in the database. Not all - just some...

    Here is the SQL statement for one of the text columns:
    Code SQL:
    INSERT INTO zzz_SearchResults(TableName
           , PrimaryKeyColumn
           , PrimaryKeyValue
           , ColumnName
           , ColumnValue) 
          SELECT 'tbl_quest'
        , 'questionID'
        , [questionID]
        , 'critque'
        , CONVERT(VARCHAR(4000), [critque]) 
            FROM [tbl_quest] (NOLOCK) WHERE [critque] LIKE '%<SCRIPT%'

    And the error I'm getting is:
    Msg 511, Level 16, State 0, Line 1
    Cannot create a row of size 8073 which is greater than the allowable maximum of 8060.
    The statement has been terminated.

    The crtque field is the text field and the zzz_SearchResults table structure is
    • ResultID - identity
    • TableName - nvarchar(50)
    • PrimaryKeyColumn - nvarchar(50)
    • PrimaryKeyValue - int
    • ColumnName - nvarchar(50)
    • ColumnValue - nvarchar(4000)
    This version uses the CONVERT function to convert the field from text to varchar, but I've also tried CAST and SUBSTRING and I'm getting the error with all of these methods.

    What blatantly obvious piece of the puzzle am I missing?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    The data that you insert into the columnValue column may potentially contain 8000 bytes and together with the other columns that may exceed the maximum record length for a table. Either use a text column or insert a substring. Your other columns will at most take 320 bytes which leaves room for 7740/2=3870 characters in the columnValue column.

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    nvarchar vs varchar....doh! nvarchar takes up double the space.....that cleared it up - though I knew that and forgot all about it....

    Where's that wall? Oh, wait, there it is.



    <goes of muttering to self about being stupid>
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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