Search for all occurrences of a string in a mysql database.

Question

I'm trying to figure out how to locate all occurrences of a url in a database. I want to search all tables and all fields. But I have no idea where to start or if it's even possible.

Answer 1

Not an elegant solution, but you could achieve it with a nested looping structure

// select tables from database and store in an array

// loop through the array
foreach table in database
{
    // select columns in the table and store in an array

    // loop through the array
    foreach column in table
    {
        // select * from table where column = url
    }
}

You could probably speed this up by checking which columns contain strings while building your column array, and also by combining all the columns per table in one giant, comma-separated WHERE clause.

Answer 2

Scott gives a good example of how to do it, but the question is why would you want to? If you need to do a find-and-replace on a specific string, you could also try doing a mysqldump of your database, do a find-and-replace in an editor, then re-load the database.

Maybe if you gave some background on what you are trying to achieve, others might be able to provide better answers.

Answer 3

A simple solution would be doing something like this:

mysqldump -u myuser --no-create-info databasename > myfile.sql

Then you can just do a find on myfile.sql for the URL you want.

Answer 4

Brute force method

declare @url varchar(255)

set @url = 'stackoverflow.com'

select 'select * from ' + rtrim(tbl.name) + ' where ' + 
          rtrim(col.name) + ' like %' + rtrim(@url) + '%'
from sysobjects tbl
inner join syscolumns col on tbl.id = col.id 
and col.xtype in (167, 175, 231, 239) -- (n)char and (n)varchar, there may be others to include
and col.length > 30 -- arbitrary min length into which you might store a URL
where tbl.type = 'U'    -- user defined table

This will create a script that you could execute on the database.

select * from table1 where col1 like '%stackoverflow.com%'
select * from table1 where col2 like '%stackoverflow.com%'
select * from table2 where col3 like '%stackoverflow.com%'

etc.

Answer 5

I am looking for the same solutuion. He could be asking for the same reason as me - my database has been hacked and every link in my admin back office takes me to the hakers page rendering my admin area useless.

Answer 6

Hi,

I was looking for the same but couldn't find it, so I make a small script in PHP, you can find it at: http://tequilaphp.wordpress.com/2010/07/05/searching-strings-in-a-database-and-files/

Good luck! (I remove some private code, let me know if I didn't break it in the process :D)