Products

Solutions

Learn More

Partners

Community

Blog

About

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
 

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
 

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
helferlein.com - Michael Tobisch's Site about DNN, SQL Server, ASP.Net and other related stuff dnn-Connect.org - The most vibrant community around the DNN-platform
 
New Post
11/24/2016 10:01 AM
 
Edoardo,

maybe this one does it nicer ;-)

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

Hope it helps.
Happy DNNing!
Michael

Michael Tobisch
DNN★MVP
helferlein.com - Michael Tobisch's Site about DNN, SQL Server, ASP.Net and other related stuff dnn-Connect.org - The most vibrant community around the DNN-platform
 
New Post
11/24/2016 10:05 AM
 

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
 

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
helferlein.com - Michael Tobisch's Site about DNN, SQL Server, ASP.Net and other related stuff dnn-Connect.org - The most vibrant community around the DNN-platform
 
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.
DNN Launch Webinar
REGISTER NOW
DNN Launch Webinar
REGISTER NOW
DNN Launch Webinar
REGISTER NOW