[sqlite] Check out my photos on Facebook

[Allan Edwards]

Hi Sqlite-users,

I set up a Facebook profile where I can post my pictures, videos and events and 
I want to add you as a friend so you can see it. First, you need to join 
Facebook! Once you join, you can also create your own profile.

Thanks,
Allan

To sign up for Facebook, follow the link below:
http://www.facebook.com/p.php?i=678466945=Z4LX3464S5X1UCEHW1W5YWV


sqlite-users@sqlite.org was invited to join Facebook by Allan Edwards. If you 
do not wish to receive this type of email from Facebook in the future, please 
click on the link below to unsubscribe.
http://www.facebook.com/o.php?k=c8a8cc=1186282837=c9a944G46b53d55G0G8
Facebook's offices are located at 1601 S. California Ave., Palo Alto, CA 94304.

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

Re: [sqlite] Performance issue on records retrieval :: 100, 000 records

[Simon Slavin]

On 16 Jul 2009, at 6:22pm, MADHAVAN VINOD wrote:

> Suppose, if I don't have index, is this the desired behavior of Sqlite
> to take this much time to fetch just 10 records or am I missing
> something here.

Your first post states quite clearly '5) No INDEX created.'.  Without  
any index on your table, how else could the computer do the SELECT  
command ?

I think you don't know what indexes are for, and you haven't thought  
through the task you are giving the computer.  It may be that at this  
stage you should stop programming and read some books on how database  
systems work, to understand how best to use indexes.  If there are no  
useful indexes on your table, the computer has to read every row of  
the table to find out which rows satisfy your SELECT command.  It then  
has to work out which rows satisfy your 'WHERE' clause, and then sort  
the rows it doesn't reject into the right order.  Naturally this takes  
a lot of time.

The whole idea of indexes is to allow your computer to quickly find  
which rows it needs without having to read every single row.  So  
ideally you define an index which lets it select the right records in  
the right order without having to read any data from the table.  Your  
problem is this: you write in your original post

'So my WHERE clause is like  "CurrTime <= ExpireTime AND CurrTime >=
NextProcessingTime AND a=1 AND b < c"'

I am going to assume that all of 'CurrTime', 'ExpireTime',  
'NextProcessingTime', 'a', 'b', 'c' are fields in your table.

Your next problem is that you use, for example, 'b < c' in your SELECT  
command so you are asking for a test to done for every row in the  
table to decide if the row should be considered for the SELECT  
command: each time you do a SELECT command it has to read every row in  
the table and check to see whether 'CurrTime <= ExpireTime', 'CurrTime  
 >= NextProcessingTime', and 'b < c'.  Since you say you have 100,000  
that's 100,000 read instructions and 300,000 pieces of mathematics it  
has to do every time you do your SELECT command.  The idea of indexes  
is to have as much of possible of that done when the record was saved  
in the first place, so it doesn't have to be done every time you use a  
SELECT command.

If 'CurrTime' is a column of your table you could calculate all of  
your WHERE clause when you write each record and write the result into  
another column of the table, then index that column.  If it isn't,  
then you should certainly provide an index on ExpireTime or  
NextProcessingTime or 'a', whichever one allows the SELECT to reject  
the most records most quickly.  And with a good understanding of the  
data in your table you could make up an compound index that makes the  
SELECT work even faster.

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

Re: [sqlite] Protect against SQL injection inside of the database?

[Gerry Snyder]

On Thu, Jul 16, 2009 at 4:20 AM, Michael Schlenker  wrote:

>
>
> This is perfectly safe:
> set result [db1 eval {select * from X where label = $myStringValue and id >
> $compId}]
>
> But you MUST use {} to quote your query and not "", so sqlite gets to do
> the
> substitution (or better said convert things to prepared statements and bind
> values correctly) and not Tcl.


No reason to avoid Tcl. You can also avoid the possibility of Tcl
substitution by using :myStringValue instead of $myStringValue.

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

Re: [sqlite] Performance issue on records retrieval :: 100, 000 records

[Pavel Ivanov]

> Suppose, if I don't have index, is this the desired behavior of Sqlite
> to take this much time to fetch just 10 records or am I missing
> something here.

You're missing that SQLite have to fetch all records satisfying your
condition into memory storage, sort all these records in memory and
then pick 10 first out of them. So in this particular case it could be
expected. And I think index on column b should speed it up.

Pavel

On Thu, Jul 16, 2009 at 1:22 PM, MADHAVAN
VINOD wrote:
>
> Hello Michal,
>
> Thanks for the reply.  Please see my comments inline.
>
>>>if you always have condition a=1 (or something similar which uses =
> for
>>>comparison) you should have index which starts with this field.
>
> The possible values for this field are 1/2.  And mostly all the records
> have the value 1.  Hence I do not see any improvement in the query even
> when I created index on this column (a).
>
>
> Moreover, I tried simplifying the WHERE clause to have just "a=1 AND b <
> c AND d=0" and ORDER BY clause to have just "b ASC".
>
> The execution of this query itself took me around 3 seconds.
>
> Suppose, if I don't have index, is this the desired behavior of Sqlite
> to take this much time to fetch just 10 records or am I missing
> something here.
>
>
> Best Regards,
> Vinod N.M.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michal Seliga
> Sent: Thursday, July 16, 2009 8:37 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Performance issue on records retrieval :: 100,000
> records
>
>
>
> MADHAVAN VINOD wrote:
>>
>> 5) No INDEX created.
>>
>> The retrieval logic is such that to retrieve the oldest 10 records
> along
>> with some additional constraints (say a, b and c are columns and the
>> constraints are like a=1 AND b < c).
>>
>>
>>
>> So my WHERE clause is like  "CurrTime <= ExpireTime AND CurrTime >=
>> NextProcessingTime AND a=1 AND b < c"
>>
>> And my ORDER BY clause is " CurrTime - NextProcessingTime DESC, b ASC"
>>
>>
>>
>>
>
>
> you need index for this, otherwise lookup goes through whole table
> question is what index would help you the most.
>
> now i am not sure if i understood you correctly, are ExpireTime and
> NextProcessingTime database fields? if yes, then in addition you should
> have at
> the end of index columns one of ExpireTime or NextProcessingTime, you
> should
> choose one which can help you more (one which will help database engine
> to limit
> row count the most)
>
> so for situation you wrote i would recommend to have one of indices:
> create index ... on ...(a,ExpireTime)
> or
>
> create index ... on ...(a,NextProcessingTime )
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue on records retrieval :: 100, 000 records

[MADHAVAN VINOD]

Hello Michal,

Thanks for the reply.  Please see my comments inline.

>>if you always have condition a=1 (or something similar which uses =
for
>>comparison) you should have index which starts with this field.

The possible values for this field are 1/2.  And mostly all the records
have the value 1.  Hence I do not see any improvement in the query even
when I created index on this column (a).


Moreover, I tried simplifying the WHERE clause to have just "a=1 AND b <
c AND d=0" and ORDER BY clause to have just "b ASC".

The execution of this query itself took me around 3 seconds.  

Suppose, if I don't have index, is this the desired behavior of Sqlite
to take this much time to fetch just 10 records or am I missing
something here.


Best Regards,
Vinod N.M.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michal Seliga
Sent: Thursday, July 16, 2009 8:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance issue on records retrieval :: 100,000
records



MADHAVAN VINOD wrote:
> 
> 5) No INDEX created.
> 
> The retrieval logic is such that to retrieve the oldest 10 records
along
> with some additional constraints (say a, b and c are columns and the
> constraints are like a=1 AND b < c).  
> 
>  
> 
> So my WHERE clause is like  "CurrTime <= ExpireTime AND CurrTime >=
> NextProcessingTime AND a=1 AND b < c"
> 
> And my ORDER BY clause is " CurrTime - NextProcessingTime DESC, b ASC"
> 
>  
> 
>  


you need index for this, otherwise lookup goes through whole table
question is what index would help you the most.

now i am not sure if i understood you correctly, are ExpireTime and
NextProcessingTime database fields? if yes, then in addition you should
have at
the end of index columns one of ExpireTime or NextProcessingTime, you
should
choose one which can help you more (one which will help database engine
to limit
row count the most)

so for situation you wrote i would recommend to have one of indices:
create index ... on ...(a,ExpireTime)
or

create index ... on ...(a,NextProcessingTime )
___
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] storing and retrieving integers with value 0 (not null)

[Simon Slavin]

On 16 Jul 2009, at 5:31pm, John Machin wrote:

> sqlite> create table foo (x, y, z);
> sqlite> insert into foo (x, y, x) values ('first', 'second', 'third');
> sqlite> insert into foo values ('first', 'second', 'third');
> sqlite> select * from foo;
> first|second|
> first|second|third

Interesting.  I had expected the first row to be

third|second|

I bet the standard doesn't specify which value will be stored.

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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[John Machin]

On 17/07/2009 1:30 AM, Simon Slavin wrote:
> On 16 Jul 2009, at 2:35pm, Uijtdewilligen, Freek wrote:
> 
>> Okay, way too much time after discovering the problem, I found the
>> cause: a simple typo :)
>>
>> In the String where it was storing the column-names, it said (x, y, x,
>> etc..), and somewhere this created the null..
> 
> Congratulations and well spotted.  We've all done it.
> 
> 
> On 16 Jul 2009, at 3:36pm, John Machin wrote:
> 
>> This sounds like a bug somewhere -- having a column name twice  
>> should be
>> met with an error message, not with setting the integer column to  
>> NULL.
> 
> It's doing The Right Thing.  The SQL standard states that things like
> 
> INSERT INTO favouriteColour (person,person) VALUES ('Fred', 'Joan')
> 
> must work.  And if a default value for the other field is not declared  
> it naturally gets NULL.  It's silly, but it's right.
> 

Thanks for that, Simon; that's news to me, and rather mind-boggling:

sqlite> create table foo (x, y, z);
sqlite> insert into foo (x, y, x) values ('first', 'second', 'third');
sqlite> insert into foo values ('first', 'second', 'third');
sqlite> select * from foo;
first|second|
first|second|third
sqlite>

I always though the first variety of INSERT was preferred because it did 
some error checking that was not possible with the second variety of 
INSERT :-(

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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[Simon Slavin]

On 16 Jul 2009, at 2:35pm, Uijtdewilligen, Freek wrote:

> Okay, way too much time after discovering the problem, I found the
> cause: a simple typo :)
>
> In the String where it was storing the column-names, it said (x, y, x,
> etc..), and somewhere this created the null..

Congratulations and well spotted.  We've all done it.


On 16 Jul 2009, at 3:36pm, John Machin wrote:

> This sounds like a bug somewhere -- having a column name twice  
> should be
> met with an error message, not with setting the integer column to  
> NULL.

It's doing The Right Thing.  The SQL standard states that things like

INSERT INTO favouriteColour (person,person) VALUES ('Fred', 'Joan')

must work.  And if a default value for the other field is not declared  
it naturally gets NULL.  It's silly, but it's right.

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

[sqlite] Foreign key support

[Lothar Behrens]

Hi,

I know there is no real support for foreign keys, but I read about the  
genfkey tool in the shell.
This is probably based on the fk tool from Cody Pisto  
 that I am also using.

I added support to use the fk source as library in my code to rewrite  
DDL statements on the fly.
Now I get into trouble because I like to recreate the database tables  
over existing tables.

Doing this usually requires to delete tables and recreate them. The fk  
tool was not implemented to
parse DROP TABLE rules, DELETE and SELECT statements. So when I use  
those statements in my application
it will fail.

My problem:

I want to backport my enhancements, but:

Is there a need for such a backport?

Is it worth to enhance the fk tool to read the DROP, DELETE and SELECT  
statements to fully rewrite
any SQL statements?

I think the DROP statement is simple, but with SELECT and DELETE I see  
some problems with the complexity
of sub queries and my experience with Lex & Jacc.

I mean, I use the on the fly rewrite to avoid administrative tools  
like the Sqlite shell to create databases. Until now
I create DDL scripts from UML models by using XSLT template. So my  
fallback would be rewriting the XSLT template
instead.

Another simple solution would be deleting the database file before  
recreating it, but this will break any plans for an
upgrade functionality.

I thought I could add the fk rewrite code to another library to enable  
foreign key support, but as a library it would
break existing code, if the mentioned SQL statements are not handled  
correctly. (wxDatabaseLayer)

How do you think about adding foreign key support by on the fly  
rewriting SQL statements?
(By adding a compile time flag to activate this)

Thanks

Lothar

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[John Machin]

On 17/07/2009 12:54 AM, Uijtdewilligen, Freek wrote:
>  
>> This sounds like a bug somewhere -- having a column name twice should
>> be
>> met with an error message, not with setting the integer column to
> NULL.
>> So please give us some more information:
>>
>> In the String where it [what is "it"?] was storing the column names
>> [storing for what purpose?] it said (x, y, x, etc) [who said/typed?]
>>
> 
> Sorry, I might have a been a little bit too vague. I use static strings
> containing the table names and columns names, so when I change something
> to the database, I won't have to go through all my code. In one of these
> Strings, I myself made a typing error. When executing an INSERT, one
> column was skipped because of the error in the column names string and
> therefore the default value null was left there. 
> 
> Anyway, it works fine now, so thanks again for the help! :)

Please bear with me -- I'm trying to ascertain where the the second 
problem (bad response to the duplicate-column-name problem) exists:

So the column was skipped (resulting in a NULL being left there by 
default) because (a) SQLite (or whatever layer you were using to execute 
the inserts) skipped the column without giving your code an error 
indication or (b) your code got an error indication but ignored it and 
kept going -- which?

What was the layer that you were using to execute the inserts?

Cheers,

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

Re: [sqlite] Performance issue on records retrieval :: 100, 000 records

[Michal Seliga]

MADHAVAN VINOD wrote:
> 
> 5) No INDEX created.
> 
> The retrieval logic is such that to retrieve the oldest 10 records along
> with some additional constraints (say a, b and c are columns and the
> constraints are like a=1 AND b < c).  
> 
>  
> 
> So my WHERE clause is like  "CurrTime <= ExpireTime AND CurrTime >=
> NextProcessingTime AND a=1 AND b < c"
> 
> And my ORDER BY clause is " CurrTime - NextProcessingTime DESC, b ASC"
> 
>  
> 
>  


you need index for this, otherwise lookup goes through whole table
question is what index would help you the most.

if you always have condition a=1 (or something similar which uses = for
comparison) you should have index which starts with this field.

now i am not sure if i understood you correctly, are ExpireTime and
NextProcessingTime database fields? if yes, then in addition you should have at
the end of index columns one of ExpireTime or NextProcessingTime, you should
choose one which can help you more (one which will help database engine to limit
row count the most)

so for situation you wrote i would recommend to have one of indices:
create index ... on ...(a,ExpireTime)
or

create index ... on ...(a,NextProcessingTime )
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[Uijtdewilligen, Freek]

 
> This sounds like a bug somewhere -- having a column name twice should
> be
> met with an error message, not with setting the integer column to
NULL.
> 
> So please give us some more information:
> 
> In the String where it [what is "it"?] was storing the column names
> [storing for what purpose?] it said (x, y, x, etc) [who said/typed?]
> 

Sorry, I might have a been a little bit too vague. I use static strings
containing the table names and columns names, so when I change something
to the database, I won't have to go through all my code. In one of these
Strings, I myself made a typing error. When executing an INSERT, one
column was skipped because of the error in the column names string and
therefore the default value null was left there. 

Anyway, it works fine now, so thanks again for the help! :)

Greets, Freek



Please help Logica to respect the environment by not printing this email  / 
Pour contribuer comme Logica au respect de l'environnement, merci de ne pas 
imprimer ce mail /  Bitte drucken Sie diese Nachricht nicht aus und helfen Sie 
so Logica dabei, die Umwelt zu schützen /  Por favor ajude a Logica a respeitar 
o ambiente nao imprimindo este correio electronico.



This e-mail and any attachment is for authorised use by the intended 
recipient(s) only. It may contain proprietary material, confidential 
information and/or be subject to legal privilege. It should not be copied, 
disclosed to, retained or used by, any other party. If you are not an intended 
recipient then please promptly delete this e-mail and any attachment and all 
copies and inform the sender. Thank you.


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

[sqlite] Performance issue on records retrieval :: 100, 000 records

[MADHAVAN VINOD]

Hi All,

 

Description of my setup:

My database contains 

1) One table 

2) 20 fields (contains date field to store the inserted time)

3) 100,000 records

4) database size is 21MB.  

5) No INDEX created.

6) Sqlite version 3.5.9.

 

The retrieval logic is such that to retrieve the oldest 10 records along
with some additional constraints (say a, b and c are columns and the
constraints are like a=1 AND b < c).  

 

So my WHERE clause is like  "CurrTime <= ExpireTime AND CurrTime >=
NextProcessingTime AND a=1 AND b < c"

And my ORDER BY clause is " CurrTime - NextProcessingTime DESC, b ASC"

LIMIT 10.

 

The problem is that it is taking around 6-7 seconds to retrieve 10
records from the table containing 100,000 records.  

 

One more problem is that the UPDATE query is getting failed with error
[Databse is locked] during this retrieval.  Of course, I guess any other
query also would have failed during this period. 

 

So, please let me know, is there any tuning that can help my retrieval
to get better. 

 

I was expecting that Sqlite shall give better performance for at least
10 million records.  If I am correct, please help me out to achieve the
same or if not please let me know how best we can achieve using Sqlite.

 

Thanks in advance for your help.

 

Thanks and Regards,

Vinod N.M.

 

 

 

 

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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[John Machin]

On 16/07/2009 11:35 PM, Uijtdewilligen, Freek wrote:
> Okay, way too much time after discovering the problem, I found the
> cause: a simple typo :)
> 
> In the String where it was storing the column-names, it said (x, y, x,
> etc..), and somewhere this created the null..

This sounds like a bug somewhere -- having a column name twice should be 
met with an error message, not with setting the integer column to NULL.

So please give us some more information:

In the String where it [what is "it"?] was storing the column names 
[storing for what purpose?] it said (x, y, x, etc) [who said/typed?]

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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[Uijtdewilligen, Freek]

Okay, way too much time after discovering the problem, I found the
cause: a simple typo :)

In the String where it was storing the column-names, it said (x, y, x,
etc..), and somewhere this created the null..

Well, at least I've refreshed my SQL-knowledge, so thank you very much!

Greets, Freek  

Please help Logica to respect the environment by not printing this email  / 
Pour contribuer comme Logica au respect de l'environnement, merci de ne pas 
imprimer ce mail /  Bitte drucken Sie diese Nachricht nicht aus und helfen Sie 
so Logica dabei, die Umwelt zu schützen /  Por favor ajude a Logica a respeitar 
o ambiente nao imprimindo este correio electronico.



This e-mail and any attachment is for authorised use by the intended 
recipient(s) only. It may contain proprietary material, confidential 
information and/or be subject to legal privilege. It should not be copied, 
disclosed to, retained or used by, any other party. If you are not an intended 
recipient then please promptly delete this e-mail and any attachment and all 
copies and inform the sender. Thank you.


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

Re: [sqlite] Protect against SQL injection inside of the database?

[Adam DeVita]

http://unixwiz.net/techtips/sql-injection.html  is a nice introduction to
sql injection attacks.  (Learning by example) It also explains why binding
is far superior to trying to invent a set of rules and cleaning the input.

.

On Thu, Jul 16, 2009 at 9:01 AM, Michael Schlenker  wrote:

> Fredrik Karlsson schrieb:
> > On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenker
> wrote:
> >> Your working far too hard. The sqlite Tcl binding already does all thats
> needed.
> >>
> >> This is perfectly safe:
> >> set result [db1 eval {select * from X where label = $myStringValue and
> id >
> >> $compId}]
> >>
> >> But you MUST use {} to quote your query and not "", so sqlite gets to do
> the
> >> substitution (or better said convert things to prepared statements and
> bind
> >> values correctly) and not Tcl.
> >>
> >> Michael
> >
> > Hi Michael,
> >
> > Ok, I can see how this would be the easiest solution, but what I am
> > doing is basically a query builder (maping of comands in a specialized
> > language to pattern subselects in SQL queries). Since the statements
> > can be nested in many different ways, I cannot expect to be able to
> > construct the query and keeping track of variable names to be used in
> > the final substitution, so that I can make use of the built in binding
> > feature of sqlite It is much to much hard work.
> >
>
> I don't think so.
>
> Just use an array to store your values and prefix the names with the
> identifier of your subpattern. Now when you emit your subpattern via
> [format] or some other method just add the appropriate prefixed bind
> variables. Should not be too hard.
>
> > Instead, I think I need to make each part of the query return a
> > complete (not to be evaluated further outside of sqlite) SQL query
> > subselect statement, which is why I think I need to make sure that the
> > values I insert is safe inside an SQL statement myself.
> > Or, do you know of a Tcl command to make strings "SQL safe"? (Sorry
> > for making this into a Tcl question now..)
>
> Its the wrong way. See the mess you get with mysql_real_escape() in PHP and
> you know its wrong.
>
> Michael
>
> --
> Michael Schlenker
> Software Engineer
>
> CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
> Wiener Straße 1-3   Fax:+49 (421) 20153-41
> 28359 Bremen
> http://www.contact.de/  E-Mail: m...@contact.de
>
> Sitz der Gesellschaft: Bremen
> Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
> Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[Uijtdewilligen, Freek]

Hey Simon and John, 

First of all, thanks for the advice!

The table has been created using:

CREATE TABLE IF NOT EXISTS t_rp (x SMALLINT, y SMALLINT, z SMALLINT, ap
SMALLINT, m_id INTEGER PRIMARY KEY)

Adding NOT NULL got me an exception when trying to add a 0-coordinate. I
also can't use the command-line tool, since the database is integrated
into the Android OS.

> 
> What evidence do you have to support your assertion that it is stored
> as
>   NULL?

I can query the entire table and print it. Non-zero values are stored
and read perfectly, but for every stored 0, I get back null. I would say
the problem lies in the storing of the 0, turning it into a null.

> 
> As Simon has pointed out, 0 != '0'. If after considering that, you
feel
> you still have a problem, try selecting the offending rows using some
> criteria that are NOT dependant on column z e.g.
> 
> select rowid, z, quoted(z), typeof(z) from t_rp where row is
offending;
> 
> BTW, if your assertion is true, this query should give some output:
> 
> select rowid, z, quoted(z), typeof(z) from t_rp where z is null;

I found the IS NULL somewhere, and that at least enabled me to work
around it, using the some very ugly if-else-statements:

String db_x;
if(x==0) db_x = "(x IS NULL)";
etc.

"SELECT z FROM t_rp WHERE z==0;"
"SELECT z FROM t_rp WHERE z=='0';"
"SELECT z FROM t_rp WHERE z=0;"
"SELECT z FROM t_rp WHERE z='0';"
.. all return 0 rows

"SELECT z FROM t_rp WHERE z IS NULL;"
"SELECT typeof(z) FROM t_rp WHERE z IS NULL;"
.. return the correct number or rows

Greets, Freek


Please help Logica to respect the environment by not printing this email  / 
Pour contribuer comme Logica au respect de l'environnement, merci de ne pas 
imprimer ce mail /  Bitte drucken Sie diese Nachricht nicht aus und helfen Sie 
so Logica dabei, die Umwelt zu schützen /  Por favor ajude a Logica a respeitar 
o ambiente nao imprimindo este correio electronico.



This e-mail and any attachment is for authorised use by the intended 
recipient(s) only. It may contain proprietary material, confidential 
information and/or be subject to legal privilege. It should not be copied, 
disclosed to, retained or used by, any other party. If you are not an intended 
recipient then please promptly delete this e-mail and any attachment and all 
copies and inform the sender. Thank you.


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

Re: [sqlite] Protect against SQL injection inside of the database?

[Michael Schlenker]

Fredrik Karlsson schrieb:
> On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenker wrote:
>> Your working far too hard. The sqlite Tcl binding already does all thats 
>> needed.
>>
>> This is perfectly safe:
>> set result [db1 eval {select * from X where label = $myStringValue and id >
>> $compId}]
>>
>> But you MUST use {} to quote your query and not "", so sqlite gets to do the
>> substitution (or better said convert things to prepared statements and bind
>> values correctly) and not Tcl.
>>
>> Michael
> 
> Hi Michael,
> 
> Ok, I can see how this would be the easiest solution, but what I am
> doing is basically a query builder (maping of comands in a specialized
> language to pattern subselects in SQL queries). Since the statements
> can be nested in many different ways, I cannot expect to be able to
> construct the query and keeping track of variable names to be used in
> the final substitution, so that I can make use of the built in binding
> feature of sqlite It is much to much hard work.
> 

I don't think so.

Just use an array to store your values and prefix the names with the
identifier of your subpattern. Now when you emit your subpattern via
[format] or some other method just add the appropriate prefixed bind
variables. Should not be too hard.

> Instead, I think I need to make each part of the query return a
> complete (not to be evaluated further outside of sqlite) SQL query
> subselect statement, which is why I think I need to make sure that the
> values I insert is safe inside an SQL statement myself.
> Or, do you know of a Tcl command to make strings "SQL safe"? (Sorry
> for making this into a Tcl question now..)

Its the wrong way. See the mess you get with mysql_real_escape() in PHP and
you know its wrong.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Protect against SQL injection inside of the database?

[Fredrik Karlsson]

On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenker wrote:
> Your working far too hard. The sqlite Tcl binding already does all thats 
> needed.
>
> This is perfectly safe:
> set result [db1 eval {select * from X where label = $myStringValue and id >
> $compId}]
>
> But you MUST use {} to quote your query and not "", so sqlite gets to do the
> substitution (or better said convert things to prepared statements and bind
> values correctly) and not Tcl.
>
> Michael

Hi Michael,

Ok, I can see how this would be the easiest solution, but what I am
doing is basically a query builder (maping of comands in a specialized
language to pattern subselects in SQL queries). Since the statements
can be nested in many different ways, I cannot expect to be able to
construct the query and keeping track of variable names to be used in
the final substitution, so that I can make use of the built in binding
feature of sqlite It is much to much hard work.

Instead, I think I need to make each part of the query return a
complete (not to be evaluated further outside of sqlite) SQL query
subselect statement, which is why I think I need to make sure that the
values I insert is safe inside an SQL statement myself.
Or, do you know of a Tcl command to make strings "SQL safe"? (Sorry
for making this into a Tcl question now..)

/Fredrik


-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[John Machin]

On 16/07/2009 7:24 PM, Uijtdewilligen, Freek wrote:


> INSERT INTO t_rp (x, y, z)

> VALUES (1, 1, 0);
> it gets stored as (1,1,null).

What evidence do you have to support your assertion that it is stored as 
  NULL?

As Simon has pointed out, 0 != '0'. If after considering that, you feel 
you still have a problem, try selecting the offending rows using some 
criteria that are NOT dependant on column z e.g.

select rowid, z, quoted(z), typeof(z) from t_rp where row is offending;

BTW, if your assertion is true, this query should give some output:

select rowid, z, quoted(z), typeof(z) from t_rp where z is null;

HTH,
John

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

Re: [sqlite] Protect against SQL injection inside of the database?

[Michael Schlenker]

Fredrik Karlsson schrieb:
> Dear list,
> 
> Sorry for jumping onto the list mainly to ask a question, but it is an
> imporant one, and I have failed to find the answer on Google.
> I am developing a prototype of an application in Tcl using sqlite as
> the  backend database. Now, I know that I will be dealing with quite
> naïve users, who will not think that "!' and simialar characters are
> evil and potentially dangerous in a SQL database context. So, now I
> need to make sure that I am taking all the precautions I can to
> protect the database from evil / naïve users, and since parts of the
> application may be ported to C for speed later, I would prefer as much
> of it to happen in the SQL queries themselves, in order to make sure
> that the behaviour stays constant when porting.
> 
> My currrent strategy is to use a combination of quote() and trim() (as
> blank space at the ends of a string is not important in my
> application). So, for each string value I get from the user, I do
> something similar to
> 
> set out [format {select * from X where label == quote(trim("%s")) and
> id > %d } $myStringValue $compId ]
> 
> (Please ignore the Tcl part if you are not familiar with it.. format
> is basically (almost) sprintf in a new name )

Your working far too hard. The sqlite Tcl binding already does all thats needed.

This is perfectly safe:
set result [db1 eval {select * from X where label = $myStringValue and id >
$compId}]

But you MUST use {} to quote your query and not "", so sqlite gets to do the
substitution (or better said convert things to prepared statements and bind
values correctly) and not Tcl.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Protect against SQL injection inside of the database?

[Pavel Ivanov]

Does Tcl supports binding of parameters to prepared statement?
If yes then do just that and you will not need to do any "quotes" and
think of any "ifs".

Pavel

On Thu, Jul 16, 2009 at 3:49 AM, Fredrik Karlsson wrote:
> Dear list,
>
> Sorry for jumping onto the list mainly to ask a question, but it is an
> imporant one, and I have failed to find the answer on Google.
> I am developing a prototype of an application in Tcl using sqlite as
> the  backend database. Now, I know that I will be dealing with quite
> naïve users, who will not think that "!' and simialar characters are
> evil and potentially dangerous in a SQL database context. So, now I
> need to make sure that I am taking all the precautions I can to
> protect the database from evil / naïve users, and since parts of the
> application may be ported to C for speed later, I would prefer as much
> of it to happen in the SQL queries themselves, in order to make sure
> that the behaviour stays constant when porting.
>
> My currrent strategy is to use a combination of quote() and trim() (as
> blank space at the ends of a string is not important in my
> application). So, for each string value I get from the user, I do
> something similar to
>
> set out [format {select * from X where label == quote(trim("%s")) and
> id > %d } $myStringValue $compId ]
>
> (Please ignore the Tcl part if you are not familiar with it.. format
> is basically (almost) sprintf in a new name )
>
> So, my questions are now:
>
> 1) Can I feel safe that the string value is now "safe" (to some
> degree) regarding SQL injection?
> 2) Have I done something that will prevent me from matching values I
> really want to match by altering the original string value?
> 3) Is the integer value reasonably secure, or shouls something be done
> for that too (and then, what?)
>
> Sorry for these questions, but I would rather dot all the i:s before
> moving on in the application development. I have seen before how
> creative naïve users can be when it comes to making applications crash
> due to unforseen actions. :-)
>
> Of course, any input in this would be greatly appreciated.
>
> /Fredrik
>
> --
> "Life is like a trumpet - if you don't put anything into it, you don't
> get anything out of it."
> ___
> 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] storing and retrieving integers with value 0 (not null)

[Simon Slavin]

On 16 Jul 2009, at 10:24am, Uijtdewilligen, Freek wrote:

> I have used SMALLINT, but the same problem occurred when I
> changed it to INTEGER.

INTEGER should be correct.  Can we see the command you use to make the  
TABLE t_rp, please ?

> When I'm storing a location (x,y,z) into the database that contains  
> a 0,
> like:
>
> INSERT INTO t_rp (x, y, z)
> VALUES (1, 1, 0);
>
> it gets stored as (1,1,null).

Something is wrong at that stage, as you write.  If you type the same  
command into the sqlite3 command-line tool, do you get the same  
results ?  If you are getting any results different to what



predicts, something is wrong.

> Then, if I try to query something like
>
> SELECT z FROM t_rp WHERE z=='0';
>
> No rows are returned.

Try

SELECT z FROM t_rp WHERE z==0;

instead.  Putting quotes around the value makes it a string.

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

[sqlite] storing and retrieving integers with value 0 (not null)

[Uijtdewilligen, Freek]

Hello all,

 

I'm currently using the SQLite database in the Android OS, and running
into a problem when I'm trying to store and retrieve location
coordinates. I have used SMALLINT, but the same problem occurred when I
changed it to INTEGER.

 

When I'm storing a location (x,y,z) into the database that contains a 0,
like:

 

INSERT INTO t_rp (x, y, z)

VALUES (1, 1, 0);

 

it gets stored as (1,1,null).  Then, if I try to query something like 

 

SELECT z FROM t_rp WHERE z=='0';

 

No rows are returned. How can I make sure that the values are stored as
0, not null. I've tried setting the column to NOT NULL at creation, but
this gives me an error when I try to insert the 0.

 

 

Hopefully I've succeeded  somewhat in explaining my problem, thank you
very much for your help!

 

With kinds regards,

 

Freek Uijtdewilligen

 

 

 



Please help Logica to respect the environment by not printing this email  / 
Pour contribuer comme Logica au respect de l'environnement, merci de ne pas 
imprimer ce mail /  Bitte drucken Sie diese Nachricht nicht aus und helfen Sie 
so Logica dabei, die Umwelt zu schützen /  Por favor ajude a Logica a respeitar 
o ambiente nao imprimindo este correio electronico.



This e-mail and any attachment is for authorised use by the intended 
recipient(s) only. It may contain proprietary material, confidential 
information and/or be subject to legal privilege. It should not be copied, 
disclosed to, retained or used by, any other party. If you are not an intended 
recipient then please promptly delete this e-mail and any attachment and all 
copies and inform the sender. Thank you.

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

Re: [sqlite] Announce of the new "Versioning" extension

[Neville Franks]

Hi Alexey,
Thank you for the license change and readme.

Wednesday, July 15, 2009, 7:16:44 PM, you wrote:

AP> Hello!

AP> On Wednesday 15 July 2009 09:56:28 Neville Franks wrote:
>> Hi Alexey,
>> Thank you for this extension which could be quite interesting to many
>> SQLite users. Is there any documentation on this available, possibly
>> in your new book? I couldn't find any with the source at
>> http://mobigroup.ru/files/sqlite-ext/ 

AP> I did add README file.
AP>  
>> Also you have used the GNU License which means we cannot use this in
>> any commercial applications. It also goes against the Public Domain
>> license used by SQLite itself - see
>> http://www.sqlite.org/copyright.html It would be great if this could
>> be changed.

AP> I did change license to Public Domain same as SQLite core and my other 
extensions.

AP> P.S. Added indexes in new "Versioning" version.

AP>  README =
AP> "Versioning" SQLite extension

AP> Copyright 2009 Alexey Pechnikov 
AP> The code is public domain.


AP> The extension may be used for table versioning and replication.

AP> Functions:
AP> versioning_table(SOURCE) - add versioning support for SOURCE table by
AP> 1. drop if exists previously created _versioning_SOURCE table
AP> 2. add _versioning_SOURCE table to store versions
AP> 4. copy current state of SOURCE table
AP> 3. create triggers on SOURCE table

AP> unversioning_table(SOURCE) - remove versioning support for
AP> SOURCE table. Doesn't drop _versioning_SOURCE table but only remove SOURCE 
triggers!

AP> The _versioning_SOURCE table consists all fields of SOURCE table
AP> without any checks or constraints and some additional fields
AP> _date REAL, _action TEXT, _rowid INTEGER
AP> to store date of perform action on SOURCE row, action name ('I' -
AP> insert, 'U' - update, 'D' - delete) and original record rowid.

AP> 
AP> Add versioning example:
AP> CREATE TABLE key (name text not null);
AP> select versioning_table('key');
AP> .schema
AP> CREATE TABLE _undo(sql TEXT, status TEXT);
AP> CREATE TABLE _versioning_key(name text, _date REAL, _action TEXT, _rowid 
INTEGER);
AP> CREATE TABLE key (name text not null);
AP> CREATE INDEX versioning_key_date_idx on _versioning_key(_date);
AP> CREATE INDEX versioning_key_rowid_date_idx on _versioning_key(_rowid,_date);
AP> CREATE TRIGGER _versioning_key_d AFTER DELETE ON key BEGIN INSERT
AP> INTO _versioning_key (_date, _action, _rowid) values
AP> (julianday('now'), 'D', old.rowid);END;
AP> CREATE TRIGGER _versioning_key_i AFTER INSERT ON key BEGIN INSERT
AP> INTO _versioning_key SELECT *, julianday('now') as _date, 'I' as
AP> _action, new.rowid as _rowid FROM key WHERE rowid=new.rowid;END;
AP> CREATE TRIGGER _versioning_key_u AFTER UPDATE ON key BEGIN INSERT
AP> INTO _versioning_key SELECT *, julianday('now') as _date, 'U' as
AP> _action, new.rowid as _rowid FROM key WHERE rowid=new.rowid;END;

AP> 
AP> Versioning example:
AP> insert into key (name) values ('test key 1');
AP> insert into key (name) values ('test key 1');
AP> delete from key;
AP> .header on
AP> select * from _versioning_key;
AP> name|_date|_action|_rowid
AP> test key 1|2455027.87582762|I|1
AP> test key 1|2455027.87582772|I|2
AP> |2455027.87709961|D|1
AP> |2455027.87709961|D|2

AP> 
AP> Now you can select versions of SOURCE row by rowid:
AP> .header on
AP> select * from _versioning_key where _rowid=1;
AP> name|_date|_action|_rowid
AP> test key 1|2455027.87582762|I|1
AP> |2455027.87709961|D|1

AP> 
AP> For replication can be selected versions of all records by
AP> current time which were changed after previous syncronization (1 hour ago, 
as example):
AP> .header on
AP> select * from _versioning_key where _date>julianday('now','-1 hour');
AP> name|_date|_action|_rowid
AP> test key 1|2455027.87582762|I|1
AP> test key 1|2455027.87582772|I|2
AP> |2455027.87709961|D|1
AP> |2455027.87709961|D|2

AP> These records may be synced by sql dump or by other ways.

AP> Best regards, Alexey Pechnikov.
AP> http://pechnikov.tel/



-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

Re: [sqlite] Compile warnings

[MikeW]

D. Richard Hipp  writes:
...snip
> 
> Many of the warnings you mention could be suppressed by initializing
> some variables.  But those initializations are technically unnecessary
> and thus slow things down.  (Most will probably make no measureable
> difference in speed, but an unnecessary initialization in an
> inner loop can make a big difference.)  So I am disinclined to
> fix them.
> 
> Note that it is exceedingly unlikely that any of the warnings
> mentioned could be a real bug, since if that were the case the
> warning would appear with the -O0 option in addition to the -O2
> option.  Since the warnings only appear with -O2, they are
> probably just optimizer artifacts.
> 

Actually, according to the GCC documentation, GCC only detects
use of uninitialised variables if the optimiser is turned on.

Hence using -O0 (=opt off) does not throw up those warnings
since the usage is not checked.

Regards,
MikeW

PS. yes, I am looking at cleaning up our SQLite build output !




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

[sqlite] Protect against SQL injection inside of the database?

[Fredrik Karlsson]

Dear list,

Sorry for jumping onto the list mainly to ask a question, but it is an
imporant one, and I have failed to find the answer on Google.
I am developing a prototype of an application in Tcl using sqlite as
the  backend database. Now, I know that I will be dealing with quite
naïve users, who will not think that "!' and simialar characters are
evil and potentially dangerous in a SQL database context. So, now I
need to make sure that I am taking all the precautions I can to
protect the database from evil / naïve users, and since parts of the
application may be ported to C for speed later, I would prefer as much
of it to happen in the SQL queries themselves, in order to make sure
that the behaviour stays constant when porting.

My currrent strategy is to use a combination of quote() and trim() (as
blank space at the ends of a string is not important in my
application). So, for each string value I get from the user, I do
something similar to

set out [format {select * from X where label == quote(trim("%s")) and
id > %d } $myStringValue $compId ]

(Please ignore the Tcl part if you are not familiar with it.. format
is basically (almost) sprintf in a new name )

So, my questions are now:

1) Can I feel safe that the string value is now "safe" (to some
degree) regarding SQL injection?
2) Have I done something that will prevent me from matching values I
really want to match by altering the original string value?
3) Is the integer value reasonably secure, or shouls something be done
for that too (and then, what?)

Sorry for these questions, but I would rather dot all the i:s before
moving on in the application development. I have seen before how
creative naïve users can be when it comes to making applications crash
due to unforseen actions. :-)

Of course, any input in this would be greatly appreciated.

/Fredrik

-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do people think of SQLite as a file or as a database

[Yan Bertrand]

Same here. For me the fact that it manages quick access to data through indexes 
- and uses SQL query language - makes it a database of its own right and merits.

-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
De la part de Michal Seliga
Envoyé : jeudi 16 juillet 2009 09:18
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Do people think of SQLite as a file or as a database

for me sqlite is database engine. and its files i call databases

CadMapper wrote:
> This is not a technical question about SQLite.  I want to you how people in
> general think about SQLite.  Is that a file or a database?  When you talk
> about it, do you refer to it as file or database?
> 
> Thanks for your input in advance!
___
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] Do people think of SQLite as a file or as a database

[Michal Seliga]

for me sqlite is database engine. and its files i call databases

CadMapper wrote:
> This is not a technical question about SQLite.  I want to you how people in
> general think about SQLite.  Is that a file or a database?  When you talk
> about it, do you refer to it as file or database?
> 
> Thanks for your input in advance!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users