SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Thread: OK, I'm confused...
-
Nov 24, 2009, 11:20 #1
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 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)
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
-
Nov 24, 2009, 12:12 #2
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- 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.
-
Nov 24, 2009, 12:27 #3
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 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