Re: [sqlite] [EXTERNAL] Difference between hex notation and string notation

[Hick Gunter]

The X'' notation returns a blob. LIKE works with strings. Comparing a string to 
a blob of the same content always returns false.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Sascha Ziemann
Gesendet: Freitag, 13. Dezember 2019 10:16
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Difference between hex notation and string notation

I have a problem to find rows in a database when I write in hex notation:

CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL); INSERT INTO LOG VALUES 
(X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
down');
SELECT ROWID,MSG FROM LOG; --
returns both rows
SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down';  --
returns just the second
SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; -- returns 
both rows

This looks like a bug to me.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL Murder Mystery

[Simon Slavin]

A little light relief:



Chosen SQL variation is SQLite !
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persisting Database Lock Issue

[Rowan Worth]

On Fri, 13 Dec 2019 at 23:50, 고예찬  wrote:

> Hello, I am experiencing `database is locked` error. I wonder if anyone has
> gone through or resolved similar issue.
>
> To illustrate, I have a .db file with below settings:
> ```
> PRAGMA journal_mode=WAL;
> PRAGMA wal_autocheckpoint=128;
> PRAGMA journal_size_limit=0;
> ```
> and I run two programs in python - one writing the data 1 row per second,
> one read / upload / then delete the data every 30 seconds.
>
> Normally, it works fine but occasionally after power failure and reboot(the
> programs run on an IoT device), the reader program throws `database is
> locked` error and never goes away after since, while the writer program
> seems to work fine. This lock doesn't go away even after program/system
> restart.


This is an odd symptom as the fcntl/POSIX file locks (sqlite's default
locking mechanism under linux) are attributed to processes, and the kernel
never writes them to disk. Is there some kind of network file system
involved or does the IoT device have its own SSD?


> One interesting fact is that if I do `cp data.db data1.db && rm data.db &&
> mv data1.db data.db`. The error always goes away completely. Though it
> happens again after several hard reboot.
>

This series of commands gets around the locks because it creates two copies
of the database contents stored in different inodes. The kernel's locking
table is based on inodes rather than file paths, so you end up with
existing processes accessing the deleted inode and newer processes
accessing the new inode, even though they're both referring to the same
path ./data.db (sidenote: this is also a great way to corrupt databases).


> It would be very helpful if anyone can give any advice.
>

There's not really any operating system/file system mechanism which
adequately explains the symptoms you're seeing, so I'd be looking into your
software components. Figure out what is holding the lock and why. Look for
sqlite_stmts which are never disposed - these will retain their locks (the
sqlite3_next_stmt function can be useful for finding these).

Also, look for code which interacts with data.db directly using regular
file syscalls rather than going via sqlite. Because this:


> If I leave the problem as it is(writer keeps writing, and reader
> keeps retrying upon error), the error thrown changes to `database disk
> image is malformed`.
>

Strongly suggests there is a process somewhere which is touching data.db
without obeying sqlite's expected locking protocol (which prevents
corruption). Even something as innocent as an open()/close() on the file
can cause problems, because of the semantics of POSIX locks (section 2.2 of
https://www.sqlite.org/howtocorrupt.html)

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

Re: [sqlite] A crash bug in sqlite

[Chris Brody]

> Yes.  I discovered the same thing independently.  The previous fix was
> subtly wrong.  Please try the latest trunk version.

Will there be a patch release, or should we just wait for the next minor?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance vs. memory trade-off question

[Andy Bennett]

Hi,


(3)  Each database connection uses about 72 KB less heap space.


How much is this as a percentage of a freshly initialised connection and 
how much as a percentage of some kind of "typical" connection (is there 
even one?)?





andy...@ashurst.eu.org
http://www.ashurst.eu.org/
http://www.gonumber.com/andyjpb
0x7EBA75FF
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Coding standard

[Richard Hipp]

On 12/12/19, Richard Hipp  wrote:
> On 12/12/19, Arthur Blondel  wrote:

> Authur sent me a spreadsheet with 432 warnings (not 32000 as
> originally mentioned).

Apparently the first list the OP sent was filtered to show only
"security" warnings.  Arthur sent me the complete list of 31859
warnings this morning, which I have uploaded to
https://sqlite.org/tmp/complete_codesonar_warnings.csv for your
perusal.

I did some spot-checking.  The additional warnings are no better than
the first set, and in some cases quite a bit worse.  I'll have more to
say about CodeSonar later - I have some travel over the next few days
so it might be late in the week before I can get back to this...

All of the warnings are against this check-in:
https://sqlite.org/src/info/18db032d058f1436

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

Re: [sqlite] CVE-2019-19317

[Gary R. Schmidt]

On 15/12/2019 10:16, Yongheng Chen wrote:

When we report the bugs, we said that they were from 3.31 version, but people 
in mitre changed them to 3.30.1. We just reported what we found. And the commit 
we reported in the bug report is referencing to the official GitHub repo.

Of course the people at Mitre changed the version number, they do not 
create a CVE for *unreleased* software.


It has already been pointed out that GitHub is not the official 
repository, it is a mirror.
You should be using the Fossil repository to test unreleased versions, 
which means you will get the latest version.


Also, reporting bugs here (or to sqlite-dev) would be the polite thing 
to do, as it gives the developers a chance to fix things before the 
software gets released, rather than causing a CVE to be generated, for a 
problem that does not yet exist in the real world.


And it means that I (and others) won't be having to answer email from 
customers on Monday (their time) and Tuesday (my time) where they are in 
a complete panic because they've discovered[1] that a CVE has been 
raised on a component of the products, and, "Oh, no, the sky is falling, 
what shall we do, what shall we do?!?!?!"


Cheers,
GaryB-)

1 - Yes, they're smart enough to troll the CVE lists looking for 
problems, but not smart enough to evaluate the possible effects of the 
problem.

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