Implicit conversion from data type varchar(max) ..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Implicit conversion from data type varchar(max) ..

Author

Topic

Steve Pickster
Starting Member

3 Posts

Posted - 2009-11-28 : 18:12:54

I need to clean a db from SQL injected scripts. The bak file is from a 2000 db and ntext, text, and image data types have been changed to nvarchar(max), varchar(max), and varbinary(max),
I have a SQL script which changes all the text types but the image/varbinary returns
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

when executing

...
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar' , 'varbinary')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
...

I know the binary data is corrupt but they need to clean it up to have google remove the blackage of their website.
Is there a way to selective remove the scripts or due I have to flush all rows +1300 ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-29 : 12:50:02

can you explain your problem with some data ?

Steve Pickster
Starting Member

3 Posts

Posted - 2009-11-30 : 17:15:52

Some +1000 images converted til varbinary(max) have been sqlinjected with some male text. My plan now is to replace the text with a default text, så the code now looks like
...
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ( 'varbinary')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = SELECT * FROM OPENROWSET(BULK '
+ QUOTENAME(@ReplaceStr, '''') + ', SINGLE_BLOB) AS x )' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END

where the @ReplaceStr is equal to the path to the default picture.
GOt an error at the moment:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

Steve Pickster
Starting Member

3 Posts

Posted - 2009-11-30 : 17:21:36

OK - I have solved the problem with the error and now shiftet 1309 mal. text with a default picture.
IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = (SELECT * FROM OPENROWSET(BULK '
+ QUOTENAME(@ReplaceStr, '''') + ', SINGLE_BLOB) AS x )' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources