Products

Evoq Content Overview Content Creation Workflow Asset Management Mobile Responsive Personalization Content Analytics SEO Integrations Security Website PerformanceEvoq Engage Overview Community Management  Dashboard  Analytics  Member Profile Gamification Advocate Marketing Community Engagement  Ideas  Answers  Discussions  Groups  Wikis  Events Mobile ReadyDNN Support PackagesEvoq: CMS FeaturesEvoq OnDemandCompare DNN's CMS Products

Solutions

Content Management SystemMulti-Channel PublishingDigital MarketingOnline Community ManagementIntranet Software SolutionOur CustomersPrime

Resources

Test DrivesSchedule A DemoDocumentation CenterWebinarsWhite PapersProduct ManualsRequest PricingData SheetsCase StudiesEvoq Preferred Products Content Management Ecommerce Forms Identity Management and Authentication Site Management Social Themes

Partners

DNN Partners Partner Directory Become a DNN Partner Partner Program Overview  Hosting  Implementation  ISV  Training  Competencies

Community

Participate Ask a Question Forums Community Showcase DNN MVP  Program Overview  Lifetime MVPs  Honorary MVPs Subscribe to DNN Digest Advisory Groups  Awareness Advisory Group  Developer Advisory Group  Partner Advisory Group  Technology Advisory GroupLearn Documentation Wiki Community Blog Video Library Project History Development RoadmapDownload DNN Store Language Packs Manuals Nightly BuildsSecurity Policies Security Center

Blog

About

News Press Releases News Coverage Press KitCareersContact DNN

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Did anyone write a query to look for a specific string in all DNN tables?Did anyone write a query to look for a specific string in all DNN tables?
Previous
 
Next
New Post
11/24/2016 6:22 AM
 
Edoardo Antonini


Joined: 12/11/2006
Posts: 137

I've had this issue with an ***hole that injected junk links and content into my DNN installation somehow. Still can't figure out exactly how.

By doing so he compromised my DNN installation. Pages would not load because he injected forbidden characters that got loaded during the module loading process on a page. The result were websites with empty pages and error messages showing

Did someone write a query that goes looking for a specific string in all nvarchar fields in the DB? Is this even possible or conceivable or are DB contents so variable there is absolutely no way to do this in a batch and has to be manually done table by table?

Any help with this issue will be greatly appreciated.

 
 
New Post
11/24/2016 9:17 AM
 
Michael Tobisch


Michael Tobisch's Avatar

https://www.dnnwerk.at
Joined: 6/27/2005
Posts: 2083

Edoardo,

This is a nice task - As a quick solution I would do it like this:

DECLARE @sqlCmd nvarchar(max)
DECLARE @table nvarchar(max)
DECLARE @column nvarchar(max)

DECLARE cur CURSOR FOR
SELECT
   t.name,
   c.name
FROM
   sys.tables t
   INNER JOIN sys.columns c ON c.object_id = OBJECT_ID(t.name)
   INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE
   ty.name = 'nvarchar';

OPEN cur
FETCH NEXT FROM cur INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlCmd = 'SELECT ' + @column + ' FROM ' + @table + ' WHERE ' + @column + ' LIKE ''%Some Stupid Text%'''
   EXEC sp_executesql @sqlCmd
   FETCH NEXT FROM cur INTO @table, @column
END
CLOSE cur
DEALLOCATE cur

But there are other ways as well - e.g. CTEs...

Happy DNNing!
Michael

Michael Tobisch
DNN★MVP

dnn-Connect.org - The most vibrant community around the DNN-platform
 
New Post
11/24/2016 10:01 AM
 
Michael Tobisch


Michael Tobisch's Avatar

https://www.dnnwerk.at
Joined: 6/27/2005
Posts: 2083
Edoardo,

maybe this one does it nicer ;-)

http://stackoverflow.com/questions/17...

Hope it helps.
Happy DNNing!
Michael

Michael Tobisch
DNN★MVP

dnn-Connect.org - The most vibrant community around the DNN-platform
 
New Post
11/24/2016 10:05 AM
 
Edoardo Antonini


Joined: 12/11/2006
Posts: 137

Thanks for the suggestione Michael,

unfortunately it did not work. The query was completed with errors and the results it displayed were not particularly helpful. I get a list of empty boxes

http://www.mediafire.com/view/42tyzmlxv4i89ht/SQL_QueryWindow1.jpg

I think they contain the names of the columns but I don't know the names of the Tables they're in so it's not much help. Moreover, does that mean the string is present in those columns? Because if it does the list is quite long and discouraging.

Thanks again
 
New Post
11/24/2016 10:10 AM
 
Michael Tobisch


Michael Tobisch's Avatar

https://www.dnnwerk.at
Joined: 6/27/2005
Posts: 2083

Edoardo,

Which one did you try? I tried the one from my first post, that works fine... but as I wrote above, it is only a quick (and dirty) solution.

Maybe there are also columns of the type ntext affected, so you should change the appropriate line to

ty.name = 'nvarchar' OR ty.name = 'ntext';

If you want the table and column names as well, you should use

SET @sqlCmd = 'SELECT ''' + @table + ''' AS TableName, ''' + @column + ''' AS ColumnName, ' + @column + ' AS ColumnValue FROM ' + @table + ' WHERE ' + @column + ' LIKE ''%Some Stupid Text%'''

Of course there will be empty results from tables that don't contain the string you're looking for, so you have to scroll through the whole result set to find values.

Happy DNNing!
Michael

Michael Tobisch
DNN★MVP

dnn-Connect.org - The most vibrant community around the DNN-platform
 
 Page 1 of 3
123Next 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Did anyone write a query to look for a specific string in all DNN tables?Did anyone write a query to look for a specific string in all DNN tables?


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out