Re: [sqlite] small sqlite fts snippet function or Fts Bug!

[boscowitch]

Yeah, -ID 4 was just a desperate experiment for a hack with longer data
in the search to see if it would lead the snippet function to start
grabbing the data from the start (or at least one "word"/char more).

The offsets beeing wrong and therefore the ... was kinda expected of
me, but in case it worked I would have manually substracted the offset
and put the markers in myself... so it wasn't part of the bug report
just a test how it behaves in that case.

Good to know for the future that its already fixed, thx for taking care
of it so fast!

boscowitch

Am Mittwoch, den 28.01.2015, 02:04 +0700 schrieb Dan Kennedy:
> On 01/27/2015 06:48 PM, boscowitch wrote:
> >
> >
> > and the in an sqlite shell (SQLite version 3.8.8.1 2015-01-20 16:51:25)
> > I get following for a select with snippet:
> >
> > EXAMPLE OUTPUT:
> > sqlite> select docid,*,snippet(test) from test where german match "a";
> > 1|[1] a b c|1] a b c
> > 2|[{[_.,:;[1] a b c|1] a b c
> > 3|1[1] a b c|1[1] a b c
> > 4|[1] a b c|1] a b c
> > 5|​[1] a b c|​[1] a b c
> >
> >
> >
> > -As you can see for id 1 and 2  is at the right position
> > but all beginning non-alphanumerical [,{, etc. are just left out in the
> > snippet.
> >
> >
> > -ID 4 does not help and breaks the offsets so even worse
> >
> 
> Thanks for reporting this. The issue with (1) and (2) is now fixed here:
> 
>http://www.sqlite.org/src/info/adc9283dd9b
> 
> I think it is a bug in the input data causing the problem in (4). The 
> values inserted into "test" and "testdata" are just slightly different.
> 
> Dan.
> 
> 
> ___
> 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] small sqlite fts snippet function or Fts Bug!

[Dan Kennedy]

On 01/27/2015 06:48 PM, boscowitch wrote:



and the in an sqlite shell (SQLite version 3.8.8.1 2015-01-20 16:51:25)
I get following for a select with snippet:

EXAMPLE OUTPUT:
sqlite> select docid,*,snippet(test) from test where german match "a";
1|[1] a b c|1] a b c
2|[{[_.,:;[1] a b c|1] a b c
3|1[1] a b c|1[1] a b c
4|[1] a b c|1] a b c
5|​[1] a b c|​[1] a b c



-As you can see for id 1 and 2  is at the right position
but all beginning non-alphanumerical [,{, etc. are just left out in the
snippet.


-ID 4 does not help and breaks the offsets so even worse



Thanks for reporting this. The issue with (1) and (2) is now fixed here:

  http://www.sqlite.org/src/info/adc9283dd9b

I think it is a bug in the input data causing the problem in (4). The 
values inserted into "test" and "testdata" are just slightly different.


Dan.


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

Re: [sqlite] Converting *.sqlite3 files

[James K. Lowden]

On Mon, 26 Jan 2015 19:26:49 +
Luke Niewiadomski  wrote:

> I am looking to translate *.sqlite3 files into *.csv, or similar
> format.  Would you be willing to point me in the right direction?  I
> appreciate any help on this.  

for T in $(sqlite3 -noheader $DB 
"select name from SQLITE_MASTER 
 where type = 'table'");
do 
sqlite3 -csv db "select * from $T" > ${DB}.$T.csv
done

That's the command you want, subsituting your database name for "$DB".
I can appreciate that it's not easy to read if you don't know Bourne
shell syntax, but if you can find someone who does, that will produce
one csv file for every table in the database.  

HTH.  

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

Re: [sqlite] Converting *.sqlite3 files

[Ronnie Overby]

Luke, I'm willing to help out. Is this a one time conversion or do you need
to do it on an ongoing basis?

On Mon, Jan 26, 2015 at 2:26 PM, Luke Niewiadomski <
lniewiadom...@thezerobase.com> wrote:

> I am looking to translate *.sqlite3 files into *.csv, or similar format.
> Would you be willing to point me in the right direction?  I appreciate any
> help on this.  I am not technically apt enough to figure this out on my own.
>
>
>
> Thank you!
>
> -Luke Niewiadomski
>
> ___
> 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] Converting *.sqlite3 files

[Simon Slavin]

On 26 Jan 2015, at 7:26pm, Luke Niewiadomski  
wrote:

> I am looking to translate *.sqlite3 files into *.csv, or similar format.  
> Would you be willing to point me in the right direction?  I appreciate any 
> help on this.  I am not technically apt enough to figure this out on my own.

You can download the SQLite shell tool from the download page.  Here's the 
documentation:



You'll see a section called 'CSV Export' which should help do what you want but 
you can omit the last command (the '.system' one).

Don't hesitate to post again if this isn't what you want.

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

Re: [sqlite] fuzzer-generated apparent ASAN violation (oob read)

[Richard Hipp]

On 1/27/15, Michal Zalewski  wrote:
> One more find. Tested on 64-bit x86 Linux box, version 3.8.8.1.
>
> printf "create table t0(\211 DEFAULT(0=0)NOT/**/NULL);REPLACE into t0
> select'';" >test.sql
>
> ./sqlite_asan 

Thanks.  Fix is here: https://www.sqlite.org/src/info/e098de69100


> This does not crash under normal conditions, but both ASAN and
> Valgrind claim an out-of-bound read, so they are probably not wrong.
>
> ASAN:
>
> ERROR: AddressSanitizer: heap-buffer-overflow on address
> 0x6080b898 at pc 0x0090427c bp 0x7fff1cf0 sp
> 0x7fff1ce8
> READ of size 8 at 0x6080b898 thread T0
> #0 0x90427b in sqlite3ExprCollSeq
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x90427b)
> #1 0x98e96a in sqlite3BinaryCompareCollSeq
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x98e96a)
> #2 0x96f78b in codeCompare
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x96f78b)
> #3 0x97c15c in sqlite3ExprCodeTarget
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x97c15c)
> #4 0x977fae in sqlite3ExprCode
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x977fae)
> #5 0x9c11b3 in sqlite3GenerateConstraintChecks
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x9c11b3)
> #6 0x8a87a2 in sqlite3Insert
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x8a87a2)
> #7 0x83f810 in yy_reduce (/home/lcamtuf/afl/BIN/sqlite3_asan+0x83f810)
> ...
>
> 0x6080b898 is located 32 bytes to the right of 88-byte region
> [0x6080b820,0x6080b878)
> allocated by thread T0 here:
> #0 0x49d9cb in __interceptor_malloc
> /root/COMP/llvm-3.5.1.src/projects/compiler-rt/lib/asan/asan_malloc_linux.cc:40:3
> #1 0x7e5cb1 in sqlite3MemMalloc
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x7e5cb1)
> #2 0xc5c7b7 in mallocWithAlarm
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0xc5c7b7)
> #3 0x4f74bb in sqlite3Malloc
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x4f74bb)
> #4 0x557a99 in sqlite3DbMallocRaw
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x557a99)
> #5 0x91d2ba in exprDup (/home/lcamtuf/afl/BIN/sqlite3_asan+0x91d2ba)
> #6 0x918e69 in sqlite3ExprDup
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x918e69)
> #7 0x859923 in sqlite3AddDefaultValue
> (/home/lcamtuf/afl/BIN/sqlite3_asan+0x859923)
> ...
>
> Valgrind:
>
> ==16603== Invalid read of size 8
> ==16603==at 0x5D8670: sqlite3ExprCollSeq (sqlite3.c:81927)
> ==16603==by 0x60F29A: codeCompare (sqlite3.c:82059)
> ==16603==by 0x6B39F5: sqlite3ExprCodeTarget (sqlite3.c:84448)
> ==16603==by 0x6B8A01: sqlite3ExprCode (sqlite3.c:85009)
> ==16603==by 0x411402: sqlite3GenerateConstraintChecks (sqlite3.c:99766)
> ==16603==by 0x78C1BE: sqlite3Insert (sqlite3.c:99507)
> ==16603==by 0x7AD4B5: sqlite3Parser (sqlite3.c:124334)
> ==16603==by 0x7BE668: sqlite3RunParser (sqlite3.c:125943)
> ==16603==by 0x7C1129: sqlite3Prepare (sqlite3.c:105025)
> ==16603==by 0x7C25D0: sqlite3LockAndPrepare.part.504 (sqlite3.c:105120)
> ==16603==by 0x7C800D: sqlite3_prepare_v2 (sqlite3.c:105115)
> ==16603==by 0x425315: shell_exec.constprop.11 (shell.c:1433)
> ==16603==  Address 0x5635c68 is 24 bytes before a block of size 16 alloc'd
> ==16603==at 0x4C2845D: malloc (in
> /usr/lib64/valgrind/vgpreload_memcheck-amd64-linux.so)
> ==16603==by 0x56195E: sqlite3MemMalloc (sqlite3.c:16881)
> ==16603==by 0x49694F: sqlite3Malloc (sqlite3.c:20509)
> ==16603==by 0x497747: sqlite3DbMallocRaw (sqlite3.c:20906)
> ==16603==by 0x58AED4: sqlite3AddDefaultValue (sqlite3.c:20991)
> ==16603==by 0x7AC913: sqlite3Parser (sqlite3.c:123890)
> ==16603==by 0x7BE668: sqlite3RunParser (sqlite3.c:125943)
> ==16603==by 0x7C1129: sqlite3Prepare (sqlite3.c:105025)
> ==16603==by 0x7C25D0: sqlite3LockAndPrepare.part.504 (sqlite3.c:105120)
> ==16603==by 0x7C2C4F: sqlite3LockAndPrepare.constprop.573
> (sqlite3.c:105115)
> ==16603==by 0x7C34A4: sqlite3InitCallback (sqlite3.c:105184)
> ==16603==by 0x757DD0: sqlite3_exec (sqlite3.c:100669)
>
> Cheers,
> /mz
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Converting *.sqlite3 files

[Donald Griggs]

Hi, Luke,

It's possible you'll want to enlist a friend who has some familiarity with
databases and utilities. to help you out, since the data you're after may
not be stored into a single table (like a spreadsheet).   I don't know
enough about your needs and your experience to say.

That being said, you can download the command line sqlite utility from
 http://www.sqlite.org
Then, from page
 https://www.sqlite.org/cli.html
you have directions for runnning it.

>From your command line (don't know what operating system you're using)
you'd type (and of course, substitute your actual database file name):

sqlite3  myDatabaseFileName
.tables
[a list of tables should appear]
.mode csv
.output myDesiredOutputFile.csv
SELECT * from myDesiredTableName;
.quit

Note that periods and semicolons must appear as listed above.



> ___
> 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

[sqlite] Converting *.sqlite3 files

[Luke Niewiadomski]

I am looking to translate *.sqlite3 files into *.csv, or similar format.  Would 
you be willing to point me in the right direction?  I appreciate any help on 
this.  I am not technically apt enough to figure this out on my own.



Thank you!

-Luke Niewiadomski

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

[sqlite] fuzzer-generated apparent ASAN violation (oob read)

[Michal Zalewski]

One more find. Tested on 64-bit x86 Linux box, version 3.8.8.1.

printf "create table t0(\211 DEFAULT(0=0)NOT/**/NULL);REPLACE into t0
select'';" >test.sql

./sqlite_asan http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fuzzer-generated apparent ASAN violation (oob read)

[Michal Zalewski]

> printf "create table t0(\211 DEFAULT(0=0)NOT/**/NULL);REPLACE into t0
> select'';" >test.sql

Argh, line breaks. You can just grab this:

wget http://lcamtuf.coredump.cx/afl/vulns/sqlite-oob-read.sql

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

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

[Marcus Bergner]

My main reason for questioning the inconsistency in returned column
names between the three following cases is that SQL standards and other
SQL databases seem to all return short column names, not prefix.column
name in the three below scenarios. Having SQLite behave differently than
everyone else is a compatibility problem that can prevent people from
using SQLite in a smooth manner.

select x.id1, x.s1, y.s2 from tbl1 x inner join tbl2 y on
x.id1=y.id2;
select x.id1, x.s1, subq.s2 from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
select x.id1, x.s1, subq.* from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;

I did a small attempt yesterday at fixing my local sqlite build and I
needed a patch of about 5 lines in sqlite3ExprListSetSpan to make it
return consistent short names. I don't have the full test suite so can't
say for sure it is all good but looks fairly promising.

sqlite> select 1*2, x.id1, x.s1, subq.s2 from tbl1 x inner join (select
* from tbl2 y where y.id2=1) subq on x.id1=subq.id2;
1*2|id1|s1|s2
2|1|v1|v2
sqlite> select 1*2 as magicnumber, x.id1 as 'xyz.col', x.s1, subq.s2
from tbl1 x inner join (select * from tbl2 y where y.id2=1) subq on
x.id1=subq.id2;
magicnumber|xyz.col|s1|s2
2|1|v1|v2

Fix is pretty much this:

char *dot = (char*)memchr(pSpan->zStart, '.', (int)(pSpan->zEnd -
pSpan->zStart));
if (dot) {
  pItem->zSpan = after dot up to pSpan->zEnd
} else {
  pItem->zSpan = entire pSpan->zStart to zEnd string (same as before)
}

Kind regards,

Marcus Bergner

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

[sqlite] small sqlite fts snippet function or Fts Bug!

[boscowitch]

Hello since it this bug report (+ a dirty-fix) it might be useful for
both users and devs.
that's why I send a copy to both mailing lists! 
I hope I don't bother the diligent devs who read all of both list, sry
to them, and thx for sqlite btw. ;)!

recently I wanted to use the snippet function in sqlite for my small
sqlite dictionary (running on android but the bug occurs also on my
linux desktop).

but it behaved strangely when my entry started with "non-words"
character(s) (not alphanumeric and all Unicode (or chars>128) in short
simple tokenizer delimiters)

the snippet never prints them if they are in the beginning  of the first
word
here an examples to demonstrate:

EXAMPLE SETUP SQL:
create table testdata (german);
create virtual table test using fts4(content="testdata",german);

insert into testdata(german) VALUES ("[1] a b c");
insert into test(docid,german) VALUES(1,"[1] a b c ");

insert into testdata(german) VALUES ("[{[_.,:;[1] a b c");
insert into test(docid,german) VALUES(2,"[{[_.,:;[1] a b c "); 

insert into testdata(german) VALUES ("1[1] a b c");
insert into test(docid,german) VALUES(3,"1[1] a b c "); 

insert into testdata(german) VALUES ("[1] a b c");
insert into test(docid,german) VALUES(4,"1[1] a b c "); 

insert into testdata(german) 
VALUES(char(8203,91,49,93,32,97,32,98,32,99));
insert into test(docid,german)
VALUES(5,char(8203,91,49,93,32,97,32,98,32,99));


and the in an sqlite shell (SQLite version 3.8.8.1 2015-01-20 16:51:25)
I get following for a select with snippet:

EXAMPLE OUTPUT:
sqlite> select docid,*,snippet(test) from test where german match "a";
1|[1] a b c|1] a b c
2|[{[_.,:;[1] a b c|1] a b c
3|1[1] a b c|1[1] a b c
4|[1] a b c|1] a b c
5|​[1] a b c|​[1] a b c



-As you can see for id 1 and 2  is at the right position
but all beginning non-alphanumerical [,{, etc. are just left out in the
snippet.

-ID 3 works but has an additional 1 that should not be there so no
solution...

-ID 4 does not help and breaks the offsets so even worse

-ID 5 works BUT this is a dirty fix i found.
it adds an Unicode character ('ZERO WIDTH SPACE' (U+200B)) in front
which obviously cant be seen and doesnt "break" the offsets (just shifts
them all +1)
I didn't test it yet on android but I hope so, since it supports
Unicode ... 
obviously this is not a nice solution or one for more simpler/embedded
systems.


(btw. the same bug occurs also with fts3 and also with no special
content option)
here a small example for normal fts4 with a more custom snippet call:

create virtual table test using fts4(german);
insert into test VALUES("[1] a b c");

sqlite> select *,snippet(test,"#","#","...",0,64) from test where german
match 'a';
[1] a b c|1] #a# b c



regards boscowitch

PS: please excuse the "german" ;) and all English spelling errors 

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