Re: [sqlite] NULL always greater?

[Dan Kennedy]

The basic rule is that the result of any comparison involving a
NULL value (including comparing against another NULL) is NULL.

See the following for the details:

http://www.sqlite.org/nulls.html

Dan.


On Tue, 2007-01-30 at 16:41 -0800, Clark Christensen wrote:
> I've read through numerous discussions here about comparing values with null, 
> and how SQLite functions work with null values, and I thought I understood.
> 
> Now it seems appropriate to use the max(col1, col2) function to find the 
> latest of two dates (integer Unix times), and some rows will contain null in 
> one column or the other.  But, max() always returns null when one of its args 
> is null.  That just seems backwards :-))
> 
> FWIW, I'm on 3.3.12 on both Windows and Linux.
> 
> Any help is appreciated.
> 
> Thanks!
> 
>  -Clark
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Peter James]

On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


Thoughts anyone?  Are there less drastic measures that might
be taken to prevent this kind of abuse?



A couple of people here mentioned CAPTCHA's.  This is sort of the standard
for preventing automated abuse (intentional or unintentional), and there are
lots of example implementations out there, maybe even in whatever "custom
software" you use. :-)

http://en.wikipedia.org/wiki/Captcha

Even if you don't go the CAPTCHA route, just forcing an HTTP POST to begin a
download will probably filter out a large proportion of errant traffic or
web bots.  I see your robots.txt file is in order for the downloads area,
but of course that's just a gentleman's agreement...

Re: [sqlite] Obtaining randomness on win32

[Roger Binns]

John Stanton wrote:
> This might help with Win32.
> http://msdn2.microsoft.com/en-us/library/aa387694.aspx

That function is only available on Vista, but it includes a pointer to
CryptGenRandom which is on all Windows versions including Windows CE -
CE doc at http://msdn2.microsoft.com/en-us/library/ms936077.aspx

There is an example using the API at
http://msdn2.microsoft.com/en-us/library/aa382048.aspx

There is also a brief survey and sample code for various RNGs at
http://www.codeproject.com/useritems/PRNG.asp

Roger

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Do I need to use sqlite3_close after a failed sqlite3_open?

[Roger Binns]

Jay Sprenkle wrote:
> On 1/24/07, Jef Driesen <[EMAIL PROTECTED]> wrote:
>>
>> Do I need to use sqlite3_close if the call to sqlite3_open indicated an
>> error? The documentation for sqlite3_open says "An sqlite3* handle is
>> returned in *ppDb, even if an error occurs." So I assumed the answer is
>> yes.
> 
> I never do, since if open fails I assumed the handle wasn't valid.
> It almost never happens so the consequences of being wrong are pretty
> small.

Memory is allocated for an error message.  sqlite_close will free that :-)

Roger

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Roger Binns]

[EMAIL PROTECTED] wrote:
> Thoughts anyone?  Are there less drastic measures that might
> be taken to prevent this kind of abuse?

It will take a little bit of work, but one solution is to start
throttling traffic for the relevant parties, increasing the throttling
the more they seem to abuse your site.

If it is a legitimate user then their downloads etc will eventually
complete, and if not they will consume smaller and smaller amounts of
bandwidth.

Roger

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Limit statement size?

[Joe Wilson]

Your INSERT OR REPLACE statement is in error. 
You have fewer columns in your SELECT clause than are specified 
in your INSERT column name list. You should have seen an error like 
this in SQLite version 3.3.12:

  SQL error: X values for Y columns

Assuming PATIENT_ID is the sole unique key for A3TestB67_J and
your SQL column counts match, the REPLACE should work.

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> Have tried this, but it didn't alter the table, although there was no error:
> 
> INSERT OR REPLACE INTO 
> A3TestB67_J(PATIENT_ID,
> ENTRY_ID_E1,
> READ_CODE_E1,
> TERM_TEXT_E1,
> START_DATE_E1,
> ADDED_DATE_E1,
> NUMERIC_VALUE_E1,
> ENTRY_ID_E2,
> READ_CODE_E2,
> TERM_TEXT_E2,
> START_DATE_E2,
> ADDED_DATE_E2,
> NUMERIC_VALUE_E2,
> ENTRY_ID_E3,
> READ_CODE_E3,
> TERM_TEXT_E3,
> START_DATE_E3,
> ADDED_DATE_E3,
> NUMERIC_VALUE_E3,
> ENTRY_ID_E4,
> READ_CODE_E4,
> TERM_TEXT_E4,
> START_DATE_E4,
> ADDED_DATE_E4,
> NUMERIC_VALUE_E4)
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1
> left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID)
> left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID)
> left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID)



 

No need to miss a message. Get email on-the-go 
with Yahoo! Mail for Mobile. Get started.
http://mobile.yahoo.com/mail 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] UNIQUE constraint on column

[Shane Harrelson]

when i try to insert a row into a table that has a UNIQUE constraint
on a column, and I get the SQLITE_CONSTRAINT result code because i'm
inserting a duplicate value, is there anyway to determine the rowid of
the conflict?

looking at the internals of the VDBE, i found that the rowid of the
conflicting row is pushed on top of the VDBE stack.

if i'm willing to violate the interface, i can dereference the rowid
from the internals of the VDBE struct.  i'd rather not do this... is
there a more formal mechanism for getting this value without having do
to do a separate "select" query?  something like
sqlite3_last_insert_rowid() -- ie.  sqlite3_last_conflict_rowid()?

thanks.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite documentation

[Michael Iatrou]

When the date was Tuesday 30 January 2007 03:29, P Kishor wrote:

> On 1/29/07, Clark Christensen <[EMAIL PROTECTED]> wrote:
> > Puneet,
> >
> > How about "make doc"?  If you have TCL, that seems to generate the
> > HTML output in ./doc.  If you don't, I'd be happy to send it to you.
>
> Thanks Clark. I had, and it did. Me happy.
>
> I am wondering though, how would I have known this if Clark hadn't
> shown me how? I did ./configure --help, and that didn't seem to point
> me anywhere. I poked around on sqlite.org, and didn't find anything
> there as well. Maybe I missed something obvious...

In README there is a reference to publish.sh, where you can see how to 
generate the HTML documentation. On the other hand, ``make doc'' is mostly 
an educated guess, when it comes to documentation generation :-)

-- 
 Michael Iatrou


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Michael Iatrou]

When the date was Tuesday 30 January 2007 16:53, [EMAIL PROTECTED] wrote:

> So my question is really more like this:  Who will get upset
> if www.sqlite.org ceases to function for win98 users?

You can definitely answer this question based on server's logs but still, 
banning users based on OS/UA string is much much less "politically correct" 
than banning based on IP/requests per minute.

Just my 2 cents.

-- 
 Michael Iatrou


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Can't build 3.3.12 on my Unix

[Joe Wilson]

> > Try setting sqlite3_int64 to just "int" and compiling with
> > -DSQLITE_32BIT_ROWID=1.  That might work.
> > --
> > D. Richard Hipp  <[EMAIL PROTECTED]>
>
> "So apparently your compiler cannot cast a double to type xlong_t."
> That's because I did the earlier xlong stuff by hand.  I can try your above
> recommendation, and let you know.
> 
> I should mention 1: That I am running SCO Openserver 5 [popularity not
> withstanding], and 2: That I had to change the makefile because the vanilla
> make on my system does not like "TCC +=" style entries.
> 
> I probably should ask my question this way: What is the *safe* method for
> 32 bit machines to build Sqlite 3.3x?

Setting sqlite3_int64 to just "int" and compiling with -DSQLITE_32BIT_ROWID=1
will not work on some compilers due to certain bit shifts used in SQLite
that are not valid for 32 bit types.

See:
 http://marc.theaimsgroup.com/?t=11645507631&r=1&w=2

 Ticket 2089: Decouple sqlite_int64 from other 64bit datatypes
 http://www.sqlite.org/cvstrac/tktview?tn=2089

A better idea would be to simply use GCC to build SQLite on your SCO box.

Side note... the patch in the SQLite ticket, which used to work, is now garbage:

 http://www.sqlite.org/cvstrac/attach_get/311/patch-for-int32-support2.txt

Is there some corruption in the SQLite CVSTrac database?



 

Bored stiff? Loosen up... 
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] questions on SQLITE_BUSY and other error codes

[Steve Yan]

Hi,

I just joined the newsgroup and started using SQLite.

I have a few questions related to SQLITE_BUSY and other error codes:

1. What API calls could return SQLITE_BUSY?

2. if the DB file already exists, to check the status of 
sqlite3_open(...), should I only check if it returns SQLITE_OK? Could it 
return SQLITE_BUSY? Do I need to add any retry code around 
sqlite3_open(...)?


3. What error codes do I need to check in my application for the 
following calls?


sqlite3_exec(...)
sqlite3_prepare(...)
sqlite3_step(...)
sqlite3_finalize(...)
sqlite3_close(...)

4. What is the prefered way to handle SQLITE_BUSY?

Thanks a lot,
Steve

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Limit statement size?

[Joe Wilson]

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> I can see now what the trouble is if I do the SELECT without the INSERT OR
> REPLACE (shortened):

Can't suggest anything without seeing the schema for all the tables 
involved and any unique indexes related to those tables.

It should work. Perhaps you're not specifying some columns related to 
a unique index on the table being updated. Maybe there's a bug in REPLACE?
Specifically, what does ".schema A3TestB67_J" return?

I suspect this REPLACE technique with its reduced number of database lookups
ought to be much faster than all those subselects you are using now for 
every column, but if you're happy with the timings with the old way and 
it works, there's no point changing it. 

I am curious with it not updating the table, though.

> 
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1,
> GROUP_2 g2,
> GROUP_3 g3,
> GROUP_4 g4
> where
> t1.PATIENT_ID = g2.PID and
> t1.PATIENT_ID = g3.PID and
> t1.PATIENT_ID = g4.PID
> 
> I only get the rows that have entries in all groups. So, this is like an
> inner join and I need a left join.
> Have tried this, but it didn't alter the table, although there was no error:
> 
> INSERT OR REPLACE INTO 
> A3TestB67_J(PATIENT_ID,
> ENTRY_ID_E1,
> READ_CODE_E1,
> TERM_TEXT_E1,
> START_DATE_E1,
> ADDED_DATE_E1,
> NUMERIC_VALUE_E1,
> ENTRY_ID_E2,
> READ_CODE_E2,
> TERM_TEXT_E2,
> START_DATE_E2,
> ADDED_DATE_E2,
> NUMERIC_VALUE_E2,
> ENTRY_ID_E3,
> READ_CODE_E3,
> TERM_TEXT_E3,
> START_DATE_E3,
> ADDED_DATE_E3,
> NUMERIC_VALUE_E3,
> ENTRY_ID_E4,
> READ_CODE_E4,
> TERM_TEXT_E4,
> START_DATE_E4,
> ADDED_DATE_E4,
> NUMERIC_VALUE_E4)
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1
> left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID)
> left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID)
> left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID)
> 
> My old method is actually quite fast and not sure if I can improve on it.
> 
> RBS
> 
> 
> 
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: 30 January 2007 05:53
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Limit statement size?
> 
> --- RB Smissaert <[EMAIL PROTECTED]> wrote:
> > Thanks, that is how I understood it to be.
> > I must be overlooking something simple here.
> 
> Check your SELECT sub-statement within the REPLACE statement to see 
> what rows it returns.
> 
> .header on
> .mode tabs
> 
> create table t1(id primary key, e2_a, e2_b, e3_a, e3_b);
> insert into t1 values(3, 30,31, 23,230);
> insert into t1 values(4, 40,41, 24,240);
> insert into t1 values(5, 50,51, 25,250);
> 
> create table e2(id primary key, a, b);
> insert into e2 values(3, 300, 310);
> insert into e2 values(4, 400, 410);
> insert into e2 values(5, 500, 510);
> 
> create table e3(id primary key, a, b);
> insert into e3 values(3, 23.1, 230.1);
> insert into e3 values(4, 24.1, 240.1);
> insert into e3 values(5, 25.1, 250.1);
> 
> select * from t1 order by id;
> 
> replace into t1(id, e2_a, e2_b, e3_a, e3_b)
>   select t1.id, e2.a, e2.b, e3.a, e3.b
>   from t1, e2, e3 
>   where t1.id = e2.id and t1.id = e3.id;
> 
> select * from t1 order by id;
> 
> id  e2_ae2_be3_ae3_b
> 3   30  31  23  230
> 4   40  41  24  240
> 5   50  51  25  250
> 
> id  e2_ae2_be3_ae3_b
> 3   300 310 23.1230.1
> 4   400 410 24.1240.1
> 5   500 510 25.1250.1
> 
> 
> > 
> > RBS
> > 
> > -Original Message-
> > From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
> > Sent: 29 January 2007 23:52
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Limit statement size?
> > 
> > RB Smissaert wrote:
> > > Had a go at this, but sofar I haven't been able yet to get it to work.
> > > I get no error, but A3Test115_J remains just at it is.
> > > I couldn't find much information about INSERT OR REPLACE in the SQLite
> > > documentation. What exactly should it do?




 

Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
http://answers.yahoo.com/dir/?link=list&sid=396546091

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] PHP 5 and sqlite 3

[Tom Shaw]

There is no entry when I execute the below, yet the update acts as if 
all is OK yet nothing is updated (works same if beginTransaction and 
commit is uncommented).


What is interesting is that the reverse (eg INSERT attempted first works OK.

My concern (other than I what to know why it doesn't work) is that 
the "normal" case would be UPDATE and thus using the reverse sems 
like it will be slower.


TIA,

Tom

try {
//$db_conn->beginTransaction();
		$sql = "UPDATE av_summary SET tot = tot + 1 WHERE 
name='$av_system';";

$result = $db_conn->exec($sql);
//$db_conn->commit();
} catch (PDOException $e) {
//$db_conn->beginTransaction();
		$sql = "INSERT INTO av_summary (name, tot, sig, 
huristic, paranoid) VALUES ('$av_system', 1, 0, 0, 0);";

$result = $db_conn->exec($sql);
//$db_conn->commit();
		$sql = "CREATE TABLE $av_system (virus VARCHAR 
UNIQUE, cnt INTEGER);";

$result = $db_conn->query($sql);
}


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] NULL always greater?

[Clark Christensen]

I've read through numerous discussions here about comparing values with null, 
and how SQLite functions work with null values, and I thought I understood.

Now it seems appropriate to use the max(col1, col2) function to find the latest 
of two dates (integer Unix times), and some rows will contain null in one 
column or the other.  But, max() always returns null when one of its args is 
null.  That just seems backwards :-))

FWIW, I'm on 3.3.12 on both Windows and Linux.

Any help is appreciated.

Thanks!

 -Clark



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Limit statement size?

[RB Smissaert]

I can see now what the trouble is if I do the SELECT without the INSERT OR
REPLACE (shortened):

select
t1.PATIENT_ID,
g2.ENTRY_ID,
g2.READ_CODE,
g2.TERM_TEXT,
g2.START_DATE,
g2.ADDED_DATE,
g2.NUMERIC_VALUE,
g3.ENTRY_ID,
g3.READ_CODE,
g3.TERM_TEXT,
g3.START_DATE,
g3.ADDED_DATE,
g3.NUMERIC_VALUE,
g4.ENTRY_ID,
g4.READ_CODE,
g4.TERM_TEXT,
g4.START_DATE,
g4.ADDED_DATE,
g4.NUMERIC_VALUE
from
A3TestB67_J t1,
GROUP_2 g2,
GROUP_3 g3,
GROUP_4 g4
where
t1.PATIENT_ID = g2.PID and
t1.PATIENT_ID = g3.PID and
t1.PATIENT_ID = g4.PID

I only get the rows that have entries in all groups. So, this is like an
inner join and I need a left join.
Have tried this, but it didn't alter the table, although there was no error:

INSERT OR REPLACE INTO 
A3TestB67_J(PATIENT_ID,
ENTRY_ID_E1,
READ_CODE_E1,
TERM_TEXT_E1,
START_DATE_E1,
ADDED_DATE_E1,
NUMERIC_VALUE_E1,
ENTRY_ID_E2,
READ_CODE_E2,
TERM_TEXT_E2,
START_DATE_E2,
ADDED_DATE_E2,
NUMERIC_VALUE_E2,
ENTRY_ID_E3,
READ_CODE_E3,
TERM_TEXT_E3,
START_DATE_E3,
ADDED_DATE_E3,
NUMERIC_VALUE_E3,
ENTRY_ID_E4,
READ_CODE_E4,
TERM_TEXT_E4,
START_DATE_E4,
ADDED_DATE_E4,
NUMERIC_VALUE_E4)
select
t1.PATIENT_ID,
g2.ENTRY_ID,
g2.READ_CODE,
g2.TERM_TEXT,
g2.START_DATE,
g2.ADDED_DATE,
g2.NUMERIC_VALUE,
g3.ENTRY_ID,
g3.READ_CODE,
g3.TERM_TEXT,
g3.START_DATE,
g3.ADDED_DATE,
g3.NUMERIC_VALUE,
g4.ENTRY_ID,
g4.READ_CODE,
g4.TERM_TEXT,
g4.START_DATE,
g4.ADDED_DATE,
g4.NUMERIC_VALUE
from
A3TestB67_J t1
left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID)
left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID)
left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID)

My old method is actually quite fast and not sure if I can improve on it.

RBS



-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 30 January 2007 05:53
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> Thanks, that is how I understood it to be.
> I must be overlooking something simple here.

Check your SELECT sub-statement within the REPLACE statement to see 
what rows it returns.

.header on
.mode tabs

create table t1(id primary key, e2_a, e2_b, e3_a, e3_b);
insert into t1 values(3, 30,31, 23,230);
insert into t1 values(4, 40,41, 24,240);
insert into t1 values(5, 50,51, 25,250);

create table e2(id primary key, a, b);
insert into e2 values(3, 300, 310);
insert into e2 values(4, 400, 410);
insert into e2 values(5, 500, 510);

create table e3(id primary key, a, b);
insert into e3 values(3, 23.1, 230.1);
insert into e3 values(4, 24.1, 240.1);
insert into e3 values(5, 25.1, 250.1);

select * from t1 order by id;

replace into t1(id, e2_a, e2_b, e3_a, e3_b)
  select t1.id, e2.a, e2.b, e3.a, e3.b
  from t1, e2, e3 
  where t1.id = e2.id and t1.id = e3.id;

select * from t1 order by id;

id  e2_ae2_be3_ae3_b
3   30  31  23  230
4   40  41  24  240
5   50  51  25  250

id  e2_ae2_be3_ae3_b
3   300 310 23.1230.1
4   400 410 24.1240.1
5   500 510 25.1250.1


> 
> RBS
> 
> -Original Message-
> From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
> Sent: 29 January 2007 23:52
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Limit statement size?
> 
> RB Smissaert wrote:
> > Had a go at this, but sofar I haven't been able yet to get it to work.
> > I get no error, but A3Test115_J remains just at it is.
> > I couldn't find much information about INSERT OR REPLACE in the SQLite
> > documentation. What exactly should it do?
> 
> It will try to do an INSERT. If the PATIENT_ID field is already in the 
> file, it will delete the old conflicting entry before inserting.
> 
> The best write-up is at:
> 
> http://sqlite.org/lang_conflict.html
> 
> 
> HTH,
> 
> Gerry



 


No need to miss a message. Get email on-the-go 
with Yahoo! Mail for Mobile. Get started.
http://mobile.yahoo.com/mail 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Does SQLite support user-defined data-types ?

[Igor Tandetnik]

Jerome CORRENOZ <[EMAIL PROTECTED]> wrote:

I'm starting with SQLite and I would like to know if it is possible to
create user-defined data-types through the following SQL command:
create type MyType ... ?


No.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Robert L Cochran]

I fix computers for customers during evenings and weekends and I have 
only have 2 customers (so far) using Windows 98. Nearly everyone, even 
the many seniors among my clients, use Windowx XP and/or OS X.


I feel that banning Win 98 machines is therefore appropriate. Serious 
developers wouldn't be using or developing on Windows 98 any more than 
Windows 3.1. If someone truly needs to download from your site, that 
person can contact you or post to this list and I'm sure some happy 
arrangement can be made. Give them the dump. That's what I would do.


I'm still the only Linux user in my circle of associates but then...the 
Linux folks would probably fix their machines themselves rather than pay 
me to do it. So they'd never think of contacting me to begin with.


Bob Cochran
Greenbelt, Maryland, USA

[EMAIL PROTECTED] wrote:

Last night, a single user (or, at least, a single IP address)
in China that self-identified as running windows98 and
Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
24980 times and  sqlite-source-3_3_12.zip 25044 times
over about a 5 hour period, sucking up significant
bandwidth in the process.

I've seen this type of thing before and have on occasion
banned specific IP addresses from the website using

   iptables -A INPUT -s  -j DROP

But lately, there have been so many problems coming from
win98 and moz4 that I'm thinking of banning all traffic
that self-identifies as such in the User-Agent string of
the HTTP header.

Thoughts anyone?  Are there less drastic measures that might
be taken to prevent this kind of abuse?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Can't build 3.3.12 on my Unix

[drh]

[EMAIL PROTECTED] wrote:
> 
> I probably should ask my question this way: What is the *safe* method for
> 32 bit machines to build Sqlite 3.3x?
> 

"Safe" is relative.  I know of a few companies that use the technique
I outlined earlier.  But I have never personally tested a 32-bit build
so I cannot say what problems might come up.  I would expect to find
problems if you try to insert an integer that cannot be represented in
only 32-bits, for example.  But as long as you stick to smaller
integers I'm guessing everything will likely work ok.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Florian Weimer]

* Terry Jones:

> Given 50K attempts in 5 hours, this is either a bug somewhere or it's
> automated, likely the latter.

I've seen broken proxies which acted as accidental traffic amplifiers.
It's not necessarily a deliberate attack.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Can't build 3.3.12 on my Unix

[GBanschbach]

<[EMAIL PROTECTED]> wrote on 01/30/2007 02:42:11 PM:

> [EMAIL PROTECTED] wrote:
> > Hello all,
> >
> > Question in a nutshell:   My compiler can't handle "typedef long long "
> > (sqlite3.h) --- Is there a workaround?
> >
> > There was an include file in /usr/include/sys, which is called xlong.h.
> > Inside there is a long note about how it addresses 64 bit expressions.
I am
> > not sure it is the correct thing to try,  but I #included it,  and
changed
> > the line 88 to say
> > typedef  xlong_t sqlite_int64 ;
> > and line 89 to say:
> > typedef unsigned xlong_t  sqlite_uint64;
> >
> > Almost worked..  Instead of complaining at the very first file it
tried
> > to compile,  it compiled about 15 source files, and then I get:
> > "./src/vdbemem.c", line 256: error: invalid cast expression
>
> Line 256 attempts to cast a double to type "i64".  "i64" is defined
> in sqliteInt.h:
>
> typedef sqlite3_int64 i64;
>
> So apparently your compiler cannot cast a double to type xlong_t.
>
> Try setting sqlite3_int64 to just "int" and compiling with
> -DSQLITE_32BIT_ROWID=1.  That might work.
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
>
-

> To unsubscribe, send email to [EMAIL PROTECTED]
>
-

>
"So apparently your compiler cannot cast a double to type xlong_t."
That's because I did the earlier xlong stuff by hand.  I can try your above
recommendation, and let you know.

I should mention 1: That I am running SCO Openserver 5 [popularity not
withstanding], and 2: That I had to change the makefile because the vanilla
make on my system does not like "TCC +=" style entries.

I probably should ask my question this way: What is the *safe* method for
32 bit machines to build Sqlite 3.3x?

Thank you very much for such a fast reply!


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] journal size

[drh]

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> 
> Before I commit a transaction, I'm trying to measure the size of the
> journal file to determine typical resource usage for my application.
> 
> The journal file for 1000 inserts on an empty database is much smaller
> (as far as I can tell) then the journal file for 1000 inserts on a
> database with 100k records (which is to be expected, AFAIK).
> 
> However, trying to "stat" the journal file under Windows before the
> commit seems to produce unpredictable results (probably because of
> flushing/syncing delays).
> 

There is no mechanism in SQLite for measuring the size of a
journal file.  If "stat" is not working for you on windows,
I'm afraid there isn't much I can do to help.

SQLite itself uses GetFileSize() to determine file sizes on
win32 and that seems to work reasonably well.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Can't build 3.3.12 on my Unix

[drh]

[EMAIL PROTECTED] wrote:
> Hello all,
> 
> Question in a nutshell:   My compiler can't handle "typedef long long "
> (sqlite3.h) --- Is there a workaround?
> 
> There was an include file in /usr/include/sys, which is called xlong.h.
> Inside there is a long note about how it addresses 64 bit expressions. I am
> not sure it is the correct thing to try,  but I #included it,  and changed
> the line 88 to say
> typedef  xlong_t sqlite_int64 ;
> and line 89 to say:
> typedef unsigned xlong_t  sqlite_uint64;
> 
> Almost worked..  Instead of complaining at the very first file it tried
> to compile,  it compiled about 15 source files, and then I get:
> "./src/vdbemem.c", line 256: error: invalid cast expression

Line 256 attempts to cast a double to type "i64".  "i64" is defined
in sqliteInt.h:

typedef sqlite3_int64 i64;

So apparently your compiler cannot cast a double to type xlong_t.

Try setting sqlite3_int64 to just "int" and compiling with
-DSQLITE_32BIT_ROWID=1.  That might work.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] journal size

[Shane Harrelson]

On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> what's the most reliable method for checking the size of the jounal
> file for a database?  is there anyway to flush that or sync it during
> testing?  i tried using stat() on the jounral file but under windows
> it sometimes reports the jounral file having size 0 even though I know
> it's not empty.
>

The journal file for a database should not exist except when
a transaction is active or after a transaction failed due to
program crash or power failure.

What is it, exactly, that you are trying to do?
--
D. Richard Hipp  <[EMAIL PROTECTED]>



Before I commit a transaction, I'm trying to measure the size of the
journal file to determine typical resource usage for my application.

The journal file for 1000 inserts on an empty database is much smaller
(as far as I can tell) then the journal file for 1000 inserts on a
database with 100k records (which is to be expected, AFAIK).

However, trying to "stat" the journal file under Windows before the
commit seems to produce unpredictable results (probably because of
flushing/syncing delays).

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Can't build 3.3.12 on my Unix

[GBanschbach]

Hello all,

Question in a nutshell:   My compiler can't handle "typedef long long "
(sqlite3.h) --- Is there a workaround?

Long Version:
  I have built version 2.8.17 on my box.  Did some limited testing
seems to work so far.   Now I got 3.3.12.   I did the usual untar,
configure, make.I am getting an error related to "long long":

./libtool --mode=compile cc -g -belf -DOS_UNIX=1
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DTHREADSAFE=0 -DHAVE_USLEEP=1
-DSQLITE_OMIT_LOAD_EXTENSION=1 -I. -I./src -c ./src/vtab.c
mkdir .libs
 cc -g -belf -DOS_UNIX=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DTHREADSAFE=0
-DHAVE_USLEEP=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -I. -I./src -c ./src/vtab.c
-Kpic -DPIC -o .libs/vtab.o
"./sqlite3.h", line 88: error: invalid type combination
"./sqlite3.h", line 89: error: invalid type combination
*** Error code 1 (bu21)
phdl380-sqlite-3.3.12-# grep -in "long long" ./sqlite3.h
77:** Some compilers do not support the "long long" datatype.  So we have
88:  typedef long long int sqlite_int64;
89:  typedef unsigned long long int sqlite_uint64;
===
There was an include file in /usr/include/sys, which is called xlong.h.
Inside there is a long note about how it addresses 64 bit expressions. I am
not sure it is the correct thing to try,  but I #included it,  and changed
the line 88 to say
typedef  xlong_t sqlite_int64 ;
and line 89 to say:
typedef unsigned xlong_t  sqlite_uint64;

Almost worked..  Instead of complaining at the very first file it tried
to compile,  it compiled about 15 source files, and then I get:
./libtool --mode=compile cc -g -belf -DOS_UNIX=1
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DTHREADSAFE=0 -DHAVE_USLEEP=1
-DSQLITE_OMIT_LOAD_EXTENSION=1 -I. -I./src -c ./src/vdbemem.c
 cc -g -belf -DOS_UNIX=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DTHREADSAFE=0
-DHAVE_USLEEP=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -I. -I./src -c
./src/vdbemem.c  -Kpic -DPIC -o .libs/vdbemem.o
"./src/vdbemem.c", line 256: error: invalid cast expression
"./src/vdbemem.c", line 261: error: return value type mismatch
"./src/vdbemem.c", line 267: error: return value type mismatch
"./src/vdbemem.c", line 281: error: invalid cast expression
"./src/vdbemem.c", line 302: error: assignment type mismatch
"./src/vdbemem.c", line 303: error: invalid cast expression
"./src/vdbemem.c", line 540: error: assignment type mismatch
"./src/vdbemem.c", line 545: error: assignment type mismatch
"./src/vdbemem.c", line 555: error: operands have incompatible types: op
"<"
"./src/vdbemem.c", line 556: error: operands have incompatible types: op
">"
"./src/vdbemem.c", line 833: error: operands must have arithmetic type: op
"*"
"./src/vdbemem.c", line 833: error: assignment type mismatch
*** Error code 1 (bu21)

PS:  I realize I could take some time and try to build GCC ver 4.x, but I
hope there is a workaround here.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] journal size

[drh]

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> what's the most reliable method for checking the size of the jounal
> file for a database?  is there anyway to flush that or sync it during
> testing?  i tried using stat() on the jounral file but under windows
> it sometimes reports the jounral file having size 0 even though I know
> it's not empty.
> 

The journal file for a database should not exist except when
a transaction is active or after a transaction failed due to
program crash or power failure.  

What is it, exactly, that you are trying to do?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] journal size

[Shane Harrelson]

what's the most reliable method for checking the size of the jounal
file for a database?  is there anyway to flush that or sync it during
testing?  i tried using stat() on the jounral file but under windows
it sometimes reports the jounral file having size 0 even though I know
it's not empty.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Abuse of the SQLite website

[Dave Dyer]

 ... or just a bug in the downloader.   Imagine a disk full or
final-rename failure, which pongs back up to the download agent
which schedules a retry.

 Some kind of banning, or rate limiting mod to the server side
seems like the right solution to me.  This kind of problem
must have come up before.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[G. Roderick Singleton]

On Tue, 2007-01-30 at 12:58 +, [EMAIL PROTECTED] wrote:
> Last night, a single user (or, at least, a single IP address)
> in China that self-identified as running windows98 and
> Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
> 24980 times and  sqlite-source-3_3_12.zip 25044 times
> over about a 5 hour period, sucking up significant
> bandwidth in the process.
> 
> I've seen this type of thing before and have on occasion
> banned specific IP addresses from the website using
> 
>iptables -A INPUT -s  -j DROP
> 
> But lately, there have been so many problems coming from
> win98 and moz4 that I'm thinking of banning all traffic
> that self-identifies as such in the User-Agent string of
> the HTTP header.
> 
> Thoughts anyone?  Are there less drastic measures that might
> be taken to prevent this kind of abuse?
> 

Richard,

You might like to explore using mod_security with some custom rules to
help eliminate problems like this. See http://www.modsecurity.org/

-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech



smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] Newbie Question

[Gerry Snyder]

Roger Miskowicz wrote:
I am having a problem updating a table in one of two attached 
databases.  What I want to do is indicate in one table whether a name is 
listed in a table in the other attached database.  I am sure it is 
simple but I can't get the UPDATE criteria to work properly.


Any help would be appreciated.

Thanks,
 Roger

/* Test ATTACH two Databases SOC and Options and set one column in a 
Table in SOC*/


DETACH Opt;
DETACH SOC;

ATTACH 'SOC.db' AS SOC;
ATTACH 'Options.db' AS Opt;

UPDATE Stocks SET bOption = 0;

SELECT DISTINCT Stocks.sStockSymbol, bOption FROM Stocks INNER JOIN 
Options ON Stocks.sStockSymbol=Options.sStockSymbol ORDER BY 
Stocks.sStockSymbol;


Where are the names Stocks and Options coming from? Should the ATTACH 
statements use "AS Stocks" and "AS Options" ?



Gerry

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Newbie Question

[Roger Miskowicz]

I am having a problem updating a table in one of two attached 
databases.  What I want to do is indicate in one table whether a name is 
listed in a table in the other attached database.  I am sure it is 
simple but I can't get the UPDATE criteria to work properly.


Any help would be appreciated.

Thanks,
 Roger

/* Test ATTACH two Databases SOC and Options and set one column in a 
Table in SOC*/


DETACH Opt;
DETACH SOC;

ATTACH 'SOC.db' AS SOC;
ATTACH 'Options.db' AS Opt;

UPDATE Stocks SET bOption = 0;

SELECT DISTINCT Stocks.sStockSymbol, bOption FROM Stocks INNER JOIN 
Options ON Stocks.sStockSymbol=Options.sStockSymbol ORDER BY 
Stocks.sStockSymbol;


-- Why do I get 'SQL error: near "INNER": syntax error' at the end of 
the previous output which is

-- fine except for the error message

-- Why doesn't the following UPDATE do anything?  It has no effect and 
provides no error messages.
UPDATE Stocks INNER JOIN Options ON Stocks.sStockSymbol = 
Options.sStockSymbol SET Stocks.bOption = 1;


-- Note this is the same as the one above but does not provide an error 
message.
SELECT  DISTINCT Stocks.sStockSymbol, bOption FROM Stocks INNER JOIN 
Options ON Stocks.sStockSymbol=Options.sStockSymbol ORDER BY 
Stocks.sStockSymbol;


SELECT 'Total Number of Stocks: ',COUNT(*) FROM Stocks;
SELECT 'Number of Stocks with Options: ', COUNT(*) FROM Stocks WHERE 
bOption = 1;
SELECT 'Number of Stocks without Options: ', COUNT(*) FROM Stocks WHERE 
bOption = 0;



Also, .schema doesn't seem to do anything for ATTACHED databases, I 
assume this is intentional.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Dennis Cote]

[EMAIL PROTECTED] wrote:


Everybody seems to assume that the problem is an attack.  I
say that you should never attribute to malice what can be
explained by simple stupidity.  

I agree, it is quite probably an unintentional "attack".


So my question is really more like this:  Who will get upset
if www.sqlite.org ceases to function for win98 users?


Well... Win98 users obviously.

You should be able to summarize your logs and determine how many win98 
based clients are accessing the site. That should give you an idea of 
how many users you are likely to upset. I suspect the number is small 
relative to all the other clients, but you might be surprised.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Martin Jenkins]

[EMAIL PROTECTED] wrote:

Something stupid like that.  Maybe somebody can have a look
at the HTTP reply headers that www.sqlite.org issues and point
out any problems.

http://validator.w3.org/ reports a couple of minor problems (missing character 
encoding, no DOCTYPE and a couple of spurious  tags) but I don't know if 
it's clever enough to report header incompatibilities.


So my question is really more like this:  Who will get upset
if www.sqlite.org ceases to function for win98 users?
I only found one mention of win98 clients in a quick scan of my local 
message base (which only goes back to August '06) so I guess not too 
many people. Presumably your server logs could be checked for non moz4 
win98 users, if any. If that's the simplest solution then I'd go for it 
and look again when if the attack reappears or if someone complains.


Bl**dy vandals. :(

Martin


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Do I need to use sqlite3_close after a failed sqlite3_open?

[Jef Driesen]

Jef Driesen wrote:

[EMAIL PROTECTED] wrote:

Jef Driesen wrote:
I did. sqlite3_close is called automatically for SQLITE_NOMEM, but not 
for other cases. So I guess sqlite3_close is still needed. But then it 
shouldn't return an error, or am I wrong?

I don't think any error other than SQLITE_NOMEM is possible for
sqlite3_open().  Are you seeing some other kind of error come up?


I get SQLITE_CANTOPEN for a non-existing file (and no write permissions
to create it). Using sqlite3_close immediately afterwards returns the
same value. And sqlite3_errcode returns SQLITE_MISUSE.

I think this indicates there is definitely something wrong here. Either
the documentation is incorrect (with regards to the usage of
sqlite3_close after a failed sqlite3_open), or there is a bug in
sqlite3_open/close.

I'm using sqlite version 3.3.5 (Ubuntu Edgy package) if that matters.


I can only think of one reason why the sqlite3* handle is not released
automatically after a failure. If the handle is freed and set to null
(like is done for sqlite3_prepare), it's not possible to retrieve more
information about the error by means of the sqlite3_errcode and
sqlite3_errmsg functions.

SQLITE_NOMEM is the exception here, because a null handle is also
treated as SQLITE_NOMEM by the error functions. Releasing the handle has
no effect on the error reporting in this particular case.

_
Did you know that Windows Live Messenger is accesible on your mobile as from 
now? http://get.live.com/messenger/mobile



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Does SQLite support user-defined data-types ?

[John Stanton]

Sqlite is flexible in typing.  You can create user defined types just by 
declaring them in your SQL which creates tables.  Thereafter the API 
will return the declared type and the actual type so that you can 
process it appropriately.


Jerome CORRENOZ wrote:

Hi,

I'm starting with SQLite and I would like to know if it is possible to 
create user-defined data-types through the following SQL command: create 
type MyType ... ?


In fact, I need it to map a database with an object-oriented model, so 
that I could manage the objects in a ResultSet like it follows:

   Statement stmt = conn.createStatement();
   ResultSet rset = stmt.executeQuery(
   "SELECT * FROM EMP");
   while (rset.next()) {
   Dept dept = (Dept)rset.getObject("DEPT");
   System.out.println("Lieu du dept : "
   + dept.getLieu());
   }

Is there a way to do it with SQLite ?

Regards,
Jerome




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Do I need to use sqlite3_close after a failed sqlite3_open?

[Jef Driesen]

Jef Driesen wrote:

Do I need to use sqlite3_close if the call to sqlite3_open indicated an
error? The documentation for sqlite3_open says "An sqlite3* handle is
returned in *ppDb, even if an error occurs." So I assumed the answer is yes.

But if I try this code (on a non-existing file and no write permissions):

int rc_o = sqlite3_open (filename, &db);
if (rc_o != SQLITE_OK) {
 printf("ERROR: %i, %i, %s\n",
rc_o, sqlite3_errcode(db), sqlite3_errmsg(db));
 int rc_c = sqlite3_close (db);
 if (rc_c != SQLITE_OK) {
printf("ERROR: %i, %i, %s\n",
   rc_c, sqlite3_errcode(db), sqlite3_errmsg(db));
 }
}

I get:

ERROR: SQLITE_CANTOPEN, SQLITE_CANTOPEN, unable to open database file
ERROR: SQLITE_CANTOPEN, SQLITE_MISUSE, library routine called out of
sequence

Shouldn't sqlite3_close return SQLITE_OK?

And why is the rc_c different from the return value of the
sqlite3_errcode function? Isn't that function supposed to return the
error code of the last sqlite3_* function? Which is sqlite3_close in my
code.


I made a mistake in my real code. (I checked rc_o in the second 
comparison, instead of rc_c.) sqlite3_close does return SQLITE_OK in my

example.

But the error code obtained from sqlite3_errcode returns a different
value (SQLITE_MISUSE), which seems to indicate that calling
sqlite3_close was not needed after all. I think this is still wrong.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Lloyd Thomas]

Would not adding verification cause a problem for those include the 
downloading of sqlite in bash/make files of certain apps.


Lloydie T

- Original Message - 
From: "Rich Shepard" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, January 30, 2007 2:50 PM
Subject: Re: [sqlite] Abuse of the SQLite website



On Tue, 30 Jan 2007, Mark Richards wrote:


Returning a link that expires in an email to the requester is a method
that works nicely. It does require some programming and maintenance, but
would be a nice gatekeeper.


  Rather than this, I support the idea of an image with letters and digits
in various fonts or colors (so they cannot be readily harvested by OCR
methods) that are to be manually copied to a text entry field.

  I've no idea of the relative amounts of effort required by these 
different
approaches, but I suspect that such images are readily available on the 
Web

and a sufficiently large collection could be used with each presentation
being arbitrary. Perhaps once this is done -- and hidden from page source
view -- it would not need much maintenance.

  The inconvenience to us who access the site is the price we need to pay
given the abuse of the 'Net and the Web.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 
503-667-8863


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Rich Shepard]

On Tue, 30 Jan 2007, [EMAIL PROTECTED] wrote:


Everybody seems to assume that the problem is an attack. I say that you
should never attribute to malice what can be explained by simple
stupidity.


  In this context, Richard, I think your original idea of blocking the OS
and/or antique version of browsers requires the least amount of effort. If
that fails, then other measures can be considered.


So my question is really more like this: Who will get upset if
www.sqlite.org ceases to function for win98 users?


  Possibly both of them.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[John Stanton]

It may not be as innocent as you suspect.  There are well organized 
hacking schemes in China, with Chinese Government support in some cases. 
 One of these may have been practising on your site.  The header is not 
conclusive evidence of its origin.


[EMAIL PROTECTED] wrote:

Mark Richards <[EMAIL PROTECTED]> wrote:

Headers can always be forged as to browser and OS.  Attackers will 
eventually figure it out and then you're back to the drawing board 
again. 



Everybody seems to assume that the problem is an attack.  I
say that you should never attribute to malice what can be
explained by simple stupidity.  Sqlite.org is a (relatively)
low-bandwidth site on an underpowered server.  If somebody
really wanted to attack it and take it down, they would
not have much trouble.  A botnet could bring sqlite.org to
its knees very, very quickly.  I think the problem here is
bugs in archiac versions of Mozilla and/or windows that get
stuck in a loop issuing the same HTTP request over and
over again.  The user is probably unaware of the problem if
they aren't looking at the modem light flashing.

I'm guessing that the browser doesn't like something in the
reply header coming back from sqlite.org (perhaps it is looking
for a chineses character set or something) and so it reissues
the requesting hoping for a better result the next time.
Something stupid like that.  Maybe somebody can have a look
at the HTTP reply headers that www.sqlite.org issues and point
out any problems.  (Www.sqlite.org is driven by custom software
used nowhere else on the planet, as far as I am aware, so there
could be some subtle compatibility bugs.)

So my question is really more like this:  Who will get upset
if www.sqlite.org ceases to function for win98 users?

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[John Stanton]

My server has been under heavy attack in the past few hours and I am 
also looking for a way to lock out abusers.  I am thinking of making a 
change to tcpwrappers to identify abusers and place them on the deny 
list.  I want to permit mobile users to access services like POP3 but to 
deny an untrusted IP after it is identified as hammering the port.


The download case could be handled by logging the IP of a downloader and 
denying that IP access for a grace period, perhaps 5 or 10 minutes. 
that would not seriously impinge the capabilities of legitimate users 
but would greatly reduce the capability of abusers to create havoc.


A persistent trier could be placed on an IP deny list after a certain 
number of attempts in a specified time window.


If your HTTP server is launched bu inetd you could use the tcpwrappers 
approach.


The check could be placed in the web server.

[EMAIL PROTECTED] wrote:

Last night, a single user (or, at least, a single IP address)
in China that self-identified as running windows98 and
Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
24980 times and  sqlite-source-3_3_12.zip 25044 times
over about a 5 hour period, sucking up significant
bandwidth in the process.

I've seen this type of thing before and have on occasion
banned specific IP addresses from the website using

   iptables -A INPUT -s  -j DROP

But lately, there have been so many problems coming from
win98 and moz4 that I'm thinking of banning all traffic
that self-identifies as such in the User-Agent string of
the HTTP header.

Thoughts anyone?  Are there less drastic measures that might
be taken to prevent this kind of abuse?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[drh]

Mark Richards <[EMAIL PROTECTED]> wrote:
> 
> Headers can always be forged as to browser and OS.  Attackers will 
> eventually figure it out and then you're back to the drawing board 
> again. 

Everybody seems to assume that the problem is an attack.  I
say that you should never attribute to malice what can be
explained by simple stupidity.  Sqlite.org is a (relatively)
low-bandwidth site on an underpowered server.  If somebody
really wanted to attack it and take it down, they would
not have much trouble.  A botnet could bring sqlite.org to
its knees very, very quickly.  I think the problem here is
bugs in archiac versions of Mozilla and/or windows that get
stuck in a loop issuing the same HTTP request over and
over again.  The user is probably unaware of the problem if
they aren't looking at the modem light flashing.

I'm guessing that the browser doesn't like something in the
reply header coming back from sqlite.org (perhaps it is looking
for a chineses character set or something) and so it reissues
the requesting hoping for a better result the next time.
Something stupid like that.  Maybe somebody can have a look
at the HTTP reply headers that www.sqlite.org issues and point
out any problems.  (Www.sqlite.org is driven by custom software
used nowhere else on the planet, as far as I am aware, so there
could be some subtle compatibility bugs.)

So my question is really more like this:  Who will get upset
if www.sqlite.org ceases to function for win98 users?

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Rich Shepard]

On Tue, 30 Jan 2007, Mark Richards wrote:


Returning a link that expires in an email to the requester is a method
that works nicely. It does require some programming and maintenance, but
would be a nice gatekeeper.


  Rather than this, I support the idea of an image with letters and digits
in various fonts or colors (so they cannot be readily harvested by OCR
methods) that are to be manually copied to a text entry field.

  I've no idea of the relative amounts of effort required by these different
approaches, but I suspect that such images are readily available on the Web
and a sufficiently large collection could be used with each presentation
being arbitrary. Perhaps once this is done -- and hidden from page source
view -- it would not need much maintenance.

  The inconvenience to us who access the site is the price we need to pay
given the abuse of the 'Net and the Web.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Does SQLite support user-defined data-types ?

[Jerome CORRENOZ]

Hi,

I'm starting with SQLite and I would like to know if it is possible to 
create user-defined data-types through the following SQL command: create 
type MyType ... ?


In fact, I need it to map a database with an object-oriented model, so 
that I could manage the objects in a ResultSet like it follows:

   Statement stmt = conn.createStatement();
   ResultSet rset = stmt.executeQuery(
   "SELECT * FROM EMP");
   while (rset.next()) {
   Dept dept = (Dept)rset.getObject("DEPT");
   System.out.println("Lieu du dept : "
   + dept.getLieu());
   }

Is there a way to do it with SQLite ?

Regards,
Jerome




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Mark Richards]

[EMAIL PROTECTED] wrote:


Thoughts anyone?  Are there less drastic measures that might
be taken to prevent this kind of abuse?


Headers can always be forged as to browser and OS.  Attackers will 
eventually figure it out and then you're back to the drawing board 
again.  As tempting as this solution is, it probably won't help in the 
longer term.


Cutting off access to a specific IP requires manual maintenance.  There 
are automated solutions as were pointed out, but these can become 
cumbersome to maintain and I have read somewhere (sorry, can't give you 
a reference to it) that piles of iptables rules can cause a slow-down in 
iptables processing.


Returning a link that expires in an email to the requester is a method 
that works nicely.  It does require some programming and maintenance, 
but would be a nice gatekeeper.


I doubt that there would be any privacy concerns.  The email address 
would be used to simply route the url and then be discarded.



/m


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[David Pitcher]

You could adapt this ruleset used to block ssh bruteforce attacks:

( quoting from http://www.la-samhna.de/library/brutessh.html )
iptables -A INPUT -p tcp --dport 22 -m state --state NEW -m recent --set \
--name SSH -j ACCEPT
iptables -A INPUT -p tcp --dport 22 -m recent --update --seconds 
60 --hitcount 4 --rttl \

--name SSH -j LOG --log-prefix "SSH_brute_force "
iptables -A INPUT -p tcp --dport 22 -m recent --update --seconds 60 \
--hitcount 4 --rttl --name SSH -j DROP
For whitelisting, a possible variation (also described by Andrew Pollock) 
would be:


(1) Create a custom chain for whitelisting first:

iptables -N SSH_WHITELIST
(2) Whitelist any host(s) that you like:

iptables -A SSH_WHITELIST -s TRUSTED_HOST_IP -m recent --remove --name 
SSH -j ACCEPT

(3) Add the blocking rules:

iptables -A INPUT -p tcp --dport 22 -m state --state NEW -m recent --set \
--name SSH
iptables -A INPUT -p tcp --dport 22 -m state --state NEW -j SSH_WHITELIST
iptables -A INPUT -p tcp --dport 22 -m state --state NEW -m recent --update 
\
--seconds 60 --hitcount 4 --rttl --name SSH -j ULOG --ulog-prefix 
SSH_brute_force
iptables -A INPUT -p tcp --dport 22 -m state --state NEW -m recent --update 
\

--seconds 60 --hitcount 4 --rttl --name SSH -j DROP
David. 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[C.Peachment]

On Tue, 30 Jan 2007 12:58:01 +, [EMAIL PROTECTED] wrote:

>Last night, a single user (or, at least, a single IP address)
>in China that self-identified as running windows98 and
>Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
>24980 times and  sqlite-source-3_3_12.zip 25044 times
>over about a 5 hour period, sucking up significant
>bandwidth in the process.

>I've seen this type of thing before and have on occasion
>banned specific IP addresses from the website using

>   iptables -A INPUT -s  -j DROP

>But lately, there have been so many problems coming from
>win98 and moz4 that I'm thinking of banning all traffic
>that self-identifies as such in the User-Agent string of
>the HTTP header.

>Thoughts anyone?  Are there less drastic measures that might
>be taken to prevent this kind of abuse?


No human could click fast enough and long enough to request

(24980 + 25044) / (5 * 60) = 166.75 downloads per minute

so it is probably safe to assume that a virus or spambot is
making the requests. As such, the putative agent identifiers
are likely faked and could be changed easily to report
something more modern. This would circumvent your trap
based on User-Agent.

I think you need to place a general limit on requests from ANY
ip address using some form of throttling in the web server.





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Markus Hoenicka]

[EMAIL PROTECTED] was heard to say:

> Thoughts anyone?  Are there less drastic measures that might
> be taken to prevent this kind of abuse?

Frankly, I can't imagine someone sitting in China in front of a Win98 box and
hitting the download button 25044 times. I guess this is scripted, and they
will change the identifier as soon as they notice they're being blocked. I'm
afraid this is not going to work.

What about the spamblockers used by blogs and guestbooks, where you have to read
letters or numbers from a jpeg image before proceeding. I know this is going to
create an accessibility issue, but it seems to work fairly well.

just my 2c,

Markus

-- 
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Terry Jones]

| Last night, a single user (or, at least, a single IP address)
| in China that self-identified as running windows98 and
| Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
| 24980 times and  sqlite-source-3_3_12.zip 25044 times
| over about a 5 hour period, sucking up significant
| bandwidth in the process.
| 
| I've seen this type of thing before and have on occasion
| banned specific IP addresses from the website using
| 
|iptables -A INPUT -s  -j DROP
| 
| But lately, there have been so many problems coming from
| win98 and moz4 that I'm thinking of banning all traffic
| that self-identifies as such in the User-Agent string of
| the HTTP header.

Given 50K attempts in 5 hours, this is either a bug somewhere or it's
automated, likely the latter. In that case, dropping packets based on the
User-Agent isn't going to fix the problem - especially if it's mentioned
here...

It's more work, but something that monitors your log file and selectively
bans IP addresses and/or throttles download speed (keeping the connection
open), might serve you better.

Terry

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Clay Dowling]

[EMAIL PROTECTED] wrote:

> But lately, there have been so many problems coming from
> win98 and moz4 that I'm thinking of banning all traffic
> that self-identifies as such in the User-Agent string of
> the HTTP header.
>
> Thoughts anyone?  Are there less drastic measures that might
> be taken to prevent this kind of abuse?

It seems unlikely that any legitimate client would be visiting with such
characteristics.  With SQLite being primarily of interest to developers,
and win98 being not the most pleasant platform to develop on, I would
think that the number of legitimate requests from these machines would be
vanishingly small.

Clay Dowling
-- 
Simple Content Management
http://www.ceamus.com


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Marco Bambini]

Maybe you can limit the times per hour that a single IP address can  
download something from your web site.

For example max 50 download per file per hour for a single IP address.
This prevents current and future abuses...

---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



On Jan 30, 2007, at 1:58 PM, [EMAIL PROTECTED] wrote:


Last night, a single user (or, at least, a single IP address)
in China that self-identified as running windows98 and
Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
24980 times and  sqlite-source-3_3_12.zip 25044 times
over about a 5 hour period, sucking up significant
bandwidth in the process.

I've seen this type of thing before and have on occasion
banned specific IP addresses from the website using

   iptables -A INPUT -s  -j DROP

But lately, there have been so many problems coming from
win98 and moz4 that I'm thinking of banning all traffic
that self-identifies as such in the User-Agent string of
the HTTP header.

Thoughts anyone?  Are there less drastic measures that might
be taken to prevent this kind of abuse?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Abuse of the SQLite website

[drh]

Last night, a single user (or, at least, a single IP address)
in China that self-identified as running windows98 and
Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
24980 times and  sqlite-source-3_3_12.zip 25044 times
over about a 5 hour period, sucking up significant
bandwidth in the process.

I've seen this type of thing before and have on occasion
banned specific IP addresses from the website using

   iptables -A INPUT -s  -j DROP

But lately, there have been so many problems coming from
win98 and moz4 that I'm thinking of banning all traffic
that self-identifies as such in the User-Agent string of
the HTTP header.

Thoughts anyone?  Are there less drastic measures that might
be taken to prevent this kind of abuse?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-