Search for text in all tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Search for text in all tables

Baruch Burstein-2
Hi,

Is it possible to somehow search for/replace a string in all columns of all
tables?

Thanks

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Martin Engelschalk
Hi Baruch,

no, not with SQL as I know it.

Perhaps you can .dump and .output the database to a text file using
sqlite3 command shell, replace your text and then create the database
from the SQL using .read.

HTH
Martin

Am 04.12.2014 08:44, schrieb Baruch Burstein:
> Hi,
>
> Is it possible to somehow search for/replace a string in all columns of all
> tables?
>
> Thanks
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Paul Sanderson
Hi Baruch

I have a commercial tool that can do the search all tables bit but not
the replacing :) (not usually required by my clients - although I may
add it)

If you are a coder then it is a relatively straight forward process
along the lines of

Loop through each table
   Loop through each column
       UPDATE tablename SET colname = REPLACE(colname, 'oldtext', 'newtext')

The gotchas are how you choose to deal with strings stored in non text
fields and how unique your terms are/what you do with substrings



Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 4 December 2014 at 08:10, Martin Engelschalk
<[hidden email]> wrote:

> Hi Baruch,
>
> no, not with SQL as I know it.
>
> Perhaps you can .dump and .output the database to a text file using sqlite3
> command shell, replace your text and then create the database from the SQL
> using .read.
>
> HTH
> Martin
>
> Am 04.12.2014 08:44, schrieb Baruch Burstein:
>>
>> Hi,
>>
>> Is it possible to somehow search for/replace a string in all columns of
>> all
>> tables?
>>
>> Thanks
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Baruch Burstein-2
On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
[hidden email]> wrote:

> If you are a coder then it is a relatively straight forward process
> along the lines of
>
> Loop through each table
>    Loop through each column
>

This is the part I am having trouble with. I can loop through tables using
sqlite3_master, but how do I loop through columns? Parse the schema?


--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Martin Engelschalk
Hi Baruch,

in such cases I do a sqlite3_prepare_v2 on "select * from mytable LIMIT
1" and get the column names using sqlite3_column_count and
sqlite3_column_name.

Martin
Am 04.12.2014 11:45, schrieb Baruch Burstein:

> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
> [hidden email]> wrote:
>
>> If you are a coder then it is a relatively straight forward process
>> along the lines of
>>
>> Loop through each table
>>     Loop through each column
>>
> This is the part I am having trouble with. I can loop through tables using
> sqlite3_master, but how do I loop through columns? Parse the schema?
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Dominique Devienne
In reply to this post by Baruch Burstein-2
On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein <[hidden email]>
wrote:

> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
> > If you are a coder then it is a relatively straight forward process
> > Loop through each table
> >    Loop through each column
>
> This is the part I am having trouble with. I can loop through tables using
> sqlite3_master, but how do I loop through columns? Parse the schema?


http://www.sqlite.org/pragma.html#pragma_table_info  --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Paul Sanderson
As above I use

pragma table_info tablename

then you can loop through each row and check the type column to see if
it is text
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 4 December 2014 at 11:57, Dominique Devienne <[hidden email]> wrote:

> On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein <[hidden email]>
> wrote:
>
>> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
>> > If you are a coder then it is a relatively straight forward process
>> > Loop through each table
>> >    Loop through each column
>>
>> This is the part I am having trouble with. I can loop through tables using
>> sqlite3_master, but how do I loop through columns? Parse the schema?
>
>
> http://www.sqlite.org/pragma.html#pragma_table_info  --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
J T
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

J T
In reply to this post by Dominique Devienne
Dominique,

Why not get a column count (datareader.fieldcount (C#) or cursor.getColumnCount() (Java/Android))?

From there you should be able to simply do a
try {
    str = getString(columnIndex);
    checkValue(str);
}
catch(Exception e) {  
    // wasn't a string or the check (and replace) failed
    // you may want to catch different exceptions
}





 

 

 

-----Original Message-----
From: Dominique Devienne <[hidden email]>
To: General Discussion of SQLite Database <[hidden email]>
Sent: Thu, Dec 4, 2014 6:57 am
Subject: Re: [sqlite] Search for text in all tables


On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein <[hidden email]>
wrote:

> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
> > If you are a coder then it is a relatively straight forward process
> > Loop through each table
> >    Loop through each column
>
> This is the part I am having trouble with. I can loop through tables using
> sqlite3_master, but how do I loop through columns? Parse the schema?


http://www.sqlite.org/pragma.html#pragma_table_info  --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Paul Sanderson
In reply to this post by Paul Sanderson
Had 5 mins waiting for a conference call so knocked this bit of Python
up. No debugging, comments or testing (other than one run on a test
db)

import sqlite3
import tkinter
import tkinter.filedialog

root = tkinter.Tk()
root.withdraw()

fileName = tkinter.filedialog.askopenfilename(parent=root,
title='Select the SQLite database')
DB = sqlite3.connect(fileName)

print ('Processing ' + fileName)

tables = DB.execute("select * from sqlite_master where type like 'table'")

for tablerow in tables:
    tableName = tablerow[2]
    print ("tableName = " + tableName)
    columns = DB.execute("pragma table_info (" + tableName + ")")
    for colrow in columns:
        colName = colrow[1]
        colType = colrow[2]
        print ("colName = " + colName)
        DB.execute("UPDATE " + tableName + " SET " + colName + " =
REPLACE(" + colName + ", 'paul', 'sandy')")

DB.commit()
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 4 December 2014 at 12:31, Paul Sanderson
<[hidden email]> wrote:

> As above I use
>
> pragma table_info tablename
>
> then you can loop through each row and check the type column to see if
> it is text
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
>
>
>
> On 4 December 2014 at 11:57, Dominique Devienne <[hidden email]> wrote:
>> On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein <[hidden email]>
>> wrote:
>>
>>> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
>>> > If you are a coder then it is a relatively straight forward process
>>> > Loop through each table
>>> >    Loop through each column
>>>
>>> This is the part I am having trouble with. I can loop through tables using
>>> sqlite3_master, but how do I loop through columns? Parse the schema?
>>
>>
>> http://www.sqlite.org/pragma.html#pragma_table_info  --DD
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Roger Binns
In reply to this post by Baruch Burstein-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/03/2014 11:44 PM, Baruch Burstein wrote:
> Is it possible to somehow search for/replace a string in all
> columns of all tables?

(Disclosure: I am the APSW author)

The APSW shell includes a .find command that does the searching bit.
You also get coloured output which is nice.  It was implemented to
help find things in a database where you are unfamiliar with the
schema and wondering where the heck things are referenced amongst lots
of tables and columns.

http://rogerbinns.github.io/apsw/shell.html

The code that implements it is here:

https://github.com/rogerbinns/apsw/blob/master/tools/shell.py#L1384

It essentially has to loop over all tables, and then uses an OR
statement to check for the value in each column.  From the code you
can see it does additional work based on the value so that it may do
string, integer and LIKE comparisons simultaneously as appropriate.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlSAsisACgkQmOOfHg372QQC+QCgt4YAbvrxt1luvsnhK/r2R/0Q
l4kAoI3PvnQRvmObQqqGMAGJC1cEvehf
=X77t
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Petite Abeille-2
In reply to this post by Baruch Burstein-2

> On Dec 4, 2014, at 8:44 AM, Baruch Burstein <[hidden email]> wrote:
>
> Is it possible to somehow search for/replace a string in all columns of all
> tables?

.dump | sed ’s/old/new/g' | .read ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Roger Binns
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/04/2014 11:59 AM, Petite Abeille wrote:
>> On Dec 4, 2014, at 8:44 AM, Baruch Burstein
>> <[hidden email]> wrote:
>>
>> Is it possible to somehow search for/replace a string in all
>> columns of all tables?
>
> .dump | sed ’s/old/new/g' | .read ?

That will only work under the simplest of cases.  For example if "old"
occurs anywhere outside a value (eg table name, text of a trigger,
index, column) then the database won't be right.  Baruch also didn't
say if "old" should be an entire value match or a substring match -
the former won't work with sed reliably.

Roger

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlSA0Y4ACgkQmOOfHg372QTCDgCfbsJR9uJ/tVlYVnnn0clU1Egr
x/YAoOUuleJXlh3XEADeAm9CO/DH47qZ
=/ItF
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Petite Abeille-2

> On Dec 4, 2014, at 10:26 PM, Roger Binns <[hidden email]> wrote:
>
> That will only work under the simplest of cases.

Simplicity first and foremost.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Max Vlasov
In reply to this post by Baruch Burstein-2
On Thu, Dec 4, 2014 at 10:44 AM, Baruch Burstein <[hidden email]> wrote:
>
> Is it possible to somehow search for/replace a string in all columns of all
> tables?
>

Not particularity the answer to your question, but rather a method you
or others might use.
I once implemented a virtual table "allvalues" that outputs all
database values with (hope self-explaining) fields

  TableName, TableRowId, FieldName, Value

that allows also exploring unknown complex databases. Take for example
places.sqlite of Mozilla Firefox. If you want to explore where it
saves your visited site, you can use the query

  SELECT Distinct TableName, FieldName FROM allvalues where Value like
"%<a href="http://%">http://%"

Sqlite's virtual tables are a great tool since with a little effort on
the developer side the newly created entity starts working as a first
class citizen of the sqlite engine. Compare this to a quick hack that
outputs all raw data from a specific database to a specific media.

Max
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Dominique Devienne
On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov <[hidden email]> wrote:

> Not particularity the answer to your question, but rather a method you
> or others might use.
> I once implemented a virtual table "allvalues" that outputs all
> database values with (hope self-explaining) fields
>
>   TableName, TableRowId, FieldName, Value
>
> that allows also exploring unknown complex databases.


Thanks for sharing Max! That's a great idea. I'd never would have thought
of that.

I agree with you, Sqlite's virtual tables are great. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Where sqlite vtables are used? (was: Search for text in all tables)

decalek
On 05.12.2014 12:32, Dominique Devienne wrote:

> On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov <[hidden email]> wrote:
>
>> Not particularity the answer to your question, but rather a method you
>> or others might use.
>> I once implemented a virtual table "allvalues" that outputs all
>> database values with (hope self-explaining) fields
>>
>>    TableName, TableRowId, FieldName, Value
>>
>> that allows also exploring unknown complex databases.
>
>
> Thanks for sharing Max! That's a great idea. I'd never would have thought
> of that.
>
> I agree with you, Sqlite's virtual tables are great. --DD

Where have you seen/developed interesting vtable implementations?

Please respond with a short description and/or link to sqlite vtable
source locations you know :-)

Kind regards,
Alek

P.S. Few weeks ago, I tried to materialize few internet searches under
sqlite_addon tag in openhub.net (formerly ohloh.net) DB, but the list is
too short yet:

https://www.openhub.net/tags/sqlite_addon
https://www.openhub.net/tags/sqlite_vtable

Better ideas about where to collect/categorize available extensions?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Roger Binns
In reply to this post by Max Vlasov
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/05/2014 01:24 AM, Max Vlasov wrote:
> I once implemented a virtual table "allvalues" that outputs all
> database values with (hope self-explaining) fields
>
> TableName, TableRowId, FieldName, Value

Could you expand on how you coped with the underlying database
changing, and how you mapped virtual table rowids to the actual
database records?

Roger

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlSB+fcACgkQmOOfHg372QRZ0QCdHbaDwwE0mrE8SaITJhn5lB7K
KugAoJaBjpLVj4zemq9kqS1UsCAyvjuc
=1Jet
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Search for text in all tables

Max Vlasov
>> I once implemented a virtual table "allvalues" that outputs all
>> database values with (hope self-explaining) fields
>>
>> TableName, TableRowId, FieldName, Value
>
> Could you expand on how you coped with the underlying database
> changing, and how you mapped virtual table rowids to the actual
> database records?
>

This particular implementation was intended to be used as a
Select-only wrapper so it just iterates through every sqlite_master
table and every row of each table. I didn't support update and insert.
Rowid of this virtual table is compound bit mask starting with table
bits (able to fit the number of tables of this db), field bits
(maximum possible number of fields in a table) and the rest is rowid
of the particular table. So in theory this method won't work for
tables containing large 64-bit ids where there are not enough bits to
be used for table number and field number.

Max
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users