[sqlite] ASK SQLite algoritm to chose Index

[Scott Robison]

And here is a copy of my answer posted at stackoverflow (which assumes the
context of the question):

A see a few problems here.

   1.

   The first SQL statement (CREATE TABLE ...) is malformed due to an extra
   comma between the last column and the closing parenthesis.
   2.

   The third SQL statement (EXPLAIN QUERY PLAN SELECT ...) uses double
   quotes around the specified email. SQLite will allow this for backward
   compatibility reasons, but it is not recommended.
   3.

   The quoting of the string representing the third SQL statement is not
   quoted properly in any language I'm familiar with.
   4.

   Finally, the table name social is not defined in the limited schema you
   gave, so really we have no way of knowing what real indexes are available.

If we assume you meant "user" when you typed "social", as CL. said, the
"autoindex" in use guarantees the email is unique so it doesn't need
anything else. It might have used your explicit index as a covering index
if you didn't need the name column as well, but since your explicit index
does not include the name column (as required by the select statement), it
deems the autoindex best. Additionally, the autoindex is almost certainly
better for this case because the smaller index (only email vs email &
password) means that there will likely be fewer reads of btree pages while
trying to find the requested email address. In other words, an index on
email address only will be a smaller index than an index on email and
password.

[sqlite] ASK SQLite algoritm to chose Index

[Scott Robison]

On Sat, Feb 27, 2016 at 10:49 PM, Simon Slavin  wrote:

>
> On 28 Feb 2016, at 5:47am, Keith Medcalf  wrote:
>
> > I do not see a link ... do you see a link?
>
> I saw no link.
>
> The OP may not actually be posting to this mailing list.  He may be using
> a web interface which does the posting for him.  And the web interface may
> be faulty and not posting the link properly.
>

While I considered just leaving this thread alone, I fear that there will
be multiple posts (still without links) if everyone chooses that course of
action.

So the link appears to be:
http://stackoverflow.com/questions/35625812/sqlite-use-autoindex-instead-my-own-index

SDR

[sqlite] ASK SQLite algoritm to chose Index

[Keith Medcalf]

I do not see a link ... do you see a link?

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Christoforus Surjoputro
> Sent: Saturday, 27 February, 2016 21:12
> To: SQLite mailing list
> Subject: Re: [sqlite] ASK SQLite algoritm to chose Index
> 
> This i the link: SQLite use autoindex instead my own index
> | ? |
> | ? |  | ? | ? | ? | ? | ? |
> | SQLite use autoindex instead my own indexI've problem with SQLite
> autoindex in UNIQUE table. I've create table like below.
> c.execute('''CREATE TABLE user( id INTEGER PRIMARY KEY, email TEXT NOT...
> |
> |  |
> | View on stackoverflow.com | Preview by Yahoo |
> |  |
> | ? |
> 
> 
> 
> 
> 
> On Sunday, February 28, 2016 12:44 AM, Keith Medcalf
>  wrote:
> 
> 
> 
> No links seen from you, ever, on this thread.
> 
> SQLite automatically chooses the best index from those available.? The
> index it is choosing is probably better than the one you want it to
> use002E
> 
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Christoforus Surjoputro
> > Sent: Saturday, 27 February, 2016 09:47
> > To: SQLite mailing list
> > Subject: Re: [sqlite] ASK SQLite algoritm to chose Index
> >
> > Hi Simon.
> > I've post it in stackoverflow link I gave before. About run ANALYZE,
> > SQLite still choose their own index instead Index that I've made. Thank
> > you.
> >
> >
> >? ? On Saturday, February 27, 2016 12:49 AM, Simon Slavin
> >  wrote:
> >
> >
> >
> > On 26 Feb 2016, at 5:45pm, Christoforus Surjoputro 
> > wrote:
> >
> > > I've problem with sqlite choosing index that I think I have better
> index
> > to use. I've ask here but still didn't get why this happen? Did sqlite
> > choose the best index to use or choose the last entered index?
> >
> > SQLite doesn't know which index was entered last.? It chose the best
> index
> > based on what it knows.
> >
> > Can you post your TABLE definition and the INDEXes you defined on the
> > table, and the result of EXPLAIN QUERY PLAN on your SELECT ?
> >
> > Please run ANALYZE, then do EXPLAIN QUERY PLAN on your SELECT again and
> > tell us if anything changed.
> >
> > 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] test

[Stayros Mpampis]

?

[sqlite] Encrypt the SQL query

[Eduardo Morras]

On Thu, 25 Feb 2016 14:01:31 +0800
 wrote:

> Hi,
> 
> In my C++ program, I will invoke SQLite to execute SQL queries. But
> these queries are just stored as normal string constants in C++ so it
> is easy to be decoded via reverse engineering method. Does SQLite
> provide a good way to encrypt the SQL query strings while does not
> affect the performance when executing the queries?

No, it hasn't. And as other has said, it's not possible. But you can take other 
way, if you creates a new lemon parser entry for a different query language 
more obfuscate than SQL, you'll force the reverse engineer to learn this new 
query language. You can automate the conversion from actuals SQL queries to 
NewLang queries, even change the NewLang on each compile with different lemon 
parser code. And yes, it's too complex and the time invested (I think) is time 
lost.

> Thanks

L

---   ---
Eduardo Morras 

[sqlite] Encrypt the SQL query

[Simon Slavin]

On 27 Feb 2016, at 3:20pm, Dave Baggett  wrote:

> It would be interesting to contemplate running all of SQLite on an HSM, as 
> this would allow you to perform database transactions while ensuring the 
> database itself was kept hidden from the host computing device. I'm not aware 
> of any generically programmable HSMs capable of doing this, though, and of 
> course your database would have to entirely fit within the HSM's on-board 
> storage.

SQLite is in use in something like this.  I can't describe the custom hardware 
to you but think of a Raspberry Pi (half hand-sized circuitboard based around a 
CPU executing a stripped-down Linux) and you're not far wrong.  Various user 
factors (biometrics, etc.) are stored in the database and when someone needs 
access the database is searched.

Simon.

[sqlite] ASK SQLite algoritm to chose Index

[Christoforus Surjoputro]

Hi Simon.
I've post it in stackoverflow link I gave before. About run ANALYZE, SQLite 
still choose their own index instead Index that I've made. Thank you.


On Saturday, February 27, 2016 12:49 AM, Simon Slavin  wrote:



On 26 Feb 2016, at 5:45pm, Christoforus Surjoputro  
wrote:

> I've problem with sqlite choosing index that I think I have better index to 
> use. I've ask here but still didn't get why this happen? Did sqlite choose 
> the best index to use or choose the last entered index?

SQLite doesn't know which index was entered last.? It chose the best index 
based on what it knows.

Can you post your TABLE definition and the INDEXes you defined on the table, 
and the result of EXPLAIN QUERY PLAN on your SELECT ?

Please run ANALYZE, then do EXPLAIN QUERY PLAN on your SELECT again and tell us 
if anything changed.

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

[sqlite] Encrypt the SQL query

[Dave Baggett]

As others have pointed out, since SQLite must ultimately execute the query, it 
has to be unencrypted in memory at some point. In general, there is no way to 
protect data from prying eyes if that data must be used by a running program, 
because a competent adversary can inspect the program as it's running.

High-assurance applications such as those used for classified work incorporate 
countermeasures the make it practically harder to do this, but these offer 
absolutely no additional security from a theoretical standpoint. An example is 
the requirement to keep keys and keying material AES-wrapped in memory except 
at the point of use.

As far as I'm aware, the state of the art in protecting secrets that must be 
kept in memory is so-called "whitebox encryption". You can google it, but 
essentially the idea is to take the state of a cryptographic primitive like AES 
and explode it out into a much more complex (and therefore harder to analyze), 
but equivalent representation. Here again, though, this just makes things a bit 
harder for an adversary -- it provides no additional security from a 
theoretical standpoint.

Another approach to keeping secrets that you must use in running programs is to 
store them only in hardware security modules (HSMs). In this scenario, you have 
the HSM -- usually a USB or microSD device -- do whatever computation you need 
using its on-board CPU. The HSM then provides you the result of the computation 
(decrypted data or whatever). The HSM hardware guarantees that the secret 
itself is never revealed to the host computing device; getting the stored 
secret requires physically disassembling the HSM. HSM devices are commodity 
hardware now; you can buy one for under $50.

It would be interesting to contemplate running all of SQLite on an HSM, as this 
would allow you to perform database transactions while ensuring the database 
itself was kept hidden from the host computing device. I'm not aware of any 
generically programmable HSMs capable of doing this, though, and of course your 
database would have to entirely fit within the HSM's on-board storage. These 
devices usually only have a small amount of storage -- enough to store 4096 
keys, for example.

But if there were an HSM that shipped with a "real" amount of memory and 
storage -- and was generically programmable -- there's no reason it couldn't be 
done.

Dave

Sent with inky

 wrote:

Hi,



In my C++ program, I will invoke SQLite to execute SQL queries. But these

queries are just stored as normal string constants in C++ so it is easy to

be decoded via reverse engineering method. Does SQLite provide a good way to

encrypt the SQL query strings while does not affect the performance when

executing the queries?



Thanks







___

sqlite-users mailing list

sqlite-users at mailinglists.sqlite.org

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

[sqlite] ASK SQLite algoritm to chose Index

[Keith Medcalf]

No links seen from you, ever, on this thread.

SQLite automatically chooses the best index from those available.  The index it 
is choosing is probably better than the one you want it to use002E

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Christoforus Surjoputro
> Sent: Saturday, 27 February, 2016 09:47
> To: SQLite mailing list
> Subject: Re: [sqlite] ASK SQLite algoritm to chose Index
> 
> Hi Simon.
> I've post it in stackoverflow link I gave before. About run ANALYZE,
> SQLite still choose their own index instead Index that I've made. Thank
> you.
> 
> 
> On Saturday, February 27, 2016 12:49 AM, Simon Slavin
>  wrote:
> 
> 
> 
> On 26 Feb 2016, at 5:45pm, Christoforus Surjoputro 
> wrote:
> 
> > I've problem with sqlite choosing index that I think I have better index
> to use. I've ask here but still didn't get why this happen? Did sqlite
> choose the best index to use or choose the last entered index?
> 
> SQLite doesn't know which index was entered last.? It chose the best index
> based on what it knows.
> 
> Can you post your TABLE definition and the INDEXes you defined on the
> table, and the result of EXPLAIN QUERY PLAN on your SELECT ?
> 
> Please run ANALYZE, then do EXPLAIN QUERY PLAN on your SELECT again and
> tell us if anything changed.
> 
> 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] Encrypt the SQL query

[Teg]

Hello Admin,

I  have  all  my  important  strings  encrypted  using  AES.  They get
decrypted  at  runtime. Well, they get decrypted at run time just when
used  and  then  get  over-written  by other decrypted strings. It's a
circular queue of strings.

I just run a post process step on one of my libs to actually encrypt
the strings. It happens automatically when I build the lib.

Someone with a debugger could find then eventually but no more than 5
decrypted  strings  are  in active memory at a time. I should probably
add a function to flush them too...


Thursday, February 25, 2016, 1:01:31 AM, you wrote:

asn> Hi,

asn> In my C++ program, I will invoke SQLite to execute SQL queries. But these
asn> queries are just stored as normal string constants in C++ so it is easy to
asn> be decoded via reverse engineering method. Does SQLite provide a good way 
to
asn> encrypt the SQL query strings while does not affect the performance when
asn> executing the queries?

asn> Thanks



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



-- 
 Tegmailto:Teg at djii.com

[sqlite] Encrypt the SQL query

[Jim Callahan]

>
> queries are just stored as normal string constants in C++ so it is easy to
> be decoded via reverse engineering method.


Is the normal "reverse engineering method":
1. a hexdump of your executable?
2. debugging your executable?

A trivial cipher could be used to encode the strings prior to storing
in C++ and then decoded on the fly. That would protect against
the strings be obvious in a casual hexdump, but would provide
no protection against a determined adversary who could guess the
likely cipher (from reverse engineering your Google searches or
likely reference books).

"Show me your flowcharts and conceal your tables, and I shall continue to
be mystified. Show me your tables, and I won?t usually need your
flowcharts; they?ll be obvious."
Fredrick Brooks, Mythical Man-Machine Month, page p. 102-3
https://en.wikiquote.org/wiki/Fred_Brooks

Jim Callahan
Orlando, FL






On Thu, Feb 25, 2016 at 1:01 AM,  wrote:

> Hi,
>
> In my C++ program, I will invoke SQLite to execute SQL queries. But these
> queries are just stored as normal string constants in C++ so it is easy to
> be decoded via reverse engineering method. Does SQLite provide a good way
> to
> encrypt the SQL query strings while does not affect the performance when
> executing the queries?
>
> Thanks
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Richard Hipp]

On 2/26/16, Vince Scafaria  wrote:
> I am using System.Data.SQLite in .NET and encountering "database is locked"
> with wal using multiple threads from the same process running simple select
> statements with (separate) read-only connections.

The first database connection (in any process) that opens a WAL-mode
database file must create the *-wal and *-shm files used for
transaction control.  (Likewise, the last connection to close a
WAL-mode database will delete those files.)

But because new files must be created when opening, that means that a
read-only database connection cannot be the first connection to open a
WAL-mode database.  A read-only database connection can open and read
a WAL-mode database as long as some other (read/write) thread or
process has already opened it, but the read-only connection cannot be
the first to open it.

Try this experiment:

Before running your test program, connection to the database file
using the sqlite3.exe command-line client and type ".schema".  Leave
sqlite3.exe connected to the database - just setting there doing
nothing.  Then while sqlite3.exe is running, rerun your test program.
Let us know if that clears your problem.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Encrypt the SQL query

[Eric Rubin-Smith]

>  at some
> point the encrypted SQL wiill have to be decrypted before SQLite
> interprets it.  

Perhaps we could achieve some level of obfuscation by "pre-preparing" at 
compile time the set of all statements that the program uses, and storing the 
SQLite prepared statement objects in the program image. Then you'd just do 
argument binding at run time.

That is, you are more-or-less storing the query plan instead of the SQL. 

I guess you'd have to make a bunch of assumptions about the db schema, the 
boundedness of your universe of queries and the like, and perhaps have to hack 
a few values into some relevant structs -- I'm not enough of an expert to know 
the extent of it. 

Would something like that be workable?

Again, even if it is workable, it won't stop a sufficiently motivated and 
skilled reverse engineer.  There are people employed at e.g. Symantec whose 
entire job is to figure out what a program does, despite the efforts of 
world-class adversaries to obfuscate it.  They would probably think my above 
suggestion is adorable, and crack it during a donut break.

In order to evaluate suggestions coherently, we'd really need to know what 
threat you are trying to mitigate. 

Eric

[sqlite] Possible bug in the SQL parser

[Dan Kennedy]

On 02/27/2016 12:49 AM, Jo?o Ramos wrote:
> I'm using SQLite 3.8.10.2 and the following query illustrates the problem:
>
> WITH
> tA(id, name) AS
> (
>  SELECT 1, "a" UNION ALL SELECT 2, "b"
> ),
> tB(name) AS
> (
>  SELECT "a" UNION ALL SELECT "b"
> )
> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>
>
> There is no _id_ column in the tB table, yet the statement doesn't produce
> any error and in fact will return the ids of table tA. This doesn't seem
> correct to me.
>
>

Cannot reproduce this problem here:

dan at darkstar:~/work/sqlite/bld$ ./sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> WITH tA(id, name) AS (
...>   SELECT 1, "a" UNION ALL SELECT 2, "b"
...> ),
...> tB(name) AS (
...>   SELECT "a" UNION ALL SELECT "b"
...> )
...> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
Error: no such column: tB.id


If you were using "oid", "rowid" or "_rowid_" instead of "id", then it 
might look like it was returning the values from table tA. All views and 
CTEs in SQLite have such columns for historical reasons, but the 
contents of them is both undefined and unstable. Sometimes it's a 
sequence of integers starting at 1.

Dan.