[sqlite] Security issues in SQLite

[Saurav Sarkar]

Thanks a lot Richard for your inputs.

the link indeed looks positive enough  :).

Would like to know if anyone else has gone through similar experiences .

Best Regards,
Saurav

On Mon, Mar 23, 2015 at 5:26 PM, Richard Hipp  wrote:

> On 3/23/15, Saurav Sarkar  wrote:
> > Hi All,
> >
> > We use SQLite in our application. Ours is an windows store application
> > internally uses SQlite to store data (embedded). I use SQLitePCL library
> > which is a C# .NET based library
> >
> > I was asked to perform Fortify scans on the SQLite code of 3.8.8.3
> >
> [...]
> >
> > Anyone has come across with  any security vulnerability with SQLIte ?
> >
> > Any help/input here will be hugely appreciated.
> >
>
> The Fortify identified errors are all false-positives.  Fortify and
> other static analyzers excel at generating false-positives in SQLite.
> See https://www.sqlite.org/testing.html and especially section 11 for
> additional information.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Security issues in SQLite

[Saurav Sarkar]

Hi All,

We use SQLite in our application. Ours is an windows store application
internally uses SQlite to store data (embedded). I use SQLitePCL library
which is a C# .NET based library

I was asked to perform Fortify scans on the SQLite code of 3.8.8.3

I used the amalgamation code.

Since it is a native component and written in C. i found lot of Buffer
Overflow and memory leak errors. Also after college i have not touched c
code , so my understanding of c code has reduced :)

Just wanted to confirm my understanding and would like to solicit opinion
from the community if the issues are real threat.

For e.g. in the code SQLite3c line 16458

Error reported here is The function sqlite3OsOpenMalloc() in sqlite3.c
allocates memory on line 16467 and fails to free it.

SQLITE_PRIVATE int sqlite3OsOpenMalloc(
  sqlite3_vfs *pVfs,
  const char *zFile,
  sqlite3_file **ppFile,
  int flags,
  int *pOutFlags
){
  int rc = SQLITE_NOMEM;
  sqlite3_file *pFile;
  pFile = (sqlite3_file *)sqlite3MallocZero(pVfs->szOsFile);
  if( pFile ){
rc = sqlite3OsOpen(pVfs, zFile, pFile, flags, pOutFlags);
if( rc!=SQLITE_OK ){
  sqlite3_free(pFile);
}else{
  *ppFile = pFile;
}
  }
  return rc;
}


For memory leak .SQLitePCL and our application uses the disposable pattern
to dispose the prepared statement after its use and the connection we also
close once done.

i am not sure if closing the DB connection and prepared statement enough to
counter this problem.

In Shell.c

 abYield = (int*)sqlite3_realloc(abYield, nAlloc*sizeof(int));
}
abYield[iOp] = str_in_array(zOp, azYield);
p->aiIndent[iOp] = 0;
p->nIndent = iOp+1;

if( str_in_array(zOp, azNext) ){
  for(i=p2op; iaiIndent[i] += 2;
}
if( str_in_array(zOp, azGoto) && p2opnIndent
 && (abYield[p2op] || sqlite3_column_int(pSql, 2))
){
  for(i=p2op+1; iaiIndent[i] += 2;
}
  }

  p->iIndent = 0;
  sqlite3_free(abYield);
  sqlite3_reset(pSql);

Its saying abYield has been allocated a memory and has not been freed.
But i can see the sqlite3_free() function at the bottom which frees up the
memory.

Also i assume libraries like SQLitePCL won't use shell.c.

Some Buffer Overflow errors like

In Line 53855 of SQLite3.c

  assert( cbrk+size<=usableSize && cbrk>=iCellFirst );
testcase( cbrk+size==usableSize );
testcase( pc+size==usableSize );
put2byte(pAddr, cbrk);
if( temp==0 ){
  int x;
  if( cbrk==pc ) continue;
  temp = sqlite3PagerTempSpace(pPage->pBt->pPager);
  x = get2byte([hdr+5]);
  memcpy([x], [x], (cbrk+size) - x);
  src = temp;
}
memcpy([cbrk], [pc], size);
  }
  assert( cbrk>=iCellFirst );
  put2byte([hdr+5], cbrk);
  data[hdr+1] = 0;

Usage of memcpy is discouraged in favor to memcpy_s()
Similarly the tool is detecting lot of buffer overflow errors because of
usage of gets() ,strcpy() etc.

Since my application uses the emebedded database there is no way the input
to these methods are being given from my application. I assume i am safe ?

Anyone has come across with  any security vulnerability with SQLIte ?

Any help/input here will be hugely appreciated.

Thanks and Best Regards,
Saurav

[sqlite] Problem: you can delete a virtual table with open statements and then SEGV

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

This was originally reported as a problem in APSW (Python SQLite wrapper):

  https://github.com/rogerbinns/apsw/issues/184

The important bits are that a cursor (sqlite3_stmt) is created, and
step called once, but *not* reset or finalised:

  cursor.execute('SELECT * FROM TEMP.vt')

Then the the virtual table is dropped (different sqlite3_stmt):

  db.cursor().execute('DROP TABLE TEMP.vt')

Finally sqlite3_step is called on the sqlite3_stmt from earlier which
causes a segfault:

  rc = pModule->xNext(pCur->pVtabCursor);

This is because pModule is now null.

I can't think of any way I can realistically prevent this from
happening.  SQLite really should prevent it from happening by
disallowing the deletion of virtual tables with open statements.

I did the same steps using a real (non-virtual table) and on trying to
drop the table with open statements get:

  LockedError: database table is locked

Virtual tables should behave the same way.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUQoswACgkQmOOfHg372QSlPACgsgh/75SnQWAzhwE+HXrM5If8
SxwAoI0UTwCYJS/yTzIm3/BD45+lCPG6
=TWTA
-END PGP SIGNATURE-

[sqlite] Security issues in SQLite

[R.Smith]

On 2015-03-23 01:59 PM, Saurav Sarkar wrote:
> Thanks a lot Richard for your inputs.
>
> the link indeed looks positive enough  :).
>
> Would like to know if anyone else has gone through similar experiences .
Often.

Just to add - many times people have posted here about some or other 
static analysis tool tripping up on some SQLite code, but it's always 
(at least, in the vast majority of cases) the analysis tool producing 
the problem (mostly false positives). This is not through any flaw in 
them, the flaw lies in static analysis' premise - that a computer 
program can "read" your code and assume /All/ safety properties from it. 
Mostly this works, but many times something that looks like a problem 
really isn't one and sadly a LOT of code exists in this universe to 
appease analysis tools (and department heads) rather than contribute an 
ounce to actual code safety or efficiency.

Richard was being very kind when he said: "... excel at generating false 
positives in SQLite."  -  that sentence does not require the words: "in 
SQLite".

Also, some of your examples posted were inside ASSERT statements which 
doesn't actually make it into final compiled code, so not sure what the 
analyzer is thinking there.

Understand that I am all for using analysis tools, they have certainly 
saved a ton of problems and can quickly point out obvious issues, but 
one has to understand their shortcomings and flaws too - just like any 
other tool.


Have a great day!
Ryan

[sqlite] Security issues in SQLite

[Simon Slavin]

On 23 Mar 2015, at 11:59am, Saurav Sarkar  wrote:

> Thanks a lot Richard for your inputs.
> 
> the link indeed looks positive enough  :).
> 
> Would like to know if anyone else has gone through similar experiences .

SQLite depends on people calling certain library routines in a certain way.  
So, for instance, they might be told to call sqlite3_bind_blob() which 
allocates memory, but to pass as a parameter a routine which will release that 
memory.  Naturally the analysis tool correctly detects a 'leak' in the routine 
because it cannot tell when the routine pointed-to by a parameter is going to 
be called.

Other routines get called in pairs with, again, one allocating memory and the 
other releasing it.  Most analysis tools see that the first part of the pair 
'leaks' memory and generate an error for it, because they do not know that the 
programmer is expected to call the other routine later.

So don't worry about it.  If you have a human spotting any problem in the 
source code people here will pay a lot of attention to you, and bugs will be 
fixed.  But static analysis tools cannot do a useful job for memory leaks in 
something like SQLite.

Simon.

[sqlite] 55c21521 causes build break on older GCC

[Joe Prostko]

On Thu, Mar 19, 2015 at 11:29 PM, Joe Prostko  wrote:

> In any case, this is more an FYI than me asking for a fix, as Fossil and
SQLite can be built just fine using our GCC 4 compiler.  That said, if
SQLite is expected to build with older GCC compilers, then it would affect
other platforms as well.

Just in case anybody saw my original message and wondered what became of
things, I wanted to state that Richard fixed the issue with check-in
de9da317d4df3efe .

Thanks for addressing it so quickly!

- joe

[sqlite] Query help

[Martin Engelschalk]

Sorry, i was too fast.

Correction

SELECT engine,coalesce(groupname,'*') as 
groupname,databasename,key,value FROM EnginePreferences left join groups 
on (groups.groupid = EnginePreferences.groupid);

OR

SELECT engine,groupname,databasename,key,value
  FROM EnginePreferences
 left join (select groupid,groupname from groups
   union
 select 0, '*') g_helper  on ( g_helper.groupid = 
EnginePreferences.groupid);

Martin
Am 23.03.2015 um 10:04 schrieb Martin Engelschalk:
> Hi,
>
> SELECT engine,coalesce(groupname,*) as 
> groupname,databasename,key,value FROM EnginePreferences left join 
> groups on (groups.groupid = EnginePreferences.groupid);
>
> OR
>
> SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value
>  FROM EnginePreferences
> left join (select groupid,groupname from groups
>   union
> select 0, '*') g_helper  on ( g_helper.groupid = 
> EnginePreferences.groupid);
>
> HTH
> Martin
>
> Am 23.03.2015 um 09:50 schrieb Marco Bambini:
>> I have a table EnginePreference:
>> CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, 
>> databasename TEXT COLLATE NOCASE, key TEXT COLLATE NOCASE, value 
>> TEXT, groupid INTEGER, UNIQUE(engine,databasename,key))
>>
>> and a table Groups:
>> CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT 
>> UNIQUE COLLATE NOCASE)
>>
>> I need to select from EnginePreferences replacing groupid with 
>> groupname and I can do that with:
>> SELECT engine,groupname,databasename,key,value FROM EnginePreferences 
>> left join groups on (groups.groupid = EnginePreferences.groupid);
>>
>> What I really need is ALSO to replace groupname with * if groupid is 0.
>> Please note that groupid 0 is never written into the Groups table so 
>> my original query would return NULL as groupname with groupid is 0 
>> instead of the required "*".
>>
>> Any idea?
>> -- 
>> Marco Bambini
>> http://www.sqlabs.com
>> http://twitter.com/sqlabs
>> http://instagram.com/sqlabs
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

[sqlite] Query help

[Martin Engelschalk]

Hi,

SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value 
FROM EnginePreferences left join groups on (groups.groupid = 
EnginePreferences.groupid);

OR

SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value
  FROM EnginePreferences
 left join (select groupid,groupname from groups
   union
 select 0, '*') g_helper  on ( g_helper.groupid = 
EnginePreferences.groupid);

HTH
Martin

Am 23.03.2015 um 09:50 schrieb Marco Bambini:
> I have a table EnginePreference:
> CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT 
> COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, 
> UNIQUE(engine,databasename,key))
>
> and a table Groups:
> CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT UNIQUE 
> COLLATE NOCASE)
>
> I need to select from EnginePreferences replacing groupid with groupname and 
> I can do that with:
> SELECT engine,groupname,databasename,key,value FROM EnginePreferences left 
> join groups on (groups.groupid = EnginePreferences.groupid);
>
> What I really need is ALSO to replace groupname with * if groupid is 0.
> Please note that groupid 0 is never written into the Groups table so my 
> original query would return NULL as groupname with groupid is 0 instead of 
> the required "*".
>
> Any idea?
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
> http://instagram.com/sqlabs
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

[sqlite] Query help

[Marco Bambini]

I have a table EnginePreference:
CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT 
COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, 
UNIQUE(engine,databasename,key))

and a table Groups:
CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT UNIQUE COLLATE 
NOCASE)

I need to select from EnginePreferences replacing groupid with groupname and I 
can do that with:
SELECT engine,groupname,databasename,key,value FROM EnginePreferences left join 
groups on (groups.groupid = EnginePreferences.groupid);

What I really need is ALSO to replace groupname with * if groupid is 0.
Please note that groupid 0 is never written into the Groups table so my 
original query would return NULL as groupname with groupid is 0 instead of the 
required "*".

Any idea?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs

[sqlite] Security issues in SQLite

[Richard Hipp]

On 3/23/15, Saurav Sarkar  wrote:
> Hi All,
>
> We use SQLite in our application. Ours is an windows store application
> internally uses SQlite to store data (embedded). I use SQLitePCL library
> which is a C# .NET based library
>
> I was asked to perform Fortify scans on the SQLite code of 3.8.8.3
>
[...]
>
> Anyone has come across with  any security vulnerability with SQLIte ?
>
> Any help/input here will be hugely appreciated.
>

The Fortify identified errors are all false-positives.  Fortify and
other static analyzers excel at generating false-positives in SQLite.
See https://www.sqlite.org/testing.html and especially section 11 for
additional information.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] What is wrong with this simple SQL?

[Hick Gunter]

The sub-select is within an "inner namespace" to the whole query. You are free 
to reference fields defined in all tables occuring in the subselect's FROM list 
IN ADDITION TO any fields defined in tables occurring in the main query's FROM 
list. This is a requirement for correlated subqueries. When the SQLite parser 
detects an unqualified name, it will attempt to resolve the name by searching 
the whole namespace - failing in the example because there are 2 fields of the 
same name. A qualified name restricts the search to the defined database/table 
- failing inthe example because there is no field of the desired name there.

-Urspr?ngliche Nachricht-
Von: Bart Smissaert [mailto:bart.smissaert at gmail.com]
Gesendet: Sonntag, 22. M?rz 2015 15:49
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] What is wrong with this simple SQL?

But I thought that as the non-aliased column emis_number is in the sub-select 
(select emis_number from DIABETICS) that column name could only apply to the 
table DIABETICS and there should be no ambiguity.

RBS

On Sun, Mar 22, 2015 at 2:33 PM, Ketil Froyn  wrote:

> But both the patients table and the DIABETIC_ISSUES_LAST table have
> columns called emis_number. Since your query turns out to be valid
> despite not doing what you expected, sqlite doesn't know which of
> those columns you're referring to. So it looks like the "ambiguous
> column name" is in fact the correct error message.
>
> Regards, Ketil
>
> On 22 March 2015 at 15:15, Bart Smissaert 
> wrote:
> > Sorry, that table did indeed not have a column named emis_number, my
> > mistake.
> > Still, the error message ambiguous column name doesn't seem quite right.
> > Should that not also be no such column: emis_number?
> >
> > RBS
> >
> > On Sun, Mar 22, 2015 at 2:06 PM, Igor Tandetnik 
> wrote:
> >
> >> On 3/22/2015 8:50 AM, Bart Smissaert wrote:
> >>
> >>> select g.gp_name, d.emis_number from DIABETIC_ISSUES_LAST d inner
> >>> join patients p on(d.emis_number = p.emis_number) inner join
> >>> gp_table g on(p.usual_gp_index_number = g.gp_id) where
> >>> d.emis_number not in(select DB.emis_number from DIABETICS DB)
> >>>
> >>> I get:
> >>>
> >>> no such column: DB.emis_number
> >>>
> >>
> >> So, the table DIABETICS doesn't have a column named emis_number
> >>
> >>  This runs fine:
> >>>
> >>> select emis_number from DIABETIC_ISSUES_LAST where emis_number not
> >>> in(select emis_number from DIABETICS)
> >>>
> >>
> >> emis_number in the sub-select is DIABETIC_ISSUES_LAST.emis_number,
> >> not DIABETICS.emis_number
> >>
> >>  So, how should I do this?
> >>>
> >>
> >> First, you have to figure out *what* you are trying to do. In light
> >> for the fact that DIABETICS doesn't have a column named
> >> emis_number, it's
> not
> >> at all clear.
> >> --
> >> Igor Tandetnik
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user
> >> s
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> -Ketil
> ___
> 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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Query times vary between 0.2 s and 30 s for very

[Hick Gunter]

SQLite creates an ephemeral table for the IN list,giving O(log n) performance 
for lookups.

>-Urspr?ngliche Nachricht-
>Von: James K. Lowden [mailto:jklowden at schemamania.org]
>Gesendet: Samstag, 21. M?rz 2015 20:43
>An: sqlite-users at mailinglists.sqlite.org
>Betreff: Re: [sqlite] Query times vary between 0.2 s and 30 s for very
>
>On Sat, 21 Mar 2015 19:01:16 +0100
>"Mario M. Westphal"  wrote:
>
>> For now I have increased the threshold for IN clauses (instead of TEMP
>> tables) and use WHERE IN (SELECT ? from TEMP) instead of a JOIN.
>
>...
>There is also the question of linear vs binary searches.  When you supply a 
>list of constants to IN, most if not all DBMSs search the list sequentially.  
>When IN (or EXISTS) is supplied from an indexed column, the search is often 
>binary.  For a small number of elements, there's no >distinction.  For 1000 
>elements, it's 2 orders of magnitude: 1000 hops versus 10.
>
>--jkl


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] What is wrong with this simple SQL?

[Bart Smissaert]

Well, select column A from table B, kind of implies that that column A can
only come from table B.
This is what most people would think I would guess.

RBS

On Sun, Mar 22, 2015 at 10:27 PM, Igor Tandetnik  wrote:

> On 3/22/2015 11:50 AM, Bart Smissaert wrote:
>
>> Still, in this particular case it seems odd as there is only one column
>> and
>> one table in the sub-select.
>>
>
> I'm not sure I understand what significance you ascribe to this fact. Why
> again should the number of columns or tables in subselect matter?
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>