Re: [sqlite] Is there a better way to get this information other than modifying SQLite?
-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
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
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
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?
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?
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
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?
-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?
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?
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
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
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
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++
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(), &db); // ç 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++
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(), &db); // ç 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++
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(), &db); // ç 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
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
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
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
> 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
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
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
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
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
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
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
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
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
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