Re: [sqlite] Is there a better way to get this information other than modifying SQLite?

2011-10-20 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 20/10/11 14:39, Peter Aronson wrote:
> I'm porting code from DBMS platforms that have grants and revokes.  One
> of the functions gets a list of available tables and what SQL
> operations you can perform on them (select, insert, update, delete).  A
> table in a database attached to read-only would have select privilges,
> but a table in a database attached read/write would have all four.

You can't change the code now, but in the Python community there is talk
of the look before you leap versus easier to ask forgiveness than
permission idioms.  ie check everything for doing the action versus just
doing it and handling failure gracefully.

I'd advocate moving to the latter idiom for several reasons.  First is
that it removes race conditions and similar kinds of changes happening
underneath your feet.  The second is that permissions are getting more and
more complicated, and it gets harder and harder to tell if something would
be allowed other than just trying it.

> So, write a virtual table to that contains the information I've added
> to the pragma?  That's possible, but I'd still have to go inside SQLite
> somehow to get the information in the first place.  So, I'm not seeing
> a lot of advantage here.

The reason is that you can do SQL on virtual tables (eg SELECT ... WHERE)
which makes it far more useful than a pragma.  The other is that DRH has
expressed a preference for virtual tables over pragmas in tickets where
the issue came up.  It is extremely unlikely that an existing pragma will
be altered in the core SQLite code.  You at least have a shot of a virtual
table being added to SQLite.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6gus8ACgkQmOOfHg372QTG6wCdHqLmo5kUzNpxRsDaW5vfEtmH
Ku8AoI6YHWfMpl7JQDWl1xiUT1cX2icF
=UqmB
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Doc bug in on-line description of round() function

2011-10-20 Thread Jay A. Kreibich 
On Thu, Oct 20, 2011 at 04:03:28PM -0700, Peter Aronson scratched on the wall:

> The "Using SQLite" book, I notice gets it right, however.

  Score!

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Doc bug in on-line description of round() function

2011-10-20 Thread Peter Aronson 
Here's where I let my pedantic side out to play.  The documentation for the 
round() function on the SQLite website at 
http://www.sqlite.org/lang_corefunc.html says:

"The round(X,Y) function returns a string representation of the floating-point 
value X rounded to Y digits to the right of the decimal point. If the Y 
argument 
is omitted, the X value is truncated to an integer."

However, if you actually say execute this SQL:

select typeof(round(1.1));

You get real as a result, not text.  Looking at roundFunc()'s source code in 
sqlite3.c (from func.c) it indeed calls sqlite3_result_double(), not 
sqlite3_result_text().

The "Using SQLite" book, I notice gets it right, however.

Best,

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

Re: [sqlite] does a unique constraint imply an index

2011-10-20 Thread BareFeetWare 
Hi Sean,

In reply to:

> In postgres, I know that if I declare a column or set of columns to have a 
> unique constraint, there is also an index created on those columns. Does the 
> same thing happen in sqlite

Yes.

For example:

create table Test (Name text collate nocase unique not null);

gives:

select * from SQLite_Master;

type|name|tbl_name|rootpage|sql
table|Test|Test|15|CREATE TABLE Test (Name text collate nocase unique not null)
index|sqlite_autoindex_Test_1|Test|16|

Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Is there a better way to get this information other than modifying SQLite?

2011-10-20 Thread Simon Slavin 

On 20 Oct 2011, at 9:15pm, Peter Aronson wrote:

> What I have done at the moment is added a fourth column named "readonly" to 
> pragma database_list (it gets the value for this column from a function I 
> added 
> called sqlite3BtreeIsreadonly(), which in turn
> is a wrapper around sqlite3PagerIsreadonly()),



> and a fourth column named "desc" 
> to pragma index_info (it gets the value for this column from 
> pIdx->aSortOrder[i]).

Not so sure about the first of these, but your extension to 'PRAGMA index_info' 
would appear to be an obviously useful one.  From my point of view the column 
should always contain 'ASC' or 'DESC' but other people on this list may have 
other opinions.

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

Re: [sqlite] Is there a better way to get this information other than modifying SQLite?

2011-10-20 Thread Peter Aronson 
Roger,

> Out of curiousity why do you want to know this?  Note that even if a
> database is opened at the SQLite level readonly it can still be written to
> at the operating system level - an example would be recovering from the
> journal.

I'm porting code from DBMS platforms that have grants and revokes.  One of the 
functions gets a list of available tables and what SQL operations you can 
perform on them (select, insert, update, delete).  A table in a database 
attached to read-only would have select privilges, but a table in a database 
attached read/write would have all four.

> In terms of exporting information, I'd recommend making virtual tables
> available rather than modifying existing pragmas.

So, write a virtual table to that contains the information I've added to the 
pragma?  That's possible, but I'd still have to go inside SQLite somehow to get 
the information in the first place.  So, I'm not seeing a lot of advantage here.

I have considered copying and renaming the pragmas to something like 
database_list2 and index_info2.

Best,

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

Re: [sqlite] sql statement to update the data in the table

2011-10-20 Thread Joanne Pham 
SELECT AES_ENCRYPT(password, 'abcddsfddafdasfddasd');
is work!
I think I need to find out what is the data type and data lengh for storing the 
encrypt password 
Thanks,
JP



From: Simon Slavin 
To: Joanne Pham ; General Discussion of SQLite Database 

Sent: Wednesday, October 19, 2011 6:24 PM
Subject: Re: [sqlite] sql statement to update the data in the table


On 20 Oct 2011, at 1:49am, Joanne Pham wrote:

> it seems like it didn't work.
> For example the password is 'password'. I ran the update statement below and 
> do the AES_DECRYPT the password is null instead of 'password'.

Try just

SELECT AES_ENCRYPT(password, 'abcddsfddafdasfddasd');

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

Re: [sqlite] Is there a better way to get this information other than modifying SQLite?

2011-10-20 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 20/10/11 13:15, Peter Aronson wrote:
> The first is whether a database  currently attached to the database
> connection was open read-only or read/write.

Out of curiousity why do you want to know this?  Note that even if a
database is opened at the SQLite level readonly it can still be written to
at the operating system level - an example would be recovering from the
journal.

In terms of exporting information, I'd recommend making virtual tables
available rather than modifying existing pragmas.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6gkeAACgkQmOOfHg372QTFiQCeNtjqXgG9pnfDwrSMfKG9zAAR
RO0An2z74tow08RS9TXaFYM/9feb4zWc
=U3uH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a better way to get this information other than modifying SQLite?

2011-10-20 Thread Petite Abeille 

On Oct 20, 2011, at 10:15 PM, Peter Aronson wrote:

> And while I suppose I could 
> ask for these changes to be made as enhancements to
> SQLite, I assume from the lack of them at this time that they are not exactly 
> common requirements.

Well, prising out any type of metadata from SQLite is a recurrent request (e.g. 
constraint information, meaningful error messages, etc). 

> Any suggestions?

Unfortunately, no. Keep on pressing though :)

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

[sqlite] Is there a better way to get this information other than modifying SQLite?

2011-10-20 Thread Peter Aronson 
In the course of porting some software to use SQLite, I found I have needed 
some 
information that I could not figure out how to get from SQLite without undo 
effort, but that SQLite actually "knows".  The first is whether a database 
currently attached to the database connection was open read-only or 
read/write.  
The second is whether a column within an index is sorted ascending or 
descending.

The first could be obtained by trying to create then delete an object in each 
attached database (as returned by pragma database_list) in turn.  This is not 
particularly elegant, and would be kind of problematic if there are multiple 
simultaneous writers.  But I don't have to support multiple simultaneous 
writers 
at the moment, so it should work.  It seems like it could be a bit expensive, 
though.

The second could be obtained by parsing the index creation statement in stored 
in sqlite_master.  I do not particularly want to parse SQL if I can possibly 
avoid it!

What I have done at the moment is added a fourth column named "readonly" to 
pragma database_list (it gets the value for this column from a function I added 
called sqlite3BtreeIsreadonly(), which in turn
is a wrapper around sqlite3PagerIsreadonly()), and a fourth column named "desc" 
to pragma index_info (it gets the value for this column from 
pIdx->aSortOrder[i]).

Now the above changes are fairly clean and simple (and surrounded by ifdefs) 
and 
only apply to information returned by the pragma statements.  However, I don't 
really want to get involved in porting
them each time I decide to switch to a new release of SQLite (the code that 
uses 
these is in a library that links in sqlite3.o/sqlite3.obj directly, so I don't 
have to worry about running against an unmodified
version of SQLite by accident).  On the other hand, I don't especially like any 
of the alternate approaches I have come up with.  And while I suppose I could 
ask for these changes to be made as enhancements to
SQLite, I assume from the lack of them at this time that they are not exactly 
common requirements.

Any suggestions?

Thanks!

Peter

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

Re: [sqlite] does a unique constraint imply an index

2011-10-20 Thread Sean Pieper 
Many thanks. 

I think my confusion was from the overloading of "automatic index" to mean both 
"implicit, static" and "generated at runtime"

-sean

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: Thursday, October 20, 2011 10:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] does a unique constraint imply an index

On Thu, Oct 20, 2011 at 09:02:55AM -0700, Sean Pieper scratched on the wall:
> I apologize for the newbie question, but the answer isn't obvious from
> looking through the site.
>
> In postgres, I know that if I declare a column or set of columns to
> have a unique constraint, there is also an index created on those
> columns. Does the same thing happen in sqlite, or does the
> optimization engine see the unique constraint as a hint to
> dynamically create an "automatic" index if existing indices
> seem insufficient?

  There are some SQLite-specific issues concerning "INTEGER PRIMARY KEY"
  columns (which, in PostgreSQL speak, will automatically map to the OID
  column), but the general answer is "yes", and automatic index is implied:

  http://sqlite.org/lang_createtable.html

  SQL Data Constraints

  [...]

  INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY
  constraints are implemented by creating an index in the database
  (in the same way as a "CREATE UNIQUE INDEX" statement would).
  Such an index is used like any other index in the database to
  optimize queries. As a result, there often no advantage (but
  significant overhead) in creating an index on a set of columns
  that are already collectively subject to a UNIQUE or PRIMARY KEY
  constraint. 



 -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] does a unique constraint imply an index

2011-10-20 Thread Jay A. Kreibich 
On Thu, Oct 20, 2011 at 09:02:55AM -0700, Sean Pieper scratched on the wall:
> I apologize for the newbie question, but the answer isn't obvious from
> looking through the site.
>
> In postgres, I know that if I declare a column or set of columns to
> have a unique constraint, there is also an index created on those
> columns. Does the same thing happen in sqlite, or does the
> optimization engine see the unique constraint as a hint to
> dynamically create an "automatic" index if existing indices
> seem insufficient?

  There are some SQLite-specific issues concerning "INTEGER PRIMARY KEY"
  columns (which, in PostgreSQL speak, will automatically map to the OID
  column), but the general answer is "yes", and automatic index is implied:

  http://sqlite.org/lang_createtable.html

  SQL Data Constraints

  [...]

  INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY
  constraints are implemented by creating an index in the database
  (in the same way as a "CREATE UNIQUE INDEX" statement would).
  Such an index is used like any other index in the database to
  optimize queries. As a result, there often no advantage (but
  significant overhead) in creating an index on a set of columns
  that are already collectively subject to a UNIQUE or PRIMARY KEY
  constraint. 



 -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] does a unique constraint imply an index

2011-10-20 Thread Sean Pieper 
I apologize for the newbie question, but the answer isn't obvious from looking 
through the site.

In postgres, I know that if I declare a column or set of columns to have a 
unique constraint, there is also an index created on those columns. Does the 
same thing happen in sqlite, or does the optimization engine see the unique 
constraint as a hint to dynamically create an "automatic" index if existing 
indices seem insufficient?

-sean

---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing SQLite in C++

2011-10-20 Thread Arbol One 
Excellent!

Thanks dude!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Thursday, October 20, 2011 11:06 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Testing SQLite in C++

Do not include sqlite3ext.h. Applications need only sqlite3.h.

Pavel


On Thu, Oct 20, 2011 at 11:02 AM, Arbol One  wrote:
> Hey kids, I am testing SQLite in the hope that I can use it in my program,
> but I get this confusing error msg, Can anybody help?
>
>
>
> Error message
>
> 
>
> D:\dev\sqlitetest\main.cpp:14:10: error: 'sqlite3_api' was not declared in
> this scope
>
>
>
> Code
>
> ~
>
> #include 
>
> #include "sqlite/sqlite3.h"
>
> #include "sqlite/sqlite3ext.h"
>
> #include 
>
> int main() {
>
>    sqlite3 *db;
>
>    Glib::ustring dbName("sqliteTest");
>
>    int rc;
>
>
>
>    rc = sqlite3_open(dbName.c_str(), ); // ç Error Message comes as
soon
> as I add this line
>
>
>
>    std::cout << "Hello world!" << std::endl;
>
>    return 0;
>
> }
>
>
>
> Thanks 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

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

Re: [sqlite] Testing SQLite in C++

2011-10-20 Thread Pavel Ivanov 
Do not include sqlite3ext.h. Applications need only sqlite3.h.

Pavel


On Thu, Oct 20, 2011 at 11:02 AM, Arbol One  wrote:
> Hey kids, I am testing SQLite in the hope that I can use it in my program,
> but I get this confusing error msg, Can anybody help?
>
>
>
> Error message
>
> 
>
> D:\dev\sqlitetest\main.cpp:14:10: error: 'sqlite3_api' was not declared in
> this scope
>
>
>
> Code
>
> ~
>
> #include 
>
> #include "sqlite/sqlite3.h"
>
> #include "sqlite/sqlite3ext.h"
>
> #include 
>
> int main() {
>
>    sqlite3 *db;
>
>    Glib::ustring dbName("sqliteTest");
>
>    int rc;
>
>
>
>    rc = sqlite3_open(dbName.c_str(), ); // ç Error Message comes as soon
> as I add this line
>
>
>
>    std::cout << "Hello world!" << std::endl;
>
>    return 0;
>
> }
>
>
>
> Thanks 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

[sqlite] Testing SQLite in C++

2011-10-20 Thread Arbol One 
Hey kids, I am testing SQLite in the hope that I can use it in my program,
but I get this confusing error msg, Can anybody help?

 

Error message



D:\dev\sqlitetest\main.cpp:14:10: error: 'sqlite3_api' was not declared in
this scope

 

Code

~

#include 

#include "sqlite/sqlite3.h"

#include "sqlite/sqlite3ext.h"

#include 

int main() {

sqlite3 *db;

Glib::ustring dbName("sqliteTest");

int rc;

 

rc = sqlite3_open(dbName.c_str(), ); // ç Error Message comes as soon
as I add this line

 

std::cout << "Hello world!" << std::endl;

return 0;

}

 

Thanks in advance!!

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

Re: [sqlite] string conatenated sql statements

2011-10-20 Thread Igor Tandetnik 
Gert Corthout  wrote:
> I can see only 1 very long-shot security issue. Assuming I am a malafide 
> programmer at our company I could add ESCAPE ']' to a
> vital query that takes user input and then use ]' to break out and inject 
> some SQL in the live system, right? 

A malicious developer with access to the codebase would likely have lots of 
ways to wreak havoc, with or without prepared statements.
-- 
Igor Tandetnik

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

Re: [sqlite] string conatenated sql statements

2011-10-20 Thread Simon Slavin 

On 20 Oct 2011, at 1:55pm, Gert Corthout wrote:

> I can see only 1 very long-shot security issue. Assuming I am a malafide 
> programmer at our company I could add ESCAPE ']' to a vital query that takes 
> user input and then use ]' to break out and inject some SQL in the live 
> system, right? 

That is not a problem in SQLite itself.  Your own software might be written in 
such a way as to handle an escape character in a strange way, but as far as 
SQLite is concerned it's just another character.

However, a rogue programmer at your company could build anything they want into 
your system, unless it requires the use of a paranoid API which checks 
everything for plausibility.  The only place I've worked in that did that was a 
bank.

The one thing to watch out for in SQLite is the semi-colon character if you 
ever use _exec() instead of _prepare().  Exec will happily execute any number 
of SQL commands separated by semi-colons.  In one of my programming libraries, 
the one routine I have that has to call _exec() scans its parameter for the 
semi-colon character by default.  It requires another parameter to be set to 
allow execution of anything with semi-colons in.  I set that this only if the 
command has to handle an arbitrary text parameter that might have a semi-colon 
in, which is rare since they don't occur in things like names, phone numbers, 
addresses or email addresses.

This is not a problem if you always use _prepare(), since that cannot be 
tricked by a semi-colon into processing a second command.

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

Re: [sqlite] string conatenated sql statements

2011-10-20 Thread Gert Corthout 

whoops, forget that, it's not possible as the sql injection would undo the 
ESCAPE clause
 

> From: gert_corth...@hotmail.com
> To: sqlite-users@sqlite.org
> Date: Thu, 20 Oct 2011 14:55:00 +0200
> Subject: Re: [sqlite] string conatenated sql statements
> 
> 
> 
> 
> 
> > To: sqlite-users@sqlite.org
> > From: itandet...@mvps.org
> > Date: Thu, 20 Oct 2011 07:55:26 -0400
> > Subject: Re: [sqlite] string conatenated sql statements
> > 
> > Gert Corthout  wrote:
> > > My argument so far is that parametrized queries are way faster if used 
> > > properly.
> > > The next obvious argument is sql injection. On all string input a simple 
> > > conversion is done: any ' is replaced by '', that's it.
> > > This seems to block off any sql injection right there as the escape 
> > > character \ doesn't work in sqlite. 
> > 
> > Yes, this should be sufficient to prevent the attack. %q specifier in 
> > sqlite3_mprintf performs the same manipulation, for the same reasons:
> > 
> > http://www.sqlite.org/c3ref/mprintf.html
> > 
> > > Alternatively can I make sql statements fail by including funky 
> > > characters or character combinations?
> > 
> > It would be difficult to get SQLite to crash outright. It would take any 
> > sequence of bytes and stuff it into the database as-is. That said, you 
> > might get strange results with strings that are not well-formed UTF-8 or 
> > UTF-16 sequences (depending on which API flavor you are using). However, 
> > this is equally true for strings bound as parameters as well as string 
> > literals embedded directly into the statement.
> > 
> > Performance is really the strongest argument. sqlite3_prepare is a fairly 
> > expensive operation, it's beneficial to run it once and reuse the statement 
> > many times with different parameters. Plus the time you save on not having 
> > to pre-process the strings, plus the peace of mind knowing that you haven't 
> > accidentally missed a spot where such pre-processing would be necessary.
> > -- 
> 
> Thank you for your response, I tought as much.
> I can see only 1 very long-shot security issue. Assuming I am a malafide 
> programmer at our company I could add ESCAPE ']' to a vital query that takes 
> user input and then use ]' to break out and inject some SQL in the live 
> system, right? 
> 
> kind regards,
> Gert 
> ___
> 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] string conatenated sql statements

2011-10-20 Thread Gert Corthout 


 

> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Thu, 20 Oct 2011 07:55:26 -0400
> Subject: Re: [sqlite] string conatenated sql statements
> 
> Gert Corthout  wrote:
> > My argument so far is that parametrized queries are way faster if used 
> > properly.
> > The next obvious argument is sql injection. On all string input a simple 
> > conversion is done: any ' is replaced by '', that's it.
> > This seems to block off any sql injection right there as the escape 
> > character \ doesn't work in sqlite. 
> 
> Yes, this should be sufficient to prevent the attack. %q specifier in 
> sqlite3_mprintf performs the same manipulation, for the same reasons:
> 
> http://www.sqlite.org/c3ref/mprintf.html
> 
> > Alternatively can I make sql statements fail by including funky characters 
> > or character combinations?
> 
> It would be difficult to get SQLite to crash outright. It would take any 
> sequence of bytes and stuff it into the database as-is. That said, you might 
> get strange results with strings that are not well-formed UTF-8 or UTF-16 
> sequences (depending on which API flavor you are using). However, this is 
> equally true for strings bound as parameters as well as string literals 
> embedded directly into the statement.
> 
> Performance is really the strongest argument. sqlite3_prepare is a fairly 
> expensive operation, it's beneficial to run it once and reuse the statement 
> many times with different parameters. Plus the time you save on not having to 
> pre-process the strings, plus the peace of mind knowing that you haven't 
> accidentally missed a spot where such pre-processing would be necessary.
> -- 

Thank you for your response, I tought as much.
I can see only 1 very long-shot security issue. Assuming I am a malafide 
programmer at our company I could add ESCAPE ']' to a vital query that takes 
user input and then use ]' to break out and inject some SQL in the live system, 
right? 
 
kind regards,
Gert  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] string conatenated sql statements

2011-10-20 Thread Black, Michael (IS) 
See if this satisifies your needs...a complete example showing parameterized 
statements fixing the problem...



http://codesnippets.joyent.com/posts/show/2384





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Gert Corthout [gert_corth...@hotmail.com]
Sent: Thursday, October 20, 2011 3:21 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] string conatenated sql statements


hello,

I am looking at the data layer of my company's software and noticed we build 
our queries with string concatenation (including user input). As a former 
Oracle and SqlServer developer this sends shivers down my spine. I am trying to 
convince management to get a budget for a switch to parametrized queries.

My argument so far is that parametrized queries are way faster if used properly.
The next obvious argument is sql injection. On all string input a simple 
conversion is done: any ' is replaced by '', that's it. This seems to block off 
any sql injection right there as the escape character \ doesn't work in sqlite.

So my question is, is there any way to perform a sqlinjection attack?
Alternatively can I make sql statements fail by including funky characters or 
character combinations?

thanks in advance,
Gert
___
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] Problems with nested Joins

2011-10-20 Thread Fabrizio Steiner 
Hello 

I'm currently evaluating SQLite for replacing a Microsoft Access (Jet) Database 
in one of our projects. We use a data layer abstraction which allows us to 
quickly implement other database. Therefore we allow the user of the data acces 
layer to make arbitrary nested joins. Currently I have a problem when using a 
nested join as a right hand side of another join (doesn't depent if it's a 
inner join or left outer join).

I use the following test database. 

CREATE TABLE TableA (
  IDinteger PRIMARY KEY NOT NULL,
  ForeignB  integer NOT NULL DEFAULT -1
);

CREATE TABLE TableB (
  IDinteger PRIMARY KEY NOT NULL,
  ForeignC  integer NOT NULL DEFAULT -1
);

CREATE TABLE TableC (
  IDinteger PRIMARY KEY NOT NULL,
  Value  integer NOT NULL DEFAULT -1
);

INSERT INTO TableA(ForeignB) Values(1);
INSERT INTO TableA(ForeignB) Values(-1);
INSERT INTO TableB(ForeignC) Values(1);
INSERT INTO TableC(Value) Values(333);

Basically TableA has an optional foreign key to TableB and TableB has a foreign 
key to TableC.

Now we would like to execute the following SQL.

SELECT *
FROM  
  TableA
  LEFT OUTER JOIN
  ( 
TableB 
INNER JOIN TableC 
ON TableB.ForeignC=TableC.ID
  ) 
  ON TableA.ForeignB=TableB.ID;

Executing the statement above yields to a 'SQL Error: no such column: 
TableB.ID'. Although accordingly to http://sqlite.org/lang_select.html this 
should be a valid statement. Executing the same statement on other databases 
e.g. MsAccess/SQL Server works as expected.

Same applies if one replaces the LEFT OUTER JOIN with an INNER JOIN.

SELECT *
FROM  
  TableA 
  INNER JOIN
  ( 
TableB 
INNER JOIN TableC 
ON TableB.ForeignC=TableC.ID
  ) 
  ON TableA.ForeignB=TableB.ID;

Executing the statement above yields to a 'SQL Error: no such column: 
TableB.ID'. I know one could say, the Join (TableA -> (Nested)) doesn't know 
anything about TableB. But let's try to change the order of the join.

SELECT *
FROM  
  ( 
 TableB 
 INNER JOIN TableC 
 ON TableB.ForeignC=TableC.ID
  ) 
  INNER JOIN 
  TableA 
  ON TableA.ForeignB=TableB.ID;

Executing this statement now works as expected, without any error. But here one 
could also say the join ( (Nested) -> TableA)) doesn't know anything about 
TableB but still it works. Also the documentation of SQLite states the 
optimizer could rearrange the joins, with this inner join example then it 
shouldn't make any difference if the nested join is used as left or right hand 
side of the join.

I know one could rewrite both inner join examples without any brackets, leading 
to the same results of the queries. But for the first example, the left outer 
join one, it's not possible.

Another really interesting query is the following.

SELECT *
FROM  
  TableA 
  INNER JOIN
  ( 
TableB
INNER JOIN TableC
ON TableB.ForeignC=TableC.ID
  ) As T
  ON TableA.ForeignB=T.ID;

This one works, but which column will be used as T.ID, because both TableB and 
TableC have a column ID. Also no ambiguous column error is raised.

Thanks for any help regarding this problem.
Kind Regards
Fabrizio  

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

Re: [sqlite] string conatenated sql statements

2011-10-20 Thread Richard Hipp 
On Thu, Oct 20, 2011 at 7:55 AM, Igor Tandetnik  wrote:

> Gert Corthout  wrote:
> > My argument so far is that parametrized queries are way faster if used
> properly.
> > The next obvious argument is sql injection. On all string input a simple
> conversion is done: any ' is replaced by '', that's it.
> > This seems to block off any sql injection right there as the escape
> character \ doesn't work in sqlite.
>
> Yes, this should be sufficient to prevent the attack. %q specifier in
> sqlite3_mprintf performs the same manipulation, for the same reasons:
>
> http://www.sqlite.org/c3ref/mprintf.html
>
> > Alternatively can I make sql statements fail by including funky
> characters or character combinations?
>
> It would be difficult to get SQLite to crash outright. It would take any
> sequence of bytes and stuff it into the database as-is. That said, you might
> get strange results with strings that are not well-formed UTF-8 or UTF-16
> sequences (depending on which API flavor you are using). However, this is
> equally true for strings bound as parameters as well as string literals
> embedded directly into the statement.
>
> Performance is really the strongest argument. sqlite3_prepare is a fairly
> expensive operation, it's beneficial to run it once and reuse the statement
> many times with different parameters. Plus the time you save on not having
> to pre-process the strings, plus the peace of mind knowing that you haven't
> accidentally missed a spot where such pre-processing would be necessary.
>


I concur.  Note also, though, that if your SQL statement contains really
large strings or blobs, it can be faster to use bound parameters even if the
statement is never reused.  With bound parameters, the strings and blobs are
copied as-is directly into the database, without having to be translated
into SQL literals and back again.


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



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

Re: [sqlite] string conatenated sql statements

2011-10-20 Thread Igor Tandetnik 
Gert Corthout  wrote:
> My argument so far is that parametrized queries are way faster if used 
> properly.
> The next obvious argument is sql injection. On all string input a simple 
> conversion is done: any ' is replaced by '', that's it.
> This seems to block off any sql injection right there as the escape character 
> \ doesn't work in sqlite. 

Yes, this should be sufficient to prevent the attack. %q specifier in 
sqlite3_mprintf performs the same manipulation, for the same reasons:

http://www.sqlite.org/c3ref/mprintf.html

> Alternatively can I make sql statements fail by including funky characters or 
> character combinations?

It would be difficult to get SQLite to crash outright. It would take any 
sequence of bytes and stuff it into the database as-is. That said, you might 
get strange results with strings that are not well-formed UTF-8 or UTF-16 
sequences (depending on which API flavor you are using). However, this is 
equally true for strings bound as parameters as well as string literals 
embedded directly into the statement.

Performance is really the strongest argument. sqlite3_prepare is a fairly 
expensive operation, it's beneficial to run it once and reuse the statement 
many times with different parameters. Plus the time you save on not having to 
pre-process the strings, plus the peace of mind knowing that you haven't 
accidentally missed a spot where such pre-processing would be necessary.
-- 
Igor Tandetnik

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

Re: [sqlite] IN condition on virtual tables misbehavior

2011-10-20 Thread Jay A. Kreibich 
On Thu, Oct 20, 2011 at 12:09:25PM +0200, Sam scratched on the wall:
> Hi,
>I'm developing a simple virtual table module and I've found possible
> misbehavior of virtual table modules when used with IN condition.

>select x from y where x in (3)
> 
>calls xFilter without any arguments, and vtab module is not able to apply
> it's filtering rules, what results in degraded performance.

>Is this a feature or bug ?

  I wouldn't consider it a bug, although it might be considered a
  limitation of the xFilter API.

  Given that IN is typically used with a list of arguments
  (e.g. "...where x in (3, 7, 16)..."), how would you propose xFilter
  be called?  You can't pass multiple arguments.  While this IN
  expression can be translated into "...where (x=3 or x=7 or x=16)...",
  again-- there isn't any easy way to pass that type of compound
  sub-condition to xFilter.
  
  In this case, SQLite might make three distinct passes of the v-table,
  asking for x=3, x=7, and x=16 in turn, and then reassembling the rows
  internally, but that would be even more costly if the v-table can't
  filter on that column.  The only trivial case is when there is only
  a single argument, and I'm not sure that's worth special-casing.

  Mostly it comes down to the optimizer's limited view of what types of
  "indexes" and conditions a v-table can efficiently process.  Without
  a very detailed understanding of the access efficiency, it is often
  better to just fall back to the safest plan, which is to scan the
  v-table once and deal with all the conditionals internally.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ANN: SQLite Data Sync released

2011-10-20 Thread SQL Maestro Group 

Hi!

SQL Maestro Group starts a new product family and announces the release of
SQLite Data Sync, a powerful and easy-to-use tool for SQLite database
contents comparison and synchronization.
http://www.sqlmaestro.com/products/sqlite/datasync/

Key product features:

1. Comparing and deploying changes to database contents.
2. Automatic creation of error-free synchronization scripts.
3. Easy-to-read difference display.
4. Saving all options to a project file for instant re-execution.
5. Custom comparison keys and flexible auto-mapping tools.
6. Full control over the comparison and synchronization.
7. Powerful command-line interface for continuous integration.

Full press-release is available at:
http://www.sqlmaestro.com/news/company/datasync_product_family_started/

Background information:
---
SQL Maestro Group offers complete database admin, development and management
tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere,
Firebird and MaxDB providing the highest performance, scalability and
reliability to meet the requirements of today's database applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com

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

[sqlite] IN condition on virtual tables misbehavior

2011-10-20 Thread Sam 
Hi,
   I'm developing a simple virtual table module and I've found possible
misbehavior of virtual table modules when used with IN condition.

   Example:
   select x from y where x = 3;

   calls (correctly) function xFilter of my vtab module with parameter 3,
but

   select x from y where x in (3)

   calls xFilter without any arguments, and vtab module is not able to apply
it's filtering rules, what results in degraded performance.
   Also in (every) EXPLAIN one can see, that vFilter op is executed _before_
actually processing IN condition:

0 Trace   0   0   0   00  NULL
1 Goto0   31  0   00  NULL
2 VOpen   0   0   0   vt  00  NULL
3 Intege  1   1   0   00  NULL
4 Intege  0   2   0   00  NULL

-- vFilter op
5 VFilte  0   29  1  00  NULL

-- IN processing
6 Noop0   0   0   00  begin I
7 If  4   15  0   00  NULL
8 Intege  1   4   0   00  NULL
9 Null0   3   0   00  NULL
10OpenEp  2   1   0   ke  00  NULL
11Null0   6   0   00  NULL
12Intege  3   5   0   00  NULL
13MakeRe  5   1   6   c   00  NULL
14IdxIns  2   6   0   00  NULL
15VColum  0   0   6   00  y.x
16IsNull  6   28  0   00  NULL
17Affini  6   1   0   c   00  NULL
18NotFou  2   28  6   1   00  end IN

19VColum  0   0   7   00  y.x
27Result  7   8   0   00  NULL
28VNext   0   6   0   00  NULL
29Close   0   0   0   00  NULL
30Halt0   0   0   00  NULL
31Transa  0   0   0   00  NULL
32Verify  0   17  0   00  NULL
33Goto0   2   0   00  NULL


   Is this a feature or bug ?

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

[sqlite] string conatenated sql statements

2011-10-20 Thread Gert Corthout 

hello,
 
I am looking at the data layer of my company's software and noticed we build 
our queries with string concatenation (including user input). As a former 
Oracle and SqlServer developer this sends shivers down my spine. I am trying to 
convince management to get a budget for a switch to parametrized queries.
 
My argument so far is that parametrized queries are way faster if used properly.
The next obvious argument is sql injection. On all string input a simple 
conversion is done: any ' is replaced by '', that's it. This seems to block off 
any sql injection right there as the escape character \ doesn't work in sqlite.
 
So my question is, is there any way to perform a sqlinjection attack?
Alternatively can I make sql statements fail by including funky characters or 
character combinations?
 
thanks in advance,
Gert  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite

2011-10-20 Thread Christoph P.U. Kukulies 

Hey kid,

give your post a meaningful subject and you'll probably get an answer 
quicker

This is the Sqlite list. Naming the subject "Sqlite" doesn't speak of
much maturity.

--
Christoph

Am 07.10.2011 17:10, schrieb Arbol One:

Hey kids.

Looking at the choices given at http://www.sqlite.org/download.html, I would
like to know what would be the best way to add SQLite to my app.

I am using Windows 7-64bit as the OS and Code::Blocks with MinGW/GNU C++.




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