[sqlite] about compile configure

[Richard Hipp]

On 12/22/15, ??? <2004wqg2008 at 163.com> wrote:
>What do you mean of "render the mmap_size mote "?

The "PRAGMA mmap_size" command sets the operating-system interface
into a mode so that at calls mmap() to read content from disk rather
than calling read().  It is often faster to use mmap() because that
avoids having to copy content from kernel space into user space.

However, mmap() mode only works if the database is reading exactly the
same content as is present on disk.  In the case of ZIPVFS, the
content on disk is compressed and encryption.  The content on disk is
different from the content used by the database engine.  The content
has to be converted when moving from disk into the application and
when moving from the application to the disk.  Because the content in
the application is different from the content on disk, mmap() won't
work.  And so when using ZIPVFS, the "PRAGMA mmap_size=NNN" command is
a no-op.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] about compile configure

[Richard Hipp]

On 12/22/15, ??? <2004wqg2008 at 163.com> wrote:
>>> >pragma mmap_size= 51200;
>>> >Pragma page_size = 8192;
>>> >Vacuum;
>>> >Pragma cache_size = N;
>  The above code has nothing to do with the speed of retrieving records,
> especially the beginning retrieve records from database.
>  Is this the right?
>

Setting the mmap_size will normally help read performance a lot.
However, if you are reading from a ZIPVFS database, the extra layer of
decryption and decompression that sits in between the disk and your
application will render the mmap_size mote - it won't matter.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] about compile configure

[Keith Medcalf]

How long does it take to retrieve one record from the database?
How long do you want it to take?

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of ???
> Sent: Tuesday, 22 December, 2015 19:49
> To: SQLite mailing list
> Subject: Re: [sqlite] about compile configure
> 
> >pragma mmap_size= 51200;
> >Pragma page_size = 8192;
> >Vacuum;
> >Pragma cache_size = N;
> 
>   The testing result shows that the above pragma statements could not
> improve the speed of retrieving records from data base.
> 
>   Best regards
>   wqg
> 
> At 2015-12-21 18:29:37, "Quan Yong Zhai"  wrote:
> >pragma mmap_size= 51200;
> >Pragma page_size = 8192;
> >Vacuum;
> >Pragma cache_size = N;
> >
> >???: ???
> >: ?2015/?12/?21 18:08
> >???: SQLite mailing list
> >??: Re: [sqlite] about compile configure
> >
> >The SQL statement is so easy.
> >the table create statement as following:
> >CREATE TABLE poiTable (poiId INTEGER NOT NULL, versionId INTEGER NOT
> NULL, regionId INTEGER , postalCode TEXT , phone TEXT , attrBitMask
> INTEGER , attributeBlob BLOB , primary key (poiId));
> >So the poiId is equal to the rowid.
> >
> >such as : select * from poiTable where poiId = ... ;
> >And execute the sql by sqlite3_prepare_v2?sqlite3_step?
> >how could I improve the performance?
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >At 2015-12-21 17:15:56, "???" <2004wqg2008 at 163.com> wrote:
> >>What Simon said is very helpful for me. Thank you very much.
> >>I only want to improve the speed of reading data from data base. Do not
> do insert?update and so on.
> >>
> >>I will try the following suggustion.
> >>PRAGMA synchronous = OFF
> >>
> >>Best regards
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>At 2015-12-21 17:03:13, "Simon Slavin"  wrote:
> >>>
> >>>On 21 Dec 2015, at 6:19am, ??? <2004wqg2008 at 163.com> wrote:
> >>>
>   The meaning of "how to use sqlite_table" is that I guess the
> sqlite_table may have contained some information which could help to
> improve speed.
>    I  am not meaning to  modify the data structure of  sqlite_master.
> >>>
> >>>There is nothing you can do with sqlite_table to improve speed.  Unless
> you have a very unusual setup there is nothing you can do with compilation
> options to improve speed.
> >>>
> >>>Since you say you are not using multi-threading or multi-process, you
> might like to read the documentation for
> >>>
> >>>PRAGMA synchronous = OFF
> >>>
> >>>This might increase speed for you.  However it also means that if your
> computer loses power or crashes while the database is open, you will lose
> more new data.
> >>>
> >>>
> >>>
> >>>However a big increase in speed comes from correct use of indexes.  If
> you have any SQL commands which include WHERE or ORDER BY, then these will
> execute faster if you have an ideal index on the table they use.  This can
> affect INSERT and UPDATE and DELETE FROM commands.  If you want help with
> this you must post your SQL commands here.
> >>>
> >>>Another big increase in speed can come from correctly using
> transactions.  When you are making changes to your database it is
> finishing the transaction with END or COMMIT which takes most of the time.
> So if you have many INSERT commands then
> >>>
> >>>INSERT ...
> >>>INSERT ...
> >>>INSERT ...
> >>>
> >>>is slow but
> >>>
> >>>BEGIN
> >>>INSERT ...
> >>>INSERT ...
> >>>INSERT ...
> >>>COMMIT
> >>>
> >>>can be much faster.  This can affect INSERT and UPDATE and DELETE
> commands.
> >>>
> >>>Simon.
> >>>___
> >>>sqlite-users mailing list
> >>>sqlite-users at mailinglists.sqlite.org
> >>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>___
> >>sqlite-users mailing list
> >>sqlite-users at mailinglists.sqlite.org
> >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >___
> >sqlite-users mailing list
> >sqlite-users at mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >___
> >sqlite-users mailing list
> >sqlite-users at mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Vulnerabilities reported in NVD

[Simon Slavin]

On 22 Dec 2015, at 1:16pm, Matthias-Christian Ott  wrote:

> Some software uses the affected versions and it's a good idea they know
> that the software is affected. It's a matter of transparency.

But it doesn't say when the vulns are fixed so it's not useful to people 
looking to fix their vulns.  The Apple version of that report says

Description:  Multiple vulnerabilities existed in SQLite v3.8.5.
These issues were addressed by updating SQLite to version 3.8.10.2.

which is more helpful.

Simon.

[sqlite] SQLite Vulnerabilities reported in NVD

[Matthias-Christian Ott]

On 2015-12-22 13:48, Richard Hipp wrote:
> I do not know where those vulnerability reports originated.  They did
> not originate from me.  For that matter, I was never consulted about
> them.  None of them represent real vulnerabilities, in my assessment.
> All of the problems identified have been fixed for a long time.

Perhaps it was part of a full disclosure consideration.

> I think that these reports achieve nothing beyond vulnerability
> fatigue.  I think it is shameful that nvd.nist.gov publishes them.

Some software uses the affected versions and it's a good idea they know
that the software is affected. It's a matter of transparency.

[sqlite] whish list for 2016

[lchis...@paradise.net.nz]

Joining the throng, here are my requests:
a) Either an ORDER BY clause/equivalent for GROUP BY, or an assurance that the
kludge of sorting a sub-query first and then grouping the result does and will
continue to work - I need this functionality.
b) A separate mailing address for documentation corrections - I see so many
typos but it doesn't seem worth spamming the whole list to request an apostrophe
be added or two letters be exchanged.
c) A vote for RANK, but I'm doing that in my application at present, post-SQL
but before printing. I know you can generate 1,2,3=,3=,5 type sequences from
self-joins but it seems a lot easier to do it in Delphi!

BTW I am having a great deal of enjoyment with SQLite in my application, which
is a scoring database for card tournaments in New Zealand. I know it's small
compared to some of the monsters I've seen described here, but I have 38 tables
and 54 views in my schema, with 400-ish players and over 2800 score records in
the national euchre tournament DB.

Len Chisholm.

[sqlite] SQLite Vulnerabilities reported in NVD

[Saurav Sarkar]

Thanks Clemens for your inputs

We are using parametrized queries and don't have any user interface where
user can modify anything currently. Its almost a read only application.
Our is a file management application

But we will come up with some functionality where user will be able to
upload random files into our application.
The files will reside into the application sandbox environment.
Once a file is being uploaded, the metadata of that file will get
inserted/updated into DB.

we will also come with rename  (a text box ) and other CRUD operations
which will require user interaction. This will again trigger the metadata
update into DB.
But the queries will be always parametrized ones.

Regards,
Saurav

On Tue, Dec 22, 2015 at 12:12 PM, Clemens Ladisch 
wrote:

> Saurav Sarkar:
> >Our application is free of any kind of SQL injection
>
> Famous last words.  :)
>
> >as we don't have any input fields.
>
> So where does your data come from?
> Does your application have any interface that an attacker
> could access?
>
> How do you create your SQL statements?
> Are you always using bound parameters?
>
> >https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3414
>
> This requires the attacker to control a collation name.
>
> >https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3415
>
> This requires the attacker to control the CHECK clause in a CREATE
> TABLE statement.
>
> >https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3416
>
> This requires the attacker to control the format string of the print()
> SQL function.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite Vulnerabilities reported in NVD

[Saurav Sarkar]

Hi All,

We use SQlite 3.8.8.3 in our Windows 8.1 universal application.
We are also using SQLitePCL as a wrapper to work from c# layer.

Our application is free of any kind of SQL injection as we don't have any
input fields.

We see three vulnerabilities reported recently.


https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3414
https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3415
https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3416

I am not aware of the SQlite internals .

I would like to know if my application can get affected due to these
vulnerabilities.

Though i know it should not be, but would like to get any
hint/comments/opinions on the above mentioned vulnerabilities.

Thanks and Regards,
Saurav

[sqlite] SQLite Vulnerabilities reported in NVD

[Simon Slavin]

On 22 Dec 2015, at 7:02am, Saurav Sarkar  wrote:

> But the queries will be always parametrized ones.

Exploits 1 and 2 are controlled by things which can't be parameterised.

I'm not 100% sure about the format string of a printf, but I can't think of a 
way to parameterise it.  So you would seem to be safe from those exploits.

I expect Richard to soon announce that the underlying problems have been fixed, 
anyway.

Simon.

[sqlite] about compile configure

[王庆刚]

 about sqlite3_exec(m_pDB, "PRAGMA synchronous = OFF;", 0,0,0);   
The testing shows that It can not improve the speed of retrieving records from 
data base.
 Thank Simon all the same.








At 2015-12-21 18:20:49, "Simon Slavin"  wrote:
>
>On 21 Dec 2015, at 10:07am, ??? <2004wqg2008 at 163.com> wrote:
>
>>CREATE TABLE poiTable (poiId INTEGER NOT NULL, versionId INTEGER NOT 
>> NULL, regionId INTEGER , postalCode TEXT , phone TEXT , attrBitMask INTEGER 
>> , attributeBlob BLOB , primary key (poiId));
>>So the poiId is equal to the rowid.
>> 
>>such as : select * from poiTable where poiId = ... ;
>>And execute the sql by sqlite3_prepare_v2?sqlite3_step?
>>how could I improve the performance?
>
>I think you are correct.  You already have an ideal index for the poiId 
>column.  So I'm afraid you will have to look at other things.
>
>One thing is that if you do not actually want all the columns you should 
>select just the columns you want instead of doing "SELECT *".
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Vulnerabilities reported in NVD

[Richard Hipp]

On 12/22/15, Simon Slavin  wrote:
>
> On 22 Dec 2015, at 7:02am, Saurav Sarkar  wrote:
>
>> But the queries will be always parametrized ones.
>
> Exploits 1 and 2 are controlled by things which can't be parameterised.
>
> I'm not 100% sure about the format string of a printf, but I can't think of
> a way to parameterise it.  So you would seem to be safe from those
> exploits.
>
> I expect Richard to soon announce that the underlying problems have been
> fixed, anyway.

I do not know where those vulnerability reports originated.  They did
not originate from me.  For that matter, I was never consulted about
them.  None of them represent real vulnerabilities, in my assessment.
All of the problems identified have been fixed for a long time.

I think that these reports achieve nothing beyond vulnerability
fatigue.  I think it is shameful that nvd.nist.gov publishes them.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SQLite Vulnerabilities reported in NVD

[Clemens Ladisch]

Saurav Sarkar:
>Our application is free of any kind of SQL injection

Famous last words.  :)

>as we don't have any input fields.

So where does your data come from?
Does your application have any interface that an attacker
could access?

How do you create your SQL statements?
Are you always using bound parameters?

>https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3414

This requires the attacker to control a collation name.

>https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3415

This requires the attacker to control the CHECK clause in a CREATE
TABLE statement.

>https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2015-3416

This requires the attacker to control the format string of the print()
SQL function.


Regards,
Clemens