Re: [sqlite] Search for text in all tables

2014-12-05 Thread 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Roger Binns 
-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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Dominique Devienne 
On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Max Vlasov 
On Thu, Dec 4, 2014 at 10:44 AM, Baruch Burstein  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
"%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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Petite Abeille 

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

Simplicity first and foremost. 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread 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
>>  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Roger Binns 
-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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread 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
 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  wrote:
>> On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein 
>> 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
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread J T 
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 <ddevie...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
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 <bmburst...@gmail.com>
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread 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  wrote:
> On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein 
> 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Dominique Devienne 
On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein 
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread 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 <
sandersonforens...@gmail.com> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Baruch Burstein 
On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
sandersonforens...@gmail.com> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread 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
 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-04 Thread 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Search for text in all tables

2014-12-03 Thread Baruch Burstein 
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users